Bug #21145 Not able to Excute SP throught VBS file.
Submitted: 19 Jul 2006 12:06 Modified: 20 Jul 2006 8:33
Reporter: Hari A Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:3.51 OS:Microsoft Windows (Windows NT)
Assigned to: CPU Architecture:Any

[19 Jul 2006 12:06] Hari A
Description:
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

How to repeat:
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------

Suggested fix:
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,'Hari','Naidu'))'at line 1
[20 Jul 2006 8:33] Tonci Grgin
This is a duplicate of #21141 posted by the same reporter