The following errors only happen when using the "USE DECLARE/FETCH"
option in the ODBC Driver. When DECLARE/FETCH is set, the data sent to
some of our programs is reduced from 2 MBytes to 100 KBytes. We want to
achieve this reduction. But, with the DECLARE/FETCH option set,
implicit transactions are used ... and fail !!
This is a PRODUCTION environment, with some 20 clients accessing the
DB.
Can anyone help? Thanks in advance.
Nuno Goncalves
....
The 4th item was truncated
The buffer size = 17 and the value is
'DD13m &nbs
p;
'
conn=20986272, query='declare SQL_CUR0140EF48 cursor for SELECT
T1.CdUsr, T1.Perfil, T2.DscPrf FROM UsrPrf T1 LEFT OUTER JOIN
PrfTbl T2 ON T1.Perfil = T2.Perfil WHERE ( T1.CdUsr = 'Nuno
Goncalves' ) AND ( T1.Perfil = 'VC' ) ORDER BY T1.CdUsr, T1.Perfil '
conn=20986272, query='fetch 64 in SQL_CUR0140EF48'
[ fetched 0 rows ]
conn=20986272, query='close SQL_CUR0140EF48'
conn=20986272, query='close SQL_CUR0140FA70'
conn=20986272, query='declare SQL_CUR0140FA70 cursor for SELECT
T1.CdUsr FROM UsrEsp T1 WHERE ( T1.CdUsr = 'Nuno Goncalves' ) ORDER BY
T1.CdUsr '
conn=20986272, query='fetch 64 in SQL_CUR0140FA70'
[ fetched 1 rows ]
conn=20986272, query='declare SQL_CUR0140FC70 cursor for SELECT
T1.CdUsr, T1.Perfil, T2.DscPrf FROM UsrPrf T1 LEFT OUTER JOIN
PrfTbl T2 ON T1.Perfil = T2.Perfil WHERE ( T1.CdUsr = 'Nuno
Goncalves' ) ORDER BY T1.CdUsr, T1.Perfil '
conn=20986272, query='fetch 64 in SQL_CUR0140FC70'
[ fetched 0 rows ]
conn=20986272, query='close SQL_CUR0140FC70'
conn=20986272, query='declare SQL_CUR0140EF48 cursor for SELECT
T1.CdUsr, T1.Perfil, T2.DscPrf FROM UsrPrf T1 LEFT OUTER JOIN
PrfTbl T2 ON T1.Perfil = T2.Perfil WHERE ( T1.CdUsr = 'Nuno
Goncalves' ) AND ( T1.Perfil = 'VC' ) ORDER BY T1.CdUsr, T1.Perfil '
conn=20986272, query='fetch 64 in SQL_CUR0140EF48'
[ fetched 0 rows ]
conn=20986272, query='close SQL_CUR0140EF48'
conn=20986272, query='close SQL_CUR0140FA70'
conn=20986272, query='declare SQL_CUR0140FA70 cursor for SELECT
T1.CdUsr FROM UsrEsp T1 WHERE ( T1.CdUsr = 'Nuno Goncalves' ) ORDER BY
T1.CdUsr '
conn=20986272, query='fetch 64 in SQL_CUR0140FA70'
[ fetched 1 rows ]
conn=20986272, query='declare SQL_CUR0140FC70 cursor for SELECT
T1.CdUsr, T1.Perfil, T2.DscPrf FROM UsrPrf T1 LEFT OUTER JOIN
PrfTbl T2 ON T1.Perfil = T2.Perfil WHERE ( T1.CdUsr = 'Nuno
Goncalves' ) ORDER BY T1.CdUsr, T1.Perfil '
conn=20986272, query='fetch 64 in SQL_CUR0140FC70'
[ fetched 0 rows ]
conn=20986272, query='close SQL_CUR0140FC70'
conn=20986272, query='declare SQL_CUR0140EF48 cursor for SELECT
T1.CdUsr, T1.Perfil, T2.DscPrf FROM UsrPrf T1 LEFT OUTER JOIN
PrfTbl T2 ON T1.Perfil = T2.Perfil WHERE ( T1.CdUsr = 'Nuno
Goncalves' ) AND ( T1.Perfil = 'VC' ) ORDER BY T1.CdUsr, T1.Perfil '
conn=20986272, query='fetch 64 in SQL_CUR0140EF48'
[ fetched 0 rows ]
conn=20986272, query='close SQL_CUR0140EF48'
conn=20986272, query='close SQL_CUR0140FA70'
conn=20986272, query='declare SQL_CUR0140FA70 cursor for SELECT
T1.CdUsr FROM UsrEsp T1 WHERE ( T1.CdUsr = 'Nuno Goncalves' ) ORDER BY
T1.CdUsr '
conn=20986272, query='fetch 64 in SQL_CUR0140FA70'
[ fetched 1 rows ]
conn=20986272, query='declare SQL_CUR0140FC70 cursor for SELECT
T1.CdUsr, T1.Perfil, T2.DscPrf FROM UsrPrf T1 LEFT OUTER JOIN
PrfTbl T2 ON T1.Perfil = T2.Perfil WHERE ( T1.CdUsr = 'Nuno
Goncalves' ) ORDER BY T1.CdUsr, T1.Perfil '
conn=20986272, query='fetch 64 in SQL_CUR0140FC70'
[ fetched 0 rows ]
conn=20986272, query='close SQL_CUR0140FC70'
conn=20986272, query='declare SQL_CUR01732D38 cursor for SELECT
T1.CnCiaR, T2.CdCia, T3.RazonSocial, T1.CnCarga, T1.TpCarga, T1.FileX,
T1.RegTot, T1.RegOK, T1.FCarga, T1.HCarga, T1.StCarga, T1.Usr, T1.Pgm,
T1.FAud, T1.HAud, T1.CntMod FROM Carga T1 LEFT OUTER JOIN Persona
T3 ON T1.CnCiaR = T3.CnPer LEFT OUTER JOIN Compania T2 ON
T1.CnCiaR = T2.CnCiaR WHERE ( T1.CnCiaR = '1' ) AND ( T1.TpCarga
= '5' ) ORDER BY T1.CnCiaR, T1.TpCarga, T1.CnCarga DESC,
T1.StCarga DESC '
conn=20986272, query='fetch 64 in SQL_CUR01732D38'
[ fetched 1 rows ]
conn=20986272, query='close SQL_CUR01732D38'
conn=20986272, query='declare SQL_CUR01728CE8 cursor for SELECT * FROM
cargar_pedido('EMB 74_2',1,'S','','Nuno Goncalves');
VACUUM pddasm;'
ERROR from backend during send_query: 'ERROR: VACUUM cannot run
inside a transaction block'
conn=20986272, query='ROLLBACK'
STATEMENT ERROR: func=SC_execute, desc='', errnum=7, errmsg='Error while
executing the query'
&nbs
p;
------------------------------------------------------------
&nbs
p;
hdbc=20986272, stmt=24284392, result=24316648
&nbs
p;
manual_result=0, prepare=1, internal=0
&nbs
p;
bindings=24284888, bindings_allocated=1
&nbs
p;
parameters=0, parameters_allocated=0
&nbs
p;
statement_type=0, statement='SELECT * FROM cargar_pedido('EMB
74_2',1,'S','','Nuno Goncalves');
VACUUM pddasm;'
&nbs
p;
stmt_with_params='declare SQL_CUR01728CE8 cursor for SELECT * FROM
cargar_pedido('EMB 74_2',1,'S','','Nuno Goncalves');
VACUUM pddasm;'
&nbs
p;
data_at_exec=-1, current_exec_param=-1, put_data=0
&nbs
p;
currTuple=-1, current_col=-1, lobj_fd=-1
&nbs
p;
maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0,
scroll_concurrency=1
&nbs
p; cursor_name='SQL_CUR01728CE8'
&nbs
p;
----------------QResult Info -------------------------------
&nbs
p;
fields=24273768, manual_tuples=0, backend_tuples=0, tupleField=0, conn=0
&nbs
p;
fetch_count=0, num_total_rows=0, num_fields=0, cursor='(NULL)'
&nbs
p;
message='ERROR: VACUUM cannot run inside a transaction block',
command='DECLARE CURSOR', notice='(NULL)'
&nbs
p; status=7, inTuples=0
CONN ERROR: func=SC_execute, desc='', errnum=110, errmsg='ERROR:
VACUUM cannot run inside a transaction block'
------------------------------------------------------------
henv=20981536, conn=20986272, status=1, num_stmts=16
sock=20981592, stmts=20981664, lobj_type=-999
---------------- Socket Info -------------------------------
socket=560, reverse=0, errornumber=0, errormsg='(NULL)'
buffer_in=20997272, buffer_out=21001376
buffer_filled_in=11, buffer_filled_out=0, buffer_read_in=11
conn=20986272, query='declare SQL_CUR01732D38 cursor for SELECT
T1.CnCiaR, T2.CdCia, T3.RazonSocial, T1.CnCarga, T1.TpCarga, T1.FileX,
T1.RegTot, T1.RegOK, T1.FCarga, T1.HCarga, T1.StCarga, T1.Usr, T1.Pgm,
T1.FAud, T1.HAud, T1.CntMod FROM Carga T1 LEFT OUTER JOIN Persona
T3 ON T1.CnCiaR = T3.CnPer LEFT OUTER JOIN Compania T2 ON
T1.CnCiaR = T2.CnCiaR WHERE ( T1.CnCiaR = '1' ) AND ( T1.TpCarga
= '5' ) ORDER BY T1.CnCiaR, T1.TpCarga, T1.CnCarga DESC,
T1.StCarga DESC '
conn=20986272, query='fetch 64 in SQL_CUR01732D38'
[ fetched 1 rows ]
conn=20986272, query='close SQL_CUR01732D38'
conn=20986272, query='declare SQL_CUR01732D38 cursor for SELECT
T1.CnCiaR, T2.CdCia, T3.RazonSocial, T1.CnCarga, T1.TpCarga, T1.FileX,
T1.RegTot, T1.RegOK, T1.FCarga, T1.HCarga, T1.StCarga, T1.Usr, T1.Pgm,
T1.FAud, T1.HAud, T1.CntMod FROM Carga T1 LEFT OUTER JOIN Persona
T3 ON T1.CnCiaR = T3.CnPer LEFT OUTER JOIN Compania T2 ON
T1.CnCiaR = T2.CnCiaR WHERE ( T1.CnCiaR = '1' ) AND ( T1.TpCarga
= '5' ) ORDER BY T1.CnCiaR, T1.TpCarga, T1.CnCarga DESC,
T1.StCarga DESC '
conn=20986272, query='fetch 64 in SQL_CUR01732D38'
[ fetched 1 rows ]
conn=20986272, query='close SQL_CUR01732D38'
conn=20986272, query='declare SQL_CUR01728CE8 cursor for SELECT * FROM
cargar_pedido('EMB 74_2',1,'S','','Nuno Goncalves');
--VACUUM pddasm;'
conn=20986272, query='fetch 64 in SQL_CUR01728CE8'
[ fetched 3 rows ]
conn=20986272, query='declare SQL_CUR01728F90 cursor for SELECT
T1.CnCiaR, T2.CdCia, T3.RazonSocial, T1.CnCarga, T1.TpCarga, T1.FileX,
T1.RegTot, T1.RegOK, T1.FCarga, T1.HCarga, T1.StCarga, T1.Usr, T1.Pgm,
T1.FAud, T1.HAud, T1.CntMod FROM Carga T1 LEFT OUTER JOIN Persona
T3 ON T1.CnCiaR = T3.CnPer LEFT OUTER JOIN Compania T2 ON
T1.CnCiaR = T2.CnCiaR WHERE ( T1.CnCiaR = '1' ) AND ( T1.CnCarga
= '4' ) ORDER BY T1.CnCiaR, T1.CnCarga '
conn=20986272, query='fetch 64 in SQL_CUR01728F90'
[ fetched 1 rows ]
conn=20986272, query='declare SQL_CUR01732238 cursor for SELECT
T1.CnCiaR, T1.CnCarga, T1.Crear FROM PddAsmH T1 WHERE ( T1.CnCiaR = '1'
) AND ( T1.CnCarga = '4' ) ORDER BY T1.CnCiaR, T1.CnCarga '
conn=20986272, query='fetch 64 in SQL_CUR01732238'
[ fetched 1 rows ]
conn=20986272, query='close SQL_CUR01408548'
ERROR from backend during send_query: 'ERROR: cursor
"sql_cur01408548" does not exist'
conn=20986272, query='declare SQL_CUR01408548 cursor for SELECT
T1.CdUsr, T1.CnCiaR, T2.CdCia, T3.RazonSocial, T1.CnPer,
T4.RazonSocial, T1.Alcance, T1.Usr, T1.Pgm, T1.FAud, T1.HAud, T1.CntMod
FROM UsrTbl T1 LEFT OUTER JOIN Persona T4 ON T1.CnPer =
T4.CnPer LEFT OUTER JOIN Persona T3 ON T1.CnCiaR = T3.CnPer LEFT
OUTER JOIN Compania T2 ON T1.CnCiaR = T2.CnCiaR WHERE (
T1.CdUsr = 'Nuno Goncalves' ) ORDER BY T1.CdUsr '
ERROR from backend during send_query: 'ERROR: current transaction
is aborted, commands ignored until end of transaction block'
conn=20986272, query='ROLLBACK'
STATEMENT ERROR: func=SC_execute, desc='', errnum=7, errmsg='Error while
executing the query'
&nbs
p;
------------------------------------------------------------
&nbs
p;
hdbc=20986272, stmt=21005640, result=21005480
&nbs
p;
manual_result=0, prepare=1, internal=0
&nbs
p;
bindings=21013120, bindings_allocated=12
&nbs
p;
parameters=21011000, parameters_allocated=1
&nbs
p;
statement_type=0, statement='SELECT T1.CdUsr, T1.CnCiaR, T2.CdCia,
T3.RazonSocial, T1.CnPer, T4.RazonSocial, T1.Alcance, T1.Usr, T1.Pgm,
T1.FAud, T1.HAud, T1.CntMod FROM {oj UsrTbl T1 LEFT OUTER JOIN
Persona T4 ON T1.CnPer = T4.CnPer LEFT OUTER JOIN Persona T3
ON T1.CnCiaR = T3.CnPer LEFT OUTER JOIN Compania T2 ON
T1.CnCiaR = T2.CnCiaR } WHERE ( T1.CdUsr = ? ) ORDER BY T1.CdUsr '
&nbs
p;
stmt_with_params='declare SQL_CUR01408548 cursor for SELECT T1.CdUsr,
T1.CnCiaR, T2.CdCia, T3.RazonSocial, T1.CnPer, T4.RazonSocial,
T1.Alcance, T1.Usr, T1.Pgm, T1.FAud, T1.HAud, T1.CntMod FROM
UsrTbl T1 LEFT OUTER JOIN Persona T4 ON T1.CnPer = T4.CnPer LEFT
OUTER JOIN Persona T3 ON T1.CnCiaR = T3.CnPer LEFT OUTER JOIN
Compania T2 ON T1.CnCiaR = T2.CnCiaR WHERE ( T1.CdUsr =
'Nuno Goncalves' ) ORDER BY T1.CdUsr '
&nbs
p;
data_at_exec=-1, current_exec_param=-1, put_data=0
&nbs
p;
currTuple=-1, current_col=-1, lobj_fd=-1
&nbs
p;
maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0,
scroll_concurrency=1
&nbs
p; cursor_name='SQL_CUR01408548'
&nbs
p;
----------------QResult Info -------------------------------
&nbs
p;
fields=24304304, manual_tuples=0, backend_tuples=0, tupleField=0, conn=0
&nbs
p;
fetch_count=0, num_total_rows=0, num_fields=0, cursor='(NULL)'
&nbs
p;
message='ERROR: current transaction is aborted, commands ignored
until end of transaction block', command='(NULL)', notice='(NULL)'
&nbs
p; status=7, inTuples=0
CONN ERROR: func=SC_execute, desc='', errnum=110, errmsg='ERROR:
VACUUM cannot run inside a transaction block'
------------------------------------------------------------
henv=20981536, conn=20986272, status=1, num_stmts=16
sock=20981592, stmts=20981664, lobj_type=-999
---------------- Socket Info -------------------------------
socket=560, reverse=0, errornumber=0, errormsg='(NULL)'
buffer_in=20997272, buffer_out=21001376
buffer_filled_in=11, buffer_filled_out=0, buffer_read_in=11
conn=20986272, query='declare SQL_CUR016F3470 cursor for SELECT
T1.CnCiaR, T1.CnCarga, T1.CnData, T1.CdPddPrv, T1.AnoFiscal,
T1.CdPedido, T1.RngPdd, T1.ProdPres, T1.CdPrdT, T1.CdPrsT, T1.CnProd,
T1.CnPres, T1.CdLinea, T1.TpET, T1.Escala, T1.BltsCnfT, T1.PrecTxt,
T1.FCarga, T1.StsPddA FROM PddAsm T1 WHERE ( T1.CnCiaR = '1' ) AND (
T1.CnCarga = '4' ) ORDER BY T1.CnCiaR, T1.CnCarga, T1.CnData '
conn=20986272, query='fetch 64 in SQL_CUR016F3470'
[ fetched 0 rows ]
conn=20986272, query='close SQL_CUR016F3470'
conn=20986272, query='declare SQL_CUR01732D38 cursor for SELECT
T1.CnCiaR, T2.CdCia, T3.RazonSocial, T1.CnCarga, T1.TpCarga, T1.FileX,
T1.RegTot, T1.RegOK, T1.FCarga, T1.HCarga, T1.StCarga, T1.Usr, T1.Pgm,
T1.FAud, T1.HAud, T1.CntMod FROM Carga T1 LEFT OUTER JOIN Persona
T3 ON T1.CnCiaR = T3.CnPer LEFT OUTER JOIN Compania T2 ON
T1.CnCiaR = T2.CnCiaR WHERE ( T1.CnCiaR = '1' ) AND ( T1.TpCarga
= '5' ) ORDER BY T1.CnCiaR, T1.TpCarga, T1.CnCarga DESC,
T1.StCarga DESC '
conn=20986272, query='fetch 64 in SQL_CUR01732D38'
[ fetched 1 rows ]
conn=20986272, query='close SQL_CUR01732D38'
conn=20986272, query='declare SQL_CUR016F3470 cursor for SELECT
T1.CnCiaR, T1.CnCarga, T1.CnData, T1.CdPddPrv, T1.AnoFiscal,
T1.CdPedido, T1.RngPdd, T1.ProdPres, T1.CdPrdT, T1.CdPrsT, T1.CnProd,
T1.CnPres, T1.CdLinea, T1.TpET, T1.Escala, T1.BltsCnfT, T1.PrecTxt,
T1.FCarga, T1.StsPddA FROM PddAsm T1 WHERE ( T1.CnCiaR = '1' ) AND (
T1.CnCarga = '4' ) ORDER BY T1.CnCiaR, T1.CnCarga, T1.CnData '
conn=20986272, query='fetch 64 in SQL_CUR016F3470'
[ fetched 0 rows ]
conn=20986272, query='close SQL_CUR016F3470'
conn=20986272, query='declare SQL_CUR01732D38 cursor for SELECT
T1.CnCiaR, T2.CdCia, T3.RazonSocial, T1.CnCarga, T1.TpCarga, T1.FileX,
T1.RegTot, T1.RegOK, T1.FCarga, T1.HCarga, T1.StCarga, T1.Usr, T1.Pgm,
T1.FAud, T1.HAud, T1.CntMod FROM Carga T1 LEFT OUTER JOIN Persona
T3 ON T1.CnCiaR = T3.CnPer LEFT OUTER JOIN Compania T2 ON
T1.CnCiaR = T2.CnCiaR WHERE ( T1.CnCiaR = '1' ) AND ( T1.TpCarga
= '5' ) ORDER BY T1.CnCiaR, T1.TpCarga, T1.CnCarga DESC,
T1.StCarga DESC '
conn=20986272, query='fetch 64 in SQL_CUR01732D38'
[ fetched 1 rows ]
conn=20986272, query='close SQL_CUR01732D38'
conn=20986272, query='declare SQL_CUR01732D38 cursor for SELECT
T1.CnCiaR, T2.CdCia, T3.RazonSocial, T1.CnCarga, T1.TpCarga, T1.FileX,
T1.RegTot, T1.RegOK, T1.FCarga, T1.HCarga, T1.StCarga, T1.Usr, T1.Pgm,
T1.FAud, T1.HAud, T1.CntMod FROM Carga T1 LEFT OUTER JOIN Persona
T3 ON T1.CnCiaR = T3.CnPer LEFT OUTER JOIN Compania T2 ON
T1.CnCiaR = T2.CnCiaR WHERE ( T1.CnCiaR = '1' ) AND ( T1.TpCarga
= '5' ) ORDER BY T1.CnCiaR, T1.TpCarga, T1.CnCarga DESC,
T1.StCarga DESC '
conn=20986272, query='fetch 64 in SQL_CUR01732D38'
[ fetched 1 rows ]
conn=20986272, query='close SQL_CUR01732D38'
conn=20986272, query='close SQL_CUR0140CC48'
ERROR from backend during send_query: 'ERROR: cursor
"sql_cur0140cc48" does not exist'
conn=20986272, query='close SQL_CUR0140FA70'
ERROR from backend during send_query: 'ERROR: current transaction
is aborted, commands ignored until end of transaction block'
conn=20986272, query='close SQL_CUR0140F550'
ERROR from backend during send_query: 'ERROR: current transaction
is aborted, commands ignored until end of transaction block'
conn=20986272, query='close SQL_CUR016F44B0'
ERROR from backend during send_query: 'ERROR: current transaction
is aborted, commands ignored until end of transaction block'
conn=20986272, query='close SQL_CUR016F6118'
ERROR from backend during send_query: 'ERROR: current transaction
is aborted, commands ignored until end of transaction block'
conn=20986272, query='close SQL_CUR0170DB30'
ERROR from backend during send_query: 'ERROR: current transaction
is aborted, commands ignored until end of transaction block'
conn=20986272, query='close SQL_CUR01722058'
ERROR from backend during send_query: 'ERROR: current transaction
is aborted, commands ignored until end of transaction block'
conn=20986272, query='close SQL_CUR01728CE8'
ERROR from backend during send_query: 'ERROR: current transaction
is aborted, commands ignored until end of transaction block'
conn=20986272, query='close SQL_CUR01728F90'
ERROR from backend during send_query: 'ERROR: current transaction
is aborted, commands ignored until end of transaction block'
conn=20986272, query='close SQL_CUR01732238'
ERROR from backend during send_query: 'ERROR: current transaction
is aborted, commands ignored until end of transaction block'
conn=20986272, PGAPI_Disconnect