Accessing SQL 2012 New Offset/Fetch Feature from VFP9

[stanlyn]

Hi,

I'm having issues with the new SQL Server 2012 odbc driver and VFP9sp(latest). I'm trying to use the new offset and fetch feature that sql 2012 has. So far, all versions of SQL odbc drivers are failing when using... It does get a valid handle, however the SQLexec command is what is failing with a value of 0...

lcDSNLess="driver={SQL Server Native Client 11.0};server=Dazz;uid=sa;pwd=111111;DATABASE=DRI_131"
gnConnHandle=Sqlstringconnect(m.lcDSNLess)

?gnConnHandle
lcSql = 'select top 1000 * from PartyName ' + ;
'order by party_name, page_type, party_type offset 0 rows fetch next 20 rows only'

lnSuccess = SQLExec(gnConnHandle, lcSql, 'MyCursor')

If lnSuccess > 0
Select 'MyCursor'
Browse

SQLDisconnect(gnConnHandle)
Else
Messagebox('Sql Error')
Endif

Thanks, Stanley

 

[Mike Lewis]

Stanley,

You can't use a TOP N clause with OFFSET .. FETCH. It doesn't make sense. Try removing the TOP 1000 from your query and see if that solves the problem.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Mike Lewis]

But that wouldn't explain why SQLEXEC() is returning zero (I assume that's what you meant when you said it "is failing with a value of 0").

