Re: [PyWrapper-devel] SQL syntax

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

milko,
can you install & configure graphviz to understand the db structure?
too many aliases...
i got the feeling there might be the reason. in EURISCO its working.
m

On 16.11.2006, at 00:14, Skofic A. Milko (IPGRI) wrote:

If you try it on dsa=3DEURISCO_TEST it will not work

<?xml version=3D'1.0' encoding=3D'UTF-8'?>
<request>
	<header>
		<source =
accesspoint=3D"http://tapir.grinfo.net:8080/pywrapper/=20
pywrapper?dsa=3DEURISCO_TEST" sendtime=3D"2006-11-13T12:54:29.43"/>
	</header>
	<search count=3D'true' start=3D'0' limit=3D'50'>
		<externalOutputModel =
location=3D"http://eurisco.ecpgr.org/models/=20
output/MCPD_EURISCO_1.00.xml"/>
		<filter>
			<and>
				<like>
					<concept =
id=3D'GENUS@mcpd_eurisco_100' />
					<literal value=3D'B*' />
				</like>
				<not>
					<isNull>
						<concept =
id=3D'LATITUDED@mcpd_eurisco_100' />
					</isNull>
				</not>
				<not>
					<isNull>
						<concept =
id=3D'LONGITUDED@mcpd_eurisco_100' />
					</isNull>
				</not>
			</and>
		</filter>
		<orderBy>
			<concept id=3D'INSTCODE@mcpd_eurisco_100' />
			<concept id=3D'GENUS@mcpd_eurisco_100' />
			<concept id=3D'ACCENUMB@mcpd_eurisco_100' />
		</orderBy>
	</search>
</request>

It returns:

<diagnostic code=3D"DBS_SQL_EXEC" time=3D"2006-11-16T00:14:13.41" =20
level=3D"error">
The following SQL statement produced a DB error: SELECT COUNT=20
(DISTINCT ACC.AccessionID) FROM acc AS ACC JOIN acc_tax AS TAXA ON =20
ACC.TaxonomyID =3D TAXA.TaxonomyID WHERE (TAXA.GENUS LIKE %s AND NOT =20
COORDINATES.LATITUDE is NULL AND NOT COORDINATES.LONGITUDE is NULL)
</diagnostic>

On Nov 15, 2006, at 23:56 , Markus D=F6ring wrote:

> well, the request you posted works too.
> so where's the problem?
> ___
> <?xml version=3D'1.0' encoding=3D'UTF-8'?>
> <request>
> 	<header>
> 		<source =
accesspoint=3D"http://tapir.grinfo.net:8080/pywrapper/=20
> pywrapper?dsa=3DEURISCO" sendtime=3D"2006-11-13T12:54:29.43"/>
> 	</header>
> 	<search count=3D'true' start=3D'0' limit=3D'50'>
> 		<externalOutputModel =
location=3D"http://eurisco.ecpgr.org/models/=20
> output/MCPD_EURISCO_1.00.xml"/>
> 		<filter>
> 			<and>
> 				<like>
> 					<concept =
id=3D'GENUS@mcpd_eurisco_100' />
> 					<literal value=3D'B*' />
> 				</like>
> 				<not>
> 					<isNull>
> 						<concept =
id=3D'LATITUDED@mcpd_eurisco_100' />
> 					</isNull>
> 				</not>
> 				<not>
> 					<isNull>
> 						<concept =
id=3D'LONGITUDED@mcpd_eurisco_100' />
> 					</isNull>
> 				</not>
> 			</and>
> 		</filter>
> 		<orderBy>
> 			<concept id=3D'INSTCODE@mcpd_eurisco_100' />
> 			<concept id=3D'GENUS@mcpd_eurisco_100' />
> 			<concept id=3D'ACCENUMB@mcpd_eurisco_100' />
> 		</orderBy>
> 	</search>
> </request>
> ---
>
> markus
>
>
>
>
>
>
> On 15.11.2006, at 23:17, Skofic A. Milko (IPGRI) wrote:
>
> I tried it in the training database and it works.
>
> You can try queries directly on my site, the address is
> http://tapir.grinfo.net:8080/pywrapper
> the passwords are all the default one.
>
> On Nov 15, 2006, at 22:38 , Markus D=F6ring wrote:
>
>> Milko,
>> can you try this request with the training database?
>> just paste it into the queryform and submit:
>> -----
>> <?xml version=3D'1.0' encoding=3D'UTF-8'?>
>> <request>
>> 	<header>
>> 	    <source accesspoint=3D"http://localhost:8080/pywrapper/=20
>> pywrapper?dsa=3Dtraining" sendtime=3D"2006-11-15T22:29:30.26"/>
>> 	</header>
>> 	<search count=3D'true' start=3D'0' limit=3D'5'>
>> 		<externalOutputModel =
location=3D'http://rs.tdwg.org/tapir/cs/dwc2/=20
>> model/dwc2.xml'/>
>> 		<filter>
>> 			<not>
>> 			<isNull>
>> 				<concept =
id=3D'http://digir.net/schema/conceptual/darwin/=20
>> 2003/1.0/ScientificName' />
>> 			</isNull>
>> 			</not>
>> 		</filter>
>> 	</search>
>> </request>
>> -----
>>
>> Does this work?
>> If so, does the same request work with your datasource?
>> well, it needs darwincore mappings, so if you got abcd mapped, try =20=

