Optional Feature Not Implemented -ODBC Error

[Brett Swift]

I am trying to do an insert into an Access2000 database, and my SQL statement looks like this:
INSERT INTO tblCars (Make,Name,Color,Price)
VALUES ("VW","Golf","White",3000)
However, I need to figure out how to get those double quotes into the String statement. I know with VB you can use chr(39) and that will translate into the ' " ' but can you do that in Java???
Thanks!
[ August 02, 2002: Message edited by: Brett Swift ]

[Brett Swift]

Well Brett,
I found that you can use the '\' and it will tell java that the next character is to be included into the String.

[Jim Yingst]

You may also find it useful to use a PreparedStatement instead - the setString() method allows you to specify an input string directly without having to muck around with escape characters.

[Brett Swift]

Hey, thanks... I tried the PreparedStatement and i get the error "optional feature not implemented". I am getting the feeling from other readings that the ODBC-JDBC bridge is not something is good to use... I am only going to be deploying this on one computer... anyways...thats another question altogether... Why am I getting this error??
Thanks for your help!!1

[Stu Glassman]

Just another note: shouldn't you use single quotes in an SQL string?

[Jamie Robertson]

Originally posted by Stu Glassman:
Just another note: shouldn't you use single quotes in an SQL string?

I agree on this too. All those backslashes take away from the ease of reading a query. Single quotes are the standard anyways.
about the jdbcdbc not implemented problem:
the bridge works just fine. The problem comes from the ODBC driver not implementing the desired functionality. You can upgrade your MSAccess driver ( download from Microsoft ), and you should have most of the jdbc 2.0 functionality. The problem with jdbc:odbc bridge usually comes when you are trying to implement the same code on various machines, with different OS's, varying ODBC drivers, and configurating each ODBC datasource correctly.
Jamie

[Brett Swift]

I'm using Access2000.
Double quotes is what it needs.
I instinctively used single quotes at first but it was rejected.
I know access sucks! But this is just for one PC that I'm going to install it to.

[Brett Swift]

Well I upgraded the MDAC installs:
the 2.6 SDK update, and the refresh install 2.7(mdac_type.exe).
Still get the same error:
"Optional feature not implemented"
Did i install the wrong updates? I'm not sure where to find the updates if the ones I installed werent it... I found them at
http://www.microsoft.com/data/download.htm
I'm on win2k by the way.
This is a headache... thanks for your help!!!

[Jamie Robertson]

Originally posted by Brett Swift:
Well I upgraded the MDAC installs:
the 2.6 SDK update, and the refresh install 2.7(mdac_type.exe).
Still get the same error:
"Optional feature not implemented"
Did i install the wrong updates? I'm not sure where to find the updates if the ones I installed werent it... I found them at
http://www.microsoft.com/data/download.htm
I'm on win2k by the way.
This is a headache... thanks for your help!!!

I don't know where to tell you to download it, but it's out there! My MSAccess driver supports preparedStatements, single quotes, scrollable resultsets. This is what I know about the driver ( from the control panel -> odbc datasources ):
Name: Microsoft Access Driver [*.mdb]
Version: 4.00.4403.02
File: ODBCJT32.dll
Date: 8/8/99
I believe that it is the Microsoft Jet 4.0 driver. I'll try to track down some resources and/or links
Jamie

[Brett Swift]

Well it must be something else, because this is what I have as my ODBC Driver
Microsoft Access Driver (*.mdb)
4.00.6019.00
ODBCJT32.DLL
5/4/2001
This is the Code I am using:

 try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();

java.sql.Connection con = null;
java.sql.Statement stmt = null;
java.sql.ResultSet rs = null;
con = java.sql.DriverManager.getConnection("jdbc :-o	 dbc:Car","","");
//stmt = con.createStatement();
//int result = stmt.executeUpdate(str);
//preparedStatement to fix too few parameters error.
//although this gives "optional feature not implemented" error.
java.sql.PreparedStatement pstmt = con.prepareStatement("INSERT INTO tblCars(Make,Name,Color,Price) VALUES (?,?,?,?)");
pstmt.setString(1,carX.getMake());
pstmt.setString(2,carX.getName());
pstmt.setString(3,carX.getColor());
pstmt.setFloat(4,carX.getPrice());

pstmt.executeUpdate();


addElement("added");
}
catch(Exception ivjExc)
{
com.sun.java.swing.JFrame jfMessage = new com.sun.java.swing.JFrame();
com.sun.java.swing.JOptionPane.showMessageDialog(jfMessage, ivjExc.getMessage());
} 

[Jamie Robertson]

