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 '12 at 08:58 AM in Default

PriyankaKaushik gravatar 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 '12 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][1] to get the data into a table, then you can use standard T-SQL to update depending on the values.

[1]: http://msdn.microsoft.com/en-us/library/ms162802(v=sql.90).aspx
more ▼

answered Apr 10 '12 at 09:16 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

We dont have Microsoft SQL server 2005, we are using Oracle SQL*Plus
Apr 10 '12 at 09:38 AM PriyankaKaushik
You tagged your question 'sql-server-2005', so I answered in context. Sorry. I'll retag 'oracle'
Apr 10 '12 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x375
x7

asked: Apr 10 '12 at 08:58 AM

Seen: 667 times

Last Updated: Apr 12 '12 at 01:25 PM