Bug #6191 Bug in Select count(*) via MyODBC
Submitted: 21 Oct 2004 1:27 Modified: 9 Mar 2007 19:36
Reporter: Miguel Solorzano Email Updates:
Status: Won't fix Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:3.52. OS:Windows (Windows W2k/Linux Suse)
Assigned to: CPU Architecture:Any

[21 Oct 2004 1:27] Miguel Solorzano 
Description:
Hi,

I have a strange problem in the return types of the query:

select count(*) from table gives in MySQL Version 4.0.x an return
type of long. In Version 4.1.x its an char ("0").
The query goes vi MyODBC to MySQL. I checked the ODBC Trace to see
the different return types.

With Version 4.1.x I use MyODBC Version 3.52.00, with Version 4.0.x
I use MyODBC 3.52.

I did some test on W2K and SuSE Linux, but it doesn't matter.

Here are the important lines of the odbc-trace.

Query
++++++++++++++++++++++++++++++++++++++++++++++++++++++++
vfp8            af-bc	ENTER SQLExecDirect 
		HSTMT               035F5998
		UCHAR *             0x015CF1D0 [      -3] "select count(*) from test\ 0"
		SDWORD                    -3

vfp8            af-bc	EXIT  SQLExecDirect  with return code 0 (SQL_SUCCESS)
		HSTMT               035F5998
		UCHAR *             0x015CF1D0 [      -3] "select count(*) from test\ 0"
		SDWORD                    -3

Result
++++++++++++++++++++++++++++++++++++++++++++++++++++++++
vfp8            af-bc	EXIT  SQLColAttributes  with return code 0 (SQL_SUCCESS)
		HSTMT               035F5998
		UWORD                        1 
		UWORD                        1 <SQL_COLUMN_NAME>
		PTR                0x0012F4AC
		SWORD                      255 
		SWORD *             0x00000000
		SQLLEN *            0x0012F724 (0)

vfp8            af-bc	ENTER SQLFetch 
		HSTMT               035F5998

vfp8            af-bc	EXIT  SQLFetch  with return code 0 (SQL_SUCCESS)
		HSTMT               035F5998

vfp8            af-bc	ENTER SQLGetData 
		HSTMT               035F5998
		UWORD                        1 
		SWORD                        1 <SQL_C_CHAR>
		PTR                 0x024CB151 
		SQLLEN                    22
		SQLLEN *            0x0012F5A4

vfp8            af-bc	EXIT  SQLGetData  with return code 0 (SQL_SUCCESS)
		HSTMT               035F5998
		UWORD                        1 
		SWORD                        1 <SQL_C_CHAR>
		PTR                 0x024CB151 [       1] "1"
		SQLLEN                    22
		SQLLEN *            0x0012F5A4 (1)
+++++++++++++++++++++++++++++++++++++++++++++

Does anyone know about this problem? 
I already checked the manual/bug/changes section.
But I did get any ideas.

As attachment I send the full odbc-trace.

Sincerely,
Mario
-- 

vfp8            af-bc	ENTER SQLExecDirect 
		HSTMT               035F5998
		UCHAR *             0x015CF1D0 [      -3] "select count(*) from test\ 0"
		SDWORD                    -3

vfp8            af-bc	EXIT  SQLExecDirect  with return code 0 (SQL_SUCCESS)
		HSTMT               035F5998
		UCHAR *             0x015CF1D0 [      -3] "select count(*) from test\ 0"
		SDWORD                    -3

vfp8            af-bc	ENTER SQLNumResultCols 
		HSTMT               035F5998
		SWORD *             0x0012F98C

vfp8            af-bc	EXIT  SQLNumResultCols  with return code 0 (SQL_SUCCESS)
		HSTMT               035F5998
		SWORD *             0x0012F98C (1)

vfp8            af-bc	ENTER SQLNumResultCols 
		HSTMT               035F5998
		SWORD *             0x0012F720

