Hi,
when using SQLBindParameter with SQL_C_NUMERIC input, the function
returns SQL_SUCCESS. But a call to SQLExecute() returns SQL_ERROR with
"Syntax error on )".
It seems that the psqlodbc driver did not replace the ? marker
properly and an invalid statement is passed to the server. This only
happens with SQL_C_NUMERIC, for other datatypes the marker gets
replaced.
The used statement was:
INSERT INTO numtest(num1) VALUES(?);
where num1 is of type NUMERIC(5,3)
The driver was built from source psqlodbc-09.01.0200.tar.gz for
unixODBC.
Here is the odbctrace for the specific part:
[ODBC][27210][1369515859.324218][SQLBindParameter.c][217]
Entry:
Statement = 0x1a908b0
Param Number = 1
Param Type = 1
C Type = 2 SQL_C_NUMERIC
SQL Type = 2 SQL_NUMERIC
Col Def = 5
Scale = 3
Rgb Value = 0x1a91210
Value Max = 0
StrLen Or Ind = 0x1a91230
[ODBC][27210][1369515859.324329][SQLBindParameter.c][397]
Exit:[SQL_SUCCESS]
[ODBC][27210][1369515859.324359][SQLSetDescFieldW.c][168]
Entry:
Descriptor = 0x1ab2ef0
Rec Number = 1
Field Ident = SQL_DESC_PRECISION
Value = 0x5
Buffer Length = -8
[ODBC][27210][1369515859.324402][SQLSetDescFieldW.c][246]
Exit:[SQL_SUCCESS]
[ODBC][27210][1369515859.324421][SQLSetDescFieldW.c][168]
Entry:
Descriptor = 0x1ab2ef0
Rec Number = 1
Field Ident = SQL_DESC_SCALE
Value = 0x3
Buffer Length = -8
[ODBC][27210][1369515859.324452][SQLSetDescFieldW.c][246]
Exit:[SQL_SUCCESS]
[ODBC][27210][1369515859.324480][SQLExecute.c][187]
Entry:
Statement = 0x1a908b0
[ODBC][27210][1369515859.328804][SQLExecute.c][348]
Exit:[SQL_ERROR]
DIAG [42601] FEHLER: Syntaxfehler bei <BB>)<AB>;
Error while executing the query
And the psqlodbc trace:
[140356464904000]**** PGAPI_AllocStmt: hdbc = 0x1aae8d0, stmt = 0x1ab2510
[140356464904000]CC_add_statement: self=0x1aae8d0, stmt=0x1ab2510
[140356464904000][[SQLGetStmtAttr]] Handle=27993360 10010
[140356464904000]PGAPI_GetStmtAttr Handle=0x1ab2510 10010
[140356464904000][[SQLGetStmtAttr]] Handle=27993360 10011
[140356464904000]PGAPI_GetStmtAttr Handle=0x1ab2510 10011
[140356464904000][[SQLGetStmtAttr]] Handle=27993360 10012
[140356464904000]PGAPI_GetStmtAttr Handle=0x1ab2510 10012
[140356464904000][[SQLGetStmtAttr]] Handle=27993360 10013
[140356464904000]PGAPI_GetStmtAttr Handle=0x1ab2510 10013
[140356464904000][SQLPrepareW][140356464904000]PGAPI_Prepare: entering...
[140356464904000]**** PGAPI_Prepare: STMT_ALLOCATED, copy
[140356464904000][SQLBindParameter][140356464904000]PGAPI_BindParameter: entering...
[140356464904000]extend_parameter_bindings: entering ... self=0x1ab26b8, parameters_allocated=0, num_params=1,(nil)
[140356464904000]exit extend_parameter_bindings=0x1a91150
[140356464904000]extend_iparameter_bindings: entering ... self=0x1ab2728, parameters_allocated=0, num_params=1
[140356464904000]exit extend_iparameter_bindings=0x1a65fa0
[140356464904000]extend_putdata_info: entering ... self=0x1ab27f0, parameters_allocated=0, num_params=1
[140356464904000]exit extend_putdata_info=0x1a67310
[140356464904000]PGAPI_BindParameter: ipar=0, paramType=1, fCType=2, fSqlType=2, cbColDef=5,
ibScale=3,[140356464904000]rgbValue=0x1a91210(0),pcbValue=0x1a91230
[140356464904000][SQLSetDescFieldW][140356464904000]PGAPI_SetDescField h=0x1ab2688(10011) rec=1 field=1005 val=0x5,-8
[140356464904000][SQLSetDescFieldW][140356464904000]PGAPI_SetDescField h=0x1ab2688(10011) rec=1 field=1006 val=0x3,-8
[140356464904000][SQLExecute][140356464904000]PGAPI_Execute: entering...1
[140356464904000]PGAPI_Execute: clear errors...
[140356464904000]PGAPI_NumParams: entering...
[140356464904000]SC_scanQueryAndCountParams: entering...
[140356464904000]prepareParameters was not called, prepare state:3
[140356464904000]SC_recycle_statement: self= 0x1ab2510
[140356464904000]PDATA_free_params: ENTER, self=0x1ab27f0
[140356464904000]PDATA_free_params: EXIT
[140356464904000]Exec_with_parameters_resolved: copying statement params: trans_status=1, len=36, stmt='INSERT INTO
numtest(num1)VALUES(?);'
[140356464904000] stmt_with_params = 'INSERT INTO numtest(num1) VALUES(?);'
[140356464904000]about to begin SC_execute
[140356464904000] it's NOT a select statement: stmt=0x1ab2510
[140356464904000]CC_send_query: conn=0x1aae8d0, query='INSERT INTO numtest(num1) VALUES(?);'
conn=0x1aae8d0, query='INSERT INTO numtest(num1) VALUES(?);'
[140356464904000]send_query: done sending query 42bytes flushed
[140356464904000]in QR_Constructor
[140356464904000]exit QR_Constructor
[140356464904000]read -1, global_socket_buffersize=4096
[140356464904000]Lasterror=11
[140356464904000]!!! poll ret=1 revents=1
[140356464904000]read 99, global_socket_buffersize=4096
[140356464904000]send_query: got id = 'E'
[140356464904000]send_query: 'E' - SFEHLER
ERROR from backend during send_query: 'SFEHLER'
[140356464904000]send_query: 'E' - C42601
ERROR from backend during send_query: 'C42601'
[140356464904000]send_query: 'E' - MSyntaxfehler bei »)«
ERROR from backend during send_query: 'MSyntaxfehler bei »)«'
[140356464904000]send_query: 'E' - P35
ERROR from backend during send_query: 'P35'
[140356464904000]send_query: 'E' - Fsrc\backend\parser\scan.l
ERROR from backend during send_query: 'Fsrc\backend\parser\scan.l'
[140356464904000]send_query: 'E' - L1002
ERROR from backend during send_query: 'L1002'
[140356464904000]send_query: 'E' - Rscanner_yyerror
ERROR from backend during send_query: 'Rscanner_yyerror'
[140356464904000]read -1, global_socket_buffersize=4096
[140356464904000]Lasterror=11
[140356464904000]!!! poll ret=1 revents=1
[140356464904000]read 6, global_socket_buffersize=4096
[140356464904000]send_query: got id = 'Z'
[140356464904000]SC_set_Result(1ab2510, 1a90f40)[140356464904000]QResult: enter DESTRUCTOR
STATEMENT ERROR: func=SC_execute, desc='(null)', errnum=7, errmsg='Error while executing the query'
[140356464904000]STATEMENT ERROR: func=SC_execute, desc='(null)', errnum=7, errmsg='Error while executing the query'
------------------------------------------------------------
hdbc=0x1aae8d0, stmt=0x1ab2510, result=0x1a90f40
prepare=3, internal=0
bindings=(nil), bindings_allocated=0
parameters=0x1a91150, parameters_allocated=1
statement_type=1, statement='INSERT INTO numtest(num1) VALUES(?);'
stmt_with_params='INSERT INTO numtest(num1) VALUES(?);'
data_at_exec=-1, current_exec_param=-1, put_data=0
currTuple=-1, current_col=-1, lobj_fd=-1
maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0, scroll_concurrency=1
cursor_name='SQL_CUR0x1ab2510'
----------------QResult Info -------------------------------
fields=0x1a91090, backend_tuples=(nil), tupleField=0, conn=(nil)
fetch_count=0, num_total_rows=0, num_fields=0, cursor='(NULL)'
message='FEHLER: Syntaxfehler bei »)«', command='(NULL)', notice='(NULL)'
status=7, inTuples=0
CONN ERROR: func=SC_execute, desc='(null)', errnum=110, errmsg='FEHLER: Syntaxfehler bei »)«'
[140356464904000]CONN ERROR: func=SC_execute, desc='(null)', errnum=110, errmsg='FEHLER: Syntaxfehler bei »)«'
------------------------------------------------------------
henv=0x1a9cc90, conn=0x1aae8d0, status=1, num_stmts=16
sock=0x1aae730, stmts=0x1a9c9b0, lobj_type=-999
---------------- Socket Info -------------------------------
socket=6, reverse=0, errornumber=0, errormsg='(NULL)'
buffer_in=27905280, buffer_out=27989248
buffer_filled_in=6, buffer_filled_out=0, buffer_read_in=6
[140356464904000]retval=-1
[140356464904000][SQLGetDiagRecW][140356464904000]PGAPI_GetDiagRec entering type=3 rec=1
[140356464904000]ER_ReturnError: status = 7, msg = #FEHLER: Syntaxfehler bei »)«;
Error while executing the query#
[140356464904000] szSqlState = '42601',len=63, szError='FEHLER: Syntaxfehler bei »)«;
Error while executing the query'
[140356464904000]PGAPI_GetDiagRec exiting 0
[140356464904000][SQLGetDiagRecW][140356464904000]PGAPI_GetDiagRec entering type=3 rec=2
[140356464904000]ER_ReturnError: status = 7, msg = #FEHLER: Syntaxfehler bei »)«;
Error while executing the query#
[140356464904000]PGAPI_GetDiagRec exiting 100
[140356464904000][SQLGetDiagRecW][140356464904000]PGAPI_GetDiagRec entering type=3 rec=1
[140356464904000]ER_ReturnError: status = 7, msg = #FEHLER: Syntaxfehler bei »)«;
Error while executing the query#
[140356464904000] szSqlState = '42601',len=63, szError='FEHLER: Syntaxfehler bei »)«;
Error while executing the query'
--
Daniel Vogelbacher
www.chaospixel.com
cytrinox@freenode/ircnet/quakenet
