chava | 22 Sep 18:54 2014
Picon

Re: 126 Error Connecting With psqlODBC 32 bit

i have tried by adding microsoft.data.odbc.dll manually in
c:\programfiles\psqlodbc\0903\bin
and it is working without errors in windows 7-32 bit, prior to it i was
getting the same 126 error code which was rectified later

--
View this message in context: http://postgresql.1045698.n5.nabble.com/126-Error-Connecting-With-psqlODBC-32-bit-tp5804761p5819937.html
Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Bala krishna Devasani | 22 Sep 11:49 2014
Picon

Enable debug logging

how to enable debug logging for postgresql.. can anyone provide steps to enable debug logging.


Thanks & Regards
Balakrishna

Bala krishna Devasani | 22 Sep 08:56 2014
Picon

Problem in Re-using the prepared statement

Desc: i have a problem executing the prepared statement more than once. 1st time it executing correct and when i try to execute same prepared statement next time it is failed giving the error like this.

details: i am executing a prepared statement with binding some parameters ,after executing i am resting the prepared statement and unbinding the parameters. when try to execute the same prepared statement it is giving the error :"27:Error fetching numeric attribute: ColAttribute for this type not implemented yet".  i am using libodbc++ library. when i debug it is going wrong at 

Line :ResultSet* rs=ODBCXX_OPERATOR_NEW_DEBUG(__FILE__, __LINE__) ResultSet(this,hstmt_,hideMe);

Error: "27:Error fetching numeric attribute: ColAttribute for this type not implemented yet"


PostgreSQL version number you are running:

How you installed PostgreSQL:PostgreSQL 9.3.5, compiled by Visual C++ build 1600, 64-bit

Changes made to the settings in the postgresql.conf file: No

Operating system and version:windows 8.1 (64-bit)

What program you're using to connect to PostgreSQL:ODBC 3.5 (libodbc++) library
 
Is there anything relevant or unusual in the PostgreSQL server logs?:No
 
For questions about any kind of error:

What you were doing when the error happened / how to cause the error:"27:Error fetching numeric attribute: ColAttribute for this type not implemented yet"


Thanks & Regards
Balakrishna
srr | 19 Sep 11:03 2014
Picon

MS SQL 2008 crush after trunsaction commit

Hello!

I have next configuration:

Win Serv 2008 r2 x64 standart
MS SQL 2008 x64
psqlodbc_09_03_0300-x64-1

when I run

set  XACT_ABORT  ON
begin tran
   insert into    OpenQuery(PG,     'SELECT * From test '    )
  values (7)
commit tran

Ms sql crush:

SQL Server is terminating because of fatal exception c0000005. This error
may be caused by an unhandled Win32 or C++ exception, or by an access
violation encountered during exception handling. Check the SQL error log for
any related stack dumps or messages. This exception forces SQL Server to
shutdown. To recover from this error, restart the server (unless SQLAgent is
configured to auto restart).

