SequeLink parse error - bug ???

Good morning experts,

I got stuck with following problem

Trying to access FileMaker Pro 8 data (Win XP) in order to synch VisualCE 9 PDA application.
The FMPro Adv DataDirect SequeLink 5.4 ODBC driver is installed and DSN defined - test connect with FMPro Application sucessfull.
Setting up for Table export and synchronisation I receive following error

[DataDirect][ODBC SequeLink driver][ODBC Socket][Datadirect][ODBC Filemaker driver][Filemaker] Parse Error in SQL.

The ODBC log shows that this error is raised with following SQL statement

select * from "Clients" where (OID <> 0) and (OID is not NULL)

OID being an empty number field in the 'clients' table  that is used for synchronising
Tried to populate all OID fields with 0 (zero) but got the same result
Went through the FileMaker odbc documentation but could not check whether NULL is acceptable for number fields.

As far I know this query is legal but not recognised by the driver ...

Aside of exporting to text or other I cannot find a way to solve this.

any suggestions , replacement drivers ...

Thanks by avance
homeredunordAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lesouefCommented:
the query effectiveley looks ok, but the "null" concept in fmaker is somehow vague.
I would use >0 since if the field is empty, it won't take as > 0 anyway.
0
homeredunordAuthor Commented:
Hi Mr Lesouef,
Thanks for Your answer and suggestion though IOD could receive at a
later stage a negative integer value and should therefore be retrieved
even though its value is not >0.
but
Since FileMaker Pro does not distinguish numerical and boolean field types
but would You know if  FileMaker Pro + SequeLink driver react adequately to boolean queries:
 because
(IOD <> 0) AND (IOD is Not NULL)  is then logically equivalent to IOD is TRUE .

If this is accepted by FMPro+ driver

how should the SQL statement be written then
   
SELECT * from 'clients' WHERE IOD = TRUE  
or
SELECT * from 'clients' WHERE IOD is TRUE

Thanks by advance,
homeredunord  
0
lesouefCommented:
internally, fm is able to identify a null field with the simple logic test "IS", and the answer is 0/1 or true/false depending on text/numeric field type used to store the answer (calculation type).
how is that translated by the odbc driver, no idea since I am always using fm a ODBC client.
so, I would guess 'is true' is correct.
but the area is hairy! I also use servoy with sybase anywhere and I have the same problems, for instance:
a field in a new blank record would answer correctly to a null test. if I put a value in it, then delete it, it won't be null anymore, but empty!
so I found out it is safer to test for empty fields where querying a foreign database:
in yr case that would be:
SELECT * from clients WHERE IOD <> ''

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
homeredunordAuthor Commented:
Thanks for the explanation: I think this wraps it up.
I will conduct a series of tests and act upon the results.
By the way I just started to use Servoy and am very favorably impressed by the posibilities this development environment offers.
Thanks a lot for Your help, in absence of additional suggestions I will close this question in a few days.
homeredunord
0
lesouefCommented:
servoy is very nice on user interface control.
I regret the lack of numerous plug-ins so far, and I am still 3 times slower to design things compared to fm.
but its remote usage through public lines is "wunderbar" compared to a poor web companion ergonomy.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.