Strange Behaviour SQLEXEC

[OldSmelly]

Hello All,

When i run these lines of codes

CLOSE ALL
CLEAR ALL
SQLDISCONNECT(0)
gcOdbcConnect="cOdbc"
STORE SQLCONNECT(gcOdbcConnect) TO gnOdbcHandle
SQLEXEC(gnOdbcHandle,"Select * from kpnawgrp where ISNULL(opzegdatum) or EMPTY(opzegdatum) or opzegdatum > DATE()","vLedenTotaalStatistiek")
*
SELECT * FROM vLedenTotaalStatistiek WHERE GROEPSCODE = "TEST" INTO CURSOR AANTALNIETOPZEGGERS
STORE RECCOUNT("AANTALNIETOPZEGGERS") TO nRecords


If I Run this program 25 times I get 5 different results
I think it's because the first cursor is created with a sqlexec (odbc) command... I know that this is just a test because I can put the "GROEPSCODE = "TEST"" in the first
select statement but It still is a verry strange thing because when I replace the sqlexec with native vfp it works like a dream

So to cut it short : When you do a Select statement on a cursor wich is created with sqlexec you get varying results.

BTW : I runs on a standalone database so no other users And it's in Foxpro 7.0

 

[Mike Lewis]

OldSmelly,

What back end database are you using? Remember, the functions that you put in the SELECT will be interpreted according to the version of SQL supported by the back end. For example, in SQL Server, the ISNULL() function works differently from VFP's ISNULL(). Also, it uses GETDATE() rather than DATE().

I know that doesn't completely answer your question, but it is something to keep in mind.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[OldSmelly]

Does anyone has any clue what is happening ?

Thanks

 

[OldSmelly]

Thanks Mike...

It's on a native VFP database

And The result I get from The SQLEXEC cursor is always the same BUT the result I get from the second select statement is different...

 

[OldSmelly]

I think I have the solution found.....

In the ODBC manager You can set "fetch data in backround"
I turned it of An know it seems to work

 

[Mike Lewis]

OldSmelly,

Yes, that sounds plausible. With that setting enabled, only part of the vLedenTotaalStatistiek cursor will available when you start running the second SELECT. When you switch the setting off, your program will suspend until the entire cursor is available.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com