I would expect SQLEXEC() to return -1 in this case. It only returns zero if you are using asynchronous processing, and the command is still in progress. I don't know if that's an implication of the OFFSET ... FETCH clause (I don't see why it should be). It might be that you have explicitly set the connection's Asynchronous property to .T. for some reason.

If the problem persists, you could use SQLGETPROP() to check the value of Asynchronous. If it is .T., use SQLSETPROP() to set it .F. But that doesn't sound very likely.

I suggest you remove the TOP N clause first, and see what happens after that.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[stanlyn]

OK Mike, you are correct in stating that the "Top N" is out of place, however after a lot of testing, the 64bit SQL Server driver is what is failing at the SQLexec command. Thanks for pointing that out... The "Top N" part was just one of the many ways that I was testing to get the SQLexec command to work on the 64 bit driver.

To sum up... Using VFPsp2 on all 3 systems I find that the new offset/fetch commands:

1. works with the "Microsoft SQL Server Native Client 10.0" on XP-32bit and Win7-32bit,
2. works with the "Microsoft SQL Server Native Client 11.0" on Win7-32bit, (not available for XP-32b),
3. does not work with the "Microsoft SQL Server Native Client 11.0" on Win7-64bit, (actually, a simple "select * from PartyNames" does not work with either of the "Microsoft SQL Server Native Client 11.0, version 2011.110.2100.60" or the "SQL Server, version 6.01.7601.17514" odbc drivers.

Maybe there is an older 64bit version of these drivers that will work with VFP9sp2, anybody know?????

Thanks, Stanley

 

[stanlyn]

Hi Mike,

>> lnSuccess = SQLExec(gnConnHandle, lcSql, 'MyCursor')

When using 64bit odbc drivers the debugger clearly shows lnSuccess as 0, even after it returns control back to me the user. I was as you said expecting a -1 for failure.

Update to previous post... I did find and install the Native 10.0 64bit driver. Same "does not work" results as the 11.0 driver. When trying to install the 2008r2 32 bit version, it compains that that its the wrong driver and quits.

So, how do I get VFP talking to SQL 2012 on a 64 bit machine???

Thanks, Stanley

 

[Olaf Doschke]

VFP is 32 bit and can only work with 32bit ODBC drivers. SQL Server can be 64bit anyway, if you set up communication to TCP/IP that is a protocol bridge working to connect a 32bit VFP process with a 64bit SQL Server process, you can't make use of shared memory or named pipes and you could only, if SQL Server ran locally, anyway.

There must be 32bit ODBC drivers you can install and use.

Bye, Olaf.

 

[Olaf Doschke]

Look for C:\Windows\SysWOW64\sqlncli.dll, it should already be there, it`s the 32bit odbc driver. If it wouldn't be there you even wouldn't get a connection. If even your simplest SQLs won't work, look into the SQL Server configuration. It needs to use TCP/&IP and allow remote connections, even if you for now work locally only. These are typical restrictions of a fresh SQL Server installation you first have to overcome and only halfways a 64bit issue.

Bye, Olaf.

 

[stanlyn]

Hi Olaf,

I'm not using Sql Express. The SQL server is on a different machine on the lan. I have been trying to create a DSNless connection string that uses the 32bit odbc stuff in the C:\Windows\SysWOW64\ folder and so far I haven't been successful.

The idea is to have 64bit versions of Win7, Win8, Server2008r2 and Server2012 run a VFP9sp2 application that uses a SQL 2012 backend. Currently, Win7-32b works fine. However all the new server versions are pure 64b only.

I'm migrating a VFP app to SQL and now MS has abandoned 32bit anything, as I think Win8 is now only 64b.

Any idea on what a DSNless connection string would look like? I've been trying different suggestions as mentioned by connectionstrings.com. Here is one that is close to what I need, but I don't see where I direct it to use the 32bit/SysWOW64 drivers instead of the native 64b version one.

lcDSNLess = "driver={SQL Server Native Client 10.0};Server=DZAS;Database=DRI_KY131;Uid=sa;Pwd=123x666"

Thanks, Stanley

 

[Olaf Doschke]

0 As return value of SQLExec neither means success nor failure, it means a command still executes. That means you don't have any driver problems. Your query just takes loing. If you don't want asynchronous execution change it by SQLSETPROP().

Also:

1. Local SQL Server doiesn' mean it has to be Express.

2.Win7 64bit has a 32bit subsystem, always. And that's wha VFP uses, always. SysWow64 is the 32bit subsystem. In long it means System32WindowsOnWindows64. On the other side System32 stayed with that name, even though it's the 64bit core system folder, MS just stayed with that name for downward compatibility reasons. Don't blame me for that naming conventions. But that's how it is.

3. VFP can only see and use 32bit drivers. There's no misunderstanding about this: The bitness of drivers needs to match the bitness of the client process, not the bitness of the SQL Server, especially as ODBC driver and server are seperated by LAN. Your app talks to ODBC DLLs, ODBC forwards this via TCP/IP to the server side Service, which can be 32bit or 64bit and that doesn't matter at all, if it's separated by LAN.

Bye, Olaf.

 

[stanlyn]

Hi Olaf,

>> 0 As return value of SQLExec neither means success nor failure, it means a command still executes. That means you don't have any driver problems. Your query just takes loing.

The documentation states that a 0 means as you stated, its still executing. However, it is NOT executing and has finished, because 1) control is returned to either me or the debugger, where I can do whatever, or the debugger quickly steps to the next line and the trace is clearly showing lnSuccess = 0, and it doesn't change even after it has executed the lines after. 2) This works instantly on both the xp-32 and win7-32 bit machines.


>> 1. Local SQL Server doiesn' mean it has to be Express.

Agreed, that is why I also stated that it was not on the local physical machine, but on the same network, which I understand to be remote. In other words, if it's not on the physical machine its considered as remote, whether on a lan or wan. Is my understanding correct?


>> 2.Win7 64bit has a 32bit subsystem, always. And that's wha VFP uses, always.

So, by merely executing 32b VFP9sp2 odbc connections on a 64bit machine will automatically use the 32bit odbc subsystem? I am finding that this is not true at all, otherwise, we would not be talking here now, as I have not been able to get anything VFP/odbc to work under 64bit windows.


>> 3. VFP can only see and use 32bit drivers. There's no misunderstanding about this: The bitness of drivers needs to match the bitness of the client process, not the bitness of the SQL Server, especially as ODBC driver and server are seperated by LAN. Your app talks to ODBC DLLs, ODBC forwards this via TCP/IP to the server side Service, which can be 32bit or 64bit and that doesn't matter at all, if it's separated by LAN.

Agree, I get that...

So, what would a DSNless connection string look like on a 64bit windows running VFPsp2 talking to SQL via odbc32?

Thanks, Stanley

 

[Olaf Doschke]

>The documentation states that a 0 means as you stated, its still executing. However, it is NOT executing and has finished, because 1) control is returned to either me or the debugger, where I can do whatever
Yes, and that is the nature of asynchronous calls, it returns immediately and still in parallel to your code. And what you describe is perfectly matching asynchrous execution.

The help says about this:

In Asynchronous mode, you must call each function repeatedly until it returns a value other than 0 (still executing).

I said use SQLSETPROP() to turn that mode off:
SQLSetProp(lnH,"Asynchronous",.F.)

Bye, Olaf.

 

[stanlyn]

More info...

?SQLGETPROP(0,'Asynchronous') returns .F. on XP and returns .T. on Win64.

After issueing SQLSETPROP(gnConnHandle, "Asynchronous", .F.), it appears to be working on Win64, I hope. More testing later before putting this to rest.

Thanks, Stanley

 

[Olaf Doschke]

>So, what would a DSNless connection string look like on a 64bit windows running VFPsp2 talking to SQL via odbc32?

There is no part of a connectionstring pointing to use 32bit or 64bit drivers. You just have to insure both driver types are installed, they don't differ in name. That's why I said look into the SysWOWO64 folder, you can also start SysWow64\odbcad32.exe and you'll see what drivers foxpro sees and can use.

You are successfully using a 32bit driver already, otherwise you would get -1, and never ever 0 as return value of SQLEXEC().

Bye, Olaf.

 

[Olaf Doschke]

Ok, I just installed sql2012 evaluation as instance SQL2012 on Win764bit and 32bit ODBC drivers are installed by default. Installed AdventureWorks2012 sample database and gave me db access permissions.

So this works on 64bit OS with VFP, even on the same computer.

lcDSNLess="driver={SQL Server Native Client 11.0};server=(local)\SQL2012;Trusted_Connection=Yes;Database=AdventureWorks2012"
gnConnHandle=Sqlstringconnect(m.lcDSNLess)
? gnConnHandle
? SQLGetProp(gnConnHandle,"Asynchronous")
? SQLExec(gnConnHandle,"select * from [Sales].[Customer] Order by customerid offset 20 rows fetch next 20 rows only","curCustomers")

Your connection string should be ok, as already said, otherwise sqlstringconnect should already error and give no connection handle>0. If that's not the case, cehck your connection string.
Asynchronous =.f. is a standard sqlproperty, SQLGetProp(gnConnHandle,"Asynchronous") returns .F., if that's not the case, I gave you the SQLSetProp needed.
SqlExec returns 1, if that's 0, go back to SQLSetProp/SQLGetProp and see what's the matter.

Finally If I instead try
? SQLExec(gnConnHandle,"select top 1000 * from [Sales].[Customer] Order by customerid offset 20 rows fetch next 20 rows only","curCustomers")
The SQLExec returns -1 and AERROR() retrieves infos about the error: It's SQL Server error 37000: "Top cannot be used in a query (or subquery) using OFFSET"

So Mike also is right, Top doesn't work in conjunction with Offset. As you say this same code works on other combinations of OS and ODBC, that's no possible, as the query itself can't ever work woth TOP and OFFSET. double check and then come back.

Bye, Olaf.

 

[Olaf Doschke]

PS: To be more precise "37000" is the SQL State, SQL Error is 10741.

 

[stanlyn]

Hi Olaf,

>> You are successfully using a 32bit driver already, otherwise you would get -1, and never ever 0 as return value of SQLEXEC().

Yes on a 32 bit machine. I was getting the 0 only on 64 bit machines. It turns out that the SQLGETPROP(gnConnHandle, "Asynchronous") setting was true, and after setting it to false, its now working as expected.


>> There is no part of a connectionstring pointing to use 32bit or 64bit drivers. You just have to insure both driver types are installed, they don't differ in name.

Thank you for the clarification...


>> So Mike also is right, Top doesn't work in conjunction with Offset. As you say this same code works on other combinations of OS and ODBC, that's no possible, as the query itself can't ever work woth TOP and OFFSET. double check and then come back.

Yes, I agree... See my earlier response as you must have missed it...


How are you using AERROR() to retrieve that message?

Thanks, Stanley

 

[Olaf Doschke]

You simply call AERROR() to lket it create an array of error informations in case SQLConnect() or SQLExec() or any such SQL Pass Through functions return -1 for an error, and also after TABLEUPDATE() returns .F.
It also doesn't hurt to call it anyway, but in case SQL Passthrough reports success AERROR() may repoort any last error, that happend previously.

Take a look into the help topic of AERROR() for the special case of 1536, which is denoting an ODBC Error.

You have to actively retrieve that error information array, there is no ERROR thrown, no VFP exception like in the CATCH part of TRY..ENDTRY nor ON ERROR, but also in these cases you get further info about errors via AERROR(). Did you never use that function?

[URL unfurl="true"]http://msdn.microsoft.com/en-us/library/68a9h5tf(v=vs.80).aspx[/url]

Bye, Olaf.

 

[Olaf Doschke]

>you must have missed it
Yes, I missed some parts of your answers, sorry.

>It turns out that the SQLGETPROP(gnConnHandle, "Asynchronous") setting was true, and after setting it to false, its now working as expected.
OK, But it doesn't get that way by default. The help on SQLSetProp says it's default setting is .F., so look for "SQLSETPROP" or "Asynchronous" via the Code Refrences Tool. Your code or code you inherited has to set up Asynchronous execution at some place. It's not bad overall, you just have to handle it different. For example you can start many SQLExec Calls or many Remote Views or Cursoradapters at once via a connection set as asynchronous and thereby better utilize the multithreading/-processing capabilities of the SQL Server side.

But you can also execute multiple selects in synchronous mode:

lcDSNLess="driver={SQL Server Native Client 11.0};server=(local)\SQL2012;Trusted_Connection=Yes;Database=AdventureWorks2012"
gnConnHandle=Sqlstringconnect(m.lcDSNLess)
? gnConnHandle
? SQLSetProp(gnConnHandle,"Asynchronous",.F.)

Text To lcSQL Noshow
   Select * from [Sales].[Customer] Order by customerid offset  0 rows fetch next 20 rows only;
   Select * from [Sales].[Customer] Order by customerid offset 20 rows fetch next 20 rows only;
   Select * from [Sales].[Customer] Order by customerid offset 40 rows fetch next 20 rows only;
EndText

? SQLExec(gnConnHandle,lcSQL,"curCustomersPage") && retrieves first result
? SQLMoreResults(gnConnHandle,"curCustomersPage",laCount) && retrieves further results

That creates three cursors curCustomersPage, curCustomersPage1 and curCustomersPage2. Of course with Asynchronous .T. you can make several SQLExec calls and wait for the results to come back. Don't expect too much though, it's all sharing the same bandwidth anyway. It's easier to work in synchronous mode, in multi user applications the server will be utilized enough by concurrent users.

Bye, Olaf.

 

[stanlyn]

>> OK, But it doesn't get that way by default.
I knew that I've never set it in code. Found that it was ticked in the Remote options tab. I discovered the setting and value when I done a side-by-side comparison of VFP options from a Win7-64 and XP-32 machine, both with vfp9sp2.

>> AERROR(). Did you never use that function?
No, I had good error reporting before VFP9 (I skipped v8). VFP7's on error stuff worked well and when VFP9 came out, I got less help info from VFP. I'm getting limited info from the debugger with 'on error set step on' command such as line(), message(x), program(). Along those lines I find that error() does not work, so any help in getting a reliable way to see errors is welcome and it looks like the info from your link has the magic.

Thanks, Stanley

 

[stanlyn]

>> You have to actively retrieve that error information array, there is no ERROR thrown, no VFP exception like in the CATCH part of TRY..ENDTRY nor ON ERROR, but also in these cases you get further info about errors via AERROR().

So, if no errors are thrown, how and where do you create the function structure as the help file is only showing its usage in an "on error" routine. It appears this function must already exist and setting there waiting for an error to happen, and if "there is no ERROR thrown, no VFP exception like in the CATCH part of TRY..ENDTRY nor ON ERROR", then what triggers it? Code please...

Thanks, Stanley