MySQL Bugs: #21141: Not able to insert records in table.

Bug #21141	Not able to insert records in table.
Submitted:	19 Jul 2006 9:29	Modified:	8 Mar 2007 1:54
Reporter:	Hari A	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51	OS:	Windows (Windows NT)
Assigned to:		CPU Architecture:	Any

Description:
Hi,
I am trying to access Mysql StoredProcedure in VBScript,
I am getting an error.
plz help me out in this issue.

Regards
Hari naidu.
email:harinaidu@symphony.cc

Error Description:
-----------------
[MySQL][OBDC 3.51 Driver][mysqld-5.0.19-nt]You have an error in your SQL Syntax; chech the manual that corresponds to your MySql server version for the right syntax to use near '( call _usp(1,'aa','bb'))'at line 1

How to repeat:
VBScript Code:
--------------
dim Connection1
dim RecordSet1
dim cmd 
dim dbcon
dim dbcmd

Set Connection1 = CreateObject("ADODB.Connection")
Set RecordSet1 = CreateObject("ADODB.Recordset")
Set cmd = CreateObject("ADODB.Command")

Connection1.Open cConnectionString 
Wscript.Echo "Connection is Opend"

dim userCN,col2,col3

userCN	= CleanUpValue(NameRecordArray(0))
Col2	= CleanUpValue(NameRecordArray(1))
Col3	= CleanUpValue(NameRecordArray(2))

WScript.Echo "User ID   :"&userCN
WScript.Echo "First Name:"&Col2
WScript.Echo "Last      :"&Col3

' WScript.Echo "Cmd:"&cmd.ActiveConnection

set cmd.ActiveConnection = Connection1
on error resume next
cmd.CommandType = adCmdStoredProc
WScript.Echo "IN SP "
cmd.CommandText = "usp_insDummy"
WScript.Echo "IN SP Executed.."

cmd.Parameters.Append cmd.CreateParameter("?ip_col1", adInteger, adParamInput,4, userCN)
cmd.Parameters.Append cmd.CreateParameter("?ip_col2", adVarChar,adParamInput,50, col2)
cmd.Parameters.Append cmd.CreateParameter("?ip_col3", adVarChar, adParamInput,50, col3)

cmd.Execute
WScript.Echo "SP Executed.."
wscript.echo err.description

Connection1.Close

Set Connection1 = nothing
Set RecordSet1 = nothing

--------------------------

Suggested fix:
[MySQL][OBDC 3.51 Driver][mysqld-5.0.19-nt]You have an error in your SQL Syntax; chech the manual that corresponds to your MySql server version for the right syntax to use near '( call _usp(1,'aa','bb'))'at line 1

Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Explanation:
  You did not provide data dump suitable for import in MySQL server nor do I know the structure of your SP. Please provide requested data and a simpler test case.

VBScript File:
-----------------

Option Explicit

'AUTHOR : Hari Naidu.A
'DATED  : 14-07-2006
'Check Constant Values Site :
http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=122
'*******************************************************************************
****
'  This script will take WellCentives from Laboratory in Excel Format 
'  and upload values to "excz_measurements" table
'*******************************************************************************
****
'  Parameter - FileName
'  Usage - cscript DataLoadLab.vbs <filename.csv>
'*******************************************************************************
****

Const cConnectionString= "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=
192.168.10.18; DATABASE=sweeperTest; UID= swuser; pwd=swuser;OPTION=3"

Const adOpenForwardOnly = 0
Const adOpenDynamic = 2
Const adOpenStatic = 3
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3

'---- ParameterDirectionEnum Values ----
Const adParamUnknown = &H0000
Const adParamInput = &H0001
Const adParamOutput = &H0002
Const adParamInputOutput = &H0003
Const adParamReturnValue = &H0004

'---- CommandTypeEnum Values ----
Const adCmdUnknown = &H0008
Const adCmdText = &H0001
Const adCmdTable = &H0002
Const adCmdStoredProc = &H0004
Const adCmdFile = &H0100
Const adCmdTableDirect = &H0200

Const adInteger=3
Const adDate=7
Const adVarChar=200

Const cGoodUser = 1
Const cFailedUser = 0

Const cFieldsCount = 3

' mail 
Const cTestUserEmail = "harinaidu@symphony.cc"
Const cTest = 0   

dim FileSystemObject
dim ReadFileObject
dim ReadFileName
dim WriteFileObject
dim WriteFileName
dim ScriptArgumentsObject
dim ReadLineValue
dim GoodNameCountValue
dim AllNameCountValue
dim AddStatus
dim Usr

dim usrRefMailId,usrRefUserName,usrRefUsrPassword

