Thread: Values in the select

> The version of ODBC that I've got installed is 08.00.0102 (via MSI installed).

Has Unicode version same problem? Could you try psqlodbc 08.01.0104?
Do you get same result?

If there is still the problem could you send here mylog output?

I can't see how you connect to pgsql (what options do you use?).

Regards,

Luf

Dear Luf,

The connectionstring that is used is the following:

Provider=MSDASQL.1;Password=<snip>;Persist Security Info=True;User
ID=intwis2;Data Source=PostgreSQL;Extended
Properties="DSN=PostgreSQL;DATABASE=intwis9;SERVER=localhost;PORT=5432;SSLMO
DE=prefer;UID=intwis2;PWD=intwis2;ReadOnly=0;Protocol=6.4;FakeOidIndex=0;Sho
wOidColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;Sock
et=8192;UnknownSizes=0;MaxVarcharSize=254;MaxLongVarcharSize=8190;Debug=0;Co
mmLog=0;Optimizer=1;Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsL
ongVarchar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=
dd_;;LFConversion=1;UpdatableCursors=0;DisallowPremature=0;TrueIsMinus1=0;BI
=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=0;LowerCaseIdentifier=0"

The contents of psqlodbc_2972.log:

conn=120025968, PGAPI_DriverConnect(
in)='DSN=PostgreSQL;UID=intwis2;PWD=xxxxxxx;', fDriverCompletion=0
DSN info:
DSN='PostgreSQL',server='localhost',port='5432',dbase='intwis9',user='intwis
2',passwd='xxxxx'

onlyread='0',protocol='6.4',showoid='0',fakeoidindex='0',showsystable='0'
          conn_settings='',conn_encoding='OTHER'
          translation_dll='',translation_option=''

And of mylog_2972.log

[144]globals.extra_systable_prefixes = 'dd_;'
[144][[SQLAllocHandle]][144]**** in PGAPI_AllocEnv **
[144]** exit PGAPI_AllocEnv: phenv = 120063888 **
[144][[SQLSetEnvAttr]] att=200,2
[144][[SQLAllocHandle]][144]PGAPI_AllocConnect: entering...
[144]**** PGAPI_AllocConnect: henv = 120063888, conn = 120025968
[144]EN_add_connection: self = 120063888, conn = 120025968
[144]       added at i =0, conn->henv = 120063888, conns[i]->henv =
120063888
[144][SQLGetInfo(30)][144]PGAPI_GetInfo: entering...fInfoType=77
[144]PGAPI_GetInfo: p='03.00', len=5, value=0, cbMax=12
[144][[SQLSetConnectAttr]] 103
[144]PGAPI_SetConnectAttr 103
[144]PGAPI_SetConnectOption: entering fOption = 103 vParam = 15
[144][SQLDriverConnect][144]PGAPI_DriverConnect: entering...
[144]**** PGAPI_DriverConnect: fDriverCompletion=0,
connStrIn='DSN=PostgreSQL;UID=intwis2;PWD=xxxxxxx;'
[144]our_connect_string = 'DSN=PostgreSQL;UID=intwis2;PWD=xxxxxxx;'
[144]attribute = 'DSN', value = 'PostgreSQL'
[144]copyAttributes:
DSN='PostgreSQL',server='',dbase='',user='',passwd='xxxxx',port='',sslmode='
',onlyread='',protocol='',conn_settings='',disallow_premature=-1)
[144]attribute = 'UID', value = 'intwis2'
[144]copyAttributes:
DSN='PostgreSQL',server='',dbase='',user='intwis2',passwd='xxxxx',port='',ss
lmode='',onlyread='',protocol='',conn_settings='',disallow_premature=-1)
[144]attribute = 'PWD', value = 'xxxxx'
[144]copyAttributes:
DSN='PostgreSQL',server='',dbase='',user='intwis2',passwd='xxxxx',port='',ss
lmode='',onlyread='',protocol='',conn_settings='',disallow_premature=-1)
[144]globals.extra_systable_prefixes = 'dd_;'
[144]globals.extra_systable_prefixes = 'dd_;'
[144]our_connect_string = 'DSN=PostgreSQL;UID=intwis2;PWD=xxxxxxx;'
[144]attribute = 'DSN', value = 'PostgreSQL'
[144]CopyCommonAttributes:
A7=100;A8=8192;A9=0;B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C
0=0;C1=0;C2=dd_;[144]attribute = 'UID', value = 'intwis2'
[144]CopyCommonAttributes:
A7=100;A8=8192;A9=0;B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C
0=0;C1=0;C2=dd_;[144]attribute = 'PWD', value = 'xxxxx'
[144]CopyCommonAttributes:
A7=100;A8=8192;A9=0;B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C
0=0;C1=0;C2=dd_;[144]PGAPI_Disconnect: about to CC_cleanup
[144]in CC_Cleanup, self=120025968
[144]after CC_abort
[144]entering PGCONN_Destructor
[144]exiting PGCONN_Destructor
[144]after LIBPQ destructor
[144]exit CC_Cleanup
[144]PGAPI_Disconnect: done CC_cleanup
[144]PGAPI_Disconnect: returning...
[144][[SQLFreeHandle]][144]PGAPI_FreeConnect: entering...
[144]**** in PGAPI_FreeConnect: hdbc=120025968
[144]enter CC_Destructor, self=120025968
[144]in CC_Cleanup, self=120025968
[144]after LIBPQ destructor
[144]exit CC_Cleanup
[144]after CC_Cleanup
[144]after free statement holders
[144]exit CC_Destructor
[144]PGAPI_FreeConnect: returning...
[144][[SQLFreeHandle]][144]**** in PGAPI_FreeEnv: env = 120063888 **
[144]in EN_Destructor, self=120063888
[144]exit EN_Destructor: rv = 1
[144]   ok

