TD-15517: SqlOpen and CURRENT OF <cursor_name>

[igotarock]

Is anyone using the SqlOpen function (and CURRENT OF <cursor_name>) with Microsoft SQL Server?

SqlOpen works in our old code using SQLBase 9.x and seems to work in a test app with SQLBase 11.5.
However, migrating the code to use Microsoft SQL Server (via a System DSN and SQL Server Native Client) results in the following SQL error:

(20034) Microsoft SQL Server:16916[Microsoft][SQL Server Native Client 10.0][SQL Server]A cursor with the name 'SQLOPENTEST' does not exist.

This error does not occur on the call to SqlOpen. It occurs on the SqlExecute of the prepared UPDATE statement containing the WHERE CURRENT OF clause.

I have tried to follow the Team Developer 5.2 Documentation sections below with no luck.

• Connecting SQLWindows Objects to Databases : Connecting to Microsoft SQL Server : Positioned updates https://support.guptatechnologies.com/Do ... .html#3847
• Connecting SQLWindows Objects to Databases : Overview : Positioned updates https://support.guptatechnologies.com/Do ... .html#3928

Any help/comments/questions are appreciated. Thanks.

Currently using Team Developer v5.2-SP3 Build 24308 and Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 Express Edition with Advanced Services on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)

[igotarock]

I'm back to looking at getting SqlOpen and CURRENT OF <cursor_name> to work with Microsoft SQL Server.

I've attached a test app that I have been playing with but, so far, have not been successful.

I have three functions (SQLOpenTest01, SQLOpenTest02, SQLOpenTest03) that try to accomplish the SqlOpen/CURRENT OF functionality.
SQLOpenTest01 is representative of code that was working with SQLBase 9.0.1.
SQLOpenTest02 contains code from Team Developer 5.2 documentation on positioned updates.
SQLOpenTest03 contains code mimicking examples from the Microsoft SQL Server Transact-SQL Web pages, DECLARE CURSOR (http://msdn.microsoft.com/en-us/library/ms180169.aspx) and UPDATE (Limiting the Rows that Are Updated (Example D): http://msdn.microsoft.com/en-us/library ... tingValues).

Any help/comments/questions are appreciated. Thanks.

[Jeff Luther]

I'm asking internally, but I don't know of anyone who has used "positioned result set" for years. And I've never heard of anyone using with other than SQLBase, which supports that.

Additionally -- and maybe this implies that other DB brands support this -- TD v5.2 Help for SqlOpen says:

Note: this function will return an error if called against an OLE DB database connection.

How are you connecting to MS SS? ODBC or OLEDB?

I'll update this thread when I hear back from our router/DB developer.

[igotarock]

Thanks Jeff!
The code I am looking at is quite old, and we are connecting via an ODBC Systen DSN using the SQL Server Native Client.

[Jeff Luther]

The router developer had a good point. He wrote:

Is it “SQLOPENTEST” (upper case) consistently in user’s code? It may be that we were converting case in SQLbase and not ODBC?

and I notice in your test app this line:

Set strSqlStatement2 = 'UPDATE realty SET city = :strInto WHERE CURRENT OF sqlopentest'

Set "sqlopentest" to UPPERcase and see if that's the issue.

[igotarock]

Jeff,
From what I can tell, case doesn't seem to matter.
In the test app, it was consistently lower case. I went back and changed it to be consistently upper case.

Whatever the case of the cursor name ("SQLOPENTEST") is, it shows up in the SQL error as the same upper or lower case.

A cursor with the name 'SQLOPENTEST' does not exist.

[Jeff Luther]

Does does not matter, and I get the MS SS failure too. OK with SQLBase. I have opened TD defect TD-15517 for this.

[Jeff Luther]

Update on defect TD-15517: Development took a look at this, looked back through the internal history of TD and it does not appear that SqlOpen and associated WHERE syntax was ever supported in TD for MS SQL Server.

So, based on what development found out, in TD for MS SS while TD-15517 is still open and a little research is being done, it appears that this is an "As Designed" issue for MS SS. That is, it was never implemented for MS SS.