my SQL passthrough problem

Good day to all,

could i ask where i got wrong with this code , because when i run the .prg for this code, at first try it won't work, then the second time around it works, as if no error. Like when you open VFP, then at the command window you execute the do command, it won't work, but at the second or 3rd try it works.

Please help...thanks in advance

LOCAL lcConnect

STORE 0 TO lcConnect

STORE SQLCONNECT('My SQL Server Connection','' ) TO lcConnect && to determine if u r connected to the proper server.

IF lcConnect <= 0 && if you can't connect to the specified server then exit.

= MESSAGEBOX('Cannot make a connection to the server!', 16, 'SQL Connection Error')

RELEASE lcConnect

RETURN

ELSE

SQLSETPROP(lcConnect, "PacketSize", 2048 )

SQLEXEC(lcConnect,"SELECT * FROM employee","mycursorname")

BROWSE

USE IN mycursorname

SQLDISCONNECT(lcConnect)

ENDIF

RELEASE lcConnect

Visual FoxPro1

“It won't work”

What doesn’t work Do you get an error message Or are you just failing to connect

Dave M.

Ps. You can use AErrors() to see what the connection error is if you get lcConnect <0

Hello Dave,

The connection is OK, but when it reach at the brows line, it stop right there and it gives an error "No table is open in the current work area", ...then when i try again, OK it shows the brows fields, then press ESC key to exit ...again it generate an error at the sqldisconnect line "Invalid call issued while executing a SQLEXEC() sequence"

Why is it that it sometimes behave abnormally at first attempt, or 2nd attempt, but if it can execute the brows at the 2nd or 3rd attempt, it works fine , ...it shows no more problem

Thank you very much in advance for the explanation regarding this matter.

Madix_t

>> The connection is OK, but when it reach at the brows line, it stop right there and it gives an error "No table is open in the current work area", ...then when i try again, OK it shows the brows fields, then press ESC key to exit ...again it generate an error at the sqldisconnect line "Invalid call issued while executing a SQLEXEC() sequence

First. both SQLSETPROP and SQLEXEC are functions. This means that they return values - you are not checking the values so how do you know they are working

Second, as Dave has told you, you need to findo out what the SQL Error is (not the FoxPro one!). You do this using AERROR(). Try using code like this:

lnRes = SQLSETPROP(lcConnect, "PacketSize", 2048 )
IF lnRes < 1
AERROR( laErr )
laErr[3]
ENDIF

lnRes = SQLEXEC(lcConnect,"SELECT * FROM employee","mycursorname")
IF lnRes < 1
AERROR( laErr )
laErr[3]
ENDIF

Thank you AndyKr

I try your code and it gives an error ".NULL.", what is that mean why is it that in the second try i can get the data i wanted, why not automatically take immediately at first attempt in fact it's the same code. i ran at the second time around

Thank you

Madix_t

>>I try your code and it gives an error ".NULL.", what is that mean

I have no idea! WHAT gives you NULL SQLSETPROP SQLEXEC NULL is NOT a valid error message so the code you have posted must be incorrect in some way. Either it works, or you will get an error message - there are no other options here.

>> why is it that in the second try i can get the data i wanted

I have no idea.

Thank you sir for your time.

It just occurred to me. Did you check the other contents of the error array

The third element can be NULL if the error message had no additional information (though for a SQL Server error there should ALWAYS be an additional parameter). Maybe you are getting a FoxPro error instead...

Try checking the other elements (especially 2 and 5) : Here are the definitions for them from the Version 8.0 Help File:

Element number	Description
1	Numeric. The number of the error. Identical to the value returned by ERROR( ).
2	Character. The text of the error message. Identical to the value returned by MESSAGE( ).
3	The null value. However, if the error has an additional error parameter, contains the text of the error parameter. Almost identical to the value returned by SYS(2018), the difference being that AERROR( ) returns mixed case but SYS(2018) returns all uppercase.
4	The null value. However, as appropriate, contains the number of the work area in which the error occurred.
5	The null value. However, if a trigger failed (error 1539), contains one of the following numeric values:1 – Insert trigger failed.2 – Update trigger failed.3 – Delete trigger failed.
6	The null value.
7	The null value.

