JDBC and SQL

[Eimo]

Hi,

Can anyone help me, I'm trying to do a program using the JDBC..I'm trying to create tables using SQL but I'm not sure what to write in the path, username, and password field????

Here is what I wrote...

try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("driver", "username", "password");
result = true;
}
catch(ClassNotFoundException e) {
System.out.println("Unable to load the driver class");
result = false;
}

when I try to start the program, it goes to the catchException message...

Can anyone advice please, what should I do???

[mine0926]

conn = DriverManager.getConnection("driver", "username", "password");

See this page...
http://www.rgagnon.com/javadetails/java-0112.html

[Nikhilkumr]

I don't know about the jdbc and faced always problem at the time connectivity because I am trying self and many problem arises so i want your help to do best connectivity.

[suresh.sa]

Try this

try{
class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnection("jdbc:odbc:DSN");
Statement st=con.createStatement("Select * from emp");
}

[Eimo]

Try this

try{
class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnection("jdbc:odbc:DSN");
Statement st=con.createStatement("Select * from emp");
}

What about the user name and password?
May I ask also, is emp a table??? if yes, I think in my case I just use the first and second line...righT?

[Tolls]

Try this

try{
class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnection("jdbc:odbc:DSN");
Statement st=con.createStatement("Select * from emp");
}

No, don't do this.

You have a prefectly good JDBC driver designed for Oracle. Using the JDBC/ODBC bridge is a major step backwards.

If you're going to answer questions at least make sure you know what you're talking about.

As for the original problem, first off the you need to print the full exception (e.printStackTrace()). That'll tell you exactly what's wrong.

I'm guessing that you haven't got the oracle driver jar in your runtime classpath...in which case you need to tell us how you are running this.

On top of that you need to read up on Oracles driver to check what format the url for getConnection needs to be in. It's different for each driver.

[Nikhilkumr]

I want to try for job in my hometown and my interest in development using java language so I want some questions which is minor and generally not in candidate mind when he/she prepare for interview. So I want your help sir.

[Tolls]

You ought to open your own thread on that then, not hijack someone elses.

[Eimo]

No, don't do this.

You have a prefectly good JDBC driver designed for Oracle. Using the JDBC/ODBC bridge is a major step backwards.

If you're going to answer questions at least make sure you know what you're talking about.

As for the original problem, first off the you need to print the full exception (e.printStackTrace()). That'll tell you exactly what's wrong.

I'm guessing that you haven't got the oracle driver jar in your runtime classpath...in which case you need to tell us how you are running this.

On top of that you need to read up on Oracles driver to check what format the url for getConnection needs to be in. It's different for each driver.

Thank you so much, I've moved from this problem and I created the connection and I created my tables as well... but I have now another problem in the execution time....I got this error:
java.sql.SQLException: ORA-00955: name is already used by an existing object

What that mean???? therefore, the tables are not initiated?

[Tolls]

Quoting the docs:

An attempt was made to create a database object (such as a table, view, cluster, index, or synonym) that already exists. A user's database objects must have distinct names.

You are reusing a table name or something similar.

[Eimo]

Quoting the docs:


You are reusing a table name or something similar.

Thanks for reply, I changed my tables name in the code to solve this problem, but now I have another error, and I don't know how to solve it. I'm working on it remotely. Is this could be a problem???

This is how I get the connection...
Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@bajor.engr.scu.edu:1521:db9 i",
"username", "password");

Error:
Io Exception: the network adapter could not establish the connection

Please advice???

[Tolls]

Something's wrong with your URL then.
That's what that error means.
Usually port number or your db sid, since I think you get a different error if the server ip is wrong.
According to that yous sid is "db9 i". Is that space really there in your code or is it a forum glitch?

[Eimo]

Something's wrong with your URL then.
That's what that error means.
Usually port number or your db sid, since I think you get a different error if the server ip is wrong.
According to that yous sid is "db9 i". Is that space really there in your code or is it a forum glitch?

That's a forum glitch, I'll check the URL and see... Thank you so much

[Eimo]

Hi again,
Now, I'm facing a slight problem. I wish that anyone can help me please... I created tables, and now I'm trying to fill inside it within the code. But for a reason it is not going into the method initTables at all... it only display 2 names out of 15????

This is an initiation of two tables....

