Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2004
    Posts
    10

    Unanswered: Starting sql scripts from shell script

    Hi

    I am not a DBA and I have to help someone at work so:

    Basically, there are X number of .sql files that have to be run with a specific value for the wirecenter. Then, once the sql script is run, the output has to be captured to a csv file.

    So I am supposed to write a script that:

    1. Fires off all the .sql scripts.
    2. Passes them the value of the wire center which is used to query the DB.
    3. Capture the results of each sql script in a CSV file.
    4. Highlight how much time it took the queries to execute.

    Anyone want to help me with this?

    Thanks!

  2. #2
    Join Date
    Jun 2004
    Posts
    10
    I had to do a similar effort for the oracle db and this is what I did:

    #! /bin/sh

    sqlplus -s scott/tiger@DatabaseName << ENDSQL

    WHENEVER SQLERROR EXIT 5
    WHENEVER OSERROR EXIT 10

    SPOOL ${SCI_PORT_OLD.csv}
    @create table SCI_PORT_OLD.sql
    SPOOL OFF

    SPOOL ${CREATE_CLINK.csv}
    @Create clink.sql
    SPOOL OFF

    SPOOL ${FMS_Before_Picture.csv}
    @FMS Before Picture.sql "Value_for_Wirecenter"
    SPOOL OFF

    SPOOL ${Update_FMS_PORT.csv}
    @update FMS.SCI_PORT.sql
    SPOOL OFF

    SPOOL ${FMS_After_Picture.csv}
    @FMS After Picture.sql "Value_For_Wirecenter"
    SPOOL OFF

    SPOOL ${REPORT_FMS_Fallout.csv}
    @REPORT_FMS_Fallout.sql "Value_For_Wirecenter"
    SPOOL OFF

    SPOOL ${REPORT_FMS_NON_CONVERSION.csv}
    @REPORT_FMS_Non_Conversion.sql "Value_For_Wirecenter"
    SPOOL OFF

    SPOOL ${DROP_VANC_CLINK.csv}
    @DROP VANCOUVER_CLINK.sql
    SPOOL OFF

    EXIT;
    ENDSQL

  3. #3
    Join Date
    Nov 2002
    Posts
    207
    for EachSQL in `ls *.sql`
    do
    isql -Usa -Sqwerty -i $EachSQL -o $EachSQL.csv
    done

    To get the time of execution, use select getdate() in each file.

  4. #4
    Join Date
    Jun 2004
    Posts
    10
    Quote Originally Posted by mkalsi
    for EachSQL in `ls *.sql`
    do
    isql -Usa -Sqwerty -i $EachSQL -o $EachSQL.csv
    done

    To get the time of execution, use select getdate() in each file.
    So something like this for multiple sql scripts?

    #! /bin/sh
    isql -Usa -Sqwerty -i $test1.sql -o $test1.csv

    isql -Usa -Sqwerty -i $test2.sql -o $test2.csv
    If that's correct, question:

    1. How to pass a variable to the sql scripts?

    Really appreciate your help!

  5. #5
    Join Date
    Nov 2002
    Posts
    207
    You can write the code in shell script as...

    script.sh
    #this will run all *.sql files in your current directory one by one.

    for EachSQL in `ls *.sql`
    do
    isql -Usa -Sqwerty -i $EachSQL -o $EachSQL.csv
    done

    If you want to pass a parameter, then depending on your scenario you can alter *.sql file to replace "#####" in SQL file with parameter before it gets executed or else you have to do isql instead of taking input from file as...

    script2.sh

    var1="abc"

    isql -Usa -Sqqqq <<! >sql.out
    use db
    go
    select * from table1 where name = "$var1"
    go
    !

    Hope you get the idea.

  6. #6
    Join Date
    Jun 2004
    Posts
    10
    Quote Originally Posted by mkalsi
    You can write the code in shell script as...

    script.sh
    #this will run all *.sql files in your current directory one by one.

    for EachSQL in `ls *.sql`
    do
    isql -Usa -Sqwerty -i $EachSQL -o $EachSQL.csv
    done

    If you want to pass a parameter, then depending on your scenario you can alter *.sql file to replace "#####" in SQL file with parameter before it gets executed or else you have to do isql instead of taking input from file as...

    script2.sh

    var1="abc"

    isql -Usa -Sqqqq <<! >sql.out
    use db
    go
    select * from table1 where name = "$var1"
    go
    !

    Hope you get the idea.
    Thanks.

    I will have to alter the *.sql files to accept a parameter from the script.

    One thing though: since I have multiple .sql files to invoke, do I have to do that in a loop? Or will this work?
    isql -Usa -Sqwerty -i $test1.sql -o $test1.csv
    isql -Usa -Sqwerty -i $test2.sql -o $test2.csv
    I apologize for asking dumb questions but as I mentioned, I am neither a DBA nor have I scripted that much.

    Thanks!

  7. #7
    Join Date
    Nov 2002
    Posts
    207
    ...either way is fine.

    for loop will take care of all files one-by-one, otherwise you have to run them manually.

    more questions??? keep them coming ;-)

  8. #8
    Join Date
    Jun 2004
    Posts
    10
    Quote Originally Posted by mkalsi
    ...either way is fine.

    for loop will take care of all files one-by-one, otherwise you have to run them manually.

    more questions??? keep them coming ;-)
    Ahh..so unlike oracle, you can't have the *.sql script names one after the other and have isql invoke them!

    So I have to construct a loop.

    Thank you!

  9. #9
    Join Date
    Jun 2004
    Posts
    10
    I have another question.

    I need the output in csv form.

    For my oracle script, I used inbuilt sqlplus functions:
    set verify off
    set lines 700
    set trimspool on
    set feedback off
    set termout off
    set colsep ','

    Using these, I got the output as a perfect csv file.

    Does Sybase has any such functions that I could use?

    Thanks!

  10. #10
    Join Date
    Jan 2004
    Posts
    51
    isql has -s option to specify column separator. So if you need csv file, you can specify isql -S.. -U.. -s

  11. #11
    Join Date
    Jun 2004
    Posts
    10
    Quote Originally Posted by sushant
    isql has -s option to specify column separator. So if you need csv file, you can specify isql -S.. -U.. -s
    Hi Sushant!

    I got that now, thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •