VFP9: SQL Server ODBC Connections

[Gene Wirchenko]

Dear Vixens and Reynards:

I am just starting to connect to SQL Server 2008 Express through
VFP, and I have some questions. URLs are welcome. There is a lot of
Web to sift through, and not everyone contributing is correct.

1) What exactly is a connection-level error? I have been able to
get this error with sqlexec() by connecting without specifying a
database and then trying to select, but what else does this mean?

2) What exactly is an environment-level error?

3) Suppose that I have more than one result set. (Say that I do two
queries at once.) Are the result set names guaranteed to come back in
the array in same order as the queries?

4) sqlexec() returns 0 if the command is still executing. When does
this happen, and what do I do about it when it does?

5) Because I am experimenting, I often have program crashes and have
connections left hanging. How can I close all of them in one command?
(I can loop with sqldisconnect(), but this is kludgy.)

6) Do you have any tips that seem indicated at this point?

Sincerely,

Gene Wirchenko

[Gene Wirchenko]

Post by Gene Wirchenko
I am just starting to connect to SQL Server 2008 Express through
VFP, and I have some questions. URLs are welcome. There is a lot of
Web to sift through, and not everyone contributing is correct.
1) What exactly is a connection-level error? I have been able to
get this error with sqlexec() by connecting without specifying a
database and then trying to select, but what else does this mean?
2) What exactly is an environment-level error?
3) Suppose that I have more than one result set. (Say that I do two
queries at once.) Are the result set names guaranteed to come back in
the array in same order as the queries?
4) sqlexec() returns 0 if the command is still executing. When does
this happen, and what do I do about it when it does?
5) Because I am experimenting, I often have program crashes and have
connections left hanging. How can I close all of them in one command?
(I can loop with sqldisconnect(), but this is kludgy.)
6) Do you have any tips that seem indicated at this point?

Two more:

7) How does one trap errors? Sometimes, SQL Server pops up a dialog
box. I would prefer to handle the problem in my program.

8) How does one get all of the return codes? For example, if I try
to create a database that already exists, how would I be able to tell
that that was the error?

Sincerely,

Gene Wirchenko

[ViLco]

Post by Gene Wirchenko

Post by Gene Wirchenko
1) What exactly is a connection-level error? I have been able to
get this error with sqlexec() by connecting without specifying a
database and then trying to select, but what else does this mean?
2) What exactly is an environment-level error?
3) Suppose that I have more than one result set. (Say that I do two
queries at once.) Are the result set names guaranteed to come back
in the array in same order as the queries?
4) sqlexec() returns 0 if the command is still executing. When does
this happen, and what do I do about it when it does?

Never had to work around that because in mexperience sqlexec() is always
working sinchronously, so I never got back a 0 value: always 1 or -1.

Post by Gene Wirchenko

Post by Gene Wirchenko
5) Because I am experimenting, I often have program crashes and have
connections left hanging. How can I close all of them in one
command? (I can loop with sqldisconnect(), but this is kludgy.)

Living kludgy here, too.

Post by Gene Wirchenko

Post by Gene Wirchenko
6) Do you have any tips that seem indicated at this point?

7) How does one trap errors? Sometimes, SQL Server pops up a dialog
box. I would prefer to handle the problem in my program.

I'd search around DBSETPROP and SQLserver books online to see if you can set
some property like Silent or similar.

Post by Gene Wirchenko
8) How does one get all of the return codes? For example, if I try
to create a database that already exists, how would I be able to tell
that that was the error?

just after your failed sqlexec command, issue aerror(myArray) and then
you'll get the VFP error number (always the same for ODBC), 2 descriptive
lines (elements 2 and 3 of the array) and an error code which I believe is
the error from the SQL backend. There's much info in that array.

[Kevin Clark]

Here's some sample code for how I trap errors using a Postgres
backend:

DIMENSION aCountInfo(2)
tmpline="SELECT * FROM enrollmentstudent WHERE studentnum=&dsn_Number
AND gradelevel=&dsn_gradelevel and approvrept<>'' "
IF SQLEXEC(g_sqlhandle,tmpline,"sh_enrollmentstudent",aCountInfo)>0
then
&& if we get to here, then the SQL statement worked
IF aCountInfo(2)>0 then
&& if we get to here, then we selected at least one record
sh_return=True
ENDIF
USE IN sh_enrollmentstudent
ELSE
&& SQL statement failed, so return error
=AERROR(y3)
MESSAGEBOX("Error in StudentHasApprovalRept: "+y3(2))
ENDIF

