SA11: Strange behavior when accessing SQL Anywhere 11 using Microsoft OLE DB Provider for ODBC (MSDASQL)

Andrei Iliev

unread,

Oct 6, 2008, 2:31:52 PM10/6/08

to

SA11 Strange behavior when accessing SQL Anywhere 11 using Microsoft
OLE DB Provider for ODBC (MSDASQL).

A simle select from a table or a table join returns inconsistence number
of rows using ADODB cursor type other then ForwarOnly

Step to reproduce:
1) Prepare table and data (create Tables in demo datebase 'SQL Anywhere
11 Demo')

=============================================
CREATE TABLE "DBA"."Msg" (
"id" INTEGER NOT NULL DEFAULT AUTOINCREMENT,
"Title" LONG VARCHAR NOT NULL,
PRIMARY KEY ( "id" ASC )
);
CREATE TABLE "DBA"."Section" (
"id" INTEGER NOT NULL DEFAULT AUTOINCREMENT,
"name" LONG VARCHAR NOT NULL,
PRIMARY KEY ( "id" ASC )
);

CREATE TABLE "DBA"."MsgSection" (
"id_msg" INTEGER NOT NULL,
"id_section" INTEGER NOT NULL,
"ordr" INTEGER NULL DEFAULT 0,
PRIMARY KEY ( "id_msg" ASC, "id_section" ASC )
);

ALTER TABLE "DBA"."MsgSection" ADD CONSTRAINT "Msg" FOREIGN KEY (
"id_msg" ASC ) REFERENCES "DBA"."Msg" ( "id" ) ON DELETE CASCADE;
ALTER TABLE "DBA"."MsgSection" ADD CONSTRAINT "Section" FOREIGN KEY (
"id_section" ASC ) REFERENCES "DBA"."Section" ( "id" ) ON DELETE CASCADE;

INSERT INTO "DBA"."Msg" ("id","Title") VALUES(DEFAULT,'Msg1');
INSERT INTO "DBA"."Msg" ("id","Title") VALUES(DEFAULT,'Msg2');
INSERT INTO "DBA"."Section" ("id","name") VALUES(DEFAULT,'section1');
INSERT INTO "DBA"."Section" ("id","name") VALUES(DEFAULT,'section2');
INSERT INTO "DBA"."MsgSection" ("id_msg","id_section","ordr")
VALUES(1,1,DEFAULT);

=============================================

2) Create test.vbs:
==================Start script===========
'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3

'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4

'---- CursorLocationEnum Values ----
Const adUseServer = 2
Const adUseClient = 3

'---- CommandTypeEnum Values ----
Const adCmdUnknown = &H0008
Const adCmdText = &H0001
Const adCmdTable = &H0002
Const adCmdStoredProc = &H0004
Const adCmdFile = &H0100
Const adCmdTableDirect = &H0200

Dim sSql
sSql="SELECT Msg.id,MsgSection.ordr,Msg.title FROM Msg key join
MsgSection where MsgSection.id_section= 1 order by Msg.id desc"

WScript.Echo "test1"
call Test1(sSql)

WScript.Echo "test2"
call Test2(sSql)

WScript.Echo "test2_2"
call Test2_2(sSql)

WScript.Echo "test3"
call Test3(sSql)

WScript.Echo "test4"
call Test4(sSql)

sSql="SELECT Msg.id,Msg.title FROM Msg"

WScript.Echo "test1"
call Test1(sSql)

WScript.Echo "test2"
call Test2(sSql)

WScript.Echo "test2_2"
call Test2_2(sSql)

WScript.Echo "test3"
call Test3(sSql)

WScript.Echo "test4"
call Test4(sSql)

Sub Test1(sSql)
Dim oConn
Dim oRS
set oConn= CreateObject("ADODB.Connection")
oConn.Open "SQL Anywhere 11 Demo"

Set oRs = CreateObject("ADODB.RecordSet")
oRs.Open sSQL, oConn, adOpenStatic, adLockReadOnly