CODEWITHSUNDEEP
mysqlvbscriptasp-classicvb6adodb
Anjali
Anjali

Reputation: 43

No recordset is returned if one of the column has data more than 1024 characters Mysql 8.0 and Classic ASP

I am using 32 bit MySQL ODBC connector 8.19 with Classic asp MYSQL server is 8. CHARACTER is latin1 and COLLATE is latin1_swedish_ci Connection string has OPTION=3 Storage Engine is InnoDB

Following is the table structure

CREATE TABLE Data_temp
(
ROWGUID varchar(36) default (UUID()) NOT NULL ,
col_desc varchar(10) NOT NULL ,
History varchar (4000),
PRIMARY KEY Data_temp_P_KEY(ROWGUID)
) ;

Using Adodb command run following query

Select * from Data_temp WHERE col_desc=?

and parameter is supplied by adodb create parameter.

The result is returned in adodb recordset as

set recordset=command.execute method

recordset is not returned if History column has data more than 1024 characters

If Same query is run in following scenarios as ,

  1. Select col_desc,Rowguid 
from Data_temp 
WHERE col_desc=?

    i.e. column list do not contain that large data column then recordset is returned

  2. Select col_desc,Rowguid,substring(history,1,1024) 
from Data_temp 
WHERE col_desc=?

    also works.

  3. By making column as TEXT data type instead of varchar

What could be the reason?

Is there any limit on returning the column data? One more observation instead of using parameterised query if query is run directly with value in query itself

Select * from Data_temp WHERE Col_Desc='aa'

without parameter then recordset is returned

Thanks in advance

Upvotes: 4

Views: 261

Answers (1)

Kul-Tigin
Kul-Tigin

Reputation: 16950

Take a look at these similar issues:

https://bugs.mysql.com/bug.php?id=92078
https://bugs.mysql.com/bug.php?id=93895
https://bugs.mysql.com/bug.php?id=94545

and the official recommendation:

Source: https://dev.mysql.com/doc/relnotes/connector-odbc/en/news-8-0-16.html

An exception was emitted when fetching contents of a BLOB/TEXT records after executing a statement as a server-side prepared statement with a bound parameter.

The workaround is not using parameters or specifying NO_SSPS=1 in the connection string; this allows the driver to fetch the data.

In Connector/ODBC 5.2 and after, by default, server-side prepared statements are used. When this option is set to a non-zero value, prepared statements are emulated on the client side, which is the same behavior as in 5.1 and 3.51. Added in 5.2.0.

The recommended workaround is to specify the NO_SSPS=1 in the connection string and I confirm that it works.

Upvotes: 4

Related Questions