Link to home
Start Free TrialLog in
Avatar of westerntour
westerntour

asked on

insert string array to database

Hi Expert,
I'm doing the project that need to download data from web page as a string array and use these data insert to database.  Any help will be great.
Here is my example code
-----------
try
         {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            Connection dbConn = DriverManager.getConnection("jdbc:odbc:BMAC");
            if (dbConn == null)  
            {
               System.out.println("Connection to database failed");
            }
            else
            {                        
            try
         {
            InetTemplate obj = new Inet () ;
            String strContent = obj.getURL("http://my_webpage");
            String strData = obj.getData(strContent);
            BufferedReader reader = new BufferedReader(new StringReader(strData));            
            String line;
            int count = 0;
            array = new String[120];
                  
            while ((line = reader.readLine()) != null)
            {
               array[count] = new String((line)+ "\r\n ");      
               count++;
            }
      }
       catch (Exception e)
            {
               System.out.println(e.getMessage());
               System.exit(0);
            }

     Statement stmt = dbConn.createStatement();
      String      dbSQL = "SELECT * FROM table WHERE col_Name=''";            
            ResultSet rs = stmt.executeQuery(dbSQL);
            if (!rs.next())
            {
                dbSQL = "INSERT INTO table (col_Name) VALUES (";
            for (int i=0;i<array.length;i++)
            {
            dbSQL  += "' " + array[i] + " ',";
            }
            dbSQL += ")";
            stmt.executeUpdate(dbSQL);
            }
              rs.close();
       }
                             
            dbConn.close();
            Statement stmt = null;
                                 
         }
             catch (Exception e)
            {
            }
Avatar of Mick Barry
Mick Barry
Flag of Australia image

>    ResultSet rs = stmt.executeQuery(dbSQL);

why do you do a select if you want to *insert* the strings?

sounds like u should be instead looping thru your array inserting a row for each string

for (int i=0; i<array.length; i++) {
   // insert array[i]


}

A PreparedSTatement could be used for this
Avatar of InNoCenT_Ch1ld
InNoCenT_Ch1ld

I assume that your select statement is use to find out if the data is already in the database before you insert?
ASKER CERTIFIED SOLUTION
Avatar of InNoCenT_Ch1ld
InNoCenT_Ch1ld

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
note that the 'top 1' must be there, else it will insert more than 1.. (the number of insert = the total row you have in the table, i guess)
Avatar of westerntour

ASKER

Note that before I insert array to table, all records were deleted (table is empty). String array have data like this:

String [] array = {"SAN 2012","LOS 2005","SFO 2010",...} (this array has about 100 list that are the same lenght)

I need to insert these data into 2 column of the database.
In general, I have to download the data on web page and populate the database with that information. What should I start?
Web page as like this:

SAN    1012
LOS    2005
SFO    2010

and so on (100 lines)
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
PreparedStatement stmt = conn.prepareStatement(query);

for(int i = 0; i < your_array.length; i++){
    String str = your_array[i];
    stmt.setString(1, str.substring(0, str.indexOf(' '));
    stmt.setString(2, str.substring(str.indexOf(' '));

    stmt.addBatch();
}
stmt.executeBatch();
it's error at:
stmt.setString(1, str.substring(0, str.indexOf(' '));
      ^
 stmt.setString(2, str.substring(str.indexOf(' '));
       ^
what's the error msg?
u can avaoid using substring()

for(int i = 0; i < your_array.length; i++){
    String[] str = your_array[i].split(" ");
    stmt.setString(1, str[0]);
    stmt.setString(2, str[1]);

    stmt.addBatch();
}
stmt.executeBatch();
Not the error msg, it can't compile
when u compile the source code, it will shows error message, we need to know that.

btw, are u using any IDE or just notepad?

and maybe you can post your code:
the query and the loop part
>> stmt.setString(1, str.substring(0, str.indexOf(' '));

You forgot an ) at the end:

stmt.setString ( 1, str.substring ( 0, str.indexOf ( ' ' ) ) ) ;
One ) for indexOf (), one for substring (), one for setString ()
argh!!! my mistake..
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>> argh!!! my mistake..

2 pairs of eyes are always better than 1 ;-)
not to mention my pair = old eyes...
>>"insert into table x (first_col, second_col) values (?,?)";
That means I put the question mark (?) or the real value in value(?,?).
the question mark
you put the REAL value using setString (in your case, if they are String)