Unfortunately I did not find version 08.01.0104 on the postgresql web site
(http://www.postgresql.org/ftp/odbc/versions/msi/), where can I find it?

I've tested it with the Unicode version and the same problem occours.

Yours,

Aarjan

----- Original Message -----
From: "Ludek Finstrle" <luf@pzkagis.cz>
To: "A.j. Langereis" <a.j.langereis@inter.nl.net>
Cc: <pgsql-odbc@postgresql.org>
Sent: Friday, December 23, 2005 10:52 AM
Subject: Re: [ODBC] Values in the select


> > The version of ODBC that I've got installed is 08.00.0102 (via MSI
installed).
>
> Has Unicode version same problem? Could you try psqlodbc 08.01.0104?
> Do you get same result?
>
> If there is still the problem could you send here mylog output?
>
> I can't see how you connect to pgsql (what options do you use?).
>
> Regards,
>
> Luf
>
> ---------------------------(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
>
>

Hello,

> And of mylog_2972.log

Did you enable mylog output under both buttons (Global and DataSource)?
It seems to me you don't or you didn't send me right mylog.
This only connects and disconnects (like open DataSource options dialog).

Please, enable mylog output in both dialogs (under Global and DataSource
button). Delete all C:\mylog*.log after enabling mylog output.
Than run you example app. There may appear mylog_XXXX.log in C:\
There have to be SQLExecDirect or SQLExecute with your query in the
mylog output. It's unuseable mylog in other cases.

> Unfortunately I did not find version 08.01.0104 on the postgresql web site
> (http://www.postgresql.org/ftp/odbc/versions/msi/), where can I find it?

It's not official release. It is development snapshot. Main psqlodbc
site is hosted on pgfoundry.org (http://pgfoundry.org/projects/psqlodbc/).
Development snapshot contains only psqlodbc[aw].dll. So you have to
installed psqlodbc 8.01.0102 (latest official release) before.

It could be enough to try psqlodbc 8.01.0102. And please send me mylog
output from psqlodbc 8.01.0102 (not from 8.00.XXXX or even older).

> I've tested it with the Unicode version and the same problem occours.

Ok. It could speed up locating the problem.

Thanks,

Luf

Dear Luf,

Attached is a file with both mylogs enabled.Note: I have put the log through
a parser because the lines were only delimeted with a #10 instead of both
#10 and #13

Do I only need to replace the .dll files in order to upgrade the odbc
driver?

Yours,

Aarjan

----- Original Message -----
From: "Ludek Finstrle" <luf@pzkagis.cz>
To: "A.j. Langereis" <a.j.langereis@inter.nl.net>
Cc: <pgsql-odbc@postgresql.org>
Sent: Friday, December 23, 2005 1:12 PM
Subject: Re: [ODBC] Values in the select


> Hello,
>
> > And of mylog_2972.log
>
> Did you enable mylog output under both buttons (Global and DataSource)?
> It seems to me you don't or you didn't send me right mylog.
> This only connects and disconnects (like open DataSource options dialog).
>
> Please, enable mylog output in both dialogs (under Global and DataSource
> button). Delete all C:\mylog*.log after enabling mylog output.
> Than run you example app. There may appear mylog_XXXX.log in C:\
> There have to be SQLExecDirect or SQLExecute with your query in the
> mylog output. It's unuseable mylog in other cases.
>
> > Unfortunately I did not find version 08.01.0104 on the postgresql web
site
> > (http://www.postgresql.org/ftp/odbc/versions/msi/), where can I find it?
>
> It's not official release. It is development snapshot. Main psqlodbc
> site is hosted on pgfoundry.org (http://pgfoundry.org/projects/psqlodbc/).
> Development snapshot contains only psqlodbc[aw].dll. So you have to
> installed psqlodbc 8.01.0102 (latest official release) before.
>
> It could be enough to try psqlodbc 8.01.0102. And please send me mylog
> output from psqlodbc 8.01.0102 (not from 8.00.XXXX or even older).
>
> > I've tested it with the Unicode version and the same problem occours.
>
> Ok. It could speed up locating the problem.
>
> Thanks,
>
> Luf
>
>

Nice sample.
Perhaps you can use a DSN connection and in the PostgreSQL ODBC Driver setup for the connection turn on
mylog.
Or you can use the ODBC Administrator, under the tracing tab, Start Tracing.
Or in the server postgresql.conf temporarily turn the logging on for all statements, to capture lower
level ODBC communication.

Do the ODBC connection encoding, and the database encoding match?

A.j. Langereis wrote:
> Dear all,
>
> I have written an application that uses Postgresql (8.1.0) trough an ODBC (ANSI) connection. I noticed that a query
like"select 'bar' as foo" does not give the value "bar" in the column "foo". This works normally under PGadmin and even
onother databases via ODBC. Am I overlooking something or might this be a bug? 
>
> Values that I do get are like "#0#0#0#0#0"
>
> A piece of Delphi code to show the problem:
>
> procedure show_problem(conn : TADOConnection)
> var
>     qry : TAdoQuery;
>     datasource: TDataSource;
> begin
>     qry := TADOQuery.create(nil);
>     qry.Connection:=conn;
>     qry.SQL.Text:= 'select ''bar'' as foo';
>
>     qry.Open;
>
>     if not qry.eof then
>         begin
>         datasource := TDataSource.Create(nil);
>         datasource.dataset := qry;
>
>         //datasource.DataSet.Fields[1].AsString now has the value of '#0#0#0#0#0'
>         //datasource.DataSet.Fields[1].FieldName is now equal to 'foo'
>
>         datasource.free;
>         end;
>
>     qry.close;
>     qry.free;
> end;
>
> The version of ODBC that I've got installed is 08.00.0102 (via MSI installed).
>
> Yours,
>
> Aarjan

> Attached is a file with both mylogs enabled.Note: I have put the log through
> a parser because the lines were only delimeted with a #10 instead of both
> #10 and #13

The log is truncated in the middle of SQLExecDirect operation. I can't
tell you anything now. I need whole log.
I don't see neither what driver read from backend nor how you get tha data.

Regards,

Luf

> > Attached is a file with both mylogs enabled.Note: I have put the log through
> > a parser because the lines were only delimeted with a #10 instead of both
> > #10 and #13
>
> The log is truncated in the middle of SQLExecDirect operation. I can't
> tell you anything now. I need whole log.
> I don't see neither what driver read from backend nor how you get tha data.

What's the state? Could I release it from my mind?

Thanks,

Luf

Unfortunately it has not been solved yet: I missed your previous email.

I have attached the complete log file, however the requested part is a bit
messed up. Hopefully you are still able to make any sense out of it. If not,
I'll make a new logfile, but that is a bit difficult at the moment due to a
broken down workstation.

Regards,

Aarjan

----- Original Message -----
From: "Ludek Finstrle" <luf@pzkagis.cz>
To: "A.j. Langereis" <a.j.langereis@inter.nl.net>
Cc: <pgsql-odbc@postgresql.org>
Sent: Sunday, January 08, 2006 12:12 PM
Subject: Re: [ODBC] Values in the select


>> > Attached is a file with both mylogs enabled.Note: I have put the log
>> > through
>> > a parser because the lines were only delimeted with a #10 instead of
>> > both
>> > #10 and #13
>>
>> The log is truncated in the middle of SQLExecDirect operation. I can't
>> tell you anything now. I need whole log.
>> I don't see neither what driver read from backend nor how you get tha
>> data.
>
> What's the state? Could I release it from my mind?
>
> Thanks,
>
> Luf
>
>

> I have written an application that uses Postgresql (8.1.0) trough
> an ODBC (ANSI) connection. I noticed that a query like
> "select 'bar' as foo" does not give the value "bar" in the column
> "foo". This works normally under PGadmin and even on other databases
> via ODBC. Am I overlooking something or might this be a bug?

I'm able to reproduce the problem here. Stay tuned. I hope I'll fix it
in 08.01.0107 development snapshot.

Regards,

Luf

Great! (well is it ? ;) )

Looking forward to that snapshot!

Regards,

Aarjan

----- Original Message -----
From: "Ludek Finstrle" <luf@pzkagis.cz>
To: "A.j. Langereis" <a.j.langereis@inter.nl.net>
Cc: <pgsql-odbc@postgresql.org>
Sent: Thursday, January 12, 2006 3:30 PM
Subject: Re: [ODBC] Values in the select


>> I have written an application that uses Postgresql (8.1.0) trough
>> an ODBC (ANSI) connection. I noticed that a query like
>> "select 'bar' as foo" does not give the value "bar" in the column
>> "foo". This works normally under PGadmin and even on other databases
>> via ODBC. Am I overlooking something or might this be a bug?
>
> I'm able to reproduce the problem here. Stay tuned. I hope I'll fix it
> in 08.01.0107 development snapshot.
>
> Regards,
>
> Luf
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>

> > I have written an application that uses Postgresql (8.1.0) trough
> > an ODBC (ANSI) connection. I noticed that a query like
> > "select 'bar' as foo" does not give the value "bar" in the column
> > "foo". This works normally under PGadmin and even on other databases
> > via ODBC. Am I overlooking something or might this be a bug?
>
> I'm able to reproduce the problem here. Stay tuned. I hope I'll fix it
> in 08.01.0107 development snapshot.

I has good news for you. I found the problem and fix it. psqlODBC
08.01.0107 will contain this patch. I hope I put it on the web
during weekend (the date couldn't be final).

Patch is attached.

Please review and comment

Luf