Some JDBC questions

[Bender]

Hello all.

I'm trying to write a little program in Java to do some basic things with databases (it's a class assignment), and I'm using JDBC to connect to the database and add some records.

Unfortunately for me, the specifications for this assignment say the program has to physically create the database on the disk. I've been connecting to Microsoft Access DBs (that's all I have on my home computer)... does anyone know if it's even possible to build a MS Access DB from scratch in a Java program?

If not, is there any other DB program I could use that has this functionality? Maybe Oracle?

Thanks for any suggestions.

[inkedmn]

this should help you out some...

good luck :)

http://www.javaworld.com/javaworld/javaqa/2000-09/03-qa-0922-access.html

[bwkaz]

This would be horrible to actually do, like in real life, but you could always create an empty MDB file, and then read it into another program in binary mode (a byte at a time), print out the contents of it, and then hardcode those bytes into your Java program, and write them out to a file (call it whatever.mdb) when you need to do your database creation. Then just connect to that database and do whatever you normally do to create tables, queries, etc., whatever.

I know, it's horrible, but I can't think of anything else (of course, I haven't played with JDBC either, or really even true ODBC, just MS's bastardized T-SQL, which I'm pretty sure doesn't even work on an MDB, so there may actually be much better ways of doing it... use this as a last resort only, please!).

[Bender]

Originally posted by inkedmn
this should help you out some...

good luck :)

http://www.javaworld.com/javaworld/javaqa/2000-09/03-qa-0922-access.html I have no problem connecting to the database, I need to find a way to CREATE the database. 8)

In the assignment he recommends we use ADO connection objects... but I'm not very fluent in that stuff.

[inkedmn]

i think you have to have an ODBC datasource to connect to when you use JDBC with MS Access(which, I BELIEVE, requires an existing database)...

am i wrong?

[Bender]

Originally posted by inkedmn
i think you have to have an ODBC datasource to connect to when you use JDBC with MS Access(which, I BELIEVE, requires an existing database)...

am i wrong? Yes, JDBC/ODBC requires an existant database. Like I said above, I'm thinking I need to use ActiveX Direct Objects to physically create a DB before I can connect to it.

I've seen VB code to do that, but can't find any Java examples... or even if there's ADO classes for Java. And I absolutely refuse to use VB for this assignment! :)

[inkedmn]

heh, best of luck then :)

try http://groups.google.com, i usually find what i'm looking for there

[stuka]

I think that if you MUST create an Access database, you're stuck doin' it in VB. Can you use a real RDBMS for this?

[Bender]

Originally posted by Stuka
I think that if you MUST create an Access database, you're stuck doin' it in VB. Can you use a real RDBMS for this? It can be either an Access DB, Oracle DB, or MySQL DB. Do you know of a way to make an Oracle DB with Java? (I can dl Oracle for free, but I don't have quite the $12,000 to buy MySQL)

[Strike]

Originally posted by Bender
It can be either an Access DB, Oracle DB, or MySQL DB. Do you know of a way to make an Oracle DB with Java? (I can dl Oracle for free, but I don't have quite the $12,000 to buy MySQL)
MySQL? $12,000? Are you kidding? Try $0.

[Bender]

Originally posted by Strike
MySQL? $12,000? Are you kidding? Try $0. Well, regardless of it's price, the question is still on the table. Thanks for the help anyway.

(I was thinking of SQL Server)

[Bender]

Hooray! I found a nice way to do it using a MySQL server and database.

Thanks to inkedmn, bwkaz, and Stuka for your help.

[bwkaz]

Well, I must say, I didn't help much... :P

But I'm glad you got it working.

[inkedmn]

~praise MySQL :)

[_underdog]

I personally prefer to use mySQL because you can use jdbc and stay away from the jdbc-odbc bridge. If you ever do have to connect to Access database and don't want to use odbc here is an opensource API that you could use. http://danadler.com/jacob/
It is not the easiest thing to use, but it works. Connects to Excel spreadsheets and word docs too.

[Dru Lee Parsec]

See what happens when I go away for a weekend? A long Java thread and I totally missed it :(

Sounds like you figured it out though. Although this sounds like one more assignment where the teacher doesn't know sh*t about what he's teaching. If he wanted to teach JDBC then he should simply have one program adding data to a table (or set of tables) and another program doing retrievals using Select from and joins in the SQL statement. By assigning this stupid "create the database" thing the students are stuck trying to figure out things that have nothing to do with jdbc.

[stuka]

Dru - that's not entirely true...JDBC allows all sorts of DB functionality, and creating databases is an essential part of that. Granted, it's not an everyday activity for most coders, but knowing how to do it programmatically is never a bad thing. Perhaps if the instructor had set up an Oracle or MySQL server for the students, it would have been better (that was my situation in my Database Design class last Spring). And for those who don't know, there is an Oracle JDBC driver available as well, so the bridge ain't necessary! ;)

[Bender]

Originally posted by Dru Lee Parsec
See what happens when I go away for a weekend? A long Java thread and I totally missed it :(

Sounds like you figured it out though. Although this sounds like one more assignment where the teacher doesn't know sh*t about what he's teaching. If he wanted to teach JDBC then he should simply have one program adding data to a table (or set of tables) and another program doing retrievals using Select from and joins in the SQL statement. By assigning this stupid "create the database" thing the students are stuck trying to figure out things that have nothing to do with jdbc. I think he knows what he's talking about, but he just has some problems expressing what he wants from the assignment.

The part that threw me was that the assignment specified that we could use any DBMS we wanted (Oracle, Access, SQL Server...), so I got the impression that the all the functions he wanted us to do in the assignment were possible with all the different DBMS's. Of course, when I started working on it, all I had on my machine was Access, so I started with that, made a connection, and then spent 2 days trying to figure out how to CREATE DATABASE -----; in it. Then, I just decided to try a different DBMS, downloaded MySQL and figured it out.

[Dru Lee Parsec]

I guess what I meant to say was that in the real world you use something like SQL+ or Toad or somthing to create your database. In every job I've ever had in this industry the Java code has only done select, insert, or updates on the tables.

So when I ranted "the teacher doesn't know sh*t about what he's teaching" my frustration was that the point of the assignment should have been for the students to learn how to connect to a database and access or update the data. The actual creation of the tables via Java (or any application regardless of the language) is a dangerous activity. Moreover, you end up being stumped by problems that have nothing to do with Java (as you found out) and that moves the focus away from the topic he's actually trying to teach.

Oddly enough, I've spent the past week writing code that does exacly what we're talking about. I'm poking data into a database with one set of EJBs and I'm doing various select statements via different EJBs.

[stuka]

Yeah, I know what you mean. I started writing without thoroughly reading (imagine THAT ;)). I do agree with the point you were making - creating a DB programmatically is not a good way to learn Java. If you know Java, it would be a good way to explore databases, but not vice-versa I'd think. As for creating tables programmatically, I disagree - after all, that's really what SQL+ or Toad (or DBTools, or....) are doing anyway - putting a UI on the DBMS' native API. Java and the JDBC drivers are just another way of doing this.