Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2013
    Posts
    7

    Unanswered: Bash Script to Query DB Sql Quries. (Automation using bash script)

    I was wondering anybody can help on the below scenario and the code to start this below via a bash script that runs automatically to CALL DB quries. :

    What needs to be achieved?

    I have a list of DB queries when executed on a command line brings back details in the required table, example "select * from DB_name where db_NUM="" etc brings back stored information in this particular DB table and has all entries stored.

    1.One click process to run script - I want to create a bash script that calls each SQL statement as above and references within the command line if each SQL entry has been added correctly into the database and generates a PASS or a FAIL next to each script being executed?

    Many Thanks

  2. #2
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    Code:
    echo "Starting script 1 \c"
    sqlplus script1.sql <<EOF >script1.rpt
    input data
    EOF
    if [ $? = 0 ]
      then 
        echo " pass"
    else
       echo "fail"
    fi
    Repeat for each script. If there is no keyboard input then omit the "<<EOF" and all lines down to and including EOF.
    $? is the operating system interpretation of pass or fail, which might not be the same as the application program definition.

  3. #3
    Join Date
    Feb 2013
    Posts
    7
    Thanks for the above,

    where you have referenced “input data” is this where I’ am inserting the SQL query that I want returning from this script?

    Also these DB tables are connected via a connection string, how do you go about referencing the database connection string within a bash script please so it picks up the correct queries i have refrenced?

  4. #4
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    Can you post a typical terminal session?

  5. #5
    Join Date
    Feb 2013
    Posts
    7
    hello

    A typical terminal session would look like the one attached.

    To request a particular SQL statement you would run for example on the terminal line "select * from db_table where db_NUM="" etc, this would bring back the records for this query.

    I want to insert these quries in bash and run this query from the command line and give it a pass or fail instead of executing the query manually.

    Many Thanks
    Attached Thumbnails Attached Thumbnails terminal session.png  

  6. #6
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    What are the sql statements that you type? I need to see the entire session, up to the exit or logout.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Might I suggest using Perl or another scripting language that includes better support for database access? Using a BASH script is great for "one off" or very specific, dedicated tasks but I have the feeling that your needs are more sophisticated than that so a language like Perl might be a better fit.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Feb 2013
    Posts
    7
    Thanks for the replys.. how would this be setup using a pearl script please...

    Further instructions on what iam acheving here :

    Steps. Input the following from the command line. From Login to logout

    1. Sudo su – hhr01 to login in the terminal
    2. A password is requested hit enter
    3. QueryNMR "select * from int.head where custom_NUM=05009890"
    4. Above query brings back a record in table.
    5. Input exit to logout of system terminal.
    6. A bash script to automate any SQL query I execute within a bash script to test SQL records.

  9. #9
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    Ady, thanks for the private message, but solving problems through private messaging is paid service, and does not benefit other readers.
    In the steps in the above post #8, you have left out the commands between line 2 and line 3.
    At the end of line 2 you only have a $ sign as a prompt. The shell will not process the select statement in line 3.

  10. #10
    Join Date
    Feb 2013
    Posts
    7
    Thanks for the reply,.

    ahh ok, so how do i go about processing this select statement as a output as a pass or fail to suggest any entry being submitted to a table is being submitted correctly, when ever this SQL statement is triggered.

    Thanks

  11. #11
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    Lets break this problem down into several steps.
    The first step is to create a bash script that will execute a sql request.
    What database manager are you using, and how do you connect to it?
    What sql statements do you currently type?

  12. #12
    Join Date
    Feb 2013
    Posts
    7
    Replies to the below questions...

    The first step is to create a bash script that will execute a sql request.

    - Yes that correct a batch script that triggers these SQL statements

    What database manager are you using, and how do you connect to it?

    - Iam using a version of secureCRT to connect to a database session where all the database query's can be triggered from the command line. example select * from db_name where id="123", brings back results.

    I connect to a specific environment setup example to connect to ip via the session i run a particular IP example 162.19.1.1 and this connects to all the database tables.


    What sql statements do you currently type?

    Within the session iam currently typing the following sqls below i just need a process of where iam not manually kicking of these statements and i can run them when ever.

    SQL's - running from command line via secureCRT database.

    - QueryN "select * from users with ur" - This brings back a list of users

    - QueryN "select * from int.head where custom_NUM=05009890" - this brings back entry added from a website and store the number in the database sql above.

    - QueryN "select * from int.pd where custom_NUM=0087865" - this brings back a that all data in that table for that customer number once triggred from the webpage.

    The above statements i would like to kick off using a bash/ any other method so i can run these statements automatically when ever?

    Thanks for the help.

  13. #13
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    I think I have it figured out. After you have logged into the Linux system, you first enter 'su - hhr01'. This command creates the correct environment for the database, and connects to the database so you may then enter sql commands directly at the prompt.
    You do not need a bash script, but rather a sql script to automate your processes.
    See the following link How do I execute an SQL script in MySQL? - Web Tutorials - avajava.com

  14. #14
    Join Date
    Feb 2013
    Posts
    7
    thanks for the reply.

    That's correct. I can enter SQL commands directly from the command as mentioned below.

    I can also from the command run SQL queries and run bash commands such as creating as below

    #!/bin/bash
    STRING="HELLO WORLD!!!"
    echo $STRING

    ./hello_world.sh - execute command,

    So how would i set these in my command line as below through a text file and then running then as the tutorial explains.


    The tutorial links explains inserting data as below :

    USE testdatabase;
    DROP TABLE IF EXISTS employees;
    CREATE TABLE employees (id INT, first_name VARCHAR(20), last_name VARCHAR(30));
    INSERT INTO employees (id, first_name, last_name) VALUES (1, 'John', 'Doe');
    INSERT INTO employees (id, first_name, last_name) VALUES (2, 'Bob', 'Smith');
    INSERT INTO employees (id, first_name, last_name) VALUES (3, 'Jane', 'Doe');
    SELECT * FROM employees;

    Would this be a case of creating these in a text file with my queries and then running them on my command line?

    And also is their a way in the above script to create a pass or fail code i can insert to suggest that these entry's are successful in the database?

    Thanks for the help.

  15. #15
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    If you want to create a bash script, then you need to extract the relevant lines from hhr01's .profile.
    This will show you how to connect to the database, and create the correct environment.

Posting Permissions

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