This is the code I use ( note, the connection does not use a DSN, but is dynamic )

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//connection is for Microsoft Access 97/2000 JET driver 4.0 and up
//to check your driver version:
//click Start->
//Settings->
//Control Panel->
//ODBC Data Sources (32bit)->
//Drivers tab->
//Should display Microsoft Access Driver(*.mdb) - version x.xx.xxxx.xx
//for this connection, the database named 'emp' should be located at: C:\temp\data\emp.mdb
Connection sconn = DriverManager.getConnection("jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=C:\\temp\\data\\emp2.mdb","Admin","");
String sql = "select distinct hiredate from emp where emp_fname =  ? ";
PreparedStatement ps = sconn.prepareStatement( sql );
ps.setString( 1, "Jimmy" );
ResultSet rs = ps.executeQuery();
while (rs.next())
{
System.out.println( rs.getString( "hiredate" ) );
}

Other than that, I don't know what to say. Your code looks fine. Double check to make sure your DSN is pointing to the most current version of your driver.
FYI, "too few parameters error" is usually caused by a mispelled column or table name in your sql string ( just saw this error in your commented code ).
Jamie
[ August 02, 2002: Message edited by: Jamie Robertson ]

[Brett Swift]

Jamie,
I emailed you asking to send some code there, becuase I think it was being mixed up through this forum. But I am now getting a "Data Source Name Too Long" error......
So confused!!

[Jamie Robertson]

edit -->[ you must have cut and pasted it before I removed the smilies. And yes they do mess up spacing, but it is fixed now ]
I know that it is space sensitive. You can not delete/add any more white space characters. Cut and paste it and only change the database name, user name and password.
This is the connection code I use via a DSN:

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:emp";
//DSNNAME is the datasource name you created in the control panel
Connection sconn = DriverManager.getConnection( url, "userID", "passwd");

If that does not work, create a new DSN from scratch. Make sure that the drivers point to the updated drivers. Change your code to point to the new DSN. If not, you may be stuck using statements, escaped double quotes, and non-scrollable resultsets . But don't worry, in 2 years of JDBC programming, I've needed/wanted to use scrollable resultsets anyways! There's nothing you can't accomplish using straight SQL and statements. I'm one of those Notepad coders who believes "It's the hard way or the highway!"
Jamie
[ August 02, 2002: Message edited by: Jamie Robertson ]

[Jamie Robertson]

Jamie,
I emailed you asking to send some code there, becuase I think it was being mixed up through this forum. But I am now getting a "Data Source Name Too Long" error......
So confused!!

try cutting and pasting from the code again ( before doing what I said above ). you must have copied it before I removed those annoying smilies!
Jamie

[Brett Swift]

Thanks... still not working though
Well in response to your remark about the too few parameters error.... I was told to use PreparedStatements to get rid of that, which threw me onto the "Option not implemented" or whatever that error was, and then when i tried connecting dynamically I get the "DSN too long" error..
Maybe my laptop would make a good frisbee?

[Jamie Robertson]

Originally posted by Brett Swift:
Well in response to your remark about the too few parameters error.... I was told to use PreparedStatements to get rid of that...

Like I said earlier, the reason you get the error ( as uninformative as the error message is ) means that you have mispelled a column or table name in your sql string. I have not seen any other problems produce that error message. Search the forum to see for yourself. ( I have also experienced this error ) Try to at least get the statements working, so we can progress from there ( plus, give us a sense of accomplishment! )
good luck,
Jamie

[Brett Swift]

Thats the thing though, I am using VisualAge, and i stepped through the code, let it compile the SQL statement, copied it with the input from textboxes etc...and pasted it right into a Query inside MSAccess.... the SQL inserted everything perfectly. I am trying this on a tiny database..only 4 colums in one table.. I know the SQL is working.... thats why I posted....because I did extensive searching throughout this site.
I added you to msn, not sure if you are on it... I don't think other people reading this are getting much out of it after what we've said so far, maybe if you had a few minutes I could step through this with you on msn? If its too big of a problem, no worries.... I understand.
Everything you've been saying makes total sense, but the error messages I'm getting don't! And I've cross-checked my code excessively! The dynamic DSN line of code is IDENTICAL!!! yet I still get the error message.
MSN would be great if you can.. don't worry...I won't harrass you lol

[Jamie Robertson]

Originally posted by Brett Swift:
I added you to msn, not sure if you are on it...

we've got firewalls coming out the wazoo! I'm lucky if a website loads for me!! So no msn sessions for me. That's the frustrating thing about using the bridge. Environmental differences can render previously working code to garbage. A different driver version, OS version can make even the best code crap out. Do you have any flexibility over the database vendor you use? can you switch to mySQL or some other database that has strong java support such as a type 4 driver?

[Brett Swift]

well I have a trial version of DB2,
but then it comes down to deployment... lol
I might try mySQL.
this is so frustrating!
thanks for all your help