On Error Resume Next

' Create the Arguments object

Set ScriptArgumentsObject = WScript.Arguments

' Check if any command line arguments were specified

If ScriptArgumentsObject.Count = 0 Then
   ' Alert User and Get Out
   WScript.Echo "Error Opening Input File:  You must pass a filename.  For
example:  cscript eCareUserBulkLoad.vbs yourfile.csv."
   WScript.Quit
Else
   ' Retrieve FileName
   ReadFileName = ScriptArgumentsObject.Item(0)
End If

'  Open the csv file

Set FileSystemObject=CreateObject("Scripting.FileSystemObject")
On Error Resume Next
  Set ReadFileObject=FileSystemObject.OpenTextFile(ReadFileName,1)
  If err.number <> 0 then
    WScript.Echo "Error:  " + Err.Description
    WScript.Quit
  End If

'  If successful, see if we have any records

If ReadFileObject.AtEndOfStream = True then
  WScript.Echo "Error:  This file does not contain any records."
  WScript.Quit
End If

'  If records, start parsing them
'  Open Logging File
On Error Resume Next
  WriteFileName = "c:\SweeperDataload\DataLoadStatusLog_" + Cstr(Year(Now())) +
CStr(MONTH(Now())) + CStr(Day(Now())) + CStr(Hour(Now())) + CStr(Minute(Now()))
+ ".log"
  Set WriteFileObject=FileSystemObject.OpenTextFile(WriteFileName,8,True)
  If err.number <> 0 then
     WScript.Echo "Error Opening Log File:  " + Err.Description
    WScript.Quit
  End If

GoodNameCountValue = 0
AllNameCountValue = 0
Do While ReadFileObject.AtEndOfStream <> True
  '  Make sure we have a record
  ReadLineValue = ReadFileObject.Readline
  If inStr(ReadLineValue, ",") Then
    AllNameCountValue = AllNameCountValue + 1
	
    AddStatus = AddUser(ReadLineValue)
	'wscript.Echo "Addstatus:" & AddStatus 
    If Len(s) = 0 then
      LogIt WriteFileObject,ReadLineValue, cGoodUser, ""
      GoodNameCountValue = GoodNameCountValue + 1

	  Wscript.Echo GoodNameCountValue
    Else
      LogIt  WriteFileObject, ReadLineValue, cFailedUser, AddStatus
    End if
  Else
    ReadFileObject.Skipline
  End If
Loop

WScript.Echo "Finished - Added " + Cstr(GoodNameCountValue) + " of " +
Cstr(AllNameCountValue) + " records."

WriteFileObject.close
ReadFileObject.close
Set ScriptArgumentsObject = nothing
Set FileSystemObject = nothing
Set ReadFileObject=nothing
Set WriteFileObject=nothing

function CleanUpValue(DirtyString)

  CleanUpValue = Replace(DirtyString, Chr(34), "")
 

end function

function AddUser(NameRecord)

    
    dim NameRecordArray, RegEx, UserID, x
	'Wscript.Echo " Read:"&Chr(34)
	
	NameRecordArray = split(ReadLineValue,Chr(34) +  ",")
	If UBound(NameRecordArray) <> (cFieldsCount - 1) Then
		AddUser = " - Invalid fields count (" & (UBound(NameRecordArray) + 1) & ").
Must be " & cFieldsCount
		Wscript.Echo AddUser
       Exit Function
    End If
   
 
   'cWscript.Echo NameRecordArray
  
dim Connection1
dim RecordSet1
dim cmd 
dim dbcon
dim dbcmd

Set Connection1 = CreateObject("ADODB.Connection")
Set RecordSet1 = CreateObject("ADODB.Recordset")
Set cmd = CreateObject("ADODB.Command")

Connection1.Open cConnectionString 
Wscript.Echo "Connection is Opend"

dim userCN,col2,col3

userCN	= CleanUpValue(NameRecordArray(0))
Col2	= CleanUpValue(NameRecordArray(1))
Col3	= CleanUpValue(NameRecordArray(2))

WScript.Echo "User ID   :"&userCN
WScript.Echo "First Name:"&Col2
WScript.Echo "Last      :"&Col3

' WScript.Echo "Cmd:"&cmd.ActiveConnection

set cmd.ActiveConnection = Connection1
on error resume next
cmd.CommandType = adCmdStoredProc
WScript.Echo "IN SP "
cmd.CommandText = "usp_insDummy"
WScript.Echo "IN SP Executed.."

cmd.Parameters.Append cmd.CreateParameter("?ip_col1", adInteger, adParamInput,4,
userCN)
cmd.Parameters.Append cmd.CreateParameter("?ip_col2", adVarChar,adParamInput,50,
col2)
cmd.Parameters.Append cmd.CreateParameter("?ip_col3", adVarChar,
adParamInput,50, col3)