[Kevin Clark]

Here's a tip that might help you.

Sometimes when I am running a SELECT, I'll get a sequence error or
some other system error, unrelated to the SQL code itself. When that
happens, I use SQLPREPARE first and then use SQLEXEC. That always
seems to work.

Regards,
Kevin Clark
Seton Home Study School

[Kevin Clark]

Post by Gene Wirchenko
5) Because I am experimenting, I often have program crashes and have

connections left hanging. How can I close all of them in one command?
(I can loop with sqldisconnect(), but this is kludgy.) <<

Maybe SQL server is different, but with Postgres, I have never needed
more than one connection. I make a connection and store the handle
into a global variable. If the global variable is not zero, then I
know that the connection has been made so I don't need to connect
again.

[Gene Wirchenko]

On Mon, 4 Apr 2011 08:00:13 -0700 (PDT), Kevin Clark

Post by Gene Wirchenko

Post by Gene Wirchenko
5) Because I am experimenting, I often have program crashes and have

connections left hanging. How can I close all of them in one command?
(I can loop with sqldisconnect(), but this is kludgy.) <<
Maybe SQL server is different, but with Postgres, I have never needed
more than one connection. I make a connection and store the handle
into a global variable. If the global variable is not zero, then I
know that the connection has been made so I don't need to connect
again.

It is not a matter of needing but a matter of having. If a test
program crashes, its connection is still open. If I rerun the
program, another connection will be opened. Either I have to close
VFP or run through all of the numbers.

Sincerely,

Gene Wirchenko

[Dan Freeman]

Post by Gene Wirchenko
On Mon, 4 Apr 2011 08:00:13 -0700 (PDT), Kevin Clark

Post by Gene Wirchenko

Post by Gene Wirchenko
5) Because I am experimenting, I often have program crashes and have

connections left hanging. How can I close all of them in one command?
(I can loop with sqldisconnect(), but this is kludgy.) <<
Maybe SQL server is different, but with Postgres, I have never needed
more than one connection. I make a connection and store the handle
into a global variable. If the global variable is not zero, then I
know that the connection has been made so I don't need to connect
again.

It is not a matter of needing but a matter of having. If a test
program crashes, its connection is still open. If I rerun the
program, another connection will be opened. Either I have to close
VFP or run through all of the numbers.
Sincerely,
Gene Wirchenko

When testing ANY kind of external connection an occasional reboot isn't
altogether a bad idea just to flush the flotsam down the drain.

In the early days of automation, before we'd figured out that Excel was
starting a new instance on every Createobject(), we'd occasionally get
wacko errors like "Not enough memory to quit".

A blank slate isn't a bad idea from time to time.

Dan

[Kevin Clark]

If I rerun the program, another connection will be opened.<<

Maybe I'm wrong, but I don't think you have to open another connection
each time the program runs. Assuming that you already have established
the connection within the FoxPro environment, all you need to know is
the handle in order to run more queries. If you assign the handle to
a global variable, and then check for non-zero value of the global
variable during subsequent runs of the program, you don't have to open
up subsequent connections.

Regards,
Kevin Clark

[Dan Freeman]

Post by Kevin Clark

If I rerun the program, another connection will be opened.<<

Maybe I'm wrong, but I don't think you have to open another connection
each time the program runs. Assuming that you already have established
the connection within the FoxPro environment, all you need to know is
the handle in order to run more queries. If you assign the handle to
a global variable, and then check for non-zero value of the global
variable during subsequent runs of the program, you don't have to open
up subsequent connections.
Regards,
Kevin Clark

You're not wrong, but you're having a different conversation. <s>

Storing the connection handle to a global variable (actually an
application property would be better) is all well and good, and it's
the way things should be done in a single run of a single program.

But in the crash/burn/retry development environment what happens when
you blow away your globals and try again? Blowing away the global
doesn't un-use the handle.

Dan