Re: Show max_identifier_length causes heavy query execution

>If that is the cause of your problem, then setting "Parse=1"
>will fix it.
>
>In ODBC Administrator on Windows, this option is called "Parse Statements"
>and can be found in the advanced options page 1 (Button "Datasource").

Thank you Laurenz, setting "Parse=1" solved my problem!
Anyway, another problem appeared at another point in the application, i think this is the reason why that flag was turned off.
I simplified the query that gives the error, and obtained this :

SELECT 1 as prog UNION SELECT code from aaa; --This one gives error

where aaa is a previously created table (If I create the table in the same statement, I don't get the error) :

create table aaa(
code integer -- i tried also with character varying, it's the same
);

If I write the two select in reverse order it works

SELECT code as prog from aaa UNION SELECT 1; --This one works

>Without the ODBC statements it is difficult to say something conclusive,

I attach below the log file, but I don't know what are the "ODBC statements"...Is it the "mylog" file? I'm using ADO components to connect to the database, so I don't know very much about ODBC, I'm sorry..

I analized the mylog in both cases and noticed this difference:

(This is the correct one)
[2956-2.138]parse_statement: entering...
[2956-2.138]unquoted=1, quote=0, dquote=0, numeric=0, delim=' ', token='SELECT', ptr='code as prog from aaa UNION SELECT 1;
'
[2956-2.140]SELECT
[2956-2.140]unquoted=1, quote=0, dquote=0, numeric=0, delim=' ', token='code', ptr='as prog from aaa UNION SELECT 1;
'
[2956-2.142]blevel=0 btoken=SELECT in_dot=0 in_field=0 tbname=
[2956-2.143]reallocing at nfld=0
[2956-2.144]got field='code', dot='(null)'
[2956-2.144]unquoted=1, quote=0, dquote=0, numeric=0, delim=' ', token='as', ptr='prog from aaa UNION SELECT 1;
'
[2956-2.146]blevel=0 btoken=code in_dot=0 in_field=1 tbname=
[2956-2.147]got AS
[2956-2.147]unquoted=1, quote=0, dquote=0, numeric=0, delim=' ', token='prog', ptr='from aaa UNION SELECT 1;
'
[2956-2.149]blevel=0 btoken=as in_dot=0 in_field=1 tbname=
[2956-2.149]alias for field 'code' is 'prog'
[2956-2.150]unquoted=1, quote=0, dquote=0, numeric=0, delim=' ', token='from', ptr='aaa UNION SELECT 1;
'
[2956-2.152]First [2956-2.152]FROM
[2956-2.152]unquoted=1, quote=0, dquote=0, numeric=0, delim=' ', token='aaa', ptr='UNION SELECT 1;
'
[2956-2.153]got table = 'aaa'
[2956-2.154]unquoted=1, quote=0, dquote=0, numeric=0, delim=' ', token='UNION', ptr='SELECT 1;
'
[2956-2.155]UNION... <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

(And this is the one that gives error)
[3244-1.937]parse_statement: entering...
[3244-1.937]unquoted=1, quote=0, dquote=0, numeric=0, delim=' ', token='SELECT', ptr='1 as prog UNION SELECT code from aaa;
'
[3244-1.939]SELECT
[3244-1.939]unquoted=1, quote=0, dquote=0, numeric=1, delim=' ', token='1', ptr='as prog UNION SELECT code from aaa;
'
[3244-1.941]blevel=0 btoken=SELECT in_dot=0 in_field=0 tbname=
[3244-1.942]reallocing at nfld=0
[3244-1.942]**** got numeric: nfld = 0
[3244-1.943]got field='(null)', dot='(null)'
[3244-1.944]unquoted=1, quote=0, dquote=0, numeric=0, delim=' ', token='as', ptr='prog UNION SELECT code from aaa;
'
[3244-1.945]blevel=0 btoken=1 in_dot=0 in_field=1 tbname=
[3244-1.946]got AS
[3244-1.946]unquoted=1, quote=0, dquote=0, numeric=0, delim=' ', token='prog', ptr='UNION SELECT code from aaa;
'
[3244-1.948]blevel=0 btoken=as in_dot=0 in_field=1 tbname=
[3244-1.948]alias for field '(null)' is 'prog'
[3244-1.949]unquoted=1, quote=0, dquote=0, numeric=0, delim=' ', token='UNION', ptr='SELECT code from aaa;
'
[3244-1.951]blevel=0 btoken=prog in_dot=0 in_field=0 tbname=prog
[3244-1.952]got field='UNION', dot='(null)' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
[3244-1.953]unquoted=1, quote=0, dquote=0, numeric=0, delim=' ', token='SELECT', ptr='code from aaa;

This is psqlodbc_2008.log:
[0.007]Driver Version='08.03.0400,200811070001' linking 1400 static Multithread library
[0.015]Global Options: fetch=100, socket=4096, unknown_sizes=2, max_varchar_size=255, max_longvarchar_size=8190
[0.018] disable_optimizer=0, ksqo=1, unique_index=1, use_declarefetch=0
[0.021] text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1 NAMEDATALEN=64
[0.025] extra_systable_prefixes='dd_;', conn_settings='' conn_encoding=''
[0.104] [ PostgreSQL version string = '8.3.4' ]
[0.106] [ PostgreSQL version number = '8.3' ]
[0.121]conn=031C2470, query='select oid, typbasetype from pg_type where typname = 'lo''
[0.139] [ fetched 0 rows ]
[0.148] [ Large Object oid = -999 ]
[0.150] [ Client encoding = 'UTF8' (code = 6) ]
[0.161]conn=031C2470, PGAPI_DriverConnect(out)='DSN=PostgreSQL Log;DATABASE=muna;SERVER=localhost;PORT=5432;UID=client;PWD=xxxxxx;SSLmode=disable;ReadOnly=0;Protocol=7.4-1;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=2;MaxVarcharSize=255;MaxLongVarcharSize=8190;Debug=1;CommLog=1;Optimizer=0;Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Parse=1;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;;LFConversion=1;UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=1;UseServerSidePrepare=0;LowerCaseIdentifier=0;XaOpt=1'
[1.909]conn=031C2470, query='SELECT 1 as prog UNION SELECT code from aaa;
'
[1.922] [ fetched 1 rows ]
[1.971]conn=031C2470, query='select current_schema()'
[1.989] [ fetched 1 rows ]
[2.003]conn=031C2470, query='select nspname from pg_namespace n, pg_class c where c.relnamespace=n.oid and c.oid='"aaa"'::regclass'
[2.024] [ fetched 1 rows ]
[2.095]conn=031C2470, query='select n.nspname, c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull, c.relhasrules, c.relkind, c.oid, d.adsrc from (((pg_catalog.pg_class c inner join pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.relname = E'aaa' and n.nspname = E'public') inner join pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum> 0 and a.attrelid = c.oid) inner join pg_catalog.pg_type t on t.oid = a.atttypid) left outer join pg_attrdef d on a.atthasdef and d.adrelid = a.attrelid and d.adnum = a.attnum order by n.nspname, c.relname, attnum'
[2.152] [ fetched 1 rows ]
[2.289]PGAPI_Columns: table='aaa',field_name='code',type=23,name='int4'
[2.393]STATEMENT ERROR: func=PGAPI_DescribeCol, desc='Col#=1, #Cols=1,1 keys=2', errnum=13, errmsg='Invalid column number in DescribeCol.'
[2.397] ------------------------------------------------------------
[2.398] hdbc=031C2470, stmt=031C6FD0, result=031C72E8
[2.399] prepare=0, internal=0
[2.400] bindings=031C89E0, bindings_allocated=1
[2.401] parameters=00000000, parameters_allocated=0
[2.402] statement_type=0, statement='SELECT 1 as prog UNION SELECT code from aaa;
'
[2.403] stmt_with_params='SELECT 1 as prog UNION SELECT code from aaa;
'
[2.405] data_at_exec=-1, current_exec_param=-1, put_data=0
[2.406] currTuple=-1, current_col=-1, lobj_fd=-1
[2.406] maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0, scroll_concurrency=1
[2.407] cursor_name='SQL_CUR031C6FD0'
[2.408] ----------------QResult Info -------------------------------
[2.409] fields=031C7398, backend_tuples=031C8698, tupleField=52201112, conn=031C2470
[2.411] fetch_count=0, num_total_rows=1, num_fields=1, cursor='(NULL)'
[2.412] message='(NULL)', command='SELECT', notice='(NULL)'
[2.414] status=100, inTuples=0
[2.415]CONN ERROR: func=PGAPI_DescribeCol, desc='Col#=1, #Cols=1,1 keys=2', errnum=0, errmsg='(NULL)'
[2.418] ------------------------------------------------------------
[2.419] henv=031C0A70, conn=031C2470, status=1, num_stmts=16
[2.420] sock=031C0AA0, stmts=031C0B68, lobj_type=-999
[2.421] ---------------- Socket Info -------------------------------
[2.422] socket=552, reverse=0, errornumber=0, errormsg='(NULL)'
[2.423] buffer_in=52187072, buffer_out=52191176
[2.424] buffer_filled_in=456, buffer_filled_out=0, buffer_read_in=456
[3.990]conn=031C2470, PGAPI_Disconnect

Regards,

Moreno Dissegna
Developer
S.A. Software S.r.l.

_________________________________________________________________
Più di 100 Emoticon gratis per il tuo Messenger!
http://intrattenimento.it.msn.com/emoticon