cmd.Execute
WScript.Echo "SP Executed.."
wscript.echo err.description

Connection1.Close

Set Connection1 = nothing
Set RecordSet1 = nothing

'WScript.Echo "Staqrt" + uid
'getUserDetails uid
'EmailUser usrRefUserName,usrRefUsrPassword,usrRefMailId
'WScript.Echo "End" 
end function

------------- End VBS File-------------

Sample.csv file
---------------

"1","Hari","Naidu"
"2","mani","S"
"3","asd","asd"
"4","asdasd","asdasd"
"5","dsadasd","werwer"
"6","werwer","wser"
"7","werwer","werwer"
"8","werwerwer","werwer"
"9","sdfsdf","werwer"
"10","sdfsdf","werwer"
"11","sdfsdfsdf","werwer"
"12","sdfsdf","werwsdfsdfsdf"
"13","sdfsdf","asdsafh"
"14","sdfsdf","dfsdfhj"

----- End of CSV File.--------

Table Name:DUMMY
Column Name    Datatype   
Col1           Integer
Col2           Varchar(45)
Col3           varchar(45)

----End of Table...

StoredProcedure :usp_insDummy
--------------------------
DELIMITER $$

DROP PROCEDURE IF EXISTS `sweepertest`.`usp_insDummy` $$
CREATE PROCEDURE `usp_insDummy`(
in ip_col1 int
,in ip_col2 varchar(45)
,in ip_col3 varchar(45)
)
BEGIN

insert into dummy
select ip_col1,ip_col2,ip_col3;

END $$

DELIMITER ;

-----------End of SP------

Hi,
My requirement is to import .csv file to Mysql database.
for that i have writeen vbscript file,
from vbs file , i am accessing Stored Procedure called "usp_dummy".
table name called 'DUMMY'.
i am getting following error.
plz provide me solution for this bug.
and one more thing the same code working fine in MS SQL Server database.
plz help me for this issue.

with regards
Hari naidu.
mailid:harinaidu@symphony.cc

Hi and thanks for info provided. I'm reopening this report.

Hello. While we're analyzing this problem you can use following workaround since you know parameter values beforehand: Execute 'CALL SP(firstval, secval, thirdval)' directly from ADODBCommand.

I was able to verify this bug on XP.

See attached file "testcase.txt" for details of process and results. Full or partial MDAC version details are available on request (~4MB XML file for full version report)

Reason for the error: The ADODB.Command object is adding a pair of {} around the statement to be executed if the statement is identified as a stored proc (adCmdStoredProc). 

Workarounds: Until the ADO engine can be corrected, compose all SQL calls as fully formed strings in your code and do not set the ADODB.Command.CommandType parameter to adCmdStoredProc.

Test case with results

Attachment: testcase.txt (text/plain), 3.23 KiB.

Hi Shawn
thanx for your co-operation on this issue,
but still i am getting the same problem,
please find the attachment for error description.
could you plz provide any solution for this problem.

thanx in advance.

With Regards
Hari Naidu

Hari, both Shawn and myself provided you with solution! Please read our replies carefully. You are still using command of type "StoredProc". You can do either:
  Connection1.Execute("CALL usp_insDummy("&userCN...
  or cmd.CommandType = adCmdUnspecified (or whatever) and cmd.CommandText = "CALL usp_insDummy("&userCN..., cmd.Execute()
Sorry, our job is not to write code for you but to test wether there is a bug in our products or not.

Thank you for your interest in MySQL.

Hi, Tonci 
Thank you for the help, its working fine.

With Regards
Hari Naidu.

Synopsis:
When setting CmdType to StoredProcedure ODBC CALL "escape sequence" is executed
putting {} around the statement rendering it unusable for MySQL server:
  From ADO documentation: The general syntax for calling a procedure by using the ODBC CALL escape sequence is: {[?=]call procedure_name[([parameter][,[parameter]]...)]}

Note that same script works from MS Access. There's subtle difference in the way VBS works  compared to VBA, which Access uses, that generates this error.
Even though I am not convinced this is MyODBC error, but rather undocumented difference between MS products, Peter Harvey committed a patch and pushed it to relevant source trees, Changelog entry "-- now handles statements embraced by {} (removes the braces)".
Until new version is released, please use provided workarounds.

Hi,Shawn Grgin
Thank you for providing solution on this issue.
Now it is working fine in my dataload.

With Regards
Hari Naidu

Peter's patch to remove { } around statements will be in 3.51.14.