Pulling from SQL Database

[SteveMacPSU]

Hi all,

I'm trying to pull some data from a SQL db that we have to run a report of active clients. This is what I'm using:

LOCAL lnHandle
lnHandle = SQLConnect("xxx", "XX", "XXXXXX")
IF lnHandle > -1

#1. SQLExec(lnHandle,"SELECT * FROM ENC","Results")

#2. SQLExec(lnHandle,"SELECT * FROM ENC;
where ISNULL(enddat)","Results")

SQLDisconnect(lnHandle)
ENDIF

#1 works fine, but all I want is the records that do not have an enddat. What am I doing wrong?

Thanks in advance,
Steve

 

[JimWWinter]

ISNULL() is a function to replace null values with a default value. The syntax you want for your query is this.

SQLEXEC(lnHandle,"SELECT * FROM ENC WHERE EndDat IS NULL","Results")

Regards,
Jim

 

[SteveMacPSU]

That was it! Thanks Jim. One more quick question, what is the proper syntax in that example to place the results in a cursor?

 

[Mike Lewis]

Hi Steve,

I'm sure Jim can answer this, but I'll jump in anyway.

You are already storing your resuls in a cursor. The cursor's name is the third parameter to SQLEXEC() -- "results" in this example.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[SteveMacPSU]

Okay, thanks Mike.

 

[bborissov]

Steve,
It is always a good idea to check WHY SQLEXEC() returns -1. Maybe the connections is brocken? maybe your syntax is wrong, maybe ... So just check it:

LOCAL lnHandle
lnHandle = SQLConnect("xxx", "XX", "XXXXXX")
IF lnHandle > -1
   ....  
ELSE
   AERROR(laError)
   MessageBox(laError[1,2])
ENDIF

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SteveMacPSU]

Will do, all the help is greatly appreciated.

 

[baltman]

SQL Server's ISNULL() function is equivalent to VFP's NVL() whereas VFP's ISNULL() returns a TRUE or FALSE. It is a bit confusing.

When passing SQL commands to SQL Server you need to use SQL Server syntax.

Brian

 

[Mike Lewis]

Brian,

SQL Server's ISNULL() function is equivalent to VFP's NVL()

You're absolutely right. Well spotted.

However, Steve didn't say anything about using SQL Server. It would be safer to use the IS NULL construct, which is standard across all versions of SQL:

 .... WHERE EndDate IS NULL

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[baltman]

Hi Mike,

I agree. Just trying to round out the explanation. I interpreted SQL DB to mean SQL Server and was trying to help connect all the dots.

Brian