连接VB脚本和SQL Plus,将值传递给查询
问题描述:
从文本文件中读取数据后,我尝试连接到SQLplus并将值存储在一个变量中,并试图更新表,但出现错误消息:“Unterminated字符串常量 这里是代码的样子,感谢Guido帮了我的第1步。 任何人都可以请指出错误。如果里面否则&部分,SQL查询或接错某些错误连接VB脚本和SQL Plus,将值传递给查询
dim fs, txt, line, yesno , cust_id
set fs = CreateObject("Scripting.FileSystemObject")
set txt = fs.OpenTextFile("E:\batchfiletest\Eapp3\scotia1.txt", 1, false)
' loop through all the lines
do while not txt.AtEndOfStream
line = txt.readLine
' read the character and store it in a variable
yesno = Mid(line, 127, 1)
cust_id = Mid(line, 1,20)
' execute the correct query
if yesno = "Y" then
set WshShell = CreateObject("WScript.Shell")
set oEnv=WshShell.Environment("Process")
cmdString = "E:\oracle\product\10.2.0\db_1\BIN\sqlplusw.exe -S sysman/[email protected]
UPDATE csa_sli_all.T_CONV_quote set HOLD_CODE = 'CAQ' where quote_id = cust_id ;
commit;"
Set oExec = WshShell.Exec(cmdString)
ELSE
set WshShell = CreateObject("WScript.Shell")
set oEnv=WshShell.Environment("Process")
cmdString = "E:\oracle\product\10.2.0\db_1\BIN\sqlplusw.exe -S sysman/[email protected]
UPDATE csa_sli_all.T_CONV_quote set HOLD_CODE = 'PVQ' where quote_id = cust_id ;
commit;"
Set oExec = WshShell.Exec(cmdString)
end if
loop
MsgBox "Press OK to close when done reading the output."
答
正常更快,更安全的方法就像
Const sConnectionStringOracle = "Provider=OraOLEDB.Oracle;Data Source=xxxx.xxxxx;User id=xxx;password=xxx"
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.open sConnectionStringOracle
sql = "UPDATE csa_sli_all.T_CONV_quote set HOLD_CODE = 'CAQ' where quote_id = " & cust_id
oConn.execute(sql)
'rest of the database transactions
oConn.close
Set oConn = nothing
您可以添加错误捕获,日志记录等。 只需确保在您使用的计算机上安装了Oracle OleDb驱动程序。 如果您需要使用Sql * Plus,那么将所有事务写入一个sql文本文件并且只运行一次。您可以在发生错误时执行和调试sql。
Grtz