Re: [PyWrapper-devel] SQL syntax

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

I tried this query with the three data sources in my TAPIR http://=20
tapir.grinfo.net:8080/pywrapper

<?xml version=3D'1.0' encoding=3D'UTF-8'?>
<request>
	<header>
		<source =
accesspoint=3D"http://tapir.grinfo.net:8080/pywrapper/=20
pywrapper?dsa=3Dtraining" sendtime=3D"2006-11-15T19:26:50.57"/>
	</header>
	<search count=3D'true' start=3D'0' limit=3D'50'>
		<externalOutputModel =
location=3D"http://rs.tdwg.org/tapir/cs/abcd2.06/=20
model/abcd206.xml"/>
		<filter>
			<and>
				<like>
					<concept =
id=3D'http://www.tdwg.org/schemas/abcd/2.06/DataSets/=20
DataSet/Units/Unit/Identifications/Identification/Result/=20
TaxonIdentified/ScientificName/FullScientificNameString' />
					<literal value=3D'B*' />
				</like>
				<not>
					<isNull>
						<concept =
id=3D'http://www.tdwg.org/schemas/abcd/2.06/DataSets/=20
DataSet/Units/Unit/Gathering/SiteCoordinateSets/SiteCoordinates/=20
CoordinatesLatLong/LatitudeDecimal' />
					</isNull>
				</not>
				<not>
					<isNull>
						<concept =
id=3D'http://www.tdwg.org/schemas/abcd/2.06/DataSets/=20
DataSet/Units/Unit/Gathering/SiteCoordinateSets/SiteCoordinates/=20
CoordinatesLatLong/LongitudeeDecimal' />
					</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>

The query works with training and EURISCO, but it doesn't work with =20
EURISCO_TEST. This is because in EURISCO, and I suspect in training, =20
the latitude and longitude are in the root table, in EURISCO_TEST =20
they are in a separate table.

On Nov 15, 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 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 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