File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Insert SQL syntax error Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Insert SQL syntax error" Watch "Insert SQL syntax error" New topic
Author

Insert SQL syntax error

Jacky Luk
Ranch Hand

Joined: Aug 02, 2012
Posts: 590

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

ID = AutoIncrement
Timestamp = Now();

Object is the entire record stringed together

Any help would be nicely appreciated!
Thanks
Jack
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

My guess is that some of your column names are reserved words in Access's SQL dialect. In which case you should escape them, like this:


Jacky Luk
Ranch Hand

Joined: Aug 02, 2012
Posts: 590
Paul Clapham wrote:My guess is that some of your column names are reserved words in Access's SQL dialect. In which case you should escape them, like this:




Hi Paul, no I am afraid it isn't it.
But the Object object contains the whole record

Here is an example, would that be problematic?


[Thumbnail for access.png]

Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

The ID column is of the Autonumber type. I believe you cannot redefine its value, therefore you must not include it in the INSERT statement (but I may be wrong). And you are not setting the first and fourth parameter anywhere, that could be a problem too.

Inserting a String (using PreparedStatement) is safe, assuming you don't exceed the target field size. MS Access has MEMO data type for long texts, however I'm not sure how that works with the JDBC/ODBC bridge.
Jacky Luk
Ranch Hand

Joined: Aug 02, 2012
Posts: 590
Martin Vajsar wrote:The ID column is of the Autonumber type. I believe you cannot redefine its value, therefore you must not include it in the INSERT statement (but I may be wrong). And you are not setting the first and fourth parameter anywhere, that could be a problem too.

Inserting a String (using PreparedStatement) is safe, assuming you don't exceed the target field size. MS Access has MEMO data type for long texts, however I'm not sure how that works with the JDBC/ODBC bridge.


Hi Martin,
After following your instructions, the syntax errors persist.
Here is how it looks
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Paul was right after all. "From" is a reserved word, you need to escape the column From as he has shown you (ie, [From]). The same might be true for To.

Much better would be to rename columns that match reserved words so that you wouldn't have to escape them.
Jacky Luk
Ranch Hand

Joined: Aug 02, 2012
Posts: 590
Martin Vajsar wrote:Paul was right after all. "From" is a reserved word, you need to escape the column From as he has shown you (ie, [From]). The same might be true for To.

Much better would be to rename columns that match reserved words so that you wouldn't have to escape them.


Thanks, I am facing another set of problems. Here, first) the timestamp is empty when it gets generated. I put =Now() and Now() at that field to no avail.
second) the newRec and oldRec contains an address of some sort. As you see, Object can be converted to string by toString(). But it is converted to an address?!

I am setting the timestamp by pst.setTimestamp(3, null);
Thanks
Jack
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Jacky Luk wrote:the timestamp is empty when it gets generated. I put =Now() and Now() at that field to no avail.

That is probably because you include the Timestamp column in the INSERT statement and set it to null (pst.setTimestamp(3, null)). Omit the Timestamp column altogether.

the newRec and oldRec contains an address of some sort. As you see, Object can be converted to string by toString(). But it is converted to an address?!

If I understand it correctly, you obtain a class name and some hex number by calling toString(). This is because you didn't override the default implementation of toString(). You need to override this method in all classes that will ever be passed into the LogDatabase method and put in the code that will create the string from their contents. This isn't actually a JDBC question, so if it is still not clear, please ask in the BJ forum.

By the way, method names should start with lowercase letters in Java.
Jacky Luk
Ranch Hand

Joined: Aug 02, 2012
Posts: 590
Martin Vajsar wrote:
Jacky Luk wrote:the timestamp is empty when it gets generated. I put =Now() and Now() at that field to no avail.

That is probably because you include the Timestamp column in the INSERT statement and set it to null (pst.setTimestamp(3, null)). Omit the Timestamp column altogether.

the newRec and oldRec contains an address of some sort. As you see, Object can be converted to string by toString(). But it is converted to an address?!

If I understand it correctly, you obtain a class name and some hex number by calling toString(). This is because you didn't override the default implementation of toString(). You need to override this method in all classes that will ever be passed into the LogDatabase method and put in the code that will create the string from their contents. This isn't actually a JDBC question, so if it is still not clear, please ask in the BJ forum.