vfp8            af-bc	EXIT  SQLNumResultCols  with return code 0 (SQL_SUCCESS)
		HSTMT               035F5998
		SWORD *             0x0012F720 (1)

vfp8            af-bc	ENTER SQLColAttributes 
		HSTMT               035F5998
		UWORD                        1 
		UWORD                        2 <SQL_COLUMN_TYPE>
		PTR                0x00000000
		SWORD                        0 
		SWORD *             0x00000000
		SQLLEN *            0x0012F724

vfp8            af-bc	EXIT  SQLColAttributes  with return code 0 (SQL_SUCCESS)
		HSTMT               035F5998
		UWORD                        1 
		UWORD                        2 <SQL_COLUMN_TYPE>
		PTR                0x00000000
		SWORD                        0 
		SWORD *             0x00000000
		SQLLEN *            0x0012F724 (-5)

vfp8            af-bc	ENTER SQLColAttributes 
		HSTMT               035F5998
		UWORD                        1 
		UWORD                        6 <SQL_COLUMN_DISPLAY_SIZE>
		PTR                0x00000000
		SWORD                        0 
		SWORD *             0x00000000
		SQLLEN *            0x0012F724

vfp8            af-bc	EXIT  SQLColAttributes  with return code 0 (SQL_SUCCESS)
		HSTMT               035F5998
		UWORD                        1 
		UWORD                        6 <SQL_COLUMN_DISPLAY_SIZE>
		PTR                0x00000000
		SWORD                        0 
		SWORD *             0x00000000
		SQLLEN *            0x0012F724 (21)

vfp8            af-bc	ENTER SQLColAttributes 
		HSTMT               035F5998
		UWORD                        1 
		UWORD                        4 <SQL_COLUMN_PRECISION>
		PTR                0x00000000
		SWORD                        0 
		SWORD *             0x00000000
		SQLLEN *            0x0012F724

vfp8            af-bc	EXIT  SQLColAttributes  with return code 0 (SQL_SUCCESS)
		HSTMT               035F5998
		UWORD                        1 
		UWORD                        4 <SQL_COLUMN_PRECISION>
		PTR                0x00000000
		SWORD                        0 
		SWORD *             0x00000000
		SQLLEN *            0x0012F724 (19)

vfp8            af-bc	ENTER SQLColAttributes 
		HSTMT               035F5998
		UWORD                        1 
		UWORD                        5 <SQL_COLUMN_SCALE>
		PTR                0x00000000
		SWORD                        0 
		SWORD *             0x00000000
		SQLLEN *            0x0012F724

vfp8            af-bc	EXIT  SQLColAttributes  with return code 0 (SQL_SUCCESS)
		HSTMT               035F5998
		UWORD                        1 
		UWORD                        5 <SQL_COLUMN_SCALE>
		PTR                0x00000000
		SWORD                        0 
		SWORD *             0x00000000
		SQLLEN *            0x0012F724 (0)

vfp8            af-bc	ENTER SQLColAttributes 
		HSTMT               035F5998
		UWORD                        1 
		UWORD                        7 <SQL_COLUMN_NULLABLE>
		PTR                0x00000000
		SWORD                        0 
		SWORD *             0x00000000
		SQLLEN *            0x0012F724

vfp8            af-bc	EXIT  SQLColAttributes  with return code 0 (SQL_SUCCESS)
		HSTMT               035F5998
		UWORD                        1 
		UWORD                        7 <SQL_COLUMN_NULLABLE>
		PTR                0x00000000
		SWORD                        0 
		SWORD *             0x00000000
		SQLLEN *            0x0012F724 (0)

vfp8            af-bc	ENTER SQLColAttributes 
		HSTMT               035F5998
		UWORD                        1 
		UWORD                        9 <SQL_COLUMN_MONEY>
		PTR                0x00000000
		SWORD                        0 
		SWORD *             0x00000000
		SQLLEN *            0x0012F724

