x

Reading text from a file , evaluting it and updating table

I am stuck on a step in below script. It is reading from a text file , picking up a value, evaluating it and based ont the value returned , updating oracle table ( using SQLplus ) with one of the value returned.

All thing apart from BODY of IF and ELSE is working. So issues are either with connection or with datapassing

Please help me

 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/csaadmin@convcsd
 
     UPDATE csa_sli_all.T_CONV_quote set HOLD_CODE = 'CAQ' where quote_id =  cust_id ;"
 
      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/csaadmin@convcsd
 
     UPDATE csa_sli_all.T_CONV_quote set HOLD_CODE = 'PVQ' where quote_id =  cust_id ;"
 
     end if
 loop
more ▼

asked Apr 10, 2012 at 08:58 AM in Default

avatar image

PriyankaKaushik
0 1 1 2

I do not know how this script is going to work. But I can see you assigning value to the cmdString, but no execution? I remember one of the ways to do this, is/was to use ADODB.Connection, connection string etc. After opening connection to Oracle, execute the specific command, close the connection and so on. I guess you can google to find it somewhere.

Apr 12, 2012 at 01:25 PM Usman Butt
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Use SSIS to open and read the file, or `bcp` to get the data into a table, then you can use standard T-SQL to update depending on the values.

more ▼

answered Apr 10, 2012 at 09:16 AM

avatar image

Kev Riley ♦♦
63.8k 48 61 81

We dont have Microsoft SQL server 2005, we are using Oracle SQL*Plus

Apr 10, 2012 at 09:38 AM PriyankaKaushik

You tagged your question 'sql-server-2005', so I answered in context. Sorry. I'll retag 'oracle'

Apr 10, 2012 at 09:40 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x424
x11

asked: Apr 10, 2012 at 08:58 AM

Seen: 975 times

Last Updated: Apr 12, 2012 at 01:25 PM

Copyright 2016 Redgate Software. Privacy Policy