>> this:
>>
>> -----
>> <?xml version=3D'1.0' encoding=3D'UTF-8'?>
>> <request>
>> 	<header>
>> 		<source =
accesspoint=3D"http://localhost:8080/pywrapper/pywrapper?=20
>> dsa=3Dtraining" sendtime=3D"2006-11-15T22:29:30.26"/>
>> 	</header>
>> 	<search count=3D'false' start=3D'0' limit=3D'5'>
>> 		<externalOutputModel =
location=3D"http://rs.tdwg.org/tapir/cs/=20
>> abcd2.06/model/abcd206.xml"/>
>> 		<filter>
>> 			<and>
>> 			<not>
>> 			<isNull>
>> 				<concept =
id=3D'http://www.tdwg.org/schemas/abcd/2.06/DataSets/=20
>> DataSet/Units/Unit/Identifications/Identification/Result/=20
>> TaxonIdentified/ScientificName/FullScientificNameString' />
>> 			</isNull>
>> 			</not>
>> 			<not>
>> 			<isNull>
>> 				<concept =
id=3D'http://www.tdwg.org/schemas/abcd/2.06/DataSets/=20
>> DataSet/Units/Unit/UnitID' />
>> 			</isNull>
>> 			</not>
>> 			</and>
>> 		</filter>
>> 		<orderBy>
>> 			<concept =
id=3D'http://www.tdwg.org/schemas/abcd/2.06/DataSets/=20
>> DataSet/Units/Unit/Identifications/Identification/Result/=20
>> TaxonIdentified/ScientificName/FullScientificNameString' />
>> 		</orderBy>
>> 	</search>
>> </request>
>> -----
>>
>> for me this all works.
>> cant find any problem anywhere.
>>
>> markus
>>
>>
>>
>>
>> On 15.11.2006, at 20:31, Markus D=F6ring wrote:
>>
>> hmm. the training db works with NOT isNull.
>> can you try that too please?
>> I cant see anything wrong...
>> m
>>
>>
>> On 15.11.2006, at 19:23, Skofic A. Milko (IPGRI) wrote:
>>
>> OK:
>>
>> MySQL version 5.0.15
>>
>> SELECT COUNT(*) FROM ACC WHERE NOT LATITUDE IS NULL AND NOT =20
>> LONGITUDE IS NULL
>> returns 63603 records (correct)
>>
>> SELECT COUNT(*) FROM ACC WHERE NOT( LATITUDE IS NULL ) AND NOT=20
>> ( LONGITUDE IS NULL )
>> returns 63603 records (correct)
>>
>> SELECT COUNT(*) FROM ACC WHERE NOT( LATITUDE IS NULL AND LONGITUDE =20=

>> IS NULL )
>> returns 66650 records
>>
>> SELECT COUNT(*) FROM ACC WHERE LATITUDE IS NOT NULL AND LONGITUDE =20
>> IS NOT NULL
>> returns 63603 records (correct)
>>
>> SELECT COUNT(*) FROM ACC WHERE( (LATITUDE IS NOT NULL) AND =20
>> (LONGITUDE IS NOT NULL) )
>> returns 63603 records (correct)
>>
>> Parenthesis are not needed because I guess "LATITUDE IS NOT NULL" =20
>> is a unary expression,
>>
>> I am sending the following query:
>>
>> <?xml version=3D'1.0' encoding=3D'UTF-8'?>
>> <request>
>> 	<header>
>> 		<source =
accesspoint=3D"http://tapir.grinfo.net:8080/pywrapper/=20
>> pywrapper?dsa=3DEURISCO" sendtime=3D"2006-11-13T12:54:29.43"/>
>> 	</header>
>> 	<search count=3D'true' start=3D'0' limit=3D'50'>
>> 		<externalOutputModel =
location=3D"http://eurisco.ecpgr.org/models/=20
>> output/MCPD_EURISCO_1.00.xml"/>
>> 		<filter>
>> 			<and>
>> 				<like>
>> 					<concept =
id=3D'GENUS@mcpd_eurisco_100' />
>> 					<literal value=3D'B*' />
>> 				</like>
>> 				<not>
>> 					<isNull>
>> 						<concept =
id=3D'LATITUDED@mcpd_eurisco_100' />
>> 					</isNull>
>> 				</not>
>> 				<not>
>> 					<isNull>
>> 						<concept =
id=3D'LONGITUDED@mcpd_eurisco_100' />
>> 					</isNull>
>> 				</not>
>> 			</and>
>> 		</filter>
>> 		<orderBy>
>> 			<concept id=3D'INSTCODE@mcpd_eurisco_100' />
>> 			<concept id=3D'GENUS@mcpd_eurisco_100' />
>> 			<concept id=3D'ACCENUMB@mcpd_eurisco_100' />
>> 		</orderBy>
>> 	</search>
>> </request>
>>
>> The returned result has an SQL error:
>>
>> <diagnostic time=3D"2006-11-15T19:06:11.47" level=3D"info">
>> Executing SQL: 'SELECT COUNT(DISTINCT ACC.AccessionID) FROM acc AS =20=