try {
Statement stmt = conn.createStatement();
for (int i = 0; i < SQLData.length; i++) {
iRowCount = iRowCount+1;
iRowCount= stmt.executeUpdate("INSERT INTO authors VALUES " + SQLData[i] );

}
System.out.println( iRowCount +" Rows are inserted.");
PreparedStatement pstmtPublishers = conn.prepareStatement("INSERT INTO publishers(publisherID,publisherName) VALUES (?,?)");
for(int i=1; i <= 15; i++) {
pstmtPublishers.setInt(1, i);
pstmtPublishers.setString(2, "Publisher" + Integer.toString(i));
pstmtPublishers.executeUpdate();
}
pstmtPublishers.close();

I appreciate any help ....

[RamyaSivakanth]

put some debugging statements(s.o.p)
Paste ur complete code.
For each and evry method,write a comment on top of that so that it will be easy for you as well fopr the reviewer to give comments.

[Eimo]

put some debugging statements(s.o.p)
Paste ur complete code.
For each and evry method,write a comment on top of that so that it will be easy for you as well fopr the reviewer to give comments.

Here are the link for the code that I made, actually I'm not getting any error but I think I have a problem in initTable method, because It's not getting all the data.

BooksDB.java - File Shared from Box.net - Free Online File Storage
BooksDBConsole.java - File Shared from Box.net - Free Online File Storage

If you can help me please, I don't know what is the problem.

[Tolls]

For this code:

for (int i = 0; i < SQLData.length; i++) {
    iRowCount = iRowCount+1;
    iRowCount= stmt.executeUpdate("INSERT INTO authors VALUES " + SQLData[i] );
}

How many inserts are called (you'll need to put in the debug stuff Ranya suggested to see.
Also what are the actual INSERT statements...print them out as you go.

I suspect one of them is failing because you are not using a PreparedStatement here and are concatenating names into your INSERT statement, and one of them has something odd about it.

[Eimo]

For this code:

for (int i = 0; i < SQLData.length; i++) {
    iRowCount = iRowCount+1;
    iRowCount= stmt.executeUpdate("INSERT INTO authors VALUES " + SQLData[i] );
}

How many inserts are called (you'll need to put in the debug stuff Ranya suggested to see.
Also what are the actual INSERT statements...print them out as you go.

I suspect one of them is failing because you are not using a PreparedStatement here and are concatenating names into your INSERT statement, and one of them has something odd about it.

This is what I get when I run the program...
------------------------------------------------------------------------------------
Tables created successfully
Unable to init tables
java.sql.SQLException: ORA-00911: invalid character

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:114)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:2 08)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:542)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol .java:1311)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TT C7Protocol.java:738)
at oracle.jdbc.driver.OracleStatement.executeNonQuery (OracleStatement.java:1313)
at oracle.jdbc.driver.OracleStatement.doExecuteOther( OracleStatement.java:1232)
at oracle.jdbc.driver.OracleStatement.doExecuteWithBa tch(OracleStatement.java:1353)
at oracle.jdbc.driver.OracleStatement.doExecute(Oracl eStatement.java:1760)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTi meout(OracleStatement.java:1805)
at oracle.jdbc.driver.OracleStatement.executeUpdate(O racleStatement.java:531)
at BooksDB.initTables(BooksDB.java:84)
at BooksDBConsole.main(BooksDBConsole.java:18)
WELCOME TO BOOKS DATABASE.
Please Select from the options below:
1 - List all authors.
2 - List all publishers.
Please enter a menu option now:
1
Following are the authors names sorted alphabetically by last name and first name:
1, Khodary , Abdulaziz
2, Khodary , Abdulaziz
Please enter a menu option now:
---------------------------------------------------------------------------
The problem I think in filling the tables, because it goes inside thr for loop and stopped in the second row for the first table (I defined 15 names). and it is not going to the second loop for the second table.
I'm sure there is somethig wrong, but I tried several options and still not working...Please advice.

[Eimo]

For this code:

for (int i = 0; i < SQLData.length; i++) {
    iRowCount = iRowCount+1;
    iRowCount= stmt.executeUpdate("INSERT INTO authors VALUES " + SQLData[i] );
}

How many inserts are called (you'll need to put in the debug stuff Ranya suggested to see.
Also what are the actual INSERT statements...print them out as you go.

I suspect one of them is failing because you are not using a PreparedStatement here and are concatenating names into your INSERT statement, and one of them has something odd about it.

Thank you so much for your advice...Now, it started to fill the entire table. Now, my question is how I can retrieve from the array...
For example:

String[] Data={"(1,'F1','L1')","(2,'F2','L2')"};

If I want this array to be filled in this code...Can you advice me how I can do that???

PreparedStatement stmt = conn.prepareStatement("INSERT INTO authors (ID,firstname,lastname) VALUES (?,?,?)");

for (int i=1; i<=Data.length;i++) {
stmt.setInt(1,i);
stmt.setString(2, how can I retrieve the first name from the array),
stmt.setString(2, how can I retrieve the last name from the array),
stmt.exceuteUpdate()
}//End for

stmt.close();

-----------------------------------

I really appreciate the help and support you give...

Thank you

[mine0926]

I think what you need is a 2D array.

    String[] Data={"(1,'F1','L1')","(2,'F2','L2')"};

    //String[here to fill what [b]row[/b]][here to fill what [b]column[/b] of the specified row]
    String[][] Data={{1,"F1","L1"}, //<---- Row 1
        {2,"F2","L2"}};                 //<---- Row 2

Also, you are using String[] but its value has an integer.

    [b]String[/b][][] Data={{[b]1[/b],"F1","L1"},
        {[b]2[/b],"F2","L2"}};