Postgres Pro
Downloads
Home   >   mailing lists

Thread: Parse Statement causes a "hang" in crystal reports

Parse Statement causes a "hang" in crystal reports

From
"Wayne Armstrong"
Date:
Hi,
 If I have parse statements turned on and the following sql is issued from
crystal reports, crystal will just hang.(ugly aint it :) This happens because
the parser decides ( is the schema, and dies trying to get info on a table
called (.bacchus. This is in the 7.02.00.05 drivers.

SELECT "MGMT_SUMMARY2"."vehicle_code", "MGMT_SUMMARY2"."t_kms", "MGMT_SUMMAR
Y2"."t_fuel_cost", "MGMT_SUMMARY2"."t_fuel_qty", "MGMT_SUMMARY2"."cost_per_klm",
 "MGMT_SUMMARY2"."fuel_ecomony", "MGMT_SUMMARY2"."t_job_cost", "MGMT_SUMMARY2"."
t_part_cost", "MGMT_SUMMARY2"."t_labour_cost", "MGMT_SUMMARY2"."t_labour_hour",
"PLANT"."year_made", "PLANT"."model_code", "PLANT"."active_flag", "PLANT"."print
_flag", "TB_CONTROL"."report_start", "TB_CONTROL"."report_end", "MGMT_SUMMARY2".
"t_oil_cost", "MGMT_SUMMARY2"."t_tyre_cost", "company"."company_name", "MGMT_SUM
MARY2"."company_id" FROM   (("bacchus"."mgmt_summary2" "MGMT_SUMMARY2" LEFT OUTE
R JOIN "bacchus"."plant" "PLANT" ON ("MGMT_SUMMARY2"."company_id"="PLANT"."compa
ny_id") AND ("MGMT_SUMMARY2"."vehicle_code"="PLANT"."plant_code")) LEFT OUTER JO
IN "bacchus"."tb_control" "TB_CONTROL" ON "MGMT_SUMMARY2"."company_id"="TB_CONTR
OL"."company_id") LEFT OUTER JOIN "bacchus"."company" "company" ON "MGMT_SUMMARY
2"."company_id"="company"."company_id" WHERE  "PLANT"."active_flag"='Yes' AND "M
GMT_SUMMARY2"."company_id"=0 AND "PLANT"."print_flag"='Yes' ORDER BY "PLANT"."mo
del_code", "MGMT_SUMMARY2"."vehicle_code"


Regards,
Wayne

Re: Parse Statement causes a "hang" in crystal reports

From
Hiroshi Inoue
Date:
Wayne Armstrong wrote:
>
> Hi,
>  If I have parse statements turned on and the following sql is issued from
> crystal reports, crystal will just hang.(ugly aint it :) This happens because
> the parser decides ( is the schema, and dies trying to get info on a table
> called (.bacchus. This is in the 7.02.00.05 drivers.
>
> SELECT "MGMT_SUMMARY2"."vehicle_code", "MGMT_SUMMARY2"."t_kms", "MGMT_SUMMAR
> Y2"."t_fuel_cost", "MGMT_SUMMARY2"."t_fuel_qty", "MGMT_SUMMARY2"."cost_per_klm",
>  "MGMT_SUMMARY2"."fuel_ecomony", "MGMT_SUMMARY2"."t_job_cost", "MGMT_SUMMARY2"."
> t_part_cost", "MGMT_SUMMARY2"."t_labour_cost", "MGMT_SUMMARY2"."t_labour_hour",
> "PLANT"."year_made", "PLANT"."model_code", "PLANT"."active_flag", "PLANT"."print
> _flag", "TB_CONTROL"."report_start", "TB_CONTROL"."report_end", "MGMT_SUMMARY2".
> "t_oil_cost", "MGMT_SUMMARY2"."t_tyre_cost", "company"."company_name", "MGMT_SUM
> MARY2"."company_id" FROM   (("bacchus"."mgmt_summary2" "MGMT_SUMMARY2" LEFT OUTE
> R JOIN "bacchus"."plant" "PLANT" ON ("MGMT_SUMMARY2"."company_id"="PLANT"."compa
> ny_id") AND ("MGMT_SUMMARY2"."vehicle_code"="PLANT"."plant_code")) LEFT OUTER JO
> IN "bacchus"."tb_control" "TB_CONTROL" ON "MGMT_SUMMARY2"."company_id"="TB_CONTR
> OL"."company_id") LEFT OUTER JOIN "bacchus"."company" "company" ON "MGMT_SUMMARY
> 2"."company_id"="company"."company_id" WHERE  "PLANT"."active_flag"='Yes' AND "M
> GMT_SUMMARY2"."company_id"=0 AND "PLANT"."print_flag"='Yes' ORDER BY "PLANT"."mo
> del_code", "MGMT_SUMMARY2"."vehicle_code"

Please try the lastest snapshot dll at
    http://www.geocities.jp/inocchichichi/psqlodbc/ .

regards,
Hiroshi Inoue
    http://www.geocities.jp/inocchichichi/psqlodbc/

An extra null row is returned if rowset size is a multiple of row cache size

From
"Wayne Armstrong"
Date:
Hi,
 The odbc driver is returning an extra null filled row when the number of rows
to be retrieved is a multiple of the row cache size set in the odbc driver. If
this occurs when issuing a SQLColumns or SQLtables request, the driver will
return an error trying to convert the null filled row into column or table row
values.
 In normal usage, my smalltalk based app, can abend because the final row
returned has nulls in columns that cannot possibly be null.

 I have had a look at the current cvs source, and cannot determine where the
problem is (I am not a c progremmer - but can maintain c (with difficulty and
under protest  :).)

This is a serious problem for us which we are hitting fairly often and need to
fix real soon. I am continuing to read the odbc driver code - but any pointers
for what to try/where to look  would be real usefull.


My odbc settings are :-
Recognize unique indexes,
Use Declare fetch
Parse statements
Unknown sizes - longest
Text a long varchar
unknown as long varchar
bools as char
cache size 25
updateable cursors
dissalow premature.

Here is the bit of the odbc log where it goes wrong (I think).
This is retrieving 50 rows with a cache size of 25.

[848]next_tuple: fetch_count < fcount: returning tuple 24, fcount = 25
[848]fetch: cols=6, lf=0, opts = 68763272, opts->bindings = 68751064, buffer[]
= 1658672
[848]type = 23
[848]value = '0'
[848]copy_and_convert: field_type = 23, fctype = 4, value = '0', cbValueMax=4
[848]copy_and_convert: retval = 0
[848]fetch: cols=6, lf=1, opts = 68763272, opts->bindings = 68751064, buffer[]
= 1658680
[848]type = 1043
[848]value = 'WB-OST'
[848]copy_and_convert: field_type = 1043, fctype = 1, value = 'WB-OST',
cbValueMax=16
[848]DEFAULT: len = 6, ptr = 'WB-OST'
[848]    SQL_C_CHAR, default: len = 6, cbValueMax = 16, rgbValueBindRow =
'WB-OST'
[848]copy_and_convert: retval = 0
[848]fetch: cols=6, lf=2, opts = 68763272, opts->bindings = 68751064, buffer[]
= 1658700
[848]type = 1043
[848]value = 'WHEEL'
[848]copy_and_convert: field_type = 1043, fctype = 1, value = 'WHEEL',
cbValueMax=16
[848]DEFAULT: len = 5, ptr = 'WHEEL'
[848]    SQL_C_CHAR, default: len = 5, cbValueMax = 16, rgbValueBindRow =
'WHEEL'
[848]copy_and_convert: retval = 0
[848]fetch: cols=6, lf=3, opts = 68763272, opts->bindings = 68751064, buffer[]
= 1658720
[848]type = 1043
[848]value = 'Wheel Bearing Repack - O/S/T'
[848]copy_and_convert: field_type = 1043, fctype = 1, value = 'Wheel Bearing
Repack - O/S/T', cbValueMax=31
[848]DEFAULT: len = 28, ptr = 'Wheel Bearing Repack - O/S/T'
[848]    SQL_C_CHAR, default: len = 28, cbValueMax = 31, rgbValueBindRow =
'Wheel Bearing Repack - O/S/T'
[848]copy_and_convert: retval = 0
[848]fetch: cols=6, lf=4, opts = 68763272, opts->bindings = 68751064, buffer[]
= 1658755
[848]type = 1043
[848]value = 'No'
[848]copy_and_convert: field_type = 1043, fctype = 1, value = 'No',
cbValueMax=11
[848]DEFAULT: len = 2, ptr = 'No'
[848]    SQL_C_CHAR, default: len = 2, cbValueMax = 11, rgbValueBindRow = 'No'
[848]copy_and_convert: retval = 0
[848]fetch: cols=6, lf=5, opts = 68763272, opts->bindings = 68751064, buffer[]
= 1658770
[848]type = 1043
[848]value = 'Service'
[848]copy_and_convert: field_type = 1043, fctype = 1, value = 'Service',
cbValueMax=16
[848]DEFAULT: len = 7, ptr = 'Service'
[848]    SQL_C_CHAR, default: len = 7, cbValueMax = 16, rgbValueBindRow =
'Service'
[848]copy_and_convert: retval = 0
[848][SQLExtendedFetch][848]PGAPI_ExtendedFetch: stmt=68763192
[848]SQL_FETCH_NEXT: num_tuples=50, currtuple=25
[848]PGAPI_ExtendedFetch: new currTuple = 49
[848]manual_result = 0, use_declarefetch = 1
[848]clear obsolete 25 tuples
[848]next_tuple: sending actual fetch (25) query 'fetch 25 in SQL_CUR04193E38'
[848]send_query(): conn=18913808, query='fetch 25 in SQL_CUR04193E38'
[848]send_query: done sending query
[848]send_query: got id = 'Z'
[848]read 175, global_socket_buffersize=4096
[848]send_query: got id = 'P'
[848]send_query: got id = 'T'
[848]num_fields = 6
[848]READING ATTTYPMOD
[848]CI_read_fields: fieldname='company_id', adtid=23, adtsize=4, atttypmod=-1
[848]READING ATTTYPMOD
[848]CI_read_fields: fieldname='service_code', adtid=1043, adtsize=-1,
atttypmod=15
[848]READING ATTTYPMOD
[848]CI_read_fields: fieldname='service_type_code', adtid=1043, adtsize=-1,
atttypmod=15
[848]READING ATTTYPMOD
[848]CI_read_fields: fieldname='service_desc', adtid=1043, adtsize=-1,
atttypmod=30
[848]READING ATTTYPMOD
[848]CI_read_fields: fieldname='print_jobcard', adtid=1043, adtsize=-1,
atttypmod=10
[848]READING ATTTYPMOD
[848]CI_read_fields: fieldname='sheet_type_flag', adtid=1043, adtsize=-1,
atttypmod=15
[848]end of tuple list -- setting inUse to false: this = 18913232
[848]_next_tuple: 'C' fetch_max && fcount = 50
[848]fetch: cols=6, lf=0, opts = 68763272, opts->bindings = 68751064, buffer[]
= 1658672
[848]type = 23
[848]value = '<NULL>'
[848]copy_and_convert: field_type = 23, fctype = 4, value = '<NULL>',
cbValueMax=4
[848]copy_and_convert: retval = 0
[848]fetch: cols=6, lf=1, opts = 68763272, opts->bindings = 68751064, buffer[]
= 1658680
[848]type = 1043
[848]value = '<NULL>'
[848]copy_and_convert: field_type = 1043, fctype = 1, value = '<NULL>',
cbValueMax=16
[848]copy_and_convert: retval = 0
[848]fetch: cols=6, lf=2, opts = 68763272, opts->bindings = 68751064, buffer[]
= 1658700
[848]type = 1043
[848]value = '<NULL>'
[848]copy_and_convert: field_type = 1043, fctype = 1, value = '<NULL>',
cbValueMax=16
[848]copy_and_convert: retval = 0
[848]fetch: cols=6, lf=3, opts = 68763272, opts->bindings = 68751064, buffer[]
= 1658720
[848]type = 1043
[848]value = '<NULL>'
[848]copy_and_convert: field_type = 1043, fctype = 1, value = '<NULL>',
cbValueMax=31
[848]copy_and_convert: retval = 0
[848]fetch: cols=6, lf=4, opts = 68763272, opts->bindings = 68751064, buffer[]
= 1658755
[848]type = 1043
[848]value = '<NULL>'
[848]copy_and_convert: field_type = 1043, fctype = 1, value = '<NULL>',
cbValueMax=11
[848]copy_and_convert: retval = 0
[848]fetch: cols=6, lf=5, opts = 68763272, opts->bindings = 68751064, buffer[]
= 1658770
[848]type = 1043
[848]value = '<NULL>'
[848]copy_and_convert: field_type = 1043, fctype = 1, value = '<NULL>',
cbValueMax=16
[848]copy_and_convert: retval = 0
[848]manual_result = 0, use_declarefetch = 1
[848]next_tuple: fcount < CACHE_SIZE: fcount = 0, fetch_count = 1
[848]**** SC_fetch: end_tuples
[848][SQLExtendedFetch][848]PGAPI_ExtendedFetch: stmt=68763192
[848]SQL_FETCH_NEXT: num_tuples=50, currtuple=50
[848][SQLFreeStmt][848]PGAPI_FreeStmt: entering...hstmt=68763192, fOption=0



Regards,
Wayne Armstrong
Bacchus Management Systems
http://www.bacchus.com.au

Re: An extra null row is returned if rowset size is a multiple

From
"Wayne Armstrong"
Date:
** Reply to message from "Wayne Armstrong" <wdarmst@bacchus.com.au> on Fri, 18
Apr 2003 20:04:02 +1000
Hmm,
 Talking to myself again :)
 Ok the problem seems to be in qresult.c
 QR_next_tuple when it gets an end_of_tupple message from the backend - setups
a pointer to the first cached row and returns true if any rows hae been
retrieved for the query as a whole (rather than for this fetch)
The offending bit of code looks like:-
                               if (self->num_total_rows > 0)

                                {
                                        qlog("    [ fetched %d rows ]\n",
num_rows);
                                        mylog("_next_tuple: 'C' fetch_max &&
fcount = %d\n", self->num_total_rows);

                                        /* set to first row */
                                        self->tupleField =
self->backend_tuples+ (offset * self->num_fields);
                                        return TRUE;
                                }
                                else
                                {
I have changed that to look at if or not it retrieved any rows on this fetch
attempt and the problem goes away.

Regards,
Wayne

> Hi,
>  The odbc driver is returning an extra null filled row when the number of rows
> to be retrieved is a multiple of the row cache size set in the odbc driver. If
> this occurs when issuing a SQLColumns or SQLtables request, the driver will
> return an error trying to convert the null filled row into column or table row
> values.
>  In normal usage, my smalltalk based app, can abend because the final row
> returned has nulls in columns that cannot possibly be null.
>
>  I have had a look at the current cvs source, and cannot determine where the
> problem is (I am not a c progremmer - but can maintain c (with difficulty and
> under protest  :).)
>
> This is a serious problem for us which we are hitting fairly often and need to
> fix real soon. I am continuing to read the odbc driver code - but any pointers
> for what to try/where to look  would be real usefull.
>
>
> My odbc settings are :-
> Recognize unique indexes,
> Use Declare fetch
> Parse statements
> Unknown sizes - longest
> Text a long varchar
> unknown as long varchar
> bools as char
> cache size 25
> updateable cursors
> dissalow premature.
>
> Here is the bit of the odbc log where it goes wrong (I think).
> This is retrieving 50 rows with a cache size of 25.
>
> [848]next_tuple: fetch_count < fcount: returning tuple 24, fcount = 25
> [848]fetch: cols=6, lf=0, opts = 68763272, opts->bindings = 68751064, buffer[]
> = 1658672
> [848]type = 23
> [848]value = '0'
> [848]copy_and_convert: field_type = 23, fctype = 4, value = '0', cbValueMax=4
> [848]copy_and_convert: retval = 0
> [848]fetch: cols=6, lf=1, opts = 68763272, opts->bindings = 68751064, buffer[]
> = 1658680
> [848]type = 1043
> [848]value = 'WB-OST'
> [848]copy_and_convert: field_type = 1043, fctype = 1, value = 'WB-OST',
> cbValueMax=16
> [848]DEFAULT: len = 6, ptr = 'WB-OST'
> [848]    SQL_C_CHAR, default: len = 6, cbValueMax = 16, rgbValueBindRow =
> 'WB-OST'
> [848]copy_and_convert: retval = 0
> [848]fetch: cols=6, lf=2, opts = 68763272, opts->bindings = 68751064, buffer[]
> = 1658700
> [848]type = 1043
> [848]value = 'WHEEL'
> [848]copy_and_convert: field_type = 1043, fctype = 1, value = 'WHEEL',
> cbValueMax=16
> [848]DEFAULT: len = 5, ptr = 'WHEEL'
> [848]    SQL_C_CHAR, default: len = 5, cbValueMax = 16, rgbValueBindRow =
> 'WHEEL'
> [848]copy_and_convert: retval = 0
> [848]fetch: cols=6, lf=3, opts = 68763272, opts->bindings = 68751064, buffer[]
> = 1658720
> [848]type = 1043
> [848]value = 'Wheel Bearing Repack - O/S/T'
> [848]copy_and_convert: field_type = 1043, fctype = 1, value = 'Wheel Bearing
> Repack - O/S/T', cbValueMax=31
> [848]DEFAULT: len = 28, ptr = 'Wheel Bearing Repack - O/S/T'
> [848]    SQL_C_CHAR, default: len = 28, cbValueMax = 31, rgbValueBindRow =
> 'Wheel Bearing Repack - O/S/T'
> [848]copy_and_convert: retval = 0
> [848]fetch: cols=6, lf=4, opts = 68763272, opts->bindings = 68751064, buffer[]
> = 1658755
> [848]type = 1043
> [848]value = 'No'
> [848]copy_and_convert: field_type = 1043, fctype = 1, value = 'No',
> cbValueMax=11
> [848]DEFAULT: len = 2, ptr = 'No'
> [848]    SQL_C_CHAR, default: len = 2, cbValueMax = 11, rgbValueBindRow = 'No'
> [848]copy_and_convert: retval = 0
> [848]fetch: cols=6, lf=5, opts = 68763272, opts->bindings = 68751064, buffer[]
> = 1658770
> [848]type = 1043
> [848]value = 'Service'
> [848]copy_and_convert: field_type = 1043, fctype = 1, value = 'Service',
> cbValueMax=16
> [848]DEFAULT: len = 7, ptr = 'Service'
> [848]    SQL_C_CHAR, default: len = 7, cbValueMax = 16, rgbValueBindRow =
> 'Service'
> [848]copy_and_convert: retval = 0
> [848][SQLExtendedFetch][848]PGAPI_ExtendedFetch: stmt=68763192
> [848]SQL_FETCH_NEXT: num_tuples=50, currtuple=25
> [848]PGAPI_ExtendedFetch: new currTuple = 49
> [848]manual_result = 0, use_declarefetch = 1
> [848]clear obsolete 25 tuples
> [848]next_tuple: sending actual fetch (25) query 'fetch 25 in SQL_CUR04193E38'
> [848]send_query(): conn=18913808, query='fetch 25 in SQL_CUR04193E38'
> [848]send_query: done sending query
> [848]send_query: got id = 'Z'
> [848]read 175, global_socket_buffersize=4096
> [848]send_query: got id = 'P'
> [848]send_query: got id = 'T'
> [848]num_fields = 6
> [848]READING ATTTYPMOD
> [848]CI_read_fields: fieldname='company_id', adtid=23, adtsize=4, atttypmod=-1
> [848]READING ATTTYPMOD
> [848]CI_read_fields: fieldname='service_code', adtid=1043, adtsize=-1,
> atttypmod=15
> [848]READING ATTTYPMOD
> [848]CI_read_fields: fieldname='service_type_code', adtid=1043, adtsize=-1,
> atttypmod=15
> [848]READING ATTTYPMOD
> [848]CI_read_fields: fieldname='service_desc', adtid=1043, adtsize=-1,
> atttypmod=30
> [848]READING ATTTYPMOD
> [848]CI_read_fields: fieldname='print_jobcard', adtid=1043, adtsize=-1,
> atttypmod=10
> [848]READING ATTTYPMOD
> [848]CI_read_fields: fieldname='sheet_type_flag', adtid=1043, adtsize=-1,
> atttypmod=15
> [848]end of tuple list -- setting inUse to false: this = 18913232
> [848]_next_tuple: 'C' fetch_max && fcount = 50
> [848]fetch: cols=6, lf=0, opts = 68763272, opts->bindings = 68751064, buffer[]
> = 1658672
> [848]type = 23
> [848]value = '<NULL>'
> [848]copy_and_convert: field_type = 23, fctype = 4, value = '<NULL>',
> cbValueMax=4
> [848]copy_and_convert: retval = 0
> [848]fetch: cols=6, lf=1, opts = 68763272, opts->bindings = 68751064, buffer[]
> = 1658680
> [848]type = 1043
> [848]value = '<NULL>'
> [848]copy_and_convert: field_type = 1043, fctype = 1, value = '<NULL>',
> cbValueMax=16
> [848]copy_and_convert: retval = 0
> [848]fetch: cols=6, lf=2, opts = 68763272, opts->bindings = 68751064, buffer[]
> = 1658700
> [848]type = 1043
> [848]value = '<NULL>'
> [848]copy_and_convert: field_type = 1043, fctype = 1, value = '<NULL>',
> cbValueMax=16
> [848]copy_and_convert: retval = 0
> [848]fetch: cols=6, lf=3, opts = 68763272, opts->bindings = 68751064, buffer[]
> = 1658720
> [848]type = 1043
> [848]value = '<NULL>'
> [848]copy_and_convert: field_type = 1043, fctype = 1, value = '<NULL>',
> cbValueMax=31
> [848]copy_and_convert: retval = 0
> [848]fetch: cols=6, lf=4, opts = 68763272, opts->bindings = 68751064, buffer[]
> = 1658755
> [848]type = 1043
> [848]value = '<NULL>'
> [848]copy_and_convert: field_type = 1043, fctype = 1, value = '<NULL>',
> cbValueMax=11
> [848]copy_and_convert: retval = 0
> [848]fetch: cols=6, lf=5, opts = 68763272, opts->bindings = 68751064, buffer[]
> = 1658770
> [848]type = 1043
> [848]value = '<NULL>'
> [848]copy_and_convert: field_type = 1043, fctype = 1, value = '<NULL>',
> cbValueMax=16
> [848]copy_and_convert: retval = 0
> [848]manual_result = 0, use_declarefetch = 1
> [848]next_tuple: fcount < CACHE_SIZE: fcount = 0, fetch_count = 1
> [848]**** SC_fetch: end_tuples
> [848][SQLExtendedFetch][848]PGAPI_ExtendedFetch: stmt=68763192
> [848]SQL_FETCH_NEXT: num_tuples=50, currtuple=50
> [848][SQLFreeStmt][848]PGAPI_FreeStmt: entering...hstmt=68763192, fOption=0
>
>
>
> Regards,
> Wayne Armstrong
> Bacchus Management Systems
> http://www.bacchus.com.au
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org