Hi,
I'm using a 9.0.2 db in a PowerBuilder 12.1 application, connecting
to it via ODBC driver 09.00.0200.
I have a table with a bytea column:
CREATE TABLE docs
(
blobid character(7) NOT NULL,
descript character varying(40),
data date,
"lock" character(1),
ora character(5),
parita character(1),
tipo character(3),
dbvtesto bytea,
CONSTRAINT docs_pkey PRIMARY KEY (blobid),
CONSTRAINT fk1_docs FOREIGN KEY (blobid)
REFERENCES blob (blobid) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE
)
WITH (
OIDS=FALSE
);
When I retrieve via embedded SQL the dbvtesto column (using PK obv.)
I get it in few seconds if its length is about 10 KB, but I've to
wait 31 minutes (!) if it's about 1 MB.
If someone has a proposal ...
The logs:
psqlodbclog:
[70.282]conn=0F0EB070, query='select dbvtesto
FROM docs WHERE blobid ='000005T' '
[84.635] [ fetched 1 rows ]
[84.657]conn=0F0EB070, 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, pg_get_expr(d.adbin,
d.adrelid), case t.typtype when 'd' then t.typbasetype else 0 end,
t.typtypmod from (((pg_catalog.pg_class c inner join
pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.oid =
252865) 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'
[84.694] [ fetched 8 rows ]
[84.745]PGAPI_Columns:
table='docs',field_name='blobid',type=1042,name='bpchar'
[84.773]PGAPI_Columns:
table='docs',field_name='descript',type=1043,name='varchar'
[84.803]PGAPI_Columns:
table='docs',field_name='data',type=1082,name='date'
[84.829]PGAPI_Columns:
table='docs',field_name='lock',type=1042,name='bpchar'
[84.858]PGAPI_Columns:
table='docs',field_name='ora',type=1042,name='bpchar'
[84.888]PGAPI_Columns:
table='docs',field_name='parita',type=1042,name='bpchar'
[84.916]PGAPI_Columns:
table='docs',field_name='tipo',type=1042,name='bpchar'
[84.945]PGAPI_Columns:
table='docs',field_name='dbvtesto',type=17,name='bytea'
[1886.060]conn=0F0EB070, query='select dbvcargo
FROM blob WHERE blobid ='000005T' '
mylog:
[14596-70.280]CC_send_query: conn=0F0EB070, query='select
dbvtesto FROM docs WHERE blobid ='000005T' '
[14596-70.283]send_query: done sending query 76bytes flushed
[14596-70.284]in QR_Constructor
[14596-70.285]exit QR_Constructor
[14596-70.285]read -1, global_socket_buffersize=4096
[14596-70.286]Lasterror=10035
[14596-70.330]read 4096, global_socket_buffersize=4096
[14596-70.331]send_query: got id = 'T'
[14596-70.331]QR_fetch_tuples: cursor = '', self->cursor=00000000
[14596-70.332]num_fields = 1
[14596-70.333]READING ATTTYPMOD
[14596-70.333]CI_read_fields: fieldname='dbvtesto', adtid=17,
adtsize=-1, atttypmod=-1 (rel,att)=(252865,8)
[14596-70.335]QR_fetch_tuples: past CI_read_fields: num_fields = 1
[14596-70.336]MALLOC: tuple_size = 100, size = 800
[14596-70.336]QR_next_tuple: inTuples = true, falling through:
fcount = 0, fetch_number = 0
[14596-70.338]read 4096, global_socket_buffersize=4096
766 similar rows + last at 2315
[14596-70.594]read 2315, global_socket_buffersize=4096
[14596-70.594]qresult: len=3139786, buffer=' ....
the text ...
[14596-84.635]end of tuple list -- setting inUse to false: this =
0F0EE560 SELECT 1
[14596-84.636]_QR_next_tuple: 'C' fetch_total = 1 & this_fetch =
1
[14596-84.636]QR_next_tuple: backend_rows < CACHE_SIZE: brows =
0, cache_size = 0
[14596-84.636]QR_next_tuple: reached eof now
[14596-84.637]send_query: got id = 'Z'
[14596-84.637] done sending the query:
[14596-84.637]extend_column_bindings: entering ... self=0F0EE7C0,
bindings_allocated=0, num_columns=1
[14596-84.637]exit extend_column_bindings=1110E070
many similar rows, the blocks are repeated on 40000 bytes step
n.b.: 40000 is the value of MaxVarchar in ODBC configuration and
ByteaAsLo = 0 (but set it to 1 doesn't change the behaviour)
[14596-801.327]SQL_C_BINARY: len = 929892, copy_len = 40000
[14596-801.327]STATEMENT WARNING: func=PGAPI_GetData, desc='',
errnum=-2, errmsg='The buffer was too small for the GetData.'
[14596-801.328][SQLGetData][14596-801.328]PGAPI_GetData: enter,
stmt=0F0EE738 icol=1
[14596-801.329] num_rows = 1
[14596-801.329] value = '
[14596-815.310]**** PGAPI_GetData: icol = 0, target_type = -2,
field_type = 17, value = '
[14596-829.403]copy_and_convert: field_type = 17, fctype = -2, value
= '....
[14596-843.453]SQL_C_BINARY: len = 889892, copy_len = 40000
[14596-843.453]STATEMENT WARNING: func=PGAPI_GetData, desc='',
errnum=-2, errmsg='The buffer was too small for the GetData.'
[14596-843.454][SQLGetData][14596-843.454]PGAPI_GetData: enter,
stmt=0F0EE738 icol=1
[14596-843.454] num_rows = 1
[14596-843.454] value = '...
The ODBC configuration:
"Driver"="C:\\Programmi\\PostgreSQL\\9.0\\PSQLODBC35W.DLL"
"CommLog"="1"
"Debug"="0"
"Fetch"="100"
"Optimizer"="0"
"Ksqo"="0"
"UniqueIndex"="1"
"UseDeclareFetch"="0"
"UnknownSizes"="0"
"TextAsLongVarchar"="1"
"UnknownsAsLongVarchar"="0"
"BoolsAsChar"="1"
"Parse"="0"
"CancelAsFreeStmt"="0"
"MaxVarcharSize"="40000"
"MaxLongVarcharSize"="80000"
"ExtraSysTablePrefixes"="dd_;"
"Description"=""
"Database"="Argos_UTF"
"Servername"="localhost"
"Port"="5432"
"Username"="postgres"
"UID"="postgres"
"Password"="postgres"
"ReadOnly"="0"
"ShowOidColumn"="0"
"FakeOidIndex"="0"
"RowVersioning"="0"
"ShowSystemTables"="0"
"Protocol"="7.4-1"
"ConnSettings"=""
"DisallowPremature"="0"
"UpdatableCursors"="1"
"LFConversion"="1"
"TrueIsMinus1"="0"
"BI"="0"
"AB"="0"
"ByteaAsLongVarBinary"="0"
"UseServerSidePrepare"="0"
"LowerCaseIdentifier"="0"
"GssAuthUseGSS"="0"
"SSLmode"="disable"
"XaOpt"="1"
--
Documento senza titolo