Re: Need community help about ODBC .---- Can't fetch the data resource from server.

Jeff Eckermann,您好！

Jeff

Thanks ur tips about XP ODBC support , I have found another solution to get rid of that progam. I have search our list again, and I find a answer. Excel fetch data really gets somewhat problem. But we can use "Database Query" instead. I tried it, It's ok . Thanks again for ur tips.

Elvis Guo

======= 2004-02-01 09:17:00 您在来信中写道：=======

>Your ODBC connection is evidently working fine. There
>appears to be some problem with Excel (MS Query
>actually) handling what it is given.
>
>Similar questions have been posted to the list, but I
>don't recall seeing a solution. I have had the same
>problem myself, but I didn't find a solution. My
>Excel app handles everything via code (ADO), which
>works fine, so I never invested the time to find a
>solution. You might get some success by playing with
>the driver settings. Hopefully someone else can come
>up with a better idea.
>
>FWIW, I had the same problem working with Excel 97
>(and earlier versions of PostgreSQL and the ODBC
>driver), but was able successfully to issue queries
>using SQL, in spite of being unable to show the
>tables. But Excel XP won't let me get that far.
>--- Elvis Guo <guoxf(at)163(dot)net> wrote:
>> Hello,Everybody.
>>
>> I got some ODBC connection trouble. If u had even
>> met this situation,please kindly tell me how your
>> solved the proble. Thanks advance.
>>
>>
>> 1.My Runtime envionment:
>>
>> OS:Windows XP Pro
>> CPU:1.5G P4 Intel
>> RAM:512M
>> HD:40G
>>
>> ODBC Pakage src:
>> compressed: psqlodbc-07_03_0200.zip 3,781,611
>> uncompressed: psqlodbc.exe 4,183,040
>>
>> 2.PostgreSQL version:
>> xjy=> select version();
>> version
>>
>------------------------------------------------------------------------
>> PostgreSQL 7.3.4 on i686-pc-linux-gnu, compiled by
>> GCC gcc (GCC) 3.2.3
>>
>> My Database "xjy" have one table "employees"
>>
>> xjy=> \z
>> Access privileges for database "xjy"
>> Schema | Table | Access privileges
>> --------+-----------+----------------------------
>> public | employees | {=,guoxf=arwdRxt,test=arw}
>> public | my_seq |
>>
>> xjy=> select * from employees ;
>> id | ic | last_name | first_name
>> -----+-----+-----------+------------
>> 104 | 105 | testb | foo
>> 103 | 104 | *** | foo
>>
>> 3.After Install the ODBC driver "psqlodbc.exe",the
>> system tell me OK.
>>
>> 4.Click xp control panel to install user DSN, input
>> the correct parameters .
>>
>> 5.My PostgreSQL server have granted the access
>> privilege. I can use PgAdminIII access My database
>> "xjy" successfully.I knew PgAdminIII used libpq.dll
>> for querying. It did not use ODBC to connect the
>> server .
>>
>> 6.So, I star Excell to test If I can get the data
>> from my postgreSQL data base.Click "Data" menu,then
>> "import data".., do the step Excell told me.
>>
>> 7. I can use the Excell test database connection.
>> Its told me OK. But at the final step, I got the
>> message, Excell can't get the data source.
>>
>> 8. I enable psqlodbc_xxx.log and mylog_xxx.log ,
>> please read the following detail content. If u can
>> find what's going on there, please tell me how to
>> solve this problem . I had checked all
>> documentation. I still couldn't find any
>> answere.Please help me .
>>
>> psqlodbc_xxx.log
>> --------------------
>> conn=60503800, PGAPI_DriverConnect(
>> in)='DSN=PostgreSQL30;', fDriverCompletion=3
>> DSN info:
>>
>DSN='PostgreSQL30',server='192.168.0.12',port='5432',dbase='xjy',user='guoxf',passwd='xxxxx'
>>
>>
>onlyread='0',protocol='6.4',showoid='0',fakeoidindex='0',showsystable='0'
>> conn_settings='',conn_encoding='OTHER'
>> translation_dll='',translation_option=''
>> Global Options: Version='07.03.0200', fetch=100,
>> socket=4096, unknown_sizes=0, max_varchar_size=254,
>> max_longvarchar_size=8190
>> disable_optimizer=1, ksqo=1,
>> unique_index=1, use_declarefetch=0
>> text_as_longvarchar=1,
>> unknowns_as_longvarchar=0, bools_as_char=1
>> NAMEDATALEN=64
>> extra_systable_prefixes='pg_;',
>> conn_settings='' conn_encoding='OTHER'
>> conn=60503800, query=' '
>> conn=60503800, query='select version()'
>> [ fetched 1 rows ]
>> [ PostgreSQL version string = 'PostgreSQL 7.3.4
>> on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
>> 3.2.3' ]
>> [ PostgreSQL version number = '7.3' ]
>> conn=60503800, query='set DateStyle to 'ISO''
>> conn=60503800, query='set geqo to 'OFF''
>> conn=60503800, query='select oid from pg_type where
>> typname='lo''
>> [ fetched 0 rows ]
>> conn=60503800, query='select pg_client_encoding()'
>> [ fetched 1 rows ]
>> conn=60503800, query='set client_encoding to 'GBK''
>> conn=60503800,
>>
>PGAPI_DriverConnect(out)='DSN=PostgreSQL30;DATABASE=xjy;SERVER=192.168.0.12;PORT=5432;UID=guoxf;PWD=;ReadOnly=0;Protocol=6.4;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;MaxVarcharSize=254;MaxLongVarcharSize=8190;Debug=1;CommLog=1;Optimizer=1;Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=pg_;;LFConversion=1;UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=0'
>> conn=60503800, query='select relname, nspname,
>> relkind from pg_catalog.pg_class,
>> pg_catalog.pg_namespace where relkind in ('r', 'v')
>> and relname !~ '^pg_|^pg_' and pg_namespace.oid =
>> relnamespace order by nspname, relname'
>> [ fetched 1 rows ]
>>
>> mylog_xxx.log
>> -------------------------------------------
>> [3604]globals.extra_systable_prefixes = 'dd_;'
>> [3604][[SQLAllocHandle]][3604]**** in PGAPI_AllocEnv
>> **
>> [3604]** exit PGAPI_AllocEnv: phenv = 60497808 **
>> [3604][[SQLSetEnvAttr]] att=200,2
>> [3604][[SQLAllocHandle]][3604]PGAPI_AllocConnect:
>> entering...
>> [3604]**** PGAPI_AllocConnect: henv = 60497808, conn
>> = 60503800
>> [3604]EN_add_connection: self = 60497808, conn =
>> 60503800
>> [3604] added at i =0, conn->henv = 60497808,
>> conns[i]->henv = 60497808
>> [3604][SQLGetInfo(30)][3604]PGAPI_GetInfo:
>> entering...fInfoType=77
>> [3604]PGAPI_GetInfo: p='03.00', len=0, value=0,
>> cbMax=12
>> [3604][SQLDriverConnect][3604]PGAPI_DriverConnect:
>> entering...
>> [3604]**** PGAPI_DriverConnect: fDriverCompletion=3,
>> connStrIn='DSN=PostgreSQL30;'
>> [3604]our_connect_string = 'DSN=PostgreSQL30;'
>> [3604]attribute = 'DSN', value = 'PostgreSQL30'
>> [3604]copyAttributes:
>>
>DSN='PostgreSQL30',server='',dbase='',user='',passwd='xxxxx',port='',onlyread='',protocol='',conn_settings='',disallow_premature=-1)
>> [3604]globals.extra_systable_prefixes = 'pg_;'
>> [3604]our_connect_string = 'DSN=PostgreSQL30;'
>> [3604]attribute = 'DSN', value = 'PostgreSQL30'
>> [3604]CopyCommonAttributes:
>>
>A7=100;A8=4096;A9=0;B0=254;B1=8190;B2=1;B3=1;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=pg_;[3604]CC_connect:
>> entering...
>> [3604]CC_connect(): DSN = 'PostgreSQL30', server =
>> '192.168.0.12', port = '5432', database = 'xjy',
>> username = 'guoxf', password='xxxxx'
>> [3604]connecting to the server socket...
>> [3604]connection to the server socket succeeded.
>> [3604]sizeof startup packet = 292
>> [3604]sent the authentication block.
>> [3604]sent the authentication block successfully.
>> [3604]gonna do authentication
>> [3604]read 15, global_socket_buffersize=4096
>> [3604]auth got 'R'
>> [3604]areq = 0
>> [3604]auth got 'K'
>> [3604]auth got 'Z'
>> [3604]sending an empty query...
>> [3604]send_query(): conn=60503800, query=' '
>> [3604]send_query: done sending query
>> [3604]in QR_Constructor
>> [3604]exit QR_Constructor
>> [3604]read 3, global_socket_buffersize=4096
>> [3604]send_query: got id = 'I'
>> [3604]send_query: got id = 'Z'
>> [3604]QResult: in DESTRUCTOR
>> [3604]QResult: free memory in, fcount=0
>> [3604]QResult: free memory out
>> [3604]QResult: exit DESTRUCTOR
>> [3604]empty query seems to be OK.
>> [3604]CC_lookup_pg_version: entering...
>> [3604]PGAPI_AllocStmt: entering...
>> [3604]**** PGAPI_AllocStmt: hdbc = 60503800, stmt =
>> 60498248
>> [3604]CC_add_statement: self=60503800, stmt=60498248
>> [3604]PGAPI_ExecDirect: entering...
>> [3604]**** PGAPI_ExecDirect: hstmt=60498248,
>> statement='select version()'
>> [3604]PGAPI_ExecDirect: calling PGAPI_Execute...
>> [3604]PGAPI_Execute: entering...
>> [3604]PGAPI_Execute: clear errors...
>> [3604]recycle statement: self= 60498248
>> [3604]Exec_with_parameters_resolved: copying
>> statement params: trans_status=1, len=16,
>> stmt='select version()'
>> [3604] stmt_with_params = 'select version()'
>> [3604] Sending SELECT statement on
>> stmt=60498248, cursor_name='SQL_CUR039B2148'
>> [3604]send_query(): conn=60503800, query='select
>> version()'
>> [3604]send_query: done sending query
>> [3604]in QR_Constructor
>> [3604]exit QR_Constructor
>> [3604]read 113, global_socket_buffersize=4096
>> [3604]send_query: got id = 'P'
>> [3604]send_query: got id = 'T'
>> [3604]QR_fetch_tuples: cursor = '', self->cursor=0
>> [3604]num_fields = 1
>> [3604]READING ATTTYPMOD
>> [3604]CI_read_fields: fieldname='version', adtid=25,
>> adtsize=-1,
>=== message truncated ===
>
>
>__________________________________
>Do you Yahoo!?
>Yahoo! SiteBuilder - Free web site building tool. Try it!
>http://webhosting.yahoo.com/ps/sb/
>
>.

= = = = = = = = = = = = = = = = = = = =

　　　　　　　　致
礼！


　　　　　　　　Elvis Guo
　　　　　　　　guoxf(at)163(dot)net
　　　　　　　　　　2004-02-03