Differences found so far between MSAccess and MySQL (and Oracle)

Most of these talk about MSaccess and MySQL, but the real difference
is actually how portable in general do you want your code to be.
At least as far as this class goes, since many of you may be
testing your stuff on Windows boxes, you need to be aware of
portability issues below:
________________________________________________________________
 
To save others possibly days of debug time, here are two big differences  
I found between MSAccess and MySql (and oracle).  I found them because 
I first got my program working with MSAccess, only to find it wouldn't 
work on MySql.
 
1)  Don't end SQL statements with ";" in MySql or Oracle

***Other databases require this as well, it is up to the driver whether
 you need this or not.

2)  For adding tables and inserting values, "Statement.execute( )" may  
work in MSAccess, but "Statement.executeUpdate( )" will be needed in  
MySql.

***For creating tables and inserting values, executeUpdate should ALWAYS
  be used as per the JDBC API.  If you don't use this, you'll always run
  into portability problems.

  
3)  use "ResultSet.close()" after every query - I kept getting 
"OutOfMemory" exceptions with MySql, until I put one of these close
calls after every query.

In general:  Two of us, here at HP Boise, have been able to connect with  
the "/test" database, add tables, and access them.  Don't give up!
 


 

CASE_SENSITIVITY MATTERS FOR PORTABLE JAVA!!!!!!!!!!!!!!!!!!!!!!!!!!!
CASE_SENSITIVITY MATTERS FOR PORTABLE JAVA!!!!!!!!!!!!!!!!!!!!!!!!!!!
CASE_SENSITIVITY MATTERS FOR PORTABLE JAVA!!!!!!!!!!!!!!!!!!!!!!!!!!!

The question:
< I got this error message when I was testing the QueryDB applet on   
< MySQL.
< Error exjava.sql.SQLException: mysql does not support prepared , 
< statements.
 
< Does this mean we cannot use the PreparedStatement on the MySQL on 
< your site?
 
Apparently so.  again heavy sigh:
Brian did the research:
 
The method for a prepared statement just throws a SQL exception with
the message that MySQL doesn't support this.  After reading some docs
for MySQL, it looks like they don't support the idea of a "cursor".  A
cursor in database terms is sortof like a queue that is set up for
data that is to be retrieved a row at a time.  So, you can do a select
into a cursor, then you can say "get the next row" in a loop and
process each row one at a time.  MySQL doesn't even support a select
into a table (i.e. "SELECT foo, bar INTO store FROM foobar").
 
Posgresql supports these features.  I'll have to see about getting
that one up somewhere as well, but, not any time soon.  For next
semester

___________________________________

but then:
 
In my lab 3 submission, I do a "SELECT *". then loop on <result set>.next()
and its works just fine.  So there must be some kind of support for a
cursor.
      "while ( rs.next() ) {
           lname = rs.getString(1);
           fname = rs.getString(2);
           ... "
      If I understand your note, then this should not work... but it does.
:-)
 
I think this was from George Greene?
-------------------------------------

 
After I discovered the prepared statement problem with MySQL last Friday, I 
have done some more experiments with MySQL and Microsoft SQL Server.
 
I can run almost everything on MS SQL Server except some weird "syntax error" 
stuffs, for example, MS SQL does not like DOUBLE. I have to replace DOUBLE with 
FLOAT, then it stops complaining.
 
MySQL can execute simple SQL statement but not the PreparedStatement. It works 
for ResultSetMetaData but not for  DatabaseMetaData. With those limitations, I 
think I have to trim my JDBC lab to a simpler version when it is run on MySQL.
 
Just to share my experience here. Hope it helps.
 
Ron


CASE_SENSITIVITY MATTERS FOR PORTABLE JAVA!!!!!!!!!!!!!!!!!!!!!!!!!!!
CASE_SENSITIVITY MATTERS FOR PORTABLE JAVA!!!!!!!!!!!!!!!!!!!!!!!!!!!
CASE_SENSITIVITY MATTERS FOR PORTABLE JAVA!!!!!!!!!!!!!!!!!!!!!!!!!!!