• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Insert SQL syntax error

 
Ranch Hand
Posts: 634
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
 
Marshal
Posts: 28175
95
Eclipse IDE Firefox Browser MySQL Database
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 634
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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?
access.png
[Thumbnail for access.png]
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 634
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 634
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 634
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 634
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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

I am with this now.
Thanks
Jack
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 634
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 634
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 634
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 634
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Thanks, mar, get it working nicely
Jack
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic