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

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@163.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/