>> ACC JOIN acc_tax AS TAXA ON ACC.TaxonomyID =3D TAXA.TaxonomyID WHERE =20=

>> (TAXA.GENUS LIKE %s AND NOT COORDINATES.LATITUDE is NULL AND NOT =20
>> COORDINATES.LONGITUDE is NULL)   ' with parameters: 'B%'
>> </diagnostic>
>>
>> The error is that the parser does not consider the concepts =20
>> referenced in the isNull clause, not placing them in the FROM SQL =20
>> clause.
>>
>> Bye!
>>
>> milko
>>
>> On Nov 15, 2006, at 18:02 , Markus D=F6ring wrote:
>>
>>> Milko,
>>> for programming it would be easier to have isNotNull. But we dont =20=

>>> wanna change TAPIR and its really more consistent to NOT have it.
>>>
>>> So we gotta solve that. its a dbmod problem.
>>> ive just tried with mysql5.0 and something like this works fine:
>>>
>>> SELECT * FROM taxon WHERE NOT synonyms IS NULL
>>>
>>> what version are you using?
>>> can you try to do a simple statement like the above?
>>> if it doesnt work, how about enclosing is null in brackets like =20
>>> this:
>>>
>>> SELECT * FROM taxon WHERE NOT (synonyms IS NULL)
>>>
>>> please let me know!
>>> markus
>>>
>>> On 11/14/06, Skofic A. Milko (IPGRI) <m.s...@cg...> wrote:
>>> I was looking at how TAPIR resolves the SQL syntax for selecting =20
>>> not null elements it does:
>>> WHERE( NOT table.field IS NULL )
>>> the problem is that it is not the correct syntax, the correct one =20=

>>> is:
>>> WHERE( table.field IS NOT NULL )
>>>
>>> Wouldn't it be easier to add "isNotNull" to the unary operators?
>>>
>>
>>
>>
>
>
> Milko A. Skofic (IPGRI)
> International Plant Genetic Resources Institute
> Via dei Tre Denari, 472/a
> 00057 Maccarese (RM)
> ITALY
> E-mail: m.s...@cg...
> Tel: +30 06 6118286
>
>
>

Milko A. Skofic (IPGRI)
International Plant Genetic Resources Institute
Via dei Tre Denari, 472/a
00057 Maccarese (RM)
ITALY
E-mail: m.s...@cg...
Tel: +30 06 6118286