JDBC and SQL

[Rich81]

I have a database called: "test.fm"

I have two text fields: "name1" and "city"

I made one record:

name1 = rich

city = winnipeg

The following queries give me the following results (just prints the data):

select * from "test.fm" where name1 = 'rich'

select * from "test.fm" where name1 = 'rich' or name1 = 'blank'

select * from "test.fm" where name1 = 'rich' and city = 'winnipeg'

RESULTS:

name1: rich

city: winnipeg

Here are the queries that don't work. They return no results at all.

select * from "test.fm" where name1 = 'rich' or name1 = 'blank' and

city = 'winnipeg'

select * from "test.fm" where (name1 = 'rich' or name1 = 'blank') and

city = 'winnipeg'

This should definitely work. Any ideas, I really need help on this one.

[omiossec]

Blank is a string value like 'camel' or 'shoes' and it doesn't mean a blank record

select * from "test.fm" where name1 = 'rich' or name1 = 'blank'

Work because name1 = 'rich' work

You should try to use null or is null

[Rich81]

Sorry, I really shouldn't have used that value for name1.

This query example won't work:

select * from "test.fm" where (name1 = 'rich' or name1 = 'bob') and

city = 'winnipeg'

[omiossec]

Filemaker JDBC implementation is based on web companion. That mean that jdbc got the limitation as cdml

And in cdml you can not mixte logical operator and/or in a querry

[ERlc_the_red]

so sad...why didn't they engineer a good sql engine?