| Title: | SQL/Services Forum |
| Notice: | kits(3) ft info(7) QAR access (8) SPR access (10) |
| Moderator: | SQLSRV::MAVRIS |
| Created: | Thu Oct 13 1988 |
| Last Modified: | Fri Jun 06 1997 |
| Last Successful Update: | Fri Jun 06 1997 |
| Number of topics: | 2214 |
| Total number of notes: | 8586 |
Hi,
Got a customer trying to call a stored procedure. Using ODBC 2.0.20 or
2.10, with sql services 6.0-11 it works, with 6.1-02 it fails with
....Unable to convert character string '' to type INTEGER
procedure, C code, couple of bits from log files follow.
any advice??
the very simple procedure:
***********************
create module TEST_LENA LANGUAGE SQL
procedure TEST_LENA(:out_key_value integer);
begin
declare :x integer;
set :out_key_value = 99;
end;
end module;
***********************
Here is part of the C code, i can't read it.... but it looks ok to me.
char lc_statement[]="CALL TEST_LENA(?)";/* Statement to execute*/
if (gs_connected == 1) {
ls_statement_sz = sizeof(lc_statement);
li_rc = SQLPrepare (g_hstmt, lc_statement, ls_statement_sz);
/* Now, bind the parameters to the statement. ONLY have to bind the
** params 1 time, and then call execute with different values as much
** as needed, but okay for now.
*/
if (li_rc == SQL_SUCCESS) {
li_rc = SQLBindParameter(g_hstmt,
1, /* Param number (left to right) */
SQL_PARAM_OUTPUT, /* Parameter type (OUTPUT) */
SQL_C_SLONG, /* C Data type (longword) */
SQL_INTEGER, /* SQL type (INTEGER) */
0, /* Precision */
0, /* Scale */
&lena, /* Location of buffer */
0, /* Buffer length, ignored for datatype */
&ll_sys_cnfg_int); /* Ignored except for char or binary data */
if (li_rc != SQL_SUCCESS) {
MessageBox(0, (LPSTR)"Failed to bind SYS_CNFG_INT to 2cnd param",
(LPSTR)"fpi_test_get_sys_cnfg_item", MB_ICONHAND | MB_OK);
}
/* Execute the statement */
li_rc = SQLExecute (g_hstmt);
Client logs don't show squat, but the odbcrdb logs do. extract from
log that fails (was using 6.1-01) and will be followed by log from
sqs 6.0-11 that works
....0X001462D0: CALL TEST_LENA(?) 0X00000000
........MEMFree (addr) 0X001443A0
........_rcStmtInit 0X001462D0
........_rcReplaceVendorStrings 0X00145AF0
........_rcReplaceEscapeClause 0X00145AF0
........_fstristr_SkipLiteral 0X00000000
........_rcReplaceShorthandExt 0X00145AF0
........_fstristr_SkipLiteral 0X00000000
........_TrimBlanks 0X00000000
........_lGetStmtType 0X00000000
........_fstristr_SkipLiteral 0X00000000
........_fstristr_SkipLiteral 0X00000000
........_fstristr_SkipLiteral 0X00000000
........_fstristr_SkipLiteral 0X00000000
........_fstristr_SkipLiteral 0X00000000
........_RemoveControlChars 0X00145AF0
........_rcColmCreateList 0X001462D0
........pLstCreate 0X00000000
........pMEMAlloc (size) 0X00000014
........pMEMAlloc (addr) 0X001443A0
........pMEMAlloc (size) 0X00000004
........pMEMAlloc (addr) 0X00141ED0
........pMEMAlloc (size) 0X00000098
........pMEMAlloc (addr) 0X001463E0
........rcLstAppend 0X001443A0
........rcLstFind 0X001443A0
........_rcColmGetInfo 0X001462D0
........rcLstEnum 0X001443A0
........_lTypeParam 0X00000002
........_TrimBlanks 0X00000000
........_TypeSetDefaultDbmsStruct 0X00000000
........_rcTypeMapDbmsTypeToLib 0X00000000
........_rcTypeMapDbmsToSqlc 0X00000000
........MEMFree (addr) 0X00145AF0
SQLBindParameter 0X001462D0
........_ClearErr 0X00000000
...._rcSQLSetParam 0X00000001
........pLstCreate 0X00000000
........pMEMAlloc (size) 0X00000014
........pMEMAlloc (addr) 0X00145AF0
........pMEMAlloc (size) 0X00000004
........pMEMAlloc (addr) 0X00143CC0
........rcLstEnum 0X00145AF0
........pMEMAlloc (size) 0X0000008C
........pMEMAlloc (addr) 0X00141A80
........rcLstAppend 0X00145AF0
........rcLstFind 0X00145AF0
SQLExecute 0X001462D0
........_ClearErr 0X00000000
...._rcSQLExecute 0X001462D0
........_fstristr_SkipLiteral 0X00000000
........_fstristr_SkipLiteral 0X00000000
...._rcGetOutputParams 0X001462D0
........rcLstEnum 0X001443A0
........rcLstEnum 0X00145AF0
....dbgetdata 0X00000001
........rcLstEnum 0X001443A0
........_lTypeCopyLibToSqlc 0X001462D0
........_TrimBlanks 0X00000000
....uCnvConvert 0X011E22A8
........_fCnvIsNumeric 0X00000001
........_fCnvIsNumeric 0XFFFFFFF0
........pMEMAlloc (size) 0X00000080
........pMEMAlloc (addr) 0X00141B28
....Unable to convert character string '' to type INTEGER
........MEMFree (addr) 0X00141B28
........StmtError 0X001462D0
........pLstCreate 0X00000000
........pMEMAlloc (size) 0X00000014
........pMEMAlloc (addr) 0X00142720
........pMEMAlloc (size) 0X00000024
........pMEMAlloc (addr) 0X00141B28
........rcLstAppend 0X00142720
......SQL Error Code = 0X00001038
......Native Error Code= 0X00000000
......22005Error in assignment. 0X00000000
SQLError 0X00000000
........_rcPlaceErrorInfo 0X00000000
........uLstCount 0X00142720
........rcLstEnum 0X00142720
........pMEMAlloc (size) 0X00000200
........pMEMAlloc (addr) 0X00146DA8
........pMEMAlloc (size) 0X00000200
........pMEMAlloc (addr) 0X00146FC0
........rcFStrnCpy 0X00000200
........rcLstRemove 0X00142720
........MEMFree (addr) 0X00146FC0
........MEMFree (addr) 0X00146DA8
[Oracle][ODBC]Error in assignment.
SQLFreeStmt 0X001462D0
and here is the 6.0-11 that works...
....0X001462D0: CALL TEST_LENA(?) 0X00000000
........MEMFree (addr) 0X001443A0
........_rcStmtInit 0X001462D0
........_rcReplaceVendorStrings 0X00145AF0
........_rcReplaceEscapeClause 0X00145AF0
........_fstristr_SkipLiteral 0X00000000
........_rcReplaceShorthandExt 0X00145AF0
........_fstristr_SkipLiteral 0X00000000
........_TrimBlanks 0X00000000
........_lGetStmtType 0X00000000
........_fstristr_SkipLiteral 0X00000000
........_fstristr_SkipLiteral 0X00000000
........_fstristr_SkipLiteral 0X00000000
........_fstristr_SkipLiteral 0X00000000
........_fstristr_SkipLiteral 0X00000000
........_RemoveControlChars 0X00145AF0
........_rcColmCreateList 0X001462D0
........pLstCreate 0X00000000
........pMEMAlloc (size) 0X00000014
........pMEMAlloc (addr) 0X001443A0
........pMEMAlloc (size) 0X00000004
........pMEMAlloc (addr) 0X00141ED0
........pMEMAlloc (size) 0X00000098
........pMEMAlloc (addr) 0X001463E0
........rcLstAppend 0X001443A0
........rcLstFind 0X001443A0
........_rcColmGetInfo 0X001462D0
........rcLstEnum 0X001443A0
........_lTypeParam 0X00000002
........_TrimBlanks 0X00000000
........_TypeSetDefaultDbmsStruct 0X00000000
........_rcTypeMapDbmsTypeToLib 0X00000000
........_rcTypeMapDbmsToSqlc 0X00000000
........MEMFree (addr) 0X00145AF0
SQLBindParameter 0X001462D0
........_ClearErr 0X00000000
...._rcSQLSetParam 0X00000001
........pLstCreate 0X00000000
........pMEMAlloc (size) 0X00000014
........pMEMAlloc (addr) 0X00145AF0
........pMEMAlloc (size) 0X00000004
........pMEMAlloc (addr) 0X00143CC0
........rcLstEnum 0X00145AF0
........pMEMAlloc (size) 0X0000008C
........pMEMAlloc (addr) 0X00141A80
........rcLstAppend 0X00145AF0
........rcLstFind 0X00145AF0
SQLExecute 0X001462D0
........_ClearErr 0X00000000
...._rcSQLExecute 0X001462D0
...._rcGetOutputParams 0X001462D0
........rcLstEnum 0X001443A0
........rcLstEnum 0X00145AF0
....dbgetdata 0X00000001
........rcLstEnum 0X001443A0
........_lTypeCopyLibToSqlc 0X001462D0
........_TrimBlanks 0X00000000
....uCnvConvert 0X011E22A8
........_fCnvIsNumeric 0X00000001
........_fCnvIsNumeric 0XFFFFFFF0
........_fCnvIsNumeric 0X00000001
........_uCnvCharToInt 0X011E22A8
........_uCnvCharToDouble 0X011E22A8
........npMEMAllocNear (size) 0X00000040
........npMEMAllocNear (addr) 0X00141B28
........MEMFreeNear (addr) 0X00141B28
| T.R | Title | User | Personal Name | Date | Lines |
|---|---|---|---|---|---|
| 2133.1 | 6.1 server bug, plus ODBC isn't reporting an error correctly | SQLSRV::OXBURY | Oracle Corporation, Rdb Desktop Group|DTN 381-2704 | Thu Jan 30 1997 13:12 | 76 |
Hi Jerry,
Here's the scoop.
Briefly: Its failing in 6.1, because of how auto-commit was implemented in 6.1.
In detail:
This particular stored procedure doesn't cause a transaction to be started,
which is the root of the problem.
Here's how/why it works in 6.0:
The 6.0 server doesn't do auto-commit. When the ODBC driver executes the
prepared CALL statement, it gets the correct answer back in an EXECUTE-ACK
message. It then executes a commit to commit the transaction. If the commit
succeeds, or fails with no-transaction-outstanding or no-implicit-transaction,
then it keeps going. Therefore, the application gets the correct value back.
Here's why it doesn't work in 6.1:
The 6.1 server does do auto-commit as follows: it executes the statement (or
closes a cursor, or, etc, etc), then tries to commit the transaction (after
checking to make sure the procedure didn't commit the transaction itself). If
the commit succeeds, the result of the execute is returned in an EXECUTE-ACK
message. If the commit fails for any reason, the error is returned to the
client in an ERROR message, ie, no data. Meanwhile, back at the ranch, the ODBC
server sees the no-transaction-outstanding or no-implicit-transaction error,
assumes that's OK (and 99% of the time it is) and keeps going. However, in this
particular case, no data got returned, so it tries to convert an empty string
to an integer.
Here's how/why it works in 7.0:
The 7.0 server does auto-commit as follows: it executes the statement (or
closes a cursor, or, etc, etc), then tries to commit the transaction (after
checking to make sure the procedure didn't commit the transaction itself). If
it fails with a no-transaction-outstanding or no-implicit-transaction, it
changes the status back to success. That means that if the commit succeeded (or
failed with an expected status), then the result of the execute is returned in
an EXECUTE-ACK message. If the commit (or execute, etc) fails with an
unexpected error, then the error details are returned in an ERROR message.
At least, that's what we believe is happening - we've not had time to make the
test program 'real' and, as we've just discovered, the ODBC SDK test program
doesn't handle parameter for calls correctly. However, using my SQL/Services
test program demonstrates the problem with 6.1 and the fact that it works with
7.0. If we are correct in this assumption, then the client logs should show
this. Could you post them or mail them so we can check?
In the meantime, here are some ways to workaround the problem:
- Turn auto-commit off.
- Modify the stored procedure to do something that will cause a transaction
to be started. Eg:
procedure ODBC_TEST0(:out_key_value integer);
begin
set transaction;
set :out_key_value = 99;
commit;
end;
- Install the 7.0 server; clients can stay as is, unless they want to run it
multiversion.
Of course, one observation is that, in this particular instance (call to stored
procedure with one or more output args that doesn't cause a transaction to be
started or ended), the ODBC driver should NOT ignore no-transaction-outstanding
or no-implicit-transaction errors, but should report some other error instead,
rather than hiding that one and confusing us with a different error later on.
Hope this helps,
Si
| |||||
| 2133.2 | M5::JHAYTER | Thu Jan 30 1997 15:05 | 7 | ||
Simon, Wow, what a mouth full. Thanks. I'll send you the client logs. There were some no transaction ack errors. Jerry | |||||
| 2133.3 | Yup, that was the problem - a 6.1 server bug | SQLSRV::OXBURY | Oracle Corporation, Rdb Desktop Group|DTN 381-2704 | Thu Jan 30 1997 15:58 | 43 |
I've already replied to Jerry, but for anyone else that runs into this... Having looked at the log file that Jerry sent, the problem is as we thought: PROTOCOL LEVEL LOG CLIENT: write (logonly) ----PACKET ID: 3, PACKET SEQUENCE: 0 --------SQLSRV_EXECUTE ------------STATEMENT ID ----------------SQLSRV_GENERALIZED_NUMBER, len: 7 --------------------len: 7, value: 1000000 ------------REPEAT COUNT ----------------len: 2, value: 1 ------------AUTO COMMIT ----------------len: 2, value: 8192 <-- Non-zero value = enabled --------END OF MESSAGE PROTOCOL LEVEL LOG CLIENT: read ----PACKET LENGTH: 90 PROTOCOL LEVEL LOG CLIENT: read ----PACKET ID: 3, PACKET SEQUENCE: 0 --------ERROR ACK ------------ERROR_VALUE_TAG ----------------SQLSRV_GENERALIZED_NUMBER, len: 5 --------------------len: 5, value: -1008 <-- Here's the no-imp-trans ERROR ------------SPECIFIC_ERROR_TAG ----------------SQLSRV_GENERALIZED_NUMBER, len: 1 --------------------len: 1, value: 0 ------------SPECIFIC_ERROR_TEXT_TAG ----------------SQLSRV_ASCII_STRING, len: 62 --------------------len: 62, value: %SQL-F-NOIMPTXN, no implicit transaction to --------------------commit or rollback --------END OF MESSAGE ...with the result that the ODBC driver sees -1008, thinks everything is really OK (just that there was nothing to commit) and carries on - the problem is that it never got an EXECUTE-ACK message, so there's no data, so it gets the error trying to convert an empty string (the '') to an integer to give back to the application. If anyone else runs into this, the work-arounds are in .1 Si | |||||
| 2133.4 | M5::JHAYTER | Mon Feb 03 1997 10:02 | 15 | ||
FWIW: >In the meantime, here are some ways to workaround the problem: > >- Turn auto-commit off. CT said that worked. >- Modify the stored procedure to do something that will cause a transaction > to be started. Eg: said this did not work (but i didn't push to verify what kind of mod they really made). thanks again Simon, you closed another call. | |||||