Thread: ERROR with "Update ... where Current of"
Hi,
after a lot of studying i couldn't find out what's going wrong with my
positioned update. Whatever i have tried: SQLGetDiagRec reports: Error:
"Cursor C1 does not exists"
Here is my Code (a simplified example from MS):
...
SQLRETURN = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmtSelect);
SQLRETURN = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmtUpdate);
SQLRETURN = SQLSetCursorName(hstmtSelect, "C1", SQL_NTS);
SQLRETURN = SQLExecDirect(hstmtSelect,
"SELECT NAME, PHONE FROM CUSTOMERS",SQL_NTS);
/* in the example, a SQLBindCol followed here ... */
SQLRETURN = SQLFetch(hstmtSelect);
/* positioned update : set first customers phonenumber to empty */
SQLRETURN = SQLExecDirect(hstmtUpdate,
"UPDATE EMPLOYEE SET PHONE=\"\" WHERE CURRENT OF C1",SQL_NTS);
...SQLRETURN is set to -1, SQLGetDiagRec reports Error: "Cursor >>C1<< does
not exist"
...
psqlodbc30a.dll ist directly linked.
Here is what i have tried:
- Reading, exploring, reading, ...
- Select ... for Update
- connection Parameter UpdatableCursors=1;
- connection Parameter Autocommit off
- connection Parameter UseDeclareFetch=1
- Select ... for Update
- Select ... with hold
- older odbc Driver 8.04
- explict BEGIN
Any help would be greatly appreciated!
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/ERROR-with-Update-where-Current-of-tp4499184p4499184.html
Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.
Hi, Unfortunately the psqlodbc driver doesn't support POSITIONED UPDATE. regards, Hiroshi Inoue (2011/06/18 0:51), BGoebel wrote: > Hi, > > after a lot of studying i couldn't find out what's going wrong with my > positioned update. Whatever i have tried: SQLGetDiagRec reports: Error: > "Cursor C1 does not exists" > > Here is my Code (a simplified example from MS): > ... > SQLRETURN = SQLAllocHandle(SQL_HANDLE_STMT, hdbc,&hstmtSelect); > SQLRETURN = SQLAllocHandle(SQL_HANDLE_STMT, hdbc,&hstmtUpdate); > SQLRETURN = SQLSetCursorName(hstmtSelect, "C1", SQL_NTS); > > SQLRETURN = SQLExecDirect(hstmtSelect, > "SELECT NAME, PHONE FROM CUSTOMERS",SQL_NTS); > /* in the example, a SQLBindCol followed here ... */ > SQLRETURN = SQLFetch(hstmtSelect); > /* positioned update : set first customers phonenumber to empty */ > SQLRETURN = SQLExecDirect(hstmtUpdate, > "UPDATE EMPLOYEE SET PHONE=\"\" WHERE CURRENT OF C1",SQL_NTS); > > ...SQLRETURN is set to -1, SQLGetDiagRec reports Error: "Cursor>>C1<< does > not exist" > ... > psqlodbc30a.dll ist directly linked. > > Here is what i have tried: > - Reading, exploring, reading, ... > - Select ... for Update > - connection Parameter UpdatableCursors=1; > - connection Parameter Autocommit off > - connection Parameter UseDeclareFetch=1 > - Select ... for Update > - Select ... with hold > - older odbc Driver 8.04 > - explict BEGIN > > > Any help would be greatly appreciated! > > > > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/ERROR-with-Update-where-Current-of-tp4499184p4499184.html > Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.
Hi, thank you a lot for your message. ... a more appropriate error message by the ODBC-Driver would have been very nice. I will try to implement an update via SQLSetPos with SQL_UPDATE using a keyset_driven Cursor as it is described at http://msdn.microsoft.com/en-us/library/ms715392(v=vs.85).aspx. Does any docu exists, what is (not) implemented by the ODBC driver? regards -- View this message in context: http://postgresql.1045698.n5.nabble.com/ERROR-with-Update-where-Current-of-tp4499184p4510135.html Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.
This is the solution what i have found for my postioned update problem
It is implemented in Pascal/Delphi. To keep it short(er) i ommitted some
code (Error handling, setting String-length for returned data, type Info.
OK, "Update ... where Current of" would have been more elegant, but the
mean thing is: it works.
Thanks goes to Mr. Inoue for the hint, that SQLSetStmtAttr(...
SQL_CONCUR_ROWVER ...) is needed.
----------------------- snip ---------------
Var aRes:Integer;
hStmtSelect,hstmtUpdate,fEnvHandle,fConnectHandle:SQLHandle;
szName:ShortString;
cbName:SQLInteger;
aScroll,aSQLSmallInt:SQLSmallInt;
aConnectString:String;
aRow:Cardinal;
// -UpdatableCursors=1/0 makes no difference
// -using SQL_CURSOR_Static : Update works. DB2 Says NO.
Begin
fEnvHandle := 0;
fConnectHandle := 0;
aRes := SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, fEnvHandle);
aRes := SQLAllocHandle(SQL_HANDLE_DBC, fEnvHandle, fConnectHandle);
aSqlSmallint := 0;
aConnectString :=
'Driver={PostgreSQL};Server=127.0.0.1;Port=5432;Database=postgres;Uid=postgres;Pwd=mypwd;UpdatableCursors=1;';
aRes := SQLDriverConnect(fConnectHandle,
GetDesktopWindow,
@aConnectString[1],
length(aConnectString),
nil,
0,
aSqlSmallint,
0);
//switchin AUTOCOMMIT off
aRes := SQLSetConnectAttr(fConnectHandle,
SQL_ATTR_AUTOCOMMIT,
pointer(SQL_AUTOCOMMIT_OFF),
sizeof(SQL_AUTOCOMMIT_OFF));
aRes:= SQLAllocHandle(SQL_HANDLE_STMT, fConnectHandle, hStmtSelect);
// Cursor : KeySetDriven + SQL_CONCUR_ROWVER(=updatable)
aRes:= sqlSetStmtAttr(hStmtSelect,
SQL_ATTR_CONCURRENCY,
pointer(SQL_CONCUR_ROWVER),
sizeof(SQLSmallint));
aRes:= sqlSetStmtAttr(hStmtSelect,
SQL_ATTR_CURSOR_TYPE,
//pointer(SQL_CURSOR_STATIC), // would work
for updates with PostGreSQL. DB2: non updatable
pointer(SQL_CURSOR_KEYSET_DRIVEN),
sizeof(SQLSmallint));
// Select ...
aRes := SQLExecDirect(hstmtSelect,
pchar('SELECT name FROM customers order by nr'),
SQL_NTS);
// fetch will read name
aRes:= SQLBindCol(hstmtSelect, 1, SQL_C_CHAR, @szName[1], 50, cbName);
// fetching/reading the first row
aRow := 1;
aRes := SQLFetchScroll(hStmtSelect,
SQL_FETCH_ABSOLUTE,
arow);
// Changing data. Doing so, every time i call this snippet, the value will
be changed
szName[0]:=Chr(cbName);
IF szName[1]<'a'
THEN
szName := 'anyname'
ELSE
szName := 'ANYNAME';
cbName:=Length(szName);
// UPDATE data
aRes := SQLSetPos(hstmtSelect,
1,
SQL_UPDATE,
SQL_LOCK_UNLOCK );
// We are fetching the second row...
aRow:=2;
aRes := SQLFetchScroll(hStmtSelect,
SQL_FETCH_ABSOLUTE,
arow);
// ... and the first (changed) row again
// PostgreSQL: updated data in first row is shown, according to the ODBC
KeySetDriven property.
aRow:=1;
aRes := SQLFetchScroll(hStmtSelect,
SQL_FETCH_ABSOLUTE,
arow);
// Make changes visible to other users
// PostGreSQL: as expected: other users do not see the updates, when i do
not COMMIT them
aRes:=SQLExecDirect(hstmtUpdate,
'commit',
SQL_NTS);
// Does Fetch works without a new SELECT ?
// DB2: "...Ordinarily, all cursors close at the end of a transaction...",
there is an " with hold" Option.
// PostGreSQL : yes
aRow:=1;
aRes := SQLFetchScroll(hStmtSelect,
SQL_FETCH_ABSOLUTE,
arow);
end;
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/ERROR-with-Update-where-Current-of-tp4499184p4553441.html
Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.