how do you call a SQL script inside a java program?

Discussion in 'OT Technology' started by HardTech, Apr 16, 2005.

  1. HardTech

    HardTech hungry

    Joined:
    May 5, 2000
    Messages:
    28,103
    Likes Received:
    1
    Location:
    NorCal
    I have these scripts just ready to go, but I have no idea how to call them :hs:

    also, do i just put SQL commands in the script, and calling the script will do those commands in SQL?

    Thanks
     
  2. CompiledMonkey

    CompiledMonkey New Member

    Joined:
    Oct 26, 2001
    Messages:
    8,528
    Likes Received:
    0
    Location:
    Richmond, VA
  3. HardTech

    HardTech hungry

    Joined:
    May 5, 2000
    Messages:
    28,103
    Likes Received:
    1
    Location:
    NorCal
    let me try to explain my situation a little better

    I have no problems connecting to the database and creating tables or inserting rows... however, at the end of my program, when the user logs out, I want the tables to all be dropped and then re-created, and have all of the records re-inserted... kind of like a refresh

    but instead of having to drop 3 tables and re-create them, along with reinserting the values, I was wondering if there was a way I can call a SQL script I already made to automatically do it inside the code... the script I have is called "table.sql", and have no idea on how I would go about implementing this.

    I could just do it the old-fashioned way and do it manually, i.e. read each line of code from the script and execute it on the database, but I was hoping there was an easier way
     
  4. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    what type of database are you dealing with? i know there is a way with sql server to call something like that from the command line, so you could just essentially run a system call, and pass in the filename as the parameter. I know most database system's don't like you to just throw the whole thing into a single command with code either, so you'll definately have to break it up if you want to do it all in code. I used to do it like this:

    .sql file:
    Code:
    delete from table myTable;
    
    **END STATEMENT**
    
    drop table myTable;
    
    **END STATEMENT**
    
    create table myTable ( ....
    
    then i would open the file in code, and execute the commands one at a time, but i would split them up by that **END STATEMENT** so i could execute them one at a time.

    psudo code:
    Code:
    sSql = readFromFile( "myFile.sql" )
    sqlArray = split( sSql, "**END STATEMENTTT**")
    
    foreach( stmnt in sqlArray )
    {
        db.Execute( stmnt );
    }
    
    also, you mentioned running the script when the user exits the program? I would suggest you do that on startup instead. What if the application crashes and your script doesn't get run? What if the computer gets shut off before the app is closed? At least you know that if you run it on startup, before the app really fully loads that when the user sees his/her environment, that you have clean tables.
     
  5. CyberBullets

    CyberBullets I reach to the sky, and call out your name. If I c

    Joined:
    Nov 13, 2001
    Messages:
    11,865
    Likes Received:
    0
    Location:
    BC, Canada/Stockholm, Sweden
    if its a mysql database use jconnect. makes crossplatform dev easier
     
  6. unrealii

    unrealii professor of plant biology

    Joined:
    May 6, 2001
    Messages:
    2,037
    Likes Received:
    0
    Location:
    So CALI
    why not use cachedrowset rather than having to modify the database?
     

Share This Page