<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body bgcolor="#FFFFFF" text="#000000">
Hi, <br>
I'm using a 9.0.2 db in a PowerBuilder 12.1 application, connecting
to it via ODBC driver 09.00.0200.<br>
<br>
I have a table with a bytea column:<br>
<br>
<tt>CREATE TABLE docs<br>
(<br>
blobid character(7) NOT NULL,<br>
descript character varying(40),<br>
data date,<br>
"lock" character(1),<br>
ora character(5),<br>
parita character(1),<br>
tipo character(3),<br>
dbvtesto bytea,<br>
CONSTRAINT docs_pkey PRIMARY KEY (blobid),<br>
CONSTRAINT fk1_docs FOREIGN KEY (blobid)<br>
REFERENCES blob (blobid) MATCH SIMPLE<br>
ON UPDATE NO ACTION ON DELETE CASCADE<br>
)<br>
WITH (<br>
OIDS=FALSE<br>
);</tt><br>
<br>
<br>
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.<br>
<br>
If someone has a proposal ... <br>
<br>
The <b>logs</b>:<br>
<u>psqlodbclog</u>:<br>
<br>
[70.282]conn=0F0EB070, query='select dbvtesto
FROM docs WHERE blobid ='000005T' '<br>
[84.635] [ fetched 1 rows ]<br>
[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'<br>
[84.694] [ fetched 8 rows ]<br>
[84.745]PGAPI_Columns:
table='docs',field_name='blobid',type=1042,name='bpchar'<br>
[84.773]PGAPI_Columns:
table='docs',field_name='descript',type=1043,name='varchar'<br>
[84.803]PGAPI_Columns:
table='docs',field_name='data',type=1082,name='date'<br>
[84.829]PGAPI_Columns:
table='docs',field_name='lock',type=1042,name='bpchar'<br>
[84.858]PGAPI_Columns:
table='docs',field_name='ora',type=1042,name='bpchar'<br>
[84.888]PGAPI_Columns:
table='docs',field_name='parita',type=1042,name='bpchar'<br>
[84.916]PGAPI_Columns:
table='docs',field_name='tipo',type=1042,name='bpchar'<br>
[84.945]PGAPI_Columns:
table='docs',field_name='dbvtesto',type=17,name='bytea'<br>
[1886.060]conn=0F0EB070, query='select dbvcargo
FROM blob WHERE blobid ='000005T' '<br>
<br>
<u>mylog</u>:<br>
[14596-70.280]CC_send_query: conn=0F0EB070, query='select
dbvtesto FROM docs WHERE blobid ='000005T' '<br>
[14596-70.283]send_query: done sending query 76bytes flushed<br>
[14596-70.284]in QR_Constructor<br>
[14596-70.285]exit QR_Constructor<br>
[14596-70.285]read -1, global_socket_buffersize=4096<br>
[14596-70.286]Lasterror=10035<br>
[14596-70.330]read 4096, global_socket_buffersize=4096<br>
[14596-70.331]send_query: got id = 'T'<br>
[14596-70.331]QR_fetch_tuples: cursor = '', self->cursor=00000000<br>
[14596-70.332]num_fields = 1<br>
[14596-70.333]READING ATTTYPMOD<br>
[14596-70.333]CI_read_fields: fieldname='dbvtesto', adtid=17,
adtsize=-1, atttypmod=-1 (rel,att)=(252865,8)<br>
[14596-70.335]QR_fetch_tuples: past CI_read_fields: num_fields = 1<br>
[14596-70.336]MALLOC: tuple_size = 100, size = 800<br>
[14596-70.336]QR_next_tuple: inTuples = true, falling through:
fcount = 0, fetch_number = 0<br>
[14596-70.338]read 4096, global_socket_buffersize=4096<br>
<br>
766 similar rows + last at 2315 <br>
<br>
[14596-70.594]read 2315, global_socket_buffersize=4096<br>
[14596-70.594]qresult: len=3139786, buffer=' ....<br>
<br>
the text ...<br>
<br>
[14596-84.635]end of tuple list -- setting inUse to false: this =
0F0EE560 SELECT 1<br>
[14596-84.636]_QR_next_tuple: 'C' fetch_total = 1 & this_fetch =
1<br>
[14596-84.636]QR_next_tuple: backend_rows < CACHE_SIZE: brows =
0, cache_size = 0<br>
[14596-84.636]QR_next_tuple: reached eof now<br>
[14596-84.637]send_query: got id = 'Z'<br>
[14596-84.637] done sending the query:<br>
[14596-84.637]extend_column_bindings: entering ... self=0F0EE7C0,
bindings_allocated=0, num_columns=1<br>
[14596-84.637]exit extend_column_bindings=1110E070<br>
<br>
many similar rows, the blocks are repeated on 40000 bytes step<br>
n.b.: 40000 is the value of MaxVarchar in ODBC configuration and
ByteaAsLo = 0 (but set it to 1 doesn't change the behaviour)<br>
<br>
[14596-801.327]SQL_C_BINARY: len = 929892, copy_len = 40000<br>
[14596-801.327]STATEMENT WARNING: func=PGAPI_GetData, desc='',
errnum=-2, errmsg='The buffer was too small for the GetData.'<br>
[14596-801.328][SQLGetData][14596-801.328]PGAPI_GetData: enter,
stmt=0F0EE738 icol=1<br>
[14596-801.329] num_rows = 1<br>
[14596-801.329] value = '<br>
<br>
<br>
[14596-815.310]**** PGAPI_GetData: icol = 0, target_type = -2,
field_type = 17, value = '<br>
<br>
[14596-829.403]copy_and_convert: field_type = 17, fctype = -2, value
= '....<br>
<br>
[14596-843.453]SQL_C_BINARY: len = 889892, copy_len = 40000<br>
[14596-843.453]STATEMENT WARNING: func=PGAPI_GetData, desc='',
errnum=-2, errmsg='The buffer was too small for the GetData.'<br>
[14596-843.454][SQLGetData][14596-843.454]PGAPI_GetData: enter,
stmt=0F0EE738 icol=1<br>
[14596-843.454] num_rows = 1<br>
[14596-843.454] value = '...<br>
<br>
<br>
<b>The ODBC configuration</b>:<br>
"Driver"="C:\\Programmi\\PostgreSQL\\9.0\\PSQLODBC35W.DLL"<br>
"CommLog"="1"<br>
"Debug"="0"<br>
"Fetch"="100"<br>
"Optimizer"="0"<br>
"Ksqo"="0"<br>
"UniqueIndex"="1"<br>
"UseDeclareFetch"="0"<br>
"UnknownSizes"="0"<br>
"TextAsLongVarchar"="1"<br>
"UnknownsAsLongVarchar"="0"<br>
"BoolsAsChar"="1"<br>
"Parse"="0"<br>
"CancelAsFreeStmt"="0"<br>
"MaxVarcharSize"="40000"<br>
"MaxLongVarcharSize"="80000"<br>
"ExtraSysTablePrefixes"="dd_;"<br>
"Description"=""<br>
"Database"="Argos_UTF"<br>
"Servername"="localhost"<br>
"Port"="5432"<br>
"Username"="postgres"<br>
"UID"="postgres"<br>
"Password"="postgres"<br>
"ReadOnly"="0"<br>
"ShowOidColumn"="0"<br>
"FakeOidIndex"="0"<br>
"RowVersioning"="0"<br>
"ShowSystemTables"="0"<br>
"Protocol"="7.4-1"<br>
"ConnSettings"=""<br>
"DisallowPremature"="0"<br>
"UpdatableCursors"="1"<br>
"LFConversion"="1"<br>
"TrueIsMinus1"="0"<br>
"BI"="0"<br>
"AB"="0"<br>
"ByteaAsLongVarBinary"="0"<br>
"UseServerSidePrepare"="0"<br>
"LowerCaseIdentifier"="0"<br>
"GssAuthUseGSS"="0"<br>
"SSLmode"="disable"<br>
"XaOpt"="1"<br>
<br>
<br>
<div class="moz-signature">-- <br>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Documento senza titolo</title>
<style type="text/css">
<!--
.Stile1 {
color: #888888;
font-weight: bold;
font-family: Gill Sans Std;
}
.Stile2 {
color: #CCCCCC;
font-weight: bold;
font-size: 11px;
font-family: Gill Sans Std;
}
.Stile3 {
color: #777777;
font-weight: bold;
font-size: 13px;
font-family: Gill Sans Std;
}
.Stile4 {
color: #888888;
font-size: 11px;
font-family: Calibri;
}
.Stile5 {
color: #000000;
font-weight: bold;
font-size: 16px;
font-family: Gill Sans Std;
}
.Stile6 {color: #FF0000}
-->
</style>
<p> </p>
<div><br>
</div>
</div>
</body>
</html>