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
