Thread: Re: [PyWrapper-devel] SQL syntax
Status: Alpha
Brought to you by:
jatorre
Menu
▾
▴
pywrapper-devel
|
From: <wi...@go...> - 2006-11-15 17:02:50
|
Milko, for programming it would be easier to have isNotNull. But we dont 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 not nullelements 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? > |
|
From: Skofic A. M. (IPGRI) <m.s...@cg...> - 2006-11-15 18:24:08
|
OK: MySQL version 5.0.15 SELECT COUNT(*) FROM ACC WHERE NOT LATITUDE IS NULL AND NOT LONGITUDE =20= 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 IS =20= NULL ) returns 66650 records SELECT COUNT(*) FROM ACC WHERE LATITUDE IS NOT NULL AND LONGITUDE IS =20 NOT NULL returns 63603 records (correct) SELECT COUNT(*) FROM ACC WHERE( (LATITUDE IS NOT NULL) AND (LONGITUDE =20= IS NOT NULL) ) returns 63603 records (correct) Parenthesis are not needed because I guess "LATITUDE IS NOT NULL" is =20 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? > |
|
From: <wi...@go...> - 2006-11-15 19:18:58
|
ah, ok. so its not about the is null syntax but that its not being =20 considered for the FROM clause. thats a completely different thing. shouldnt be hard... 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 LONGITUDE =20= 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 IS =20= NULL ) returns 66650 records SELECT COUNT(*) FROM ACC WHERE LATITUDE IS NOT NULL AND LONGITUDE IS =20 NOT NULL returns 63603 records (correct) SELECT COUNT(*) FROM ACC WHERE( (LATITUDE IS NOT NULL) AND (LONGITUDE =20= IS NOT NULL) ) returns 63603 records (correct) Parenthesis are not needed because I guess "LATITUDE IS NOT NULL" is =20 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? > |
|
From: <wi...@go...> - 2006-11-15 19:31:54
|
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 LONGITUDE =20= 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 IS =20= NULL ) returns 66650 records SELECT COUNT(*) FROM ACC WHERE LATITUDE IS NOT NULL AND LONGITUDE IS =20 NOT NULL returns 63603 records (correct) SELECT COUNT(*) FROM ACC WHERE( (LATITUDE IS NOT NULL) AND (LONGITUDE =20= IS NOT NULL) ) returns 63603 records (correct) Parenthesis are not needed because I guess "LATITUDE IS NOT NULL" is =20 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? > |
|
From: Skofic A. M. (IPGRI) <m.s...@cg...> - 2006-11-15 22:02:29
|
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 |
|
From: <mar...@go...> - 2006-11-15 21:38:55
|
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/pywrapper?=20 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/2003/1.0/=20 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 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/abcd2.06/=20 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 LONGITUDE =20= 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 IS =20= NULL ) returns 66650 records SELECT COUNT(*) FROM ACC WHERE LATITUDE IS NOT NULL AND LONGITUDE IS =20 NOT NULL returns 63603 records (correct) SELECT COUNT(*) FROM ACC WHERE( (LATITUDE IS NOT NULL) AND (LONGITUDE =20= IS NOT NULL) ) returns 63603 records (correct) Parenthesis are not needed because I guess "LATITUDE IS NOT NULL" is =20 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? > |
|
From: Skofic A. M. (IPGRI) <m.s...@cg...> - 2006-11-15 22:17:56
|
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/pywrapper?=20 > 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/2003/1.0/=20 > 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 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 |
|
From: <wi...@go...> - 2006-11-15 22:56:52
|
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/pywrapper?=20 > 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/2003/1.0/=20 > 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 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 |
|
From: Skofic A. M. (IPGRI) <m.s...@cg...> - 2006-11-15 23:14:51
|
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 |
|
From: <wi...@go...> - 2006-11-16 00:26:12
|
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 |
|
From: Skofic A. M. (IPGRI) <m.s...@cg...> - 2006-11-16 08:18:20
Attachments:
EURISCO_TEST.dot
EURISCO_TEST.png
|
I tried to install it on the server but TAPIR gave me errors when loading the structure, fortunately it left a dot file before crashing. Here is the structure of the database, I send you both the dot file and a .png |
|
From: <m.d...@BG...> - 2006-11-16 09:21:28
|
Wow, how symmetrical. Cant see anything wrong here either. m > -----Original Message----- > From: pyw...@li...=20 > [mailto:pyw...@li...] On=20 > Behalf Of Skofic A. Milko (IPGRI) > Sent: Donnerstag, 16. November 2006 09:18 > To: Markus D=F6ring > Cc: pyw...@li...; Javier privat > Subject: Re: [PyWrapper-devel] SQL syntax >=20 >=20 > I tried to install it on the server but TAPIR gave me errors when =20 > loading the structure, fortunately it left a dot file before =20 > crashing. Here is the structure of the database, I send you both the =20 > dot file and a .png >=20 >=20 |
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X