By the way, method names should start with lowercase letters in Java.


Like this? String str = "insert into Log (Type, Tbl, TS, oldRec, newRec) Values (?, ?, ,?, ?)";
But I still got a syntax error for the SQL statement.

I think I should override the class which is derived from Object. But I am passing the base class to LogDatabase. What should I override?
Thanks
Jack
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Jacky Luk wrote:Like this? String str = "insert into Log (Type, Tbl, TS, oldRec, newRec) Values (?, ?, ,?, ?)";

No.

Pretend the column doesn't exist. It should then get its default value. When you include the column (and its value) in the insert statement, it will overwrite the default specified in the database. (In your previous code, you've included the column Timestamp in the column list and set it to null, this is why it ended as null.)

I think I should override the class which is derived from Object. But I am passing the base class to LogDatabase. What should I override?

You'd override the toString() method in the class you've created - the one which is derived from Object.

Actually, it would be even better if you created an interface (say, Loggable) with a method (say, toLogString()). In the logDatabase method, you'd declare the oldRec and newRec as Loggable, not Object. That way your code would be type safe - anything can be passed into parameter declared as Object (which can lead to errors easily), while only instances of classes implementing Loggable could be passed to a parameter declared as Loggable.
Jacky Luk
Ranch Hand

Joined: Aug 02, 2012
Posts: 590

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]Function sequence error

I am with this now.
Thanks
Jack
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Why did you switch to setCharacterStream? If I understand it correctly, it has worked for you when you were passing in the strings using setString.

(I don't have any experience with LOB support in MS Access over JDBC/ODBC. I'm afraid it would be fraught with troubles.)
Jacky Luk
Ranch Hand

Joined: Aug 02, 2012
Posts: 590
Martin Vajsar wrote:Why did you switch to setCharacterStream? If I understand it correctly, it has worked for you when you were passing in the strings using setString.

(I don't have any experience with LOB support in MS Access over JDBC/ODBC. I'm afraid it would be fraught with troubles.)


Every one implementing Loggable should have a polymorphic method named toLogString()
What kind of parameters should it contain in the interface

say
public interface Loggable
{
public void toLoggableString(...);
}

Thanks a lot for your continuous assistance.
Jack
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

You're welcome

You've quoted my last post, but your questions are not connected to it -- didn't you want to ask something?

Regarding toLoggableString() or toLogString() - I'd say the method shouldn't contain any parameters. It should just create a string that describes the object in as much detail as needed. You don't need any additional parameters, the instance already knows everything it needs to describe itself.
Jacky Luk
Ranch Hand

Joined: Aug 02, 2012
Posts: 590


Hello Martin,

oldRec and newRec are no longer necessary to be of Object type. As far as I concern, I need to log both hospital and patient CRUDs, so that should be something generic.
But as you say, Object can be error-prone. What should they be then?
Thanks
Jack
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

You should declare the oldSrc and newSrc as Loggable. Have the classes whose instances you want to log (hospital, patient, etc.) implement the Loggable interface. This brings in the type safety I mentioned earlier.

(These classes could extend a common parent, perhaps an abstract class which implements Loggable. This is not necessary, though, it just might help you create a meaningful hierarchy of classes. If you want to incrementally modify/refactor your existing code, just have the classes implement Loggable and perhaps add a common parent later.)
Jacky Luk
Ranch Hand

Joined: Aug 02, 2012
Posts: 590
Hi Martin, we are really close. This class results in
error: cannot inherit from final String
public class LoggableString extends String implements Loggable {

after compilation
Thanks

Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

String is a final class and cannot be extended. It really cannot be done.

However, I had thought you want to pass instances representing database records into the logDatabase method. You shouldn't ever use a plain String to represent a database record, therefore you don't need it to implement Loggable.
Jacky Luk
Ranch Hand

Joined: Aug 02, 2012
Posts: 590

Thanks, mar, get it working nicely
Jack
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Insert SQL syntax error
 
Similar Threads
want to pass uuid number from one jsp page to another jsp page
How to configure Bonecp in java project without breaking connection when more than connection
how can we use where clause with insert query using preparedStatement?
how to retrieve value from autogenerated(oracle sequence) column in a servlet.
SQL Exception:General Error