Thread: Parameters.Refresh error.
I am trying to get a VB6 program to work on a PostgreSQL ANSI DSN. This program runs fine using an AS/400 DSN and uses prepared, parameterized SQL statements via an ADODB.Command object for Inserts and Updates. An example statement is "Insert into CHAPTER (CHAPTERID, CHAPTERDESC) values (?,?)". My ConnectionString for my PostgreSQL DSN is simply "DSN=DSN Name" and I connect successfuly. However, when I use .Parameters.Refresh I get the following error: "Provider cannot derive parameter information and SetParameterInfo has not been called." When I try "Provider=PostgreSQL.1;Data Source=localhost;Location=DB Name;", I can also connect, but .Parameters.Count simply returns 0 instead of the appropriate amount. If I use a non-parameterized SQL statement, everything works great, but this is not the desired approach. Has anyone experienced something like this? Am I posting in the right place? I'm running PostgreSQL 8.1.1 as a service on Windows XP Pro SP2. Thanks, Adam
> My ConnectionString for my PostgreSQL DSN is simply "DSN=DSN Name" and I > connect successfuly. However, when I use .Parameters.Refresh I get the > following error: > > "Provider cannot derive parameter information and SetParameterInfo has > not been called." I have no idea what SQLXXX ODBC calls are represented under this. > When I try "Provider=PostgreSQL.1;Data Source=localhost;Location=DB > Name;", I can also connect, but .Parameters.Count simply returns 0 > instead of the appropriate amount. > > Has anyone experienced something like this? Am I posting in the right > place? This is the right place. Please could you send here full mylog output (turn on in Global and DataSource dialogs). It helps us to find the problematic place. Regards, Luf
Ludek Finstrle wrote:
>Please could you send here full mylog output (turn on in Global and
>DataSource dialogs). It helps us to find the problematic place.
>
Attached you will find the mylog output as well as the comm output (if
that will help).
Thanks,
Adam
Global Options: Version='08.01.0102', fetch=100, socket=0, unknown_sizes=254, max_varchar_size=8190,
max_longvarchar_size=0
disable_optimizer=0, ksqo=0, unique_index=1, use_declarefetch=0
text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1 NAMEDATALEN=64
extra_systable_prefixes='dd_;', conn_settings='' conn_encoding='OTHER'
conn=88422504, query='select version()'
[ PostgreSQL version string = 'PostgreSQL 8.1.1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
(mingw-special)']
[ PostgreSQL version number = '8.1' ]
conn=88422504, query='set DateStyle to 'ISO''
conn=88422504, query='set extra_float_digits to 2'
conn=88422504, query='select oid from pg_type where typname='lo''
conn=88422504, query='select pg_client_encoding()'
conn=88422504, query='set client_encoding to 'latin9''
conn=88422504, PGAPI_DriverConnect(out)='DSN=GALSTA
Postgres;DATABASE=GALSTA;SERVER=localhost;PORT=5432;SSLMODE=disable;UID=ADAM;PWD=xxxxxx;ReadOnly=0;Protocol=6.4;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=8192;UnknownSizes=0;MaxVarcharSize=254;MaxLongVarcharSize=8190;Debug=1;CommLog=1;Optimizer=0;Ksqo=0;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;;LFConversion=1;UpdatableCursors=0;DisallowPremature=0;TrueIsMinus1=1;BI=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=1;LowerCaseIdentifier=0'
DESCRIPTOR ERROR: func=PGAPI_SetDescField, desc='', errnum=11, errmsg='bad parameter number'
conn=88422504, PGAPI_Disconnect
[1936][[SQLAllocHandle]]
[1936]**** in PGAPI_AllocEnv **
[1936]** exit PGAPI_AllocEnv: phenv = 88422448 **
[1936][[SQLSetEnvAttr]] att=200,2
[1936][[SQLAllocHandle]]
[1936]PGAPI_AllocConnect: entering...
[1936]**** PGAPI_AllocConnect: henv = 88422448, conn = 88422504
[1936]EN_add_connection: self = 88422448, conn = 88422504
[1936] added at i =0, conn->henv = 88422448, conns[i]->henv = 88422448
[1936][SQLGetInfo(30)]
[1936]PGAPI_GetInfo: entering...fInfoType=77
[1936]PGAPI_GetInfo: p='03.00', len=5, value=0, cbMax=12
[1936][[SQLSetConnectAttr]] 103
[1936]PGAPI_SetConnectAttr 103
[1936]PGAPI_SetConnectOption: entering fOption = 103 vParam = 15
[1936][SQLDriverConnect]
[1936]PGAPI_DriverConnect: entering...
[1936]**** PGAPI_DriverConnect: fDriverCompletion=0, connStrIn='DSN=GALSTA Postgres;UID=ADAM;PWD=xxxxxx;'
[1936]our_connect_string = 'DSN=GALSTA Postgres;UID=ADAM;PWD=xxxxxx;'
[1936]attribute = 'DSN', value = 'GALSTA Postgres'
[1936]copyAttributes: DSN='GALSTA
Postgres',server='',dbase='',user='',passwd='xxxxx',port='',sslmode='',onlyread='',protocol='',conn_settings='',disallow_premature=-1)
[1936]attribute = 'UID', value = 'ADAM'
[1936]copyAttributes: DSN='GALSTA
Postgres',server='',dbase='',user='ADAM',passwd='xxxxx',port='',sslmode='',onlyread='',protocol='',conn_settings='',disallow_premature=-1)
[1936]attribute = 'PWD', value = 'xxxxx'
[1936]copyAttributes: DSN='GALSTA
Postgres',server='',dbase='',user='ADAM',passwd='xxxxx',port='',sslmode='',onlyread='',protocol='',conn_settings='',disallow_premature=-1)
[1936]globals.extra_systable_prefixes = 'dd_;'
[1936]globals.extra_systable_prefixes = 'dd_;'
[1936]our_connect_string = 'DSN=GALSTA Postgres;UID=ADAM;PWD=xxxxxx;'
[1936]attribute = 'DSN', value = 'GALSTA Postgres'
[1936]CopyCommonAttributes:
A7=100;A8=8192;A9=0;B0=254;B1=8190;B2=1;B3=1;B4=0;B5=0;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;[1936]attribute= 'UID',
value= 'ADAM'
[1936]CopyCommonAttributes:
A7=100;A8=8192;A9=0;B0=254;B1=8190;B2=1;B3=1;B4=0;B5=0;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;[1936]attribute= 'PWD',
value= 'xxxxx'
[1936]CopyCommonAttributes:
A7=100;A8=8192;A9=0;B0=254;B1=8190;B2=1;B3=1;B4=0;B5=0;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;[1936]CC_connect:
entering...
[1936]CC_connect(): DSN = 'GALSTA Postgres', server = 'localhost', port = '5432', sslmode = 'disable', database =
'GALSTA',username = 'ADAM', password='xxxxx'
[1936]connecting to the server
[1936]connecting to the database using localhost as the server
[1936]the size is 15
[1936]connection to the database succeeded.
[1936]connection to the database succeeded.
[1936]CC_lookup_pg_version: entering...
[1936]PGAPI_AllocStmt: entering...
[1936]**** PGAPI_AllocStmt: hdbc = 88422504, stmt = 88434208
[1936]CC_add_statement: self=88422504, stmt=88434208
[1936]PGAPI_ExecDirect: entering...
[1936]recycle statement: self= 88434208
[1936]PDATA_free_params: ENTER, self=88434640
[1936]**** PGAPI_ExecDirect: hstmt=88434208, statement='select version()'
[1936]PGAPI_ExecDirect: calling PGAPI_Execute...
[1936]PGAPI_Execute: entering...
[1936]PGAPI_Execute: clear errors...
[1936]recycle statement: self= 88434208
[1936]PDATA_free_params: ENTER, self=88434640
[1936]Exec_with_parameters_resolved: copying statement params: trans_status=1, len=16, stmt='select version()'
[1936] stmt_with_params = 'select version()'
[1936] Sending SELECT statement on stmt=88434208, cursor_name='SQL_CUR05456620'
[1936]send_query(): conn=88422504, query='select version()'
[1936]in QR_Constructor
[1936]exit QR_Constructor
[1936]in TL_Constructor
[1936]exit TL_Constructor
[1936]send_query: done sending query
[1936]QR_fetch_tuples: cursor = '', self->cursor=0
[1936]QR_fetch_tuples: past CI_read_fields: num_fields = 1
[1936]MALLOC: tuple_size = 100, size = 800
[1936] done sending the query:
[1936]extend_column_bindings: entering ... self=88434344, bindings_allocated=0, num_columns=1
[1936]exit extend_column_bindings
[1936]PGAPI_ExecDirect: returned 0 from PGAPI_Execute
[1936]PGAPI_Fetch: stmt = 88434208, stmt->result= 88438976
[1936]manual_result = 1, use_declarefetch = 0
[1936]**** SC_fetch: manual_result
[1936]extend_getdata_info: entering ... self=88434564, gdata_allocated=0, num_columns=1
[1936]exit extend_gdata_info
[1936]fetch: cols=1, lf=0, opts = 88434344, opts->bindings = 88444616, buffer[] = 0
[1936]PGAPI_GetData: enter, stmt=88434208
[1936] value = 'PostgreSQL 8.1.1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)'
[1936]**** PGAPI_GetData: icol = 0, fCType = 1, field_type = 25, value = 'PostgreSQL 8.1.1 on i686-pc-mingw32, compiled
byGCC gcc.exe (GCC) 3.4.2 (mingw-special)'
[1936]copy_and_convert: field_type = 25, fctype = 1, value = 'PostgreSQL 8.1.1 on i686-pc-mingw32, compiled by GCC
gcc.exe(GCC) 3.4.2 (mingw-special)', cbValueMax=128
[1936]DEFAULT: len = 88, ptr = 'PostgreSQL 8.1.1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
(mingw-special)'
[1936] SQL_C_CHAR, default: len = 88, cbValueMax = 128, rgbValueBindRow = 'PostgreSQL 8.1.1 on i686-pc-mingw32,
compiledby GCC gcc.exe (GCC) 3.4.2 (mingw-special)'
[1936]Got the PostgreSQL version string: 'PostgreSQL 8.1.1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
(mingw-special)'
[1936]Extracted PostgreSQL version number: '8.1'
[1936]PGAPI_FreeStmt: entering...hstmt=88434208, fOption=1
[1936]QResult: in DESTRUCTOR
[1936]TupleList: in DESTRUCTOR
[1936]TupleList: exit DESTRUCTOR
[1936]QResult: free memory in, fcount=101
[1936]QResult: free memory out
[1936]QResult: exit DESTRUCTOR
[1936]SC_Destructor: self=88434208, self->result=0, self->hdbc=88422504
[1936]APD_free_params: ENTER, self=88434460
[1936]IPD_free_params: ENTER, self=88434524
[1936]PDATA_free_params: ENTER, self=88434640
[1936]ARDFields_free 54566a8 bookmark=5457a28[1936]ARD_unbind_cols freeall=1 allocated=1 bindings=5458ec8
[1936]reset_a_column_binding: entering ... self=88434344, bindings_allocated=1, icol=1
[1936]APD_free_params: ENTER, self=88434460
[1936]IPD_free_params: ENTER, self=88434524
[1936]GDATA_unbind_cols freeall=1 allocated=1 gdata=5458ef0
[1936]SC_Destructor: EXIT
[1936]CC_send_settings: entering...
[1936]PGAPI_AllocStmt: entering...
[1936]**** PGAPI_AllocStmt: hdbc = 88422504, stmt = 88434208
[1936]CC_add_statement: self=88422504, stmt=88434208
[1936]PGAPI_ExecDirect: entering...
[1936]recycle statement: self= 88434208
[1936]PDATA_free_params: ENTER, self=88434640
[1936]**** PGAPI_ExecDirect: hstmt=88434208, statement='set DateStyle to 'ISO''
[1936]PGAPI_ExecDirect: calling PGAPI_Execute...
[1936]PGAPI_Execute: entering...
[1936]PGAPI_Execute: clear errors...
[1936]recycle statement: self= 88434208
[1936]PDATA_free_params: ENTER, self=88434640
[1936]Exec_with_parameters_resolved: copying statement params: trans_status=1, len=22, stmt='set DateStyle to 'ISO''
[1936] stmt_with_params = 'set DateStyle to 'ISO''
[1936] it's NOT a select statement: stmt=88434208
[1936]send_query(): conn=88422504, query='set DateStyle to 'ISO''
[1936]in QR_Constructor
[1936]exit QR_Constructor
[1936]The query was executed successfully and the query did not return any result
[1936]send_query: done sending query
[1936]QR_fetch_tuples: cursor = '', self->cursor=0
[1936]QR_fetch_tuples: past CI_read_fields: num_fields = 0
[1936]MALLOC: tuple_size = 100, size = 0
[1936]PGAPI_ExecDirect: returned 0 from PGAPI_Execute
[1936]CC_send_settings: result 0, status 1 from set DateStyle
[1936]PGAPI_ExecDirect: entering...
[1936]recycle statement: self= 88434208
[1936]QResult: in DESTRUCTOR
[1936]QResult: free memory in, fcount=101
[1936]QResult: free memory out
[1936]QResult: exit DESTRUCTOR
[1936]PDATA_free_params: ENTER, self=88434640
[1936]**** PGAPI_ExecDirect: hstmt=88434208, statement='set extra_float_digits to 2'
[1936]PGAPI_ExecDirect: calling PGAPI_Execute...
[1936]PGAPI_Execute: entering...
[1936]PGAPI_Execute: clear errors...
[1936]recycle statement: self= 88434208
[1936]PDATA_free_params: ENTER, self=88434640
[1936]Exec_with_parameters_resolved: copying statement params: trans_status=1, len=27, stmt='set extra_float_digits to
2'
[1936] stmt_with_params = 'set extra_float_digits to 2'
[1936] it's NOT a select statement: stmt=88434208
[1936]send_query(): conn=88422504, query='set extra_float_digits to 2'
[1936]in QR_Constructor
[1936]exit QR_Constructor
[1936]The query was executed successfully and the query did not return any result
[1936]send_query: done sending query
[1936]QR_fetch_tuples: cursor = '', self->cursor=0
[1936]QR_fetch_tuples: past CI_read_fields: num_fields = 0
[1936]MALLOC: tuple_size = 100, size = 0
[1936]PGAPI_ExecDirect: returned 0 from PGAPI_Execute
[1936]CC_send_settings: result 0, status 1 from set extra_float_digits
[1936]PGAPI_FreeStmt: entering...hstmt=88434208, fOption=1
[1936]QResult: in DESTRUCTOR
[1936]QResult: free memory in, fcount=101
[1936]QResult: free memory out
[1936]QResult: exit DESTRUCTOR
[1936]SC_Destructor: self=88434208, self->result=0, self->hdbc=88422504
[1936]APD_free_params: ENTER, self=88434460
[1936]IPD_free_params: ENTER, self=88434524
[1936]PDATA_free_params: ENTER, self=88434640
[1936]ARDFields_free 54566a8 bookmark=5458ea0[1936]ARD_unbind_cols freeall=1 allocated=0 bindings=0
[1936]APD_free_params: ENTER, self=88434460
[1936]IPD_free_params: ENTER, self=88434524
[1936]GDATA_unbind_cols freeall=1 allocated=0 gdata=0
[1936]SC_Destructor: EXIT
[1936]CC_lookup_lo: entering...
[1936]PGAPI_AllocStmt: entering...
[1936]**** PGAPI_AllocStmt: hdbc = 88422504, stmt = 88434208
[1936]CC_add_statement: self=88422504, stmt=88434208
[1936]PGAPI_ExecDirect: entering...
[1936]recycle statement: self= 88434208
[1936]PDATA_free_params: ENTER, self=88434640
[1936]**** PGAPI_ExecDirect: hstmt=88434208, statement='select oid from pg_type where typname='lo''
[1936]PGAPI_ExecDirect: calling PGAPI_Execute...
[1936]PGAPI_Execute: entering...
[1936]PGAPI_Execute: clear errors...
[1936]recycle statement: self= 88434208
[1936]PDATA_free_params: ENTER, self=88434640
[1936]Exec_with_parameters_resolved: copying statement params: trans_status=1, len=42, stmt='select oid from pg_type
wheretypname='lo''
[1936] stmt_with_params = 'select oid from pg_type where typname='lo''
[1936] Sending SELECT statement on stmt=88434208, cursor_name='SQL_CUR05456620'
[1936]send_query(): conn=88422504, query='select oid from pg_type where typname='lo''
[1936]in QR_Constructor
[1936]exit QR_Constructor
[1936]in TL_Constructor
[1936]exit TL_Constructor
[1936]send_query: done sending query
[1936]QR_fetch_tuples: cursor = '', self->cursor=0
[1936]QR_fetch_tuples: past CI_read_fields: num_fields = 1
[1936]MALLOC: tuple_size = 100, size = 800
[1936] done sending the query:
[1936]extend_column_bindings: entering ... self=88434344, bindings_allocated=0, num_columns=1
[1936]exit extend_column_bindings
[1936]PGAPI_ExecDirect: returned 0 from PGAPI_Execute
[1936]PGAPI_Fetch: stmt = 88434208, stmt->result= 88438976
[1936]manual_result = 1, use_declarefetch = 0
[1936]PGAPI_FreeStmt: entering...hstmt=88434208, fOption=1
[1936]QResult: in DESTRUCTOR
[1936]TupleList: in DESTRUCTOR
[1936]TupleList: exit DESTRUCTOR
[1936]QResult: free memory in, fcount=101
[1936]QResult: free memory out
[1936]QResult: exit DESTRUCTOR
[1936]SC_Destructor: self=88434208, self->result=0, self->hdbc=88422504
[1936]APD_free_params: ENTER, self=88434460
[1936]IPD_free_params: ENTER, self=88434524
[1936]PDATA_free_params: ENTER, self=88434640
[1936]ARDFields_free 54566a8 bookmark=5458ea0[1936]ARD_unbind_cols freeall=1 allocated=1 bindings=5458e78
[1936]reset_a_column_binding: entering ... self=88434344, bindings_allocated=1, icol=1
[1936]APD_free_params: ENTER, self=88434460
[1936]IPD_free_params: ENTER, self=88434524
[1936]GDATA_unbind_cols freeall=1 allocated=0 gdata=0
[1936]SC_Destructor: EXIT
[1936]CC_lookup_characterset: entering...
[1936]send_query(): conn=88422504, query='select pg_client_encoding()'
[1936]in QR_Constructor
[1936]exit QR_Constructor
[1936]in TL_Constructor
[1936]exit TL_Constructor
[1936]send_query: done sending query
[1936]QR_fetch_tuples: cursor = '', self->cursor=0
[1936]QR_fetch_tuples: past CI_read_fields: num_fields = 1
[1936]MALLOC: tuple_size = 100, size = 800
[1936]QResult: in DESTRUCTOR
[1936]TupleList: in DESTRUCTOR
[1936]TupleList: exit DESTRUCTOR
[1936]QResult: free memory in, fcount=101
[1936]QResult: free memory out
[1936]QResult: exit DESTRUCTOR
[1936]send_query(): conn=88422504, query='set client_encoding to 'latin9''
[1936]in QR_Constructor
[1936]exit QR_Constructor
[1936]The query was executed successfully and the query did not return any result
[1936]send_query: done sending query
[1936]QR_fetch_tuples: cursor = '', self->cursor=0
[1936]QR_fetch_tuples: past CI_read_fields: num_fields = 0
[1936]MALLOC: tuple_size = 100, size = 0
[1936]QResult: in DESTRUCTOR
[1936]QResult: free memory in, fcount=101
[1936]QResult: free memory out
[1936]QResult: exit DESTRUCTOR
[1936]CC_connect: returning...
[1936]szConnStrOut = 'DSN=GALSTA
Postgres;DATABASE=GALSTA;SERVER=localhost;PORT=5432;SSLMODE=disable;UID=ADAM;PWD=xxxxxx;ReadOnly=0;Protocol=6.4;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=8192;UnknownSizes=0;MaxVarcharSize=254;MaxLongVarcharSize=8190;Debug=1;CommLog=1;Optimizer=0;Ksqo=0;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;;LFConversion=1;UpdatableCursors=0;DisallowPremature=0;TrueIsMinus1=1;BI=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=1;LowerCaseIdentifier=0'
len=591,1024
[1936]PGAPI_DriverConnect: returning 0
[1936][[SQLGetDiagRec]]
[1936]PGAPI_GetDiagRec entering rec=1
[1936]**** PGAPI_ConnectError: hdbc=88422504 <0>
[1936]enter CC_get_error
[1936]enter CC_create_errormsg
[1936]msg = ''
[1936]exit CC_create_errormsg
[1936]exit CC_get_error
[1936]CC_Get_error returned nothing.
[1936]PGAPI_GetDiagRec exiting 100
[1936][SQLGetFunctions]
[1936][SQLGetInfo(30)]
[1936]PGAPI_GetInfo: entering...fInfoType=23
[1936]PGAPI_GetInfo: p='<NULL>', len=2, value=2, cbMax=2
[1936][SQLGetInfo(30)]
[1936]PGAPI_GetInfo: entering...fInfoType=24
[1936]PGAPI_GetInfo: p='<NULL>', len=2, value=2, cbMax=2
[1936][SQLGetInfo(30)]
[1936]PGAPI_GetInfo: entering...fInfoType=79
[1936]PGAPI_GetInfo: p='<NULL>', len=4, value=3, cbMax=4
[1936][SQLGetInfo(30)]
[1936]PGAPI_GetInfo: entering...fInfoType=83
[1936]PGAPI_GetInfo: p='<NULL>', len=4, value=0, cbMax=4
[1936][SQLGetInfo(30)]
[1936]PGAPI_GetInfo: entering...fInfoType=78
[1936]PGAPI_GetInfo: p='<NULL>', len=4, value=1, cbMax=4
[1936][SQLGetInfo(30)]
[1936]PGAPI_GetInfo: entering...fInfoType=81
[1936]PGAPI_GetInfo: p='<NULL>', len=4, value=15, cbMax=4
[1936][SQLGetInfo(30)]
[1936]PGAPI_GetInfo: entering...fInfoType=72
[1936]PGAPI_GetInfo: p='<NULL>', len=4, value=10, cbMax=4
[1936][SQLGetInfo(30)]
[1936]PGAPI_GetInfo: entering...fInfoType=82
[1936]PGAPI_GetInfo: p='<NULL>', len=4, value=90, cbMax=4
[1936][SQLGetInfo(30)]
[1936]PGAPI_GetInfo: entering...fInfoType=44
[1936]PGAPI_GetInfo: p='<NULL>', len=4, value=17, cbMax=4
[1936][SQLGetInfo(30)]
[1936]PGAPI_GetInfo: entering...fInfoType=43
[1936]PGAPI_GetInfo: p='<NULL>', len=4, value=1, cbMax=4
[1936][SQLGetInfo(30)]
[1936]PGAPI_GetInfo: entering...fInfoType=144
[1936]PGAPI_GetInfo30: p='<NULL>', len=4, value=0, cbMax=4
[1936][SQLGetInfo(30)]
[1936]PGAPI_GetInfo: entering...fInfoType=150
[1936]PGAPI_GetInfo30: p='<NULL>', len=4, value=4687, cbMax=4
[1936][SQLGetInfo(30)]
[1936]PGAPI_GetInfo: entering...fInfoType=167
[1936]PGAPI_GetInfo30: p='<NULL>', len=4, value=4687, cbMax=4
[1936][SQLGetInfo(30)]
[1936]PGAPI_GetInfo: entering...fInfoType=146
[1936]PGAPI_GetInfo30: p='<NULL>', len=4, value=1, cbMax=4
[1936][SQLGetInfo(30)]
[1936]PGAPI_GetInfo: entering...fInfoType=151
[1936]PGAPI_GetInfo30: p='<NULL>', len=4, value=1, cbMax=4
[1936][SQLGetInfo(30)]
[1936]PGAPI_GetInfo: entering...fInfoType=168
[1936]PGAPI_GetInfo30: p='<NULL>', len=4, value=1, cbMax=4
[1936][SQLGetInfo(30)]
[1936]PGAPI_GetInfo: entering...fInfoType=111
[1936]PGAPI_GetInfo: p='N', len=1, value=0, cbMax=512
[1936][SQLGetInfo(30)]
[1936]PGAPI_GetInfo: entering...fInfoType=16
[1936]PGAPI_GetInfo: p='', len=0, value=0, cbMax=0
[1936][SQLGetInfo(30)]
[1936]PGAPI_GetInfo: entering...fInfoType=23
[1936]PGAPI_GetInfo: p='<NULL>', len=2, value=2, cbMax=4
[1936][SQLGetInfo(30)]
[1936]PGAPI_GetInfo: entering...fInfoType=24
[1936]PGAPI_GetInfo: p='<NULL>', len=2, value=2, cbMax=4
[1936][SQLGetInfo(30)]
[1936]PGAPI_GetInfo: entering...fInfoType=46
[1936]PGAPI_GetInfo: p='<NULL>', len=2, value=2, cbMax=4
[1936][[SQLSetConnectAttr]] 1
[1936]PGAPI_SetConnectAttr 1
[1936]PGAPI_SetConnectOption: entering fOption = 1 vParam = 0
[1936]SetStmtOption(): SQL_MAX_ROWS, vParam = 0
[1936][[SQLSetConnectAttr]] 0
[1936]PGAPI_SetConnectAttr 0
[1936]PGAPI_SetConnectOption: entering fOption = 0 vParam = 0
[1936]SetStmtOption: SQL_QUERY_TIMEOUT, vParam = 0
[1936][SQLGetInfo(30)]
[1936]PGAPI_GetInfo: entering...fInfoType=6
[1936]PGAPI_GetInfo: p='PSQLODBCA.DLL', len=13, value=0, cbMax=52
[1936][SQLGetInfo(30)]
[1936]PGAPI_GetInfo: entering...fInfoType=6
[1936]PGAPI_GetInfo: p='PSQLODBCA.DLL', len=13, value=0, cbMax=52
[1936][SQLGetInfo(30)]
[1936]PGAPI_GetInfo: entering...fInfoType=6
[1936]PGAPI_GetInfo: p='PSQLODBCA.DLL', len=13, value=0, cbMax=52
[1936][SQLGetInfo(30)]
[1936]PGAPI_GetInfo: entering...fInfoType=6
[1936]PGAPI_GetInfo: p='PSQLODBCA.DLL', len=13, value=0, cbMax=48
[1936][SQLGetInfo(30)]
[1936]PGAPI_GetInfo: entering...fInfoType=1
[1936]PGAPI_GetInfo: p='<NULL>', len=2, value=0, cbMax=4
[1936][[SQLGetConnectAttr]] 109
[1936]PGAPI_GetConnectAttr 109
[1936]PGAPI_GetConnectOption: entering...
[1936][SQLGetInfo(30)]
[1936]PGAPI_GetInfo: entering...fInfoType=26
[1936]PGAPI_GetInfo: p='<NULL>', len=4, value=2, cbMax=4
[1936][[SQLAllocHandle]]
[1936]PGAPI_AllocStmt: entering...
[1936]**** PGAPI_AllocStmt: hdbc = 88422504, stmt = 88434208
[1936]CC_add_statement: self=88422504, stmt=88434208
[1936][[SQLGetStmtAttr]] Handle=88434208 10010
[1936]PGAPI_GetStmtAttr Handle=88434208 10010
[1936][[SQLGetStmtAttr]] Handle=88434208 10011
[1936]PGAPI_GetStmtAttr Handle=88434208 10011
[1936][[SQLGetStmtAttr]] Handle=88434208 10012
[1936]PGAPI_GetStmtAttr Handle=88434208 10012
[1936][[SQLGetStmtAttr]] Handle=88434208 10013
[1936]PGAPI_GetStmtAttr Handle=88434208 10013
[1936][[SQLSetStmtAttr]] Handle=88434208 18,10
[1936]PGAPI_SetStmtAttr Handle=88434208 18,10
[1936][[SQLSetStmtAttr]] Handle=88434208 18,0
[1936]PGAPI_SetStmtAttr Handle=88434208 18,0
[1936][[SQLSetStmtAttr]] Handle=88434208 17,1307176
[1936]PGAPI_SetStmtAttr Handle=88434208 17,1307176
[1936][[SQLSetStmtAttr]] Handle=88434208 17,0
[1936]PGAPI_SetStmtAttr Handle=88434208 17,0
[1936][[SQLSetDescField]] h=88434428 rec=1 field=1004 val=13f228
[1936]PGAPI_SetDescField h=88434428 rec=1 field=1004 val=13f228,0
[1936]DESCRIPTOR ERROR: func=PGAPI_SetDescField, desc='', errnum=11, errmsg='bad parameter number'
[1936][[SQLFreeHandle]]
[1936]PGAPI_FreeStmt: entering...hstmt=88434208, fOption=1
[1936]SC_Destructor: self=88434208, self->result=0, self->hdbc=88422504
[1936]APD_free_params: ENTER, self=88434460
[1936]IPD_free_params: ENTER, self=88434524
[1936]PDATA_free_params: ENTER, self=88434640
[1936]ARDFields_free 54566a8 bookmark=5458e78[1936]ARD_unbind_cols freeall=1 allocated=0 bindings=0
[1936]APD_free_params: ENTER, self=88434460
[1936]IPD_free_params: ENTER, self=88434524
[1936]GDATA_unbind_cols freeall=1 allocated=0 gdata=0
[1936]SC_Destructor: EXIT
[1936][[SQLAllocHandle]]
[1936]PGAPI_AllocStmt: entering...
[1936]**** PGAPI_AllocStmt: hdbc = 88422504, stmt = 88434208
[1936]CC_add_statement: self=88422504, stmt=88434208
[1936][[SQLGetStmtAttr]] Handle=88434208 10010
[1936]PGAPI_GetStmtAttr Handle=88434208 10010
[1936][[SQLGetStmtAttr]] Handle=88434208 10011
[1936]PGAPI_GetStmtAttr Handle=88434208 10011
[1936][[SQLGetStmtAttr]] Handle=88434208 10012
[1936]PGAPI_GetStmtAttr Handle=88434208 10012
[1936][[SQLGetStmtAttr]] Handle=88434208 10013
[1936]PGAPI_GetStmtAttr Handle=88434208 10013
[1936][[SQLSetStmtAttr]] Handle=88434208 0,30
[1936]PGAPI_SetStmtAttr Handle=88434208 0,30
[1936]PGAPI_SetStmtOption: entering...
[1936]SetStmtOption: SQL_QUERY_TIMEOUT, vParam = 30
[1936][SQLFreeStmt]
[1936]PGAPI_FreeStmt: entering...hstmt=88434208, fOption=3
[1936]APD_free_params: ENTER, self=88434460
[1936]IPD_free_params: ENTER, self=88434524
[1936]PDATA_free_params: ENTER, self=88434640
[1936][SQLPrepare]
[1936]PGAPI_Prepare: entering...
[1936]**** PGAPI_Prepare: STMT_ALLOCATED, copy
[1936][SQLGetInfo(30)]
[1936]PGAPI_GetInfo: entering...fInfoType=36
[1936]PGAPI_GetInfo: p='Y', len=1, value=0, cbMax=4
[1936][SQLFreeStmt]
[1936]PGAPI_FreeStmt: entering...hstmt=88434208, fOption=3
[1936]APD_free_params: ENTER, self=88434460
[1936]IPD_free_params: ENTER, self=88434524
[1936]PDATA_free_params: ENTER, self=88434640
[1936][SQLPrepare]
[1936]PGAPI_Prepare: entering...
[1936]**** PGAPI_Prepare: STMT_READY, change SQL
[1936][[SQLGetStmtAttr]] Handle=88434208 6
[1936]PGAPI_GetStmtAttr Handle=88434208 6
[1936]PGAPI_GetStmtOption: entering...
[1936]GetStmtOption(): SQL_CURSOR_TYPE 0
[1936][[SQLGetStmtAttr]] Handle=88434208 7
[1936]PGAPI_GetStmtAttr Handle=88434208 7
[1936]PGAPI_GetStmtOption: entering...
[1936]GetStmtOption(): SQL_CONCURRENCY 1
[1936][[SQLGetConnectAttr]] 108
[1936]PGAPI_GetConnectAttr 108
[1936]PGAPI_GetConnectOption: entering...
[1936][SQLNumParams]
[1936]PGAPI_NumParams: entering...
[1936][SQLFreeStmt]
[1936]PGAPI_FreeStmt: entering...hstmt=88434208, fOption=0
[1936]recycle statement: self= 88434208
[1936]PDATA_free_params: ENTER, self=88434640
[1936][[SQLFreeHandle]]
[1936]PGAPI_FreeStmt: entering...hstmt=88434208, fOption=1
[1936]SC_Destructor: self=88434208, self->result=0, self->hdbc=88422504
[1936]APD_free_params: ENTER, self=88434460
[1936]IPD_free_params: ENTER, self=88434524
[1936]PDATA_free_params: ENTER, self=88434640
[1936]ARDFields_free 54566a8 bookmark=5458ea0
[1936]ARD_unbind_cols freeall=1 allocated=0 bindings=0
[1936]APD_free_params: ENTER, self=88434460
[1936]IPD_free_params: ENTER, self=88434524
[1936]GDATA_unbind_cols freeall=1 allocated=0 gdata=0
[1936]SC_Destructor: EXIT
[1936][SQLDisconnect]
[1936]PGAPI_Disconnect: entering...
[1936]PGAPI_Disconnect: about to CC_cleanup
[1936]in CC_Cleanup, self=88422504
[1936]after CC_abort
[1936]entering PGCONN_Destructor
[1936]exiting PGCONN_Destructor
[1936]after LIBPQ destructor
[1936]exit CC_Cleanup
[1936]PGAPI_Disconnect: done CC_cleanup
[1936]PGAPI_Disconnect: returning...
[1936][[SQLFreeHandle]]
[1936]PGAPI_FreeConnect: entering...
[1936]**** in PGAPI_FreeConnect: hdbc=88422504
[1936]enter CC_Destructor, self=88422504
[1936]in CC_Cleanup, self=88422504
[1936]after LIBPQ destructor
[1936]exit CC_Cleanup
[1936]after CC_Cleanup
[1936]after free statement holders
[1936]exit CC_Destructor
[1936]PGAPI_FreeConnect: returning...
[1936][[SQLFreeHandle]]
[1936]**** in PGAPI_FreeEnv: env = 88422448 **
[1936]in EN_Destructor, self=88422448
[1936]exit EN_Destructor: rv = 1
[1936] ok
I think I missed something.
Are you trying to call a stored procedure with strongly typed paramters?
I mean like with
param = new Parameter("EmployeeID",adTypeChar,5,"10019")
cmd.Parameters.Add param
cmd.Execute
Has anyone made the parameterized stored procedures work?
I've only made work stored procedures (PostgreSQL user-defined functions), using a constructed string like
sql = "select my_function_name('Brad',26,'M')".
cnn.Execute sql
Is there a parameterized statement, without a stored procedure/user defined function, that can just act
against a table, like "INSERT INTO mytable (fld1, file2) VALUES (?, ?)"
Adam Biser wrote:
> Ludek Finstrle wrote:
>
>> Please could you send here full mylog output (turn on in Global and
>> DataSource dialogs). It helps us to find the problematic place.
>>
> Attached you will find the mylog output as well as the comm output (if
> that will help).
>
> Thanks,
> Adam
>
>
> ------------------------------------------------------------------------
>
> Global Options: Version='08.01.0102', fetch=100, socket=0, unknown_sizes=254, max_varchar_size=8190,
max_longvarchar_size=0
> disable_optimizer=0, ksqo=0, unique_index=1, use_declarefetch=0
> text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1 NAMEDATALEN=64
> extra_systable_prefixes='dd_;', conn_settings='' conn_encoding='OTHER'
> conn=88422504, query='select version()'
> [ PostgreSQL version string = 'PostgreSQL 8.1.1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
(mingw-special)']
> [ PostgreSQL version number = '8.1' ]
> conn=88422504, query='set DateStyle to 'ISO''
> conn=88422504, query='set extra_float_digits to 2'
> conn=88422504, query='select oid from pg_type where typname='lo''
> conn=88422504, query='select pg_client_encoding()'
> conn=88422504, query='set client_encoding to 'latin9''
> conn=88422504, PGAPI_DriverConnect(out)='DSN=GALSTA
Postgres;DATABASE=GALSTA;SERVER=localhost;PORT=5432;SSLMODE=disable;UID=ADAM;PWD=xxxxxx;ReadOnly=0;Protocol=6.4;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=8192;UnknownSizes=0;MaxVarcharSize=254;MaxLongVarcharSize=8190;Debug=1;CommLog=1;Optimizer=0;Ksqo=0;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;;LFConversion=1;UpdatableCursors=0;DisallowPremature=0;TrueIsMinus1=1;BI=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=1;LowerCaseIdentifier=0'
> DESCRIPTOR ERROR: func=PGAPI_SetDescField, desc='', errnum=11, errmsg='bad parameter number'
> conn=88422504, PGAPI_Disconnect
>
>
>
> ------------------------------------------------------------------------
>
> [1936][[SQLAllocHandle]]
> [1936]**** in PGAPI_AllocEnv **
> [1936]** exit PGAPI_AllocEnv: phenv = 88422448 **
> [1936][[SQLSetEnvAttr]] att=200,2
> [1936][[SQLAllocHandle]]
> [1936]PGAPI_AllocConnect: entering...
> [1936]**** PGAPI_AllocConnect: henv = 88422448, conn = 88422504
> [1936]EN_add_connection: self = 88422448, conn = 88422504
> [1936] added at i =0, conn->henv = 88422448, conns[i]->henv = 88422448
> [1936][SQLGetInfo(30)]
> [1936]PGAPI_GetInfo: entering...fInfoType=77
> [1936]PGAPI_GetInfo: p='03.00', len=5, value=0, cbMax=12
> [1936][[SQLSetConnectAttr]] 103
> [1936]PGAPI_SetConnectAttr 103
> [1936]PGAPI_SetConnectOption: entering fOption = 103 vParam = 15
> [1936][SQLDriverConnect]
> [1936]PGAPI_DriverConnect: entering...
> [1936]**** PGAPI_DriverConnect: fDriverCompletion=0, connStrIn='DSN=GALSTA Postgres;UID=ADAM;PWD=xxxxxx;'
> [1936]our_connect_string = 'DSN=GALSTA Postgres;UID=ADAM;PWD=xxxxxx;'
> [1936]attribute = 'DSN', value = 'GALSTA Postgres'
> [1936]copyAttributes: DSN='GALSTA
Postgres',server='',dbase='',user='',passwd='xxxxx',port='',sslmode='',onlyread='',protocol='',conn_settings='',disallow_premature=-1)
> [1936]attribute = 'UID', value = 'ADAM'
> [1936]copyAttributes: DSN='GALSTA
Postgres',server='',dbase='',user='ADAM',passwd='xxxxx',port='',sslmode='',onlyread='',protocol='',conn_settings='',disallow_premature=-1)
> [1936]attribute = 'PWD', value = 'xxxxx'
> [1936]copyAttributes: DSN='GALSTA
Postgres',server='',dbase='',user='ADAM',passwd='xxxxx',port='',sslmode='',onlyread='',protocol='',conn_settings='',disallow_premature=-1)
> [1936]globals.extra_systable_prefixes = 'dd_;'
> [1936]globals.extra_systable_prefixes = 'dd_;'
> [1936]our_connect_string = 'DSN=GALSTA Postgres;UID=ADAM;PWD=xxxxxx;'
> [1936]attribute = 'DSN', value = 'GALSTA Postgres'
> [1936]CopyCommonAttributes:
A7=100;A8=8192;A9=0;B0=254;B1=8190;B2=1;B3=1;B4=0;B5=0;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;[1936]attribute= 'UID',
value= 'ADAM'
> [1936]CopyCommonAttributes:
A7=100;A8=8192;A9=0;B0=254;B1=8190;B2=1;B3=1;B4=0;B5=0;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;[1936]attribute= 'PWD',
value= 'xxxxx'
> [1936]CopyCommonAttributes:
A7=100;A8=8192;A9=0;B0=254;B1=8190;B2=1;B3=1;B4=0;B5=0;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;[1936]CC_connect:
entering...
> [1936]CC_connect(): DSN = 'GALSTA Postgres', server = 'localhost', port = '5432', sslmode = 'disable', database =
'GALSTA',username = 'ADAM', password='xxxxx'
> [1936]connecting to the server
> [1936]connecting to the database using localhost as the server
> [1936]the size is 15
> [1936]connection to the database succeeded.
> [1936]connection to the database succeeded.
> [1936]CC_lookup_pg_version: entering...
> [1936]PGAPI_AllocStmt: entering...
> [1936]**** PGAPI_AllocStmt: hdbc = 88422504, stmt = 88434208
> [1936]CC_add_statement: self=88422504, stmt=88434208
> [1936]PGAPI_ExecDirect: entering...
> [1936]recycle statement: self= 88434208
> [1936]PDATA_free_params: ENTER, self=88434640
> [1936]**** PGAPI_ExecDirect: hstmt=88434208, statement='select version()'
> [1936]PGAPI_ExecDirect: calling PGAPI_Execute...
> [1936]PGAPI_Execute: entering...
> [1936]PGAPI_Execute: clear errors...
> [1936]recycle statement: self= 88434208
> [1936]PDATA_free_params: ENTER, self=88434640
> [1936]Exec_with_parameters_resolved: copying statement params: trans_status=1, len=16, stmt='select version()'
> [1936] stmt_with_params = 'select version()'
> [1936] Sending SELECT statement on stmt=88434208, cursor_name='SQL_CUR05456620'
> [1936]send_query(): conn=88422504, query='select version()'
> [1936]in QR_Constructor
> [1936]exit QR_Constructor
> [1936]in TL_Constructor
> [1936]exit TL_Constructor
> [1936]send_query: done sending query
> [1936]QR_fetch_tuples: cursor = '', self->cursor=0
> [1936]QR_fetch_tuples: past CI_read_fields: num_fields = 1
> [1936]MALLOC: tuple_size = 100, size = 800
> [1936] done sending the query:
> [1936]extend_column_bindings: entering ... self=88434344, bindings_allocated=0, num_columns=1
> [1936]exit extend_column_bindings
> [1936]PGAPI_ExecDirect: returned 0 from PGAPI_Execute
> [1936]PGAPI_Fetch: stmt = 88434208, stmt->result= 88438976
> [1936]manual_result = 1, use_declarefetch = 0
> [1936]**** SC_fetch: manual_result
> [1936]extend_getdata_info: entering ... self=88434564, gdata_allocated=0, num_columns=1
> [1936]exit extend_gdata_info
> [1936]fetch: cols=1, lf=0, opts = 88434344, opts->bindings = 88444616, buffer[] = 0
> [1936]PGAPI_GetData: enter, stmt=88434208
> [1936] value = 'PostgreSQL 8.1.1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)'
> [1936]**** PGAPI_GetData: icol = 0, fCType = 1, field_type = 25, value = 'PostgreSQL 8.1.1 on i686-pc-mingw32,
compiledby GCC gcc.exe (GCC) 3.4.2 (mingw-special)'
> [1936]copy_and_convert: field_type = 25, fctype = 1, value = 'PostgreSQL 8.1.1 on i686-pc-mingw32, compiled by GCC
gcc.exe(GCC) 3.4.2 (mingw-special)', cbValueMax=128
> [1936]DEFAULT: len = 88, ptr = 'PostgreSQL 8.1.1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
(mingw-special)'
> [1936] SQL_C_CHAR, default: len = 88, cbValueMax = 128, rgbValueBindRow = 'PostgreSQL 8.1.1 on i686-pc-mingw32,
compiledby GCC gcc.exe (GCC) 3.4.2 (mingw-special)'
> [1936]Got the PostgreSQL version string: 'PostgreSQL 8.1.1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
(mingw-special)'
> [1936]Extracted PostgreSQL version number: '8.1'
> [1936]PGAPI_FreeStmt: entering...hstmt=88434208, fOption=1
> [1936]QResult: in DESTRUCTOR
> [1936]TupleList: in DESTRUCTOR
> [1936]TupleList: exit DESTRUCTOR
> [1936]QResult: free memory in, fcount=101
> [1936]QResult: free memory out
> [1936]QResult: exit DESTRUCTOR
> [1936]SC_Destructor: self=88434208, self->result=0, self->hdbc=88422504
> [1936]APD_free_params: ENTER, self=88434460
> [1936]IPD_free_params: ENTER, self=88434524
> [1936]PDATA_free_params: ENTER, self=88434640
> [1936]ARDFields_free 54566a8 bookmark=5457a28[1936]ARD_unbind_cols freeall=1 allocated=1 bindings=5458ec8
> [1936]reset_a_column_binding: entering ... self=88434344, bindings_allocated=1, icol=1
> [1936]APD_free_params: ENTER, self=88434460
> [1936]IPD_free_params: ENTER, self=88434524
> [1936]GDATA_unbind_cols freeall=1 allocated=1 gdata=5458ef0
> [1936]SC_Destructor: EXIT
> [1936]CC_send_settings: entering...
> [1936]PGAPI_AllocStmt: entering...
> [1936]**** PGAPI_AllocStmt: hdbc = 88422504, stmt = 88434208
> [1936]CC_add_statement: self=88422504, stmt=88434208
> [1936]PGAPI_ExecDirect: entering...
> [1936]recycle statement: self= 88434208
> [1936]PDATA_free_params: ENTER, self=88434640
> [1936]**** PGAPI_ExecDirect: hstmt=88434208, statement='set DateStyle to 'ISO''
> [1936]PGAPI_ExecDirect: calling PGAPI_Execute...
> [1936]PGAPI_Execute: entering...
> [1936]PGAPI_Execute: clear errors...
> [1936]recycle statement: self= 88434208
> [1936]PDATA_free_params: ENTER, self=88434640
> [1936]Exec_with_parameters_resolved: copying statement params: trans_status=1, len=22, stmt='set DateStyle to 'ISO''
> [1936] stmt_with_params = 'set DateStyle to 'ISO''
> [1936] it's NOT a select statement: stmt=88434208
> [1936]send_query(): conn=88422504, query='set DateStyle to 'ISO''
> [1936]in QR_Constructor
> [1936]exit QR_Constructor
> [1936]The query was executed successfully and the query did not return any result
> [1936]send_query: done sending query
> [1936]QR_fetch_tuples: cursor = '', self->cursor=0
> [1936]QR_fetch_tuples: past CI_read_fields: num_fields = 0
> [1936]MALLOC: tuple_size = 100, size = 0
> [1936]PGAPI_ExecDirect: returned 0 from PGAPI_Execute
> [1936]CC_send_settings: result 0, status 1 from set DateStyle
> [1936]PGAPI_ExecDirect: entering...
> [1936]recycle statement: self= 88434208
> [1936]QResult: in DESTRUCTOR
> [1936]QResult: free memory in, fcount=101
> [1936]QResult: free memory out
> [1936]QResult: exit DESTRUCTOR
> [1936]PDATA_free_params: ENTER, self=88434640
> [1936]**** PGAPI_ExecDirect: hstmt=88434208, statement='set extra_float_digits to 2'
> [1936]PGAPI_ExecDirect: calling PGAPI_Execute...
> [1936]PGAPI_Execute: entering...
> [1936]PGAPI_Execute: clear errors...
> [1936]recycle statement: self= 88434208
> [1936]PDATA_free_params: ENTER, self=88434640
> [1936]Exec_with_parameters_resolved: copying statement params: trans_status=1, len=27, stmt='set extra_float_digits
to2'
> [1936] stmt_with_params = 'set extra_float_digits to 2'
> [1936] it's NOT a select statement: stmt=88434208
> [1936]send_query(): conn=88422504, query='set extra_float_digits to 2'
> [1936]in QR_Constructor
> [1936]exit QR_Constructor
> [1936]The query was executed successfully and the query did not return any result
> [1936]send_query: done sending query
> [1936]QR_fetch_tuples: cursor = '', self->cursor=0
> [1936]QR_fetch_tuples: past CI_read_fields: num_fields = 0
> [1936]MALLOC: tuple_size = 100, size = 0
> [1936]PGAPI_ExecDirect: returned 0 from PGAPI_Execute
> [1936]CC_send_settings: result 0, status 1 from set extra_float_digits
> [1936]PGAPI_FreeStmt: entering...hstmt=88434208, fOption=1
> [1936]QResult: in DESTRUCTOR
> [1936]QResult: free memory in, fcount=101
> [1936]QResult: free memory out
> [1936]QResult: exit DESTRUCTOR
> [1936]SC_Destructor: self=88434208, self->result=0, self->hdbc=88422504
> [1936]APD_free_params: ENTER, self=88434460
> [1936]IPD_free_params: ENTER, self=88434524
> [1936]PDATA_free_params: ENTER, self=88434640
> [1936]ARDFields_free 54566a8 bookmark=5458ea0[1936]ARD_unbind_cols freeall=1 allocated=0 bindings=0
> [1936]APD_free_params: ENTER, self=88434460
> [1936]IPD_free_params: ENTER, self=88434524
> [1936]GDATA_unbind_cols freeall=1 allocated=0 gdata=0
> [1936]SC_Destructor: EXIT
> [1936]CC_lookup_lo: entering...
> [1936]PGAPI_AllocStmt: entering...
> [1936]**** PGAPI_AllocStmt: hdbc = 88422504, stmt = 88434208
> [1936]CC_add_statement: self=88422504, stmt=88434208
> [1936]PGAPI_ExecDirect: entering...
> [1936]recycle statement: self= 88434208
> [1936]PDATA_free_params: ENTER, self=88434640
> [1936]**** PGAPI_ExecDirect: hstmt=88434208, statement='select oid from pg_type where typname='lo''
> [1936]PGAPI_ExecDirect: calling PGAPI_Execute...
> [1936]PGAPI_Execute: entering...
> [1936]PGAPI_Execute: clear errors...
> [1936]recycle statement: self= 88434208
> [1936]PDATA_free_params: ENTER, self=88434640
> [1936]Exec_with_parameters_resolved: copying statement params: trans_status=1, len=42, stmt='select oid from pg_type
wheretypname='lo''
> [1936] stmt_with_params = 'select oid from pg_type where typname='lo''
> [1936] Sending SELECT statement on stmt=88434208, cursor_name='SQL_CUR05456620'
> [1936]send_query(): conn=88422504, query='select oid from pg_type where typname='lo''
> [1936]in QR_Constructor
> [1936]exit QR_Constructor
> [1936]in TL_Constructor
> [1936]exit TL_Constructor
> [1936]send_query: done sending query
> [1936]QR_fetch_tuples: cursor = '', self->cursor=0
> [1936]QR_fetch_tuples: past CI_read_fields: num_fields = 1
> [1936]MALLOC: tuple_size = 100, size = 800
> [1936] done sending the query:
> [1936]extend_column_bindings: entering ... self=88434344, bindings_allocated=0, num_columns=1
> [1936]exit extend_column_bindings
> [1936]PGAPI_ExecDirect: returned 0 from PGAPI_Execute
> [1936]PGAPI_Fetch: stmt = 88434208, stmt->result= 88438976
> [1936]manual_result = 1, use_declarefetch = 0
> [1936]PGAPI_FreeStmt: entering...hstmt=88434208, fOption=1
> [1936]QResult: in DESTRUCTOR
> [1936]TupleList: in DESTRUCTOR
> [1936]TupleList: exit DESTRUCTOR
> [1936]QResult: free memory in, fcount=101
> [1936]QResult: free memory out
> [1936]QResult: exit DESTRUCTOR
> [1936]SC_Destructor: self=88434208, self->result=0, self->hdbc=88422504
> [1936]APD_free_params: ENTER, self=88434460
> [1936]IPD_free_params: ENTER, self=88434524
> [1936]PDATA_free_params: ENTER, self=88434640
> [1936]ARDFields_free 54566a8 bookmark=5458ea0[1936]ARD_unbind_cols freeall=1 allocated=1 bindings=5458e78
> [1936]reset_a_column_binding: entering ... self=88434344, bindings_allocated=1, icol=1
> [1936]APD_free_params: ENTER, self=88434460
> [1936]IPD_free_params: ENTER, self=88434524
> [1936]GDATA_unbind_cols freeall=1 allocated=0 gdata=0
> [1936]SC_Destructor: EXIT
> [1936]CC_lookup_characterset: entering...
> [1936]send_query(): conn=88422504, query='select pg_client_encoding()'
> [1936]in QR_Constructor
> [1936]exit QR_Constructor
> [1936]in TL_Constructor
> [1936]exit TL_Constructor
> [1936]send_query: done sending query
> [1936]QR_fetch_tuples: cursor = '', self->cursor=0
> [1936]QR_fetch_tuples: past CI_read_fields: num_fields = 1
> [1936]MALLOC: tuple_size = 100, size = 800
> [1936]QResult: in DESTRUCTOR
> [1936]TupleList: in DESTRUCTOR
> [1936]TupleList: exit DESTRUCTOR
> [1936]QResult: free memory in, fcount=101
> [1936]QResult: free memory out
> [1936]QResult: exit DESTRUCTOR
> [1936]send_query(): conn=88422504, query='set client_encoding to 'latin9''
> [1936]in QR_Constructor
> [1936]exit QR_Constructor
> [1936]The query was executed successfully and the query did not return any result
> [1936]send_query: done sending query
> [1936]QR_fetch_tuples: cursor = '', self->cursor=0
> [1936]QR_fetch_tuples: past CI_read_fields: num_fields = 0
> [1936]MALLOC: tuple_size = 100, size = 0
> [1936]QResult: in DESTRUCTOR
> [1936]QResult: free memory in, fcount=101
> [1936]QResult: free memory out
> [1936]QResult: exit DESTRUCTOR
> [1936]CC_connect: returning...
> [1936]szConnStrOut = 'DSN=GALSTA
Postgres;DATABASE=GALSTA;SERVER=localhost;PORT=5432;SSLMODE=disable;UID=ADAM;PWD=xxxxxx;ReadOnly=0;Protocol=6.4;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=8192;UnknownSizes=0;MaxVarcharSize=254;MaxLongVarcharSize=8190;Debug=1;CommLog=1;Optimizer=0;Ksqo=0;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;;LFConversion=1;UpdatableCursors=0;DisallowPremature=0;TrueIsMinus1=1;BI=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=1;LowerCaseIdentifier=0'
len=591,1024
> [1936]PGAPI_DriverConnect: returning 0
> [1936][[SQLGetDiagRec]]
> [1936]PGAPI_GetDiagRec entering rec=1
> [1936]**** PGAPI_ConnectError: hdbc=88422504 <0>
> [1936]enter CC_get_error
> [1936]enter CC_create_errormsg
> [1936]msg = ''
> [1936]exit CC_create_errormsg
> [1936]exit CC_get_error
> [1936]CC_Get_error returned nothing.
> [1936]PGAPI_GetDiagRec exiting 100
> [1936][SQLGetFunctions]
> [1936][SQLGetInfo(30)]
> [1936]PGAPI_GetInfo: entering...fInfoType=23
> [1936]PGAPI_GetInfo: p='<NULL>', len=2, value=2, cbMax=2
> [1936][SQLGetInfo(30)]
> [1936]PGAPI_GetInfo: entering...fInfoType=24
> [1936]PGAPI_GetInfo: p='<NULL>', len=2, value=2, cbMax=2
> [1936][SQLGetInfo(30)]
> [1936]PGAPI_GetInfo: entering...fInfoType=79
> [1936]PGAPI_GetInfo: p='<NULL>', len=4, value=3, cbMax=4
> [1936][SQLGetInfo(30)]
> [1936]PGAPI_GetInfo: entering...fInfoType=83
> [1936]PGAPI_GetInfo: p='<NULL>', len=4, value=0, cbMax=4
> [1936][SQLGetInfo(30)]
> [1936]PGAPI_GetInfo: entering...fInfoType=78
> [1936]PGAPI_GetInfo: p='<NULL>', len=4, value=1, cbMax=4
> [1936][SQLGetInfo(30)]
> [1936]PGAPI_GetInfo: entering...fInfoType=81
> [1936]PGAPI_GetInfo: p='<NULL>', len=4, value=15, cbMax=4
> [1936][SQLGetInfo(30)]
> [1936]PGAPI_GetInfo: entering...fInfoType=72
> [1936]PGAPI_GetInfo: p='<NULL>', len=4, value=10, cbMax=4
> [1936][SQLGetInfo(30)]
> [1936]PGAPI_GetInfo: entering...fInfoType=82
> [1936]PGAPI_GetInfo: p='<NULL>', len=4, value=90, cbMax=4
> [1936][SQLGetInfo(30)]
> [1936]PGAPI_GetInfo: entering...fInfoType=44
> [1936]PGAPI_GetInfo: p='<NULL>', len=4, value=17, cbMax=4
> [1936][SQLGetInfo(30)]
> [1936]PGAPI_GetInfo: entering...fInfoType=43
> [1936]PGAPI_GetInfo: p='<NULL>', len=4, value=1, cbMax=4
> [1936][SQLGetInfo(30)]
> [1936]PGAPI_GetInfo: entering...fInfoType=144
> [1936]PGAPI_GetInfo30: p='<NULL>', len=4, value=0, cbMax=4
> [1936][SQLGetInfo(30)]
> [1936]PGAPI_GetInfo: entering...fInfoType=150
> [1936]PGAPI_GetInfo30: p='<NULL>', len=4, value=4687, cbMax=4
> [1936][SQLGetInfo(30)]
> [1936]PGAPI_GetInfo: entering...fInfoType=167
> [1936]PGAPI_GetInfo30: p='<NULL>', len=4, value=4687, cbMax=4
> [1936][SQLGetInfo(30)]
> [1936]PGAPI_GetInfo: entering...fInfoType=146
> [1936]PGAPI_GetInfo30: p='<NULL>', len=4, value=1, cbMax=4
> [1936][SQLGetInfo(30)]
> [1936]PGAPI_GetInfo: entering...fInfoType=151
> [1936]PGAPI_GetInfo30: p='<NULL>', len=4, value=1, cbMax=4
> [1936][SQLGetInfo(30)]
> [1936]PGAPI_GetInfo: entering...fInfoType=168
> [1936]PGAPI_GetInfo30: p='<NULL>', len=4, value=1, cbMax=4
> [1936][SQLGetInfo(30)]
> [1936]PGAPI_GetInfo: entering...fInfoType=111
> [1936]PGAPI_GetInfo: p='N', len=1, value=0, cbMax=512
> [1936][SQLGetInfo(30)]
> [1936]PGAPI_GetInfo: entering...fInfoType=16
> [1936]PGAPI_GetInfo: p='', len=0, value=0, cbMax=0
> [1936][SQLGetInfo(30)]
> [1936]PGAPI_GetInfo: entering...fInfoType=23
> [1936]PGAPI_GetInfo: p='<NULL>', len=2, value=2, cbMax=4
> [1936][SQLGetInfo(30)]
> [1936]PGAPI_GetInfo: entering...fInfoType=24
> [1936]PGAPI_GetInfo: p='<NULL>', len=2, value=2, cbMax=4
> [1936][SQLGetInfo(30)]
> [1936]PGAPI_GetInfo: entering...fInfoType=46
> [1936]PGAPI_GetInfo: p='<NULL>', len=2, value=2, cbMax=4
> [1936][[SQLSetConnectAttr]] 1
> [1936]PGAPI_SetConnectAttr 1
> [1936]PGAPI_SetConnectOption: entering fOption = 1 vParam = 0
> [1936]SetStmtOption(): SQL_MAX_ROWS, vParam = 0
> [1936][[SQLSetConnectAttr]] 0
> [1936]PGAPI_SetConnectAttr 0
> [1936]PGAPI_SetConnectOption: entering fOption = 0 vParam = 0
> [1936]SetStmtOption: SQL_QUERY_TIMEOUT, vParam = 0
> [1936][SQLGetInfo(30)]
> [1936]PGAPI_GetInfo: entering...fInfoType=6
> [1936]PGAPI_GetInfo: p='PSQLODBCA.DLL', len=13, value=0, cbMax=52
> [1936][SQLGetInfo(30)]
> [1936]PGAPI_GetInfo: entering...fInfoType=6
> [1936]PGAPI_GetInfo: p='PSQLODBCA.DLL', len=13, value=0, cbMax=52
> [1936][SQLGetInfo(30)]
> [1936]PGAPI_GetInfo: entering...fInfoType=6
> [1936]PGAPI_GetInfo: p='PSQLODBCA.DLL', len=13, value=0, cbMax=52
> [1936][SQLGetInfo(30)]
> [1936]PGAPI_GetInfo: entering...fInfoType=6
> [1936]PGAPI_GetInfo: p='PSQLODBCA.DLL', len=13, value=0, cbMax=48
> [1936][SQLGetInfo(30)]
> [1936]PGAPI_GetInfo: entering...fInfoType=1
> [1936]PGAPI_GetInfo: p='<NULL>', len=2, value=0, cbMax=4
> [1936][[SQLGetConnectAttr]] 109
> [1936]PGAPI_GetConnectAttr 109
> [1936]PGAPI_GetConnectOption: entering...
> [1936][SQLGetInfo(30)]
> [1936]PGAPI_GetInfo: entering...fInfoType=26
> [1936]PGAPI_GetInfo: p='<NULL>', len=4, value=2, cbMax=4
> [1936][[SQLAllocHandle]]
> [1936]PGAPI_AllocStmt: entering...
> [1936]**** PGAPI_AllocStmt: hdbc = 88422504, stmt = 88434208
> [1936]CC_add_statement: self=88422504, stmt=88434208
> [1936][[SQLGetStmtAttr]] Handle=88434208 10010
> [1936]PGAPI_GetStmtAttr Handle=88434208 10010
> [1936][[SQLGetStmtAttr]] Handle=88434208 10011
> [1936]PGAPI_GetStmtAttr Handle=88434208 10011
> [1936][[SQLGetStmtAttr]] Handle=88434208 10012
> [1936]PGAPI_GetStmtAttr Handle=88434208 10012
> [1936][[SQLGetStmtAttr]] Handle=88434208 10013
> [1936]PGAPI_GetStmtAttr Handle=88434208 10013
> [1936][[SQLSetStmtAttr]] Handle=88434208 18,10
> [1936]PGAPI_SetStmtAttr Handle=88434208 18,10
> [1936][[SQLSetStmtAttr]] Handle=88434208 18,0
> [1936]PGAPI_SetStmtAttr Handle=88434208 18,0
> [1936][[SQLSetStmtAttr]] Handle=88434208 17,1307176
> [1936]PGAPI_SetStmtAttr Handle=88434208 17,1307176
> [1936][[SQLSetStmtAttr]] Handle=88434208 17,0
> [1936]PGAPI_SetStmtAttr Handle=88434208 17,0
> [1936][[SQLSetDescField]] h=88434428 rec=1 field=1004 val=13f228
> [1936]PGAPI_SetDescField h=88434428 rec=1 field=1004 val=13f228,0
> [1936]DESCRIPTOR ERROR: func=PGAPI_SetDescField, desc='', errnum=11, errmsg='bad parameter number'
> [1936][[SQLFreeHandle]]
> [1936]PGAPI_FreeStmt: entering...hstmt=88434208, fOption=1
> [1936]SC_Destructor: self=88434208, self->result=0, self->hdbc=88422504
> [1936]APD_free_params: ENTER, self=88434460
> [1936]IPD_free_params: ENTER, self=88434524
> [1936]PDATA_free_params: ENTER, self=88434640
> [1936]ARDFields_free 54566a8 bookmark=5458e78[1936]ARD_unbind_cols freeall=1 allocated=0 bindings=0
> [1936]APD_free_params: ENTER, self=88434460
> [1936]IPD_free_params: ENTER, self=88434524
> [1936]GDATA_unbind_cols freeall=1 allocated=0 gdata=0
> [1936]SC_Destructor: EXIT
> [1936][[SQLAllocHandle]]
> [1936]PGAPI_AllocStmt: entering...
> [1936]**** PGAPI_AllocStmt: hdbc = 88422504, stmt = 88434208
> [1936]CC_add_statement: self=88422504, stmt=88434208
> [1936][[SQLGetStmtAttr]] Handle=88434208 10010
> [1936]PGAPI_GetStmtAttr Handle=88434208 10010
> [1936][[SQLGetStmtAttr]] Handle=88434208 10011
> [1936]PGAPI_GetStmtAttr Handle=88434208 10011
> [1936][[SQLGetStmtAttr]] Handle=88434208 10012
> [1936]PGAPI_GetStmtAttr Handle=88434208 10012
> [1936][[SQLGetStmtAttr]] Handle=88434208 10013
> [1936]PGAPI_GetStmtAttr Handle=88434208 10013
> [1936][[SQLSetStmtAttr]] Handle=88434208 0,30
> [1936]PGAPI_SetStmtAttr Handle=88434208 0,30
> [1936]PGAPI_SetStmtOption: entering...
> [1936]SetStmtOption: SQL_QUERY_TIMEOUT, vParam = 30
> [1936][SQLFreeStmt]
> [1936]PGAPI_FreeStmt: entering...hstmt=88434208, fOption=3
> [1936]APD_free_params: ENTER, self=88434460
> [1936]IPD_free_params: ENTER, self=88434524
> [1936]PDATA_free_params: ENTER, self=88434640
> [1936][SQLPrepare]
> [1936]PGAPI_Prepare: entering...
> [1936]**** PGAPI_Prepare: STMT_ALLOCATED, copy
> [1936][SQLGetInfo(30)]
> [1936]PGAPI_GetInfo: entering...fInfoType=36
> [1936]PGAPI_GetInfo: p='Y', len=1, value=0, cbMax=4
> [1936][SQLFreeStmt]
> [1936]PGAPI_FreeStmt: entering...hstmt=88434208, fOption=3
> [1936]APD_free_params: ENTER, self=88434460
> [1936]IPD_free_params: ENTER, self=88434524
> [1936]PDATA_free_params: ENTER, self=88434640
> [1936][SQLPrepare]
> [1936]PGAPI_Prepare: entering...
> [1936]**** PGAPI_Prepare: STMT_READY, change SQL
> [1936][[SQLGetStmtAttr]] Handle=88434208 6
> [1936]PGAPI_GetStmtAttr Handle=88434208 6
> [1936]PGAPI_GetStmtOption: entering...
> [1936]GetStmtOption(): SQL_CURSOR_TYPE 0
> [1936][[SQLGetStmtAttr]] Handle=88434208 7
> [1936]PGAPI_GetStmtAttr Handle=88434208 7
> [1936]PGAPI_GetStmtOption: entering...
> [1936]GetStmtOption(): SQL_CONCURRENCY 1
> [1936][[SQLGetConnectAttr]] 108
> [1936]PGAPI_GetConnectAttr 108
> [1936]PGAPI_GetConnectOption: entering...
> [1936][SQLNumParams]
> [1936]PGAPI_NumParams: entering...
> [1936][SQLFreeStmt]
> [1936]PGAPI_FreeStmt: entering...hstmt=88434208, fOption=0
> [1936]recycle statement: self= 88434208
> [1936]PDATA_free_params: ENTER, self=88434640
> [1936][[SQLFreeHandle]]
> [1936]PGAPI_FreeStmt: entering...hstmt=88434208, fOption=1
> [1936]SC_Destructor: self=88434208, self->result=0, self->hdbc=88422504
> [1936]APD_free_params: ENTER, self=88434460
> [1936]IPD_free_params: ENTER, self=88434524
> [1936]PDATA_free_params: ENTER, self=88434640
> [1936]ARDFields_free 54566a8 bookmark=5458ea0
> [1936]ARD_unbind_cols freeall=1 allocated=0 bindings=0
> [1936]APD_free_params: ENTER, self=88434460
> [1936]IPD_free_params: ENTER, self=88434524
> [1936]GDATA_unbind_cols freeall=1 allocated=0 gdata=0
> [1936]SC_Destructor: EXIT
> [1936][SQLDisconnect]
> [1936]PGAPI_Disconnect: entering...
> [1936]PGAPI_Disconnect: about to CC_cleanup
> [1936]in CC_Cleanup, self=88422504
> [1936]after CC_abort
> [1936]entering PGCONN_Destructor
> [1936]exiting PGCONN_Destructor
> [1936]after LIBPQ destructor
> [1936]exit CC_Cleanup
> [1936]PGAPI_Disconnect: done CC_cleanup
> [1936]PGAPI_Disconnect: returning...
> [1936][[SQLFreeHandle]]
> [1936]PGAPI_FreeConnect: entering...
> [1936]**** in PGAPI_FreeConnect: hdbc=88422504
> [1936]enter CC_Destructor, self=88422504
> [1936]in CC_Cleanup, self=88422504
> [1936]after LIBPQ destructor
> [1936]exit CC_Cleanup
> [1936]after CC_Cleanup
> [1936]after free statement holders
> [1936]exit CC_Destructor
> [1936]PGAPI_FreeConnect: returning...
> [1936][[SQLFreeHandle]]
> [1936]**** in PGAPI_FreeEnv: env = 88422448 **
> [1936]in EN_Destructor, self=88422448
> [1936]exit EN_Destructor: rv = 1
> [1936] ok
>
>
>
> ------------------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
Attachment
Campbell, Greg wrote: > Is there a parameterized statement, without a stored procedure/user > defined function, that can just act against a table, like "INSERT INTO > mytable (fld1, file2) VALUES (?, ?)" Yes, there is, and this is what I'm trying to use. You set the .CommandType to adCmdText, I believe (I don't have the code on this machine). When using .Refresh (yes, I know it's probably a bit slower than adding them manually), the parameters would be added "behind-the-scenes" according to the table(s) against which you are running the command. It sounds like the whole parameters part, whether by stored procs or SQL are not yet implemented.
> >Please could you send here full mylog output (turn on in Global and > >DataSource dialogs). It helps us to find the problematic place. > > > Attached you will find the mylog output as well as the comm output (if > that will help). Are you sure you posted the right mylog output? I think this is output of the problem with parameter count = 0. I hoped you send mylog output with crash in Refresh. Please, could you send me steps which lead to problem (I don't know ADO). I don't fully understand what SQL command do you use, ... I don't see the SQL command from SQLPrepare in mylog output. Could you post it? Regards, Luf
Yes there is from the VB, data accees techniques side,.. or yes from the I or somebody I know has done this with pgodbc side? Adam Biser wrote: > Campbell, Greg wrote: > >> Is there a parameterized statement, without a stored procedure/user >> defined function, that can just act against a table, like "INSERT INTO >> mytable (fld1, file2) VALUES (?, ?)" > > > Yes, there is, and this is what I'm trying to use. You set the > .CommandType to adCmdText, I believe (I don't have the code on this > machine). When using .Refresh (yes, I know it's probably a bit slower > than adding them manually), the parameters would be added > "behind-the-scenes" according to the table(s) against which you are > running the command. > > It sounds like the whole parameters part, whether by stored procs or SQL > are not yet implemented. > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq
Attachment
Ludek Finstrle wrote: >Are you sure you posted the right mylog output? I think this is output >of the problem with parameter count = 0. I hoped you send mylog output >with crash in Refresh. > > I used the "DSN=DSN Name" connection string for this, not the "Provider=PostgreSQL.1;Data Source=localhost;Location=DB Name;" connection string. >Please, could you send me steps which lead to problem (I don't know ADO). >I don't fully understand what SQL command do you use, ... >I don't see the SQL command from SQLPrepare in mylog output. Could you >post it? > I will post the code I use to produce the error from work tomorrow. Thanks, Adam
Campbell, Greg wrote: > Yes there is from the VB, data accees techniques side,.. > or yes from the I or somebody I know has done this with pgodbc side? ADO supports parameterized SQL and DB2, which I normally work with, recommends them when executing multiple statements where only the data values change because it improves performance. So the only thing I can say is that I use them with the AS/400 and that, by Googling for "parameterized sql", others appear to use them on other systems. However, I can't get this to work with pgodbc. I will post code tomorrow so you and all can see what I mean.
oops,..goofy typing. Is that "Yes there is.., parameterization on a SELECT or DML statement is a valid VB/MS data access technique",.. or is that "Yes, I or someone I know has done this is pgodbc"? Campbell, Greg wrote: > Yes there is from the VB, data accees techniques side,.. > or yes from the I or somebody I know has done this with pgodbc side? > > > > Adam Biser wrote: > >> Campbell, Greg wrote: >> >>> Is there a parameterized statement, without a stored procedure/user >>> defined function, that can just act against a table, like "INSERT >>> INTO mytable (fld1, file2) VALUES (?, ?)" >> >> >> >> Yes, there is, and this is what I'm trying to use. You set the >> .CommandType to adCmdText, I believe (I don't have the code on this >> machine). When using .Refresh (yes, I know it's probably a bit slower >> than adding them manually), the parameters would be added >> "behind-the-scenes" according to the table(s) against which you are >> running the command. >> >> It sounds like the whole parameters part, whether by stored procs or >> SQL are not yet implemented. >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 3: Have you checked our extensive FAQ? >> >> http://www.postgresql.org/docs/faq > > > ------------------------------------------------------------------------ > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
Attachment
Thanks for clarifying. I too have used the Parametrization with command.Parameters collection in ADO with Jet/Access and MS SQL. I haven't personnaly used it with Oracle. I have never gotten it to work with PostgreSQL through any driver, although I saw a post once about it working with Npgsql for ADO.NET,..although when I tested it did not seem to work either. My test case was emulating MS stored procedures (Jet parameter queries) using the user defined function (alias stored procedures) in PostgreSQL. PostgreSQL seems to stubbornly like the "SELECT stored_proc_name(param1, param2,..)" format,..so... cmd.CommandText = "stored_proc_name" cmd.CommandType = adStoredProc cmd.Parameters.Append param cmd.Execute doesn't seem to work. Further, on Parameters there is "lazy" way and an OCD way. The lazy way is to set the statement...call cmd.Refresh,..to populate all the data types, and data sizes, then just provide the values to the parameters and execute. This way calls for an "extra" data round trip to the server to get and populate the parameters with just metadata. I'm not sure this works in pgodbc. The OCD way of course, is to... for each parameter, populate the data type and data size, and the data value. This is one less round-trip to the server, and is recommended for scaling and speed. Again, I haven't gotten this to work for stored procedures. I pass stored procedures/user defined functions,..command strings that I construct on the client "SELECT stored_proc_name(params)" My question on whether any one was doing this the parameterized way, was to try to dig out of the woodwork any of our more experience PG/ADO coders who might have stock routines for this stuff in the utility belts. Adam Biser wrote: > Campbell, Greg wrote: > >> Yes there is from the VB, data accees techniques side,.. >> or yes from the I or somebody I know has done this with pgodbc side? > > > ADO supports parameterized SQL and DB2, which I normally work with, > recommends them when executing multiple statements where only the data > values change because it improves performance. So the only thing I can > say is that I use them with the AS/400 and that, by Googling for > "parameterized sql", others appear to use them on other systems. > However, I can't get this to work with pgodbc. > > I will post code tomorrow so you and all can see what I mean. > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster
Attachment
> >Please, could you send me steps which lead to problem (I don't know ADO). > >I don't fully understand what SQL command do you use, ... > >I don't see the SQL command from SQLPrepare in mylog output. Could you > >post it? > > I will post the code I use to produce the error from work tomorrow. What's happen? I can't continue helping you without it. Regards, Luf
> What's happen? I can't continue helping you without it.
Sorry this is later than expected, things got a bit around here.
I attached the code so that spacing is preserved. It's in VB6 and just
a text file. The first method works, but none of the others do. I've
made note of the error I receive as comments in the code. If there's
anything else you need, just let me know.
Thank you,
Adam
Dim dbc As ADODB.Connection
Dim cmd As ADODB.Command
Set dbc = New ADODB.Connection
dbc.Open "DSN=GALSTA Postgres;", UserID, Password
' Verify that the connection opened properly.
If dbc.State And adStateOpen Then
Debug.Print "Connected"
' This method works.
' dbc.Execute "Insert into CHAPTER (CHAPTERID, CHAPTERDESC) values (1,'test1')"
' Start the second method.
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = dbc
cmd.CommandText = "Insert into CHAPTER (CHAPTERID, CHAPTERDESC) values (?,?)"
' Error on next line: Provider cannot derive parameter information and SetParameterInfo has not been called.
cmd.Parameters.Refresh
cmd.Parameters.Item(0).Value = 2
cmd.Parameters.Item(1).Value = "test2"
cmd.Execute
Set cmd = Nothing
' Start the third method.
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = dbc
cmd.CommandText = "Insert into CHAPTER (CHAPTERID, CHAPTERDESC) values (?,?)"
Call cmd.Parameters.Append(cmd.CreateParameter("ID", adNumeric, adParamInput, 4))
Call cmd.Parameters.Append(cmd.CreateParameter("DESC", adVarChar, adParamInput, 80))
cmd.Parameters.Item(0).Value = 3
cmd.Parameters.Item(1).Value = "test3"
' Error on next line: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if
available.No work was done.
cmd.Execute
Set cmd = Nothing
' Start the fourth method.
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = dbc
cmd.CommandText = "Insert into CHAPTER (CHAPTERID, CHAPTERDESC) values (?,?)"
Call cmd.Parameters.Append(cmd.CreateParameter("ID", adNumeric, adParamInput, 4, 4))
Call cmd.Parameters.Append(cmd.CreateParameter("DESC", adVarChar, adParamInput, 80, "test4"))
' Next line produces the same error as the third method.
cmd.Execute
Set cmd = Nothing
' Done
dbc.Close
End If
Set dbc = Nothing
> Sorry this is later than expected, things got a bit around here. Ok. I only ask becouse I hear nothing from you. > I attached the code so that spacing is preserved. It's in VB6 and just > a text file. The first method works, but none of the others do. I've > made note of the error I receive as comments in the code. If there's > anything else you need, just let me know. Please could you make something for me? Please remove first method. I know the first method is working and it only create longer mylog output and tracing too. I have no VB compiler :-( Please could you compile it for me? DSN=Postgres, user = bpref, passwd = refresh. Thank you Luf
Did you receive my last email (with the EXE attached)? I see that it didn't get posted to the group for some reason. Adam
Thu, Jan 12, 2006 at 06:13:46PM -0600, Adam Biser napsal(a): > Ludek Finstrle wrote: > >Yes, I got it. But I'm not closer to find the problem. I'm working on it > >for 3 hours (now). I only read on MS site that Parameters.Refresh can > >be used for SELECT statement. Where can I find documentation for > >Parameters in ADO? > > Does this link help you? > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscadoobjmod.asp Yes and no. It show me that the code you wrote is ok. But it doesn't give me an idea how to fix it :-( > Sorry this takes such a large chunk of your time. If I can be of any > addition assistance, please let me know! Could you test if the test app works with psqlodbc 07.03.0207? I have to solve two simplier bug reports. I hope I return my focus to this problem tomorrow or in the begin of next week. I have two hints but I don't know if it helps me. I found no documentation what ODBC driver has to support to work correctly with OLE DB to ODBC Microsoft bridge. And there is no error raising in psqlODBC mylog. Please be patient Luf
> Could you test if the test app works with psqlodbc 07.03.0207? > I have to solve two simplier bug reports. I hope I return my focus to > this problem tomorrow or in the begin of next week. I have two hints > but I don't know if it helps me. I found no documentation what > ODBC driver has to support to work correctly with OLE DB to ODBC > Microsoft bridge. And there is no error raising in psqlODBC mylog. I will gladly test this, but where can I find psqlodbc 07.03.0207? Take all the time you need. I appreciate your help. Adam
> I will gladly test this, but where can I find psqlodbc 07.03.0207? I found a zip with 07.03.0200. I will try it out as soon as possible and let you know what I find. It might be a day or two. Thanks, Adam
> >I will gladly test this, but where can I find psqlodbc 07.03.0207? > > I found a zip with 07.03.0200. I will try it out as soon as possible > and let you know what I find. It might be a day or two. I'm sorry stop this activities. I'll try it at home myself. It would be better when I do it. BTW maybe I have bad informations. It seems that psqlodbc doesn't fully support ODBC 3.0 specification. It's needed by OLE-DB to ODBC bridge that ODBC driver has to support 3.0 revision 1 at least. So maybe it's long term isssue. Regards, Luf
>BTW maybe I have bad informations. It seems that psqlodbc doesn't fully
>support ODBC 3.0 specification. It's needed by OLE-DB to ODBC bridge
>that ODBC driver has to support 3.0 revision 1 at least.
>So maybe it's long term isssue.
>
>
This may be off topic for this list, but if you need the OLE DB
interface, why not use PgOleDb instead?
Shachar
> > >I will gladly test this, but where can I find psqlodbc 07.03.0207? > > > > I found a zip with 07.03.0200. I will try it out as soon as possible > > and let you know what I find. It might be a day or two. > > I'm sorry stop this activities. I'll try it at home myself. > It would be better when I do it. I tried it againist 07.03 code and it fails to. > So maybe it's long term isssue. I'm sorry. It's long term issue. Regards, Luf
> This may be off topic for this list, but if you need the OLE DB > interface, why not use PgOleDb instead? Tried, it didn't seem to work with it either. The ODBC interface seemed to be more active, so I tried here first. :)
> I tried it againist 07.03 code and it fails to. Thanks. I had tried to use the 7.3 drivers, but I couldn't even connect with them. No problem about this being a long term issue. Take your time. I will check back.. maybe even do some poking around myself to see if I figure anything out that might be helpful. Thanks again, Adam
Adam Biser wrote:
>> This may be off topic for this list, but if you need the OLE DB
>> interface, why not use PgOleDb instead?
>
>
> Tried, it didn't seem to work with it either. The ODBC interface
> seemed to be more active, so I tried here first. :)
You mean the driver's development seemed more active :-)
Sadly, you are correct. I would still appreciate a note sent to the OLE
DB list, just so the problem is documented.
Shachar