JDBC 4

[rvy2k]

Hi everybody,

I am creating an application that will update a database (text file). The database file will be located on a server and has to be accessible by everybody accross the network.

I am a beginner, and my solution was to update the file when the user fills the form. The program would then read the fields of the form, save it to a link list and write it to a file. I am pretty sure this not the right way to do it so I am asking for better solutions =). I havent figured out a way for the other users of the network to be notified when the text file is updated.

I heard of JDBC and I dont really know what it is but would that be the best solution for my program? If no, then how should I proceed.

Thank you in advance for your input.

 

[meadandale]

hi rvy2k,

JDBC is java datbase connectivity API. It is used to provide portable database connnectivity and programming similar to ODBC.

If you are using a plain text file then you do not need to use JDBC as JDBC is a way to talk to various DBMS's using Java. If you wanted to store the user data in an access database or an Oracle, MySQL, SQL server, or other database then you could and should use JDBC.

Hope this helps.

Regards,

Charles
meadandale@yahoo.com

 

[Vepo]

Hi,

If your have to handle large data amounts, some database sollution might be better, but you can do it by using plain text files also.
If you use text files you can use lastModified() -method from File-class to check if file is changed.

 

[rvy2k]

Thanks a lot for the replies guys.

I dont really know what I should use between a database and a text file. I would love to receive your advice on that; providing that the database (or plain text file) will receive about 40 inputs a day which option should I use?

Also, I had some worries about the following if i were to use text files:
the program creates file for each day; i wrote the program so that every time the application is launched, it would go thru all the data and gather the appropriate data by reading every files; then it saves it into a link list off object containing the data. would that be a big issue over a year (ie 365 files) or longer?

I read more about JDBC and I read about SQL and I had a question:
If SQL only makes query how do I update a database thru my application?
Also, to communicate accross the network, should I use RMI?

Thanks again!

 

[pipk]

A database solution would be much more sensible as you will automatically get data and referential integrity built into the whole thing - imagine the hassle of coding all that data and domain checking validation. If you are just using your text file as a repository and you don't care about the quality of it then a text file is fine. But if you are going to use it for retrieval etc it will involve a lot of data parsing etc to retrieve the right record etc.

JDBC is fairly straightforward, not half as complex as you would think. Setting up a connection is easy, and as long as you cater for exceptions well enough it is fairly robust.

 

[pipk]

Actuall, to further add to the point - "QUERY" is such a misleading word these days - its initial meaning has been blurred and it is now applied generically to any statement containing SQL.

So a query could be a select, or an insert, or an update, or even a data definition statement such as CREATE TABLE etc - only dbase purists will argue otherwise (and probably quite rightly as it can confuce sometimes).

So in answer to another one of your questions, JDBC can handle selects, updates inserts etc.

As for communication across a network, have a look at the "java.net.*" package, which allows communication through sockets and much more besides.

 

[rvy2k]

Thanks.

I read more about JDBC, SQL and how it interacts with Microsoft Access (because thats the database I would like to use).
I had the Java SDK 1.3 installed and therefore the JDBC-ODBC bridge (correct?). The MS Access driver was already installed on my computer (because I already have Access installed on it). I added to my User DSN in the ODBC data source administrator (under windows 2000) the database I wanted to use and setup a guest account and password.

I have some weird problems with it though:
I wrote a query to create a table and I had the following error:
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in CREATE TABLE statement.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6031)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:6188)
at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:2494)
at sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:314)
at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(JdbcOdbcStatement.java:
264)
at db.makeTable(db.java:15) // this is on my stmt.excuteUpdate("CREATE TABLE DB" +
at test.main(test.java:4)

I manually created a table with 5 values and wrote a query to insert data in my Access database. No error but the table in the database wasnot updated.
Then I change the ID of the insert query to be 5 and I had a run-time error because it was trying to overwrite the 5th element in my table (which means that my java program was communicating with the database).
I changed the ID back to 6 and used the trace option in the MS ODBC data source administrator. I looked at the log file and I saw that my query was in the log but it was still not updating the mdb file....

I would greatly appreciate any help.
Thanks!

 

[rvy2k]

Thanks pipk, I think I was confused byt the word query. About java.net.*, how would I notify the users that another user has updated the database? Is there an SQL command?
I also heard about RMI, but I dont really know what it does.

Thanks again.

 

[pipk]

It is saying that the Create table statement has a syntax error. Don't know for definite if JDBC supports data definition statements - but I guess if you have read about it then it does.

As for the error, are you doing your statement as part of a try/catch block.
if so, 'catch' block print out the sql statement with the trace data, it should give you a better view on what the final sql statment looks like, and may give you a clue as to where it is in error. It could be something as simple as a missing comma, or not putting a space between certain keywords etc.

As for the failed insert, are you sure you are not 'catching' an SQLException?

 

[pipk]

As for the notification, I don't know Access very well (only well enough to create and manipulate tables) but I am sure it would allow the adding of 'triggers' to a table. A trigger is basically an event that is "triggered" when some sort of predefined activity occurs to the data in a table. You can set a trigger to respond to Inserts, deletes, updates etc. I know how to do it on an As400, but unfortunately my PC and access knowledge is appalling. You should post a note on the Microsoft Access forum, it is a busy forum and is bound to get a reply of some sorts.

 

[rvy2k]

Thanks again pipk.
I ll double check the syntax on the CREATE TABLE

For the insert, i commented out the line for the create table and put the insert query and they both have a catch.
I am pretty sure the Java program can communicate with the Access db because i tried to insert into a row that was already filled and it gave me an SQL error.

 

[wushutwist]

Access has some strange quirks regarding SQL code, definitely not standard. Don't even think about tiggers Access is not nearly that advanced of a RDBMS. Copy in the code that is giving you problems.
Wushutwist

 

[pipk]

No tiggers in Access?? oh, he was my favourite Winnie the Pooh character too!!!

 

[rvy2k]

Hey guys,

Thanks for your help.
I figured out what my problem was. It turned out that by closing my connection, everything worked fine.

wushutwist, what would i do to share a Ms Access db over a network?

 

[wushutwist]

Are you talking about file sharing or using your java app? Is it a client/server swing based app or a web app?

Yes, I also like tiggers much more than triggers.
Wushutwist

 

[rvy2k]

Hello wushutwist,

i am talking about sharing a database on a server accross the network so that multiple computer using a java application can update it.

Thanks!