Bug #112272 Connector/ODBC parametrized query and transaction with char type get error
Submitted: 6 Sep 2023 8:00 Modified: 14 Jan 2024 12:47
Reporter: Simone Abello Email Updates:
Status: No Feedback Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:8.0.33 - 8.1 OS:Windows
Assigned to: MySQL Verification Team CPU Architecture:Any

[6 Sep 2023 8:00] Simone Abello
Description:
In classic asp when use a transaction with parametrized query if i set the type of parameter as adChar, adVarChar, adBSTR in a char field get this error:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

La transazione non supporta set di record multipli con questo tipo di cursore. Modificare il tipo di cursore, eseguire il commit della transazione oppure chiudere uno dei set di record.

if i comment the line with text parameter the query work fine.

How to repeat:
Use this code in classic asp
Set ConnStoricoUBI = Server.CreateObject("ADODB.Connection")
ConnStoricoUBI.Open("DRIVER={MySQL ODBC 8.0 UNICODE Driver};port=3306;SERVER=127.0.0.1;UID=XXX;PWD=XXX;database=test;charset=UTF8;")

ConnStoricoUBI.BeginTrans

SQLSTO = "INSERT INTO storicoUbi (codScatola, scatola, codArch, codUbicazioneOLD, UbicazioneOLD, codUbicazioneNEW, UbicazioneNEW, data, codOpe) "& _
			" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);"
SQLSTO = "INSERT INTO storicoUbi (codScatola, scatola, codArch, codUbicazioneOLD, UbicazioneOLD, codUbicazioneNEW, data, codOpe) "& _
			" VALUES (?, ?, ?, ?, ?, ?, ?, ?);"
'SQLSTO = "INSERT INTO storicoUbi (codScatola, scatola, codArch, codUbicazioneOLD,  codUbicazioneNEW, data, codOpe) "& _
'			" VALUES (?, ?, ?, ?, ?, ?, ?);"
set cmdSTO = server.createobject("ADODB.Command")
cmdSTO.ActiveConnection = ConnStoricoUBI
cmdSTO.CommandText = SQLSTO
cmdSTO.CommandType = adCmdText
cmdSTO.CommandTimeout = 900

cmdSTO.Parameters.Append cmdSTO.CreateParameter("codScatola", adInteger, adParamInput, , codScatola )
cmdSTO.Parameters.Append cmdSTO.CreateParameter("scatola", adDouble, adParamInput, , scatola )
cmdSTO.Parameters.Append cmdSTO.CreateParameter("codArch", adInteger, adParamInput, , codArch )
cmdSTO.Parameters.Append cmdSTO.CreateParameter("codUbicazioneOLD", adInteger, adParamInput, , codUbicazioneOLD )
cmdSTO.Parameters.Append cmdSTO.CreateParameter("UbicazioneOLD", adChar, adParamInput, 30, UbicazioneOLD )                   '<----- error here
cmdSTO.Parameters.Append cmdSTO.CreateParameter("codUbicazioneNEW", adInteger, adParamInput, , codUbicazioneNEW )
' cmdSTO.Parameters.Append cmdSTO.CreateParameter("UbicazioneNEW", adVarChar, adParamInput, 30, UbicazioneNEW )              '<----- error here
cmdSTO.Parameters.Append cmdSTO.CreateParameter("data", adDBTimeStamp, adParamInput, , data )
cmdSTO.Parameters.Append cmdSTO.CreateParameter("codOperatore", adInteger, adParamInput, , codiceOperatore )

cmdSTO.Execute

set cmdSTO = nothing

On Error Resume Next
If Err.number = 0 and ConnStoricoUBI.Errors.Count = 0 Then
	ConnStoricoUBI.CommitTrans
Else
	ConnStoricoUBI.RollbackTrans
	'ConnStoricoUBI.close
	'set ConnStoricoUBI=nothing
	Call ChiudiConnDB("ConnStoricoUBI")

	esitoOP "Si e' verificato un errore, eseguito il rollback",""
End If
On Error GoTo 0

ConnStoricoUBI.close
set ConnStoricoUBI=nothing

if comment the line with char filed cmdSTO.Parameters.Append cmdSTO.CreateParameter("UbicazioneOLD", adChar, adParamInput, 30, UbicazioneOLD ) all work fine
[14 Dec 2023 12:47] MySQL Verification Team
Hello Simone Abello,

Thank you for the bug report.
Could you please provide table structure for "storicoUbi" to reproduce this issue at our end?

Regards,
Ashwini Patil
[15 Jan 2024 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".