Hello,

i copy paste that code i posted, and follow your advice to put error checker for that code, and that's the error "NULL"..so how could i prevent such an error, how could i eliminate that OFF & ON result .
I just take that code from help file, and trying to make use of that short code to access data., but that's it, unstable.

Thank you for any help

Madix_t

Look, there are only two options when code executes, either it works, or you get an error. It does not work "sometimes". So either your code is wrong, or you are not trapping the error properly. This code works perfectly on my machine and shows me what the error is - if it does not work on yours I cannot help you

(CHANGE THE CONNECTION STRING FIRST)
lcString = "Driver=SQL Server;Server=(local);Database=XXXX;UID=XXXX;PWD=XXXX"
lnHandle = SQLSTRINGCONNECT( lcString )
IF lnHandle < 1
MESSAGEBOX( "Unable to connect to SQL Server, check the connection string" )
RETURN
ENDIF

lnRes = SQLSETPROP( lnHandle, "PacketSize", 2048 )
IF lnRes < 1
AERROR(laErr)
lcMsg = ''
FOR lnCnt = 1 TO ALEN( laErr, 2 )
lcMsg = lcMsg + ALLTRIM( TRANSFORM( laErr[lnCnt])) + CHR(13)
NEXT
MESSAGEBOX( lcMsg, 16, "SQLSETPROP Error" )
RETURN
ENDIF

lnRes = SQLEXEC(lnHandle,"SELECT * FROM employee","mycursorname")
IF lnRes < 1
AERROR(laErr)
lcMsg = ''
FOR lnCnt = 1 TO ALEN( laErr, 2 )
lcMsg = lcMsg + ALLTRIM( TRANSFORM( laErr[lnCnt])) + CHR(13)
NEXT
MESSAGEBOX( lcMsg, 16, "SQLEXEC Error" )
RETURN
ELSE
BROWSE
ENDIF

Hello...

Yes sir I copy paste your code and try if it works, yes it works it shows the error message, but what I am wanted to correct is how to show the brows result not the error message.
Ahh...maybe the correct packet size is the problem and can be answered at SQL FORUM, so what I did i post another code to the SQL forum maybe they can answer my question. In what kind of VIRUS struck my post in there..he..he..he.., it was gone and it shows-up in this forum...just look at post titled "Connection size cannot be set" and answered by "Tomik" ...

Thanks to all who try to help my problem..

Madix

I tried this code.

It is based around the Access database / application set example.

Several observations have not been picked up:

1. Note the parameters supplied to SQLCONNECT requires NO PASSWORD

2. Therefore this is not a MS SQL or other SQL backend engine

3. The table being SQL'd is employee within the Access Database schema / container.

4. This table is EIGHT letters database named, SHORT file name

5. The table in question is EMPLOYEES consisting of nine letters, LONG file named.

Simply adding an "s" to the small SQL script call resolves the issue if the assumption (I think I'm correct is Access tables --- but what version of Access tables - long file named or short file named!

IMHO of course.............................

I hate the way this thing is left in a hung state.

DA

3. The table being SQL'd is employee within the Access Database schema / container.

But since, i never use nor familiar with access database... the more i become confuse of what you have said,... i use that code for accessing data in SQL server, my problem is it don't work at first attempt then it work the second time. i try those given code the more i got the error not the browsed table.

The more recent versions of Access give you NATIVE Access and a lite MS SQL back end option.

Just what version of MS SQL are you using

DA

i am using MS SQL2000 in my back-end database. I have no problem what-so-ever accessing MS SQL using cursoradapter.

i am just curious that sample SQL passthrough which i copy from VFP HELP and paste and try it, some gives me what the error result might be, what i wanted is the correct approach that it won't generate and error but the records in the SELECT statement.