Re: [PyWrapper-devel] SQL syntax

[<mar...@go...>]

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?
>