If i use rollback tran or work without transaction it`s working well.

Thank you!

--
View this message in context: http://postgresql.1045698.n5.nabble.com/MS-SQL-2008-crush-after-trunsaction-commit-tp5819628.html
Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Philippe Champignon | 18 Sep 11:51 2014

Cannot retrieve sequence number after an insert with OLE DB

Hello,

I just submit a bug in the psqlODBC driver 9.2/9.3 (32-bit and 64-bit).

The context :

I have a Windows program that uses the database postgresql through OLE DB and psqlODBC driver. In the database, I have a table with a sequence that generates a number whenever you insert a new row in the table. The program inserts data into the table and retrieves the auto incremented sequence value.

With Postgresql 8.2 and the psqlODBC 8.02.0400 driver, the program works perfectly. But with Postgresql 9.2 or 9.3 and the psqlODBC 9.02.0100 or 9.03.0300 driver it is impossible to retrieve the sequence number after the insertion of the line. There are always 0.

 

Tests:

 

To identify the problem, I did several tests, which allowed me to find that it was the ODBC driver that is problematic.

PsqlODBC version

Postgresql version

Result

8.02.0400

8.2

OK

9.02.0100

9.2.4

KO

8.02.0400

9.2.4

OK

8.02.0500

9.2.5

KO

9.03.0300

9.3.2

KO

 

With version 8.02.04 of the driver, the problem does not occur. But with the 8.02.05 driver version, I reproduce the problem.

So I compared the 8.02.04 driver and driver 8.02.05 sources and positioned the traces of the driver to find the bug: it is in the SC_setInsertedTable function, when parsing the name of the table to find the schema.

Explanation :

With the driver 8.02.04, in traces, we see this:

[11.819]conn=02354D10, query='INSERT INTO "iamaudit"."public"."AuditEvents" ("EvtCategory","EvtType","EvtOperation","TimeStamp","ResultCode","UserAuditID","SessionID","AccessPointID","AccessPointName","ClientIP","ClientHostname","ExtendedInfo") VALUES (1,1234,3,'2013-09-23 12:30:52'::timestamp,-2147467259,'champi','','','DEROBEE','','','')'

[11.841]conn=02354D10, query='SELECT lastval()'

[11.856]    [ fetched 1 rows ]

[11.892]conn=02354D10, query='COMMIT'

With the driver 8.02.05, in traces, we see this:

[17.297]conn=02354D10, query='INSERT INTO "iamaudit"."public"."AuditEvents" ("EvtCategory","EvtType","EvtOperation","TimeStamp","ResultCode","UserAuditID","SessionID","AccessPointID","AccessPointName","ClientIP","ClientHostname","ExtendedInfo") VALUES (1,1234,3,'2013-09-23 12:41:27'::timestamp,-2147467259,'champi','','','DEROBEE','','','')'

[17.336]conn=02354D10, 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 = 'public' and n.nspname = 'iamaudit') 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'

[17.351]    [ fetched 0 rows ]

[17.401]conn=02354D10, query='SELECT NULL'

[17.405]    [ fetched 1 rows ]

[17.439]conn=02354D10, query='COMMIT'

The problem is revealed by the correction in 8.02.0402:

3.) Put back the <at> <at> IDENTITY implementation so as not to use lastval().

During this correction, changed the use of lastval() that is dangerous by the use of curval (sequence) much more secure. Then, why this correction does not work in my case? In the trace, seen just after the insert, there is a select that returns no rows! This select is directed to search the sequences in the table. It does not work because it passes 'public' as table name  and 'iamaudit' as schema name instead of 'AuditEvents' and 'public'. Because in the insert, I use the string "iamaudit"."public"."AuditEvents"to identify the table. The table name is prefixed by the name of the base and the schema, which was not expected. Note that it is the OLE DB driver which writes the query and that uses this syntax.

 

The analysis of the insert query is performed in the function SC_setInsertedTable (file execute.c). So I modified this function to update the parsing  to my case and I tested successfully this correction.

 

Here in attachment some traces, a test case and the correction I made. Beware, it is a correction "quick & dirty" just to check that the bug was in this function. And I have not tested it in all possible cases.

 

I can provide a testcase as needed.

 

Best Regards,

Philippe CHAMPIGNON

 

Attachment (driverODBC.7z): application/octet-stream, 472 KiB

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc
Vaughn, Ben | 15 Sep 20:16 2014

FW: error

Hello,

 

I’m trying to help Barth install  PSQlodbc but he is getting this error message below.  Is there any support that could help us resolve this?

 

Thanks,

 

Ben Vaughn

Emdeon Desktop Support

 

 

From: Kelly, Barth
Sent: Monday, September 15, 2014 12:50 PM
To: Vaughn, Ben
Subject: eror

 

This message is confidential, intended only for the named recipient(s) and may contain information that is privileged or exempt from disclosure under applicable law. If you are not the intended recipient(s), you are notified that the dissemination, distribution, or copying of this message is strictly prohibited. If you receive this message in error or are not the named recipient(s), please notify the sender by return email and delete this message. Thank you.
Keith Handlon | 17 Sep 17:09 2014
Picon

solaris and openssl

I’m trying to get the 9.03.0300 odbc driver to work with ssl on solaris.  I get it to build fine.  But as soon as I add the sslmode connection option I’m getting rejected on the client end with the generic error.  I don’t see anything in the server logs (log_connections is turned on).

 

could not connect to server:

No such file or directory        Is the server running on host "<hostname>"

       (<ip_address>) and accepting       TCP/IP connections on port 5432?

 

I know the server is up and running and accepting ssl connections because I can connect to it from windows and linux both with and without ssl.  And the connection works fine without the sslmode option from solaris.

 

ldd doesn’t report any missing shared objects.  Nor does truss, though it appears from the truss log it may be searching the odbcinst for something.  I can’t seem to locate any unixodbc or postgresql doc that indicates any ssl odbcinst settings.

 

Any ideas?

蔡珂珂 | 12 Sep 04:44 2014

2014年9月12日 9:19:57 自动保存草稿

Dear sir or madam:
hello.I am a new postgressql user. And, now, I am studing psqlodbc.
There are some questions in my study. 
Question 1: In file psqlodbc/test/Makefile, I don't find clean target. But we can use "make clean" to clean the obejct. And in my test makefile without target clean, i could't use "make clean". How is it work?
Question 2: We use "make installcheck" for complete regression test, and the test works well. But  In file psqlodbc/test/Makefile, I only find "installcheck" and the work of creating .sql files. Now how is it work without some commands for pg_regress?
Question 3:What is their meaning and jobs, e.g REGRESS, EXTRA_CLEAN, REGRESS_OPTS?

Do I describ my question clearly, with my poor english?

Thanks a lot and look forward to hearing from you soon.
Best Regards.
Eric cai from beijing.


perry@processlinx.com | 8 Sep 19:39 2014

Abort!

Hello,

From time to time we get an abort message in th psqlodbc logs. We see no errors or warnings on the freebsd postgresql log. In the 'my' log below (highlighted in bold we see an Abort). I have also attached the corresponding psqlodbc log as it relates to the Abort in mylog.

Can you point me in the right direction here in order to determine the cause of this infrequent abort.

Setup configuration:

Windows 7 we have installed psqlodbc Driver Version 9.02.01.00 Unicode
Windows 7 client connects to a postgres sql server on freebsd 10.0 Postgresql 9.0.15


psqlodbc log
[184246.093]conn=04657F38, query='insert into "public"."raw_trans" ("_name", "_numericid", "_value", "_timestamp", "_quality") values (E'OMRONS.LINE34.TEMP_Z1_ACT', 0, E'100', '2014-09-03 18:38:47.054'::timestamp, 192) returning ctid'
[184246.156]    [ fetched 1 rows ]
[184246.156]conn=04657F38, query='SELECT * , "ctid", "id" FROM raw_trans where ctid = '(4705,80)' '
[184246.171]    [ fetched 1 rows ]
[184246.171]QR_REALLOC_error
[184246.171]conn=04657F38, query='ROLLBACK'
[184246.171]conn=04657F38, query='close "SQL_CUR04691F10"'


mylog log
[3284-184246.156]read 83, global_socket_buffersize=4096
[3284-184246.156]send_query: got id = 'C'
[3284-184246.156]send_query: ok - 'C' - BEGIN
[3284-184246.156]send_query: setting cmdbuffer = 'BEGIN'
[3284-184246.156]send_query: got id = 'T'
[3284-184246.156]QR_fetch_tuples: cursor = '', self->cursor=00000000
[3284-184246.156]num_fields = 1
[3284-184246.156]READING ATTTYPMOD
[3284-184246.156]CI_read_fields: fieldname='ctid', adtid=27, adtsize=6, atttypmod=-1 (rel,att)=(57800,65535)
[3284-184246.156]QR_fetch_tuples: past CI_read_fields: num_fields = 1
[3284-184246.156]MALLOC: tuple_size = 100, size = 800
[3284-184246.156]QR_next_tuple: inTuples = true, falling through: fcount = 0, fetch_number = 0
[3284-184246.156]qresult: len=9, buffer='(4705,80)'
[3284-184246.156]end of tuple list -- setting inUse to false: this = 046E9008 INSERT 0 1
[3284-184246.156]_QR_next_tuple: 'C' fetch_total = 1 & this_fetch = 1
[3284-184246.156]QR_next_tuple: backend_rows < CACHE_SIZE: brows = 0, cache_size = 0
[3284-184246.156]QR_next_tuple: reached eof now
[3284-184246.156]send_query: got id = 'Z'
[3284-184246.156]extend_column_bindings: entering ... self=050C29C8, bindings_allocated=0, num_columns=1
[3284-184246.156]exit extend_column_bindings=05455E50
[3284-184246.156]SC_set_Result(50c2940, 46e9008)[3284-184246.156]QResult: enter DESTRUCTOR
[3284-184246.156]retval=0
[3284-184246.156]PGAPI_ExecDirect: returned 0 from PGAPI_Execute
[3284-184246.156]pos_add_callback in ret=0
[3284-184246.156]positioned new ti=04691868
[3284-184246.156]selstr=SELECT * , "ctid", "id" FROM raw_trans where ctid = '(4705,80)'
[3284-184246.156]CC_send_query: conn=04657F38, query='SELECT * , "ctid", "id" FROM raw_trans where ctid = '(4705,80)' '
[3284-184246.156]send_query: done sending query 70bytes flushed
[3284-184246.156]in QR_Constructor
[3284-184246.156]exit QR_Constructor
[3284-184246.156]read 893, global_socket_buffersize=4096
[3284-184246.156]send_query: got id = 'T'
[3284-184246.156]QR_fetch_tuples: cursor = '', self->cursor=00000000
[3284-184246.156]num_fields = 23
[3284-184246.156]READING ATTTYPMOD
[3284-184246.156]CI_read_fields: fieldname='id', adtid=23, adtsize=4, atttypmod=-1 (rel,att)=(57800,1)
[3284-184246.156]READING ATTTYPMOD
[3284-184246.156]CI_read_fields: fieldname='_name', adtid=1043, adtsize=-1, atttypmod=64 (rel,att)=(57800,2)
[3284-184246.156]READING ATTTYPMOD
[3284-184246.156]CI_read_fields: fieldname='_numericid', adtid=23, adtsize=4, atttypmod=-1 (rel,att)=(57800,3)
[3284-184246.156]READING ATTTYPMOD
[3284-184246.156]CI_read_fields: fieldname='_value', adtid=1043, adtsize=-1, atttypmod=64 (rel,att)=(57800,4)
[3284-184246.156]READING ATTTYPMOD
[3284-184246.156]CI_read_fields: fieldname='_timestamp', adtid=1184, adtsize=8, atttypmod=-1 (rel,att)=(57800,5)
[3284-184246.156]READING ATTTYPMOD
[3284-184246.156]CI_read_fields: fieldname='_quality', adtid=23, adtsize=4, atttypmod=-1 (rel,att)=(57800,6)
[3284-184246.156]READING ATTTYPMOD
[3284-184246.156]CI_read_fields: fieldname='current_flag', adtid=16, adtsize=1, atttypmod=-1 (rel,att)=(57800,7)
[3284-184246.156]READING ATTTYPMOD
[3284-184246.156]CI_read_fields: fieldname='wc_id', adtid=23, adtsize=4, atttypmod=-1 (rel,att)=(57800,8)
[3284-184246.156]READING ATTTYPMOD
[3284-184246.156]CI_read_fields: fieldname='d_id', adtid=23, adtsize=4, atttypmod=-1 (rel,att)=(57800,9)
[3284-184246.156]READING ATTTYPMOD
[3284-184246.156]CI_read_fields: fieldname='s_id', adtid=23, adtsize=4, atttypmod=-1 (rel,att)=(57800,10)
[3284-184246.156]READING ATTTYPMOD
[3284-184246.156]CI_read_fields: fieldname='end_timestamp', adtid=1184, adtsize=8, atttypmod=-1 (rel,att)=(57800,11)
[3284-184246.156]READING ATTTYPMOD
[3284-184246.156]CI_read_fields: fieldname='shift_no', adtid=23, adtsize=4, atttypmod=-1 (rel,att)=(57800,12)
[3284-184246.156]READING ATTTYPMOD
[3284-184246.156]CI_read_fields: fieldname='bus_date', adtid=1082, adtsize=4, atttypmod=-1 (rel,att)=(57800,13)
[3284-184246.156]READING ATTTYPMOD
[3284-184246.156]CI_read_fields: fieldname='occurredon', adtid=1184, adtsize=8, atttypmod=-1 (rel,att)=(57800,14)
[3284-184246.156]READING ATTTYPMOD
[3284-184246.156]CI_read_fields: fieldname='production_piece', adtid=16, adtsize=1, atttypmod=-1 (rel,att)=(57800,15)
[3284-184246.156]READING ATTTYPMOD
[3284-184246.156]CI_read_fields: fieldname='work_order_id', adtid=23, adtsize=4, atttypmod=-1 (rel,att)=(57800,16)
[3284-184246.156]READING ATTTYPMOD
[3284-184246.156]CI_read_fields: fieldname='piece_length', adtid=1700, adtsize=-1, atttypmod=1179653 (rel,att)=(57800,17)
[3284-184246.171]READING ATTTYPMOD
[3284-184246.171]CI_read_fields: fieldname='elapsed_seconds', adtid=1700, adtsize=-1, atttypmod=1179653 (rel,att)=(57800,18)
[3284-184246.171]READING ATTTYPMOD
[3284-184246.171]CI_read_fields: fieldname='avg_ipm', adtid=1700, adtsize=-1, atttypmod=1179653 (rel,att)=(57800,19)
[3284-184246.171]READING ATTTYPMOD
[3284-184246.171]CI_read_fields: fieldname='ipm', adtid=1700, adtsize=-1, atttypmod=1179653 (rel,att)=(57800,20)
[3284-184246.171]READING ATTTYPMOD
[3284-184246.171]CI_read_fields: fieldname='added_piece_length', adtid=1700, adtsize=-1, atttypmod=262146 (rel,att)=(57800,21)
[3284-184246.171]READING ATTTYPMOD
[3284-184246.171]CI_read_fields: fieldname='ctid', adtid=27, adtsize=6, atttypmod=-1 (rel,att)=(57800,65535)
[3284-184246.171]READING ATTTYPMOD
[3284-184246.171]CI_read_fields: fieldname='id', adtid=23, adtsize=4, atttypmod=-1 (rel,att)=(57800,1)
[3284-184246.171]QR_fetch_tuples: past CI_read_fields: num_fields = 23
[3284-184246.171]MALLOC: tuple_size = 100, size = 18400
[3284-184246.171]QR_next_tuple: inTuples = true, falling through: fcount = 0, fetch_number = 0
[3284-184246.171]qresult: len=7, buffer='6394774'
[3284-184246.171]qresult: len=25, buffer='OMRONS.LINE34.TEMP_Z1_ACT'
[3284-184246.171]qresult: len=1, buffer='0'
[3284-184246.171]qresult: len=3, buffer='100'
[3284-184246.171]qresult: len=26, buffer='2014-09-03 18:38:47.054+00'
[3284-184246.171]qresult: len=3, buffer='192'
[3284-184246.171]qresult: len=1, buffer='t'
[3284-184246.171]qresult: len=1, buffer='6'
[3284-184246.171]qresult: len=1, buffer='5'
[3284-184246.171]qresult: len=2, buffer='68'
[3284-184246.171]qresult: len=1, buffer='1'
[3284-184246.171]qresult: len=10, buffer='2014-09-03'
[3284-184246.171]qresult: len=28, buffer='2014-09-03 18:38:48.21401+00'
[3284-184246.171]qresult: len=1, buffer='f'
[3284-184246.171]qresult: len=5, buffer='51107'
[3284-184246.171]qresult: len=4, buffer='0.00'
[3284-184246.171]qresult: len=9, buffer='(4705,80)'
[3284-184246.171]qresult: len=7, buffer='6394774'
[3284-184246.171]end of tuple list -- setting inUse to false: this = 046E90C0 SELECT 1
[3284-184246.171]_QR_next_tuple: 'C' fetch_total = 1 & this_fetch = 1
[3284-184246.171]QR_next_tuple: backend_rows < CACHE_SIZE: brows = 0, cache_size = 0
[3284-184246.171]QR_next_tuple: reached eof now
[3284-184246.171]send_query: got id = 'Z'
[3284-184246.171]QResult: free memory in, fcount=160
[3284-184246.171][[SQLEndTran]][3284-184246.171]entering PGAPI_Transact: hdbc=04657F38, henv=00000000
[3284-184246.171]PGAPI_Transact: sending on conn 04657F38 '1'
[3284-184246.171]CC_send_query: conn=04657F38, query='ROLLBACK'
[3284-184246.171]send_query: done sending query 14bytes flushed
[3284-184246.171]in QR_Constructor
[3284-184246.171]exit QR_Constructor
[3284-184246.171]read 20, global_socket_buffersize=4096
[3284-184246.171]send_query: got id = 'C'
[3284-184246.171]send_query: ok - 'C' - ROLLBACK
[3284-184246.171]send_query: setting cmdbuffer = 'ROLLBACK'
[3284-184246.171]send_query: returning res = 046E92E8
[3284-184246.171]send_query: got id = 'Z'
[3284-184246.171]CC_on_abort in
[3284-184246.171]CC_abort:  sending ABORT!
[3284-184246.171]QResult: enter DESTRUCTOR
[3284-184246.171]QResult: in QR_close_result
[3284-184246.171]QResult: free memory in, fcount=0
[3284-184246.171]QResult: free memory out
[3284-184246.171]QResult: exit close_result
[3284-184246.171]QResult: exit DESTRUCTOR
[3284-184246.171][SQLSetConnectAttrW][3284-184246.171]PGAPI_SetConnectAttr for 04657F38: 102 00000001
[3284-184246.171]PGAPI_SetConnectOption: entering fOption = 102 vParam = 1
[3284-184246.171]PGAPI_SetConnectOption: AUTOCOMMIT: transact_status=0, vparam=1
[3284-184246.171]CC_set_autocommit: 0->1
[3284-184246.171][[SQLFreeHandle]][3284-184246.171]PGAPI_FreeStmt: entering...hstmt=04691F10, fOption=1
[3284-184246.171]QResult: enter DESTRUCTOR
[3284-184246.171]QResult: in QR_close_result
[3284-184246.171]CC_send_query: conn=04657F38, query='close "SQL_CUR04691F10"'
[3284-184246.171]send_query: done sending query 29bytes flushed
[3284-184246.171]in QR_Constructor
[3284-184246.171]exit QR_Constructor
[3284-184246.171]read -1, global_socket_buffersize=4096
[3284-184246.171]Lasterror=10035

Alanoly Andrews | 8 Sep 16:03 2014

FW: [GENERAL] Re: [ADMIN] Cannot retrieve images inserted through VB and odbc, after a table reorganization.

Hello experts in postgres-odbc,

The following was the latest in a series of posts that I had made to the admin and general discussion groups.
This post contains an excerpt from the log produced by the postgres odbc client when an attempt was made to
retrieve an image stored on a backend postgres database (AIX server). The postgres database is 9.1.4
version. The column containing the image is defined as type "lo". The SQL is a simple one of the type "select
image from table where image_key=keyvalue". In the early part of the log all return codes are 0. When the
return code is 1, we get the "The buffer was too small for the GetData" and "Error reading from large object"
messages. Would any of you know what these messages mean and how we can get around the problem? The code for
sql retrieval is written in VB code. It should be noted that the same image(s) are retrievable through non
VB/ODBC applications.

Feedback very much appreciated.

Alanoly Andrews.

-----Original Message-----
From: Alanoly Andrews
Sent: Friday, September 05, 2014 11:33 AM
To: 'Adrian Klaver'; 'Craig James'
Cc: pgsql-admin <at> postgresql.org; pgsql-general <at> postgresql.org
Subject: RE: [GENERAL] Re: [ADMIN] Cannot retrieve images inserted through VB and odbc, after a table reorganization.

Hi Adrian,

1. We are storing our large objects in a table column whose datatype is "lo" (which, I understand,
ultimately is in fact "bytea").
2. The error messge returned to the VB is, yes, very generic. And the backend postgres server does not have
any messages relevant to this issue.
3. We set the trace on in the odbc driver and got a large log file, from which I give below the part that I think is
significant. It refers to the "buffer size being too small". Where, in the odc settings, do we set the
buffer size?


STELimageViewOn 834-fec          EXIT  SQLGetData  with return code 1 (SQL_SUCCESS_WITH_INFO)
                                HSTMT               0x002BFC70
                                UWORD                        2
                                SWORD                        1 <SQL_C_CHAR>
                                PTR                 0x0018EECF [       0] ""
                                SQLLEN                     0
                                SQLLEN *            0x0018EEA4 (6)

                                DIAG [01004] The buffer was too small for the GetData. (-2)

STELimageViewOn 834-fec          ENTER SQLGetData
                                HSTMT               0x002BFC70
                                UWORD                        2
                                SWORD                       -2 <SQL_C_BINARY>
                                PTR                 <unknown type>
                                SQLLEN                     0
                                SQLLEN *            0x0018EE54

STELimageViewOn 834-fec          EXIT  SQLGetData  with return code 1 (SQL_SUCCESS_WITH_INFO)
                                HSTMT               0x002BFC70
                                UWORD                        2
                                SWORD                       -2 <SQL_C_BINARY>
                                PTR                 <unknown type>
                                SQLLEN                     0
                                SQLLEN *            0x0018EE54 (46165)

                                DIAG [01004] The buffer was too small for the GetData. (-2)

STELimageViewOn 834-fec          ENTER SQLGetData
                                HSTMT               0x002BFC70
                                UWORD                        2
                                SWORD                       -2 <SQL_C_BINARY>
                                PTR                 <unknown type>
                                SQLLEN                 32768
                                SQLLEN *            0x003CE284

STELimageViewOn 834-fec          EXIT  SQLGetData  with return code -1 (SQL_ERROR)
                                HSTMT               0x002BFC70
                                UWORD                        2
                                SWORD                       -2 <SQL_C_BINARY>
                                PTR                 <unknown type>
                                SQLLEN                 32768
                                SQLLEN *            0x003CE284

                                DIAG [S1000] Error reading from large object. (1)

-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver <at> aklaver.com]
Sent: Thursday, September 04, 2014 4:43 PM
To: Alanoly Andrews; 'Craig James'
Cc: pgsql-admin <at> postgresql.org; pgsql-general <at> postgresql.org
Subject: Re: [GENERAL] Re: [ADMIN] Cannot retrieve images inserted through VB and odbc, after a table reorganization.

On 09/04/2014 08:03 AM, Alanoly Andrews wrote:
> Thanks, Adrian for the response.
>
> Yes, we are using the "large object" as per the specifications in the
> special "lo" module that we installed on the backend server. The table
> is created using the "lo" datatype for the image field and the table
> is being regularly used for insertions and retrievals using utilities
> other than ones using odbc. The odbc connection, too, works well on
> the table, as long as the table data is not reorganized after insertion(s).
>
> Here are three pages of our postgres odbc driver settings. Sorry, they
> are screen captures,  not plain text!

Hmm, to me at least nothing seems out of place. Though to be honest I am not sure what 'Bytea as LO' does in this situation.

Are you actually trying to store any large objects in bytea columns?

I searched on the error message you originally posted, but is fairly generic and covers a lot of different
error sources.

So, is there any more specific error information in either the Postgres or ODBC logs?

>
> Alanoly.


--
Adrian Klaver
adrian.klaver <at> aklaver.com

________________________________

If you no longer wish to receive any of our emails, click on
UNSUBSCRIBE.<mailto:unsubscribe <at> invera.com?subject=***Unsubscribe***> This e-mail may be
privileged and/or confidential, and the sender does not waive any related rights and obligations. Any
distribution, use or copying of this e-mail or the information it contains by other than an intended
recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or
otherwise) immediately.

Si vous ne désirez plus recevoir de nos courriels, veuillez appuyer sur
DÉSABONNEMENT.<mailto:unsubscribe <at> invera.com?subject=***Unsubscribe***> Ce courriel est
confidentiel et protégé. L'expéditeur ne renonce pas aux droits et obligations qui s'y rapportent.
Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une
personne autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courriel par
erreur, veuillez m'en aviser immédiatement, par retour de courriel ou par un autre moyen.

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc
Heikki Linnakangas | 5 Sep 20:13 2014

Buffer overflows in SQLGetData corner cases

If a PostgreSQL timestamp column is fetched using SQLGetData, into 
string buffer (SQL_C_CHAR), and the output buffer's length is exactly 20 
bytes, and the timestamp value has a year larger than 10000 or smaller 
than 0, the output buffer will be overflown.

The core of the problem is this, in copy_and_convert_field():
> 	case PG_TYPE_ABSTIME:
> 	case PG_TYPE_DATETIME:
> 	case PG_TYPE_TIMESTAMP_NO_TMZONE:
> 	case PG_TYPE_TIMESTAMP:
> 		len = 19;
> 		if (cbValueMax > len)
> 		{
> 			/* sprintf(rgbValueBindRow, "%.4d-%.2d-%.2d %.2d:%.2d:%.2d",
> 				std_time.y, std_time.m, std_time.d, std_time.hh, std_time.mm, std_time.ss); */
> 			stime2timestamp(&std_time, rgbValueBindRow, FALSE,
> 							PG_VERSION_GE(conn, 7.2) ? (int) cbValueMax - len - 2 : 0);
> 			len = strlen(rgbValueBindRow);
> 		}
> 		break;

It checks if the output buffer is at least 20 bytes wide, and bails out 
if it isn't. But 20 bytes isn't enough for some timestamp values that 
might come from a PostgreSQL server, e.g:

postgres=# select length('1011-02-15 15:49:18 BC'::timestamp::text);
  length
--------
      22
(1 row)

A better approach to this would be to pass the max length to 
stime2timestamp, and let it truncate it. stime2timestamp uses sprintf, 
which is easy to change to snprintf. As a bonus, the SQL standard 
behavior is to truncate the string anyway, rather than refuse to return 
anything if the whole value doesn't fit.

While looking at this, I noticed that the SQL_C_WCHAR conversion code 
doesn't add a NULL-terminator to the string, if the output buffer's size 
is not divisible by two. Now, that's an even more obscure corner case, 
but I think we should make sure that the returned string is always 
null-terminated, even if the buffer length is a bit strange.

I've pushed fixes for these bugs to the git repository.

- Heikki

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc


Gmane