vfp8            af-bc	EXIT  SQLColAttributes  with return code 0 (SQL_SUCCESS)
		HSTMT               035F5998
		UWORD                        1 
		UWORD                        9 <SQL_COLUMN_MONEY>
		PTR                0x00000000
		SWORD                        0 
		SWORD *             0x00000000
		SQLLEN *            0x0012F724 (0)

vfp8            af-bc	ENTER SQLColAttributes 
		HSTMT               035F5998
		UWORD                        1 
		UWORD                        1 <SQL_COLUMN_NAME>
		PTR                0x0012F4AC
		SWORD                      255 
		SWORD *             0x00000000
		SQLLEN *            0x0012F724

vfp8            af-bc	EXIT  SQLColAttributes  with return code 0 (SQL_SUCCESS)
		HSTMT               035F5998
		UWORD                        1 
		UWORD                        1 <SQL_COLUMN_NAME>
		PTR                0x0012F4AC
		SWORD                      255 
		SWORD *             0x00000000
		SQLLEN *            0x0012F724 (0)

vfp8            af-bc	ENTER SQLFetch 
		HSTMT               035F5998

vfp8            af-bc	EXIT  SQLFetch  with return code 0 (SQL_SUCCESS)
		HSTMT               035F5998

vfp8            af-bc	ENTER SQLGetData 
		HSTMT               035F5998
		UWORD                        1 
		SWORD                        1 <SQL_C_CHAR>
		PTR                 0x024CB151 
		SQLLEN                    22
		SQLLEN *            0x0012F5A4

vfp8            af-bc	EXIT  SQLGetData  with return code 0 (SQL_SUCCESS)
		HSTMT               035F5998
		UWORD                        1 
		SWORD                        1 <SQL_C_CHAR>
		PTR                 0x024CB151 [       1] "1"
		SQLLEN                    22
		SQLLEN *            0x0012F5A4 (1)

vfp8            af-bc	ENTER SQLFetch 
		HSTMT               035F5998

vfp8            af-bc	EXIT  SQLFetch  with return code 100 (SQL_NO_DATA_FOUND)
		HSTMT               035F5998

vfp8            af-bc	ENTER SQLMoreResults 
		HSTMT               035F5998

vfp8            af-bc	EXIT  SQLMoreResults  with return code 100 (SQL_NO_DATA_FOUND)
		HSTMT               035F5998

vfp8            af-bc	ENTER SQLFreeStmt 
		HSTMT               035F5998
		UWORD                        0 <SQL_CLOSE>

vfp8            af-bc	EXIT  SQLFreeStmt  with return code 0 (SQL_SUCCESS)
		HSTMT               035F5998
		UWORD                        0 <SQL_CLOSE>

vfp8            af-bc	ENTER SQLFreeStmt 
		HSTMT               035F5998
		UWORD                        3 <SQL_RESET_PARAMS>

vfp8            af-bc	EXIT  SQLFreeStmt  with return code 0 (SQL_SUCCESS)
		HSTMT               035F5998
		UWORD                        3 <SQL_RESET_PARAMS>

How to repeat:
--

Suggested fix:
--
[22 Oct 2004 16:15] MySQL Verification Team 
This driver will be substituted by 3.53.
[2 Nov 2004 2:00] Tom Price 
Had identical problem except with VFP7. My workaround was to add OPTION=16385 to the SQL connect string (1 = Don't optimize column width, an option recommended because Foxpro doesn't have varchars in remote views + 16384 = Change BIGINT columns to INT).

Would prefer that MySQL just return an int like 4.0.x did unless result is too big to be represented that way.
[29 Nov 2004 7:59] Mario Neudeck 
it works fine with "Don't optimize column widths" and "Change bigint to int" in myodbc-3.51.10
VFP80
[9 Mar 2007 19:36] Jim Winstead 
3.52 was a dead-end.