Win a copy of Five Lines of Code this week in the OO, Patterns, UML and Refactoring forum!
  • 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

Cannot Identify Syntax Error in SQL

 
Ranch Hand
Posts: 79
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am getting a syntax error in my SQL and, for the life of me, I cannot identify the issue. Does anybody see something I am overlooking?



Thanks for any help you can offer on this!
 
Master Rancher
Posts: 3862
35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Do a search by commenting out most of the lines.  Then add them back one at a time.

getting a syntax error  


Please copy the full text of the error message and paste it here. It has important info about the error.
 
Adam Wentz
Ranch Hand
Posts: 79
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Norm Radder wrote:Do a search by commenting out most of the lines.  Then add them back one at a time.

getting a syntax error  


Please copy the full text of the error message and paste it here. It has important info about the error.



There's actually not much more in the whole of the error message than what I've already shared but if you insist on seeing it...


E/AndroidRuntime: FATAL EXCEPTION: main
                 Process: j5tech.co.offthehook, PID: 5858
                 java.lang.RuntimeException: Unable to start activity ComponentInfo{j5tech.co.offthehook/j5tech.co.offthehook.AddNewActivity}: android.database.sqlite.SQLiteException: near "1": syntax error (code 1): , while compiling: CREATE TABLE schedules(_id INTEGER PRIMARY KEY AUTOINCREMENT, 1 INT NOT NULL, scheduleName TEXT NOT NULL, scheduleItems TEXT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, scheduleTimestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP)
                     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2416)
                     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2476)
                     at android.app.ActivityThread.-wrap11(ActivityThread.java)
                     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1344)
                     at android.os.Handler.dispatchMessage(Handler.java:102)
                     at android.os.Looper.loop(Looper.java:148)
                     at android.app.ActivityThread.main(ActivityThread.java:5417)
                     at java.lang.reflect.Method.invoke(Native Method)
                     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:726)
                     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:616)
                  Caused by: android.database.sqlite.SQLiteException: near "1": syntax error (code 1): , while compiling: CREATE TABLE schedules(_id INTEGER PRIMARY KEY AUTOINCREMENT, 1 INT NOT NULL, scheduleName TEXT NOT NULL, scheduleItems TEXT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, scheduleTimestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP)
                     at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
                     at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:887)
                     at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:498)
                     at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
                     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
                     at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
                     at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1674)
                     at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1605)
                     at j5tech.co.testapp.DBHelper.onCreate(DBHelper.java:59)
                     at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:251)
                     at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:163)
                     at j5tech.co.testapp.AddNewActivity.onCreate(AddNewActivity.java:90)
                     at android.app.Activity.performCreate(Activity.java:6237)
                     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1107)
                     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2369)

 
Saloon Keeper
Posts: 22260
151
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Norm Radder wrote:Do a search by commenting out most of the lines.  Then add them back one at a time.

getting a syntax error  


Please copy the full text of the error message and paste it here. It has important info about the error.



If it's like Oracle, the full text of the error message will be something like "SYNTAX ERROR IN SQL STATEMENT". Which is loads of help when you're pulling 100 columns in a single select.  

When I first saw that code, my initial thought was "that's a horrible way to do things". Then I saw it was originally posted in the Android forum and recalled having had to do something very like that myself. I no longer remember why, though.

Anyway, I'm not sure about the terminal semicolon on those statements. The semicolon is often the "go" indicator for interactive database utilities, but I'm thinking it's not technically part of the SQL.
 
Adam Wentz
Ranch Hand
Posts: 79
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Holloway wrote:

Norm Radder wrote:Do a search by commenting out most of the lines.  Then add them back one at a time.

getting a syntax error  


Please copy the full text of the error message and paste it here. It has important info about the error.



If it's like Oracle, the full text of the error message will be something like "SYNTAX ERROR IN SQL STATEMENT". Which is loads of help when you're pulling 100 columns in a single select.  

When I first saw that code, my initial thought was "that's a horrible way to do things". Then I saw it was originally posted in the Android forum and recalled having had to do something very like that myself. I no longer remember why, though.

Anyway, I'm not sure about the terminal semicolon on those statements. The semicolon is often the "go" indicator for interactive database utilities, but I'm thinking it's not technically part of the SQL.



Tim, thanks for the suggestion! I did try to remove the internal semicolon, out of curiosity, but the same error persists.
 
Rancher
Posts: 1197
22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Adam Wentz wrote:I am getting a syntax error in my SQL and, for the life of me, I cannot identify the issue. Does anybody see something I am overlooking?



Thanks for any help you can offer on this!



A few things:
1. Ugh... executing SQL that was made by a bunch of string concatenations?  Depending on where the variable values are coming from, you may be opening yourself up to a SQL Injection attack.  
2. Try printing the command strings to the console and executing those in the DB IDE (e.g. SQL Server Management Studio).  Do the SQL statements work there?
3. Try a very small, simple command, like "select 'Adam'" or "Create table #Adam (ID int)", from your Java program.  Does that work?
4. If possible try putting your SQL operations in a stored procedure and then just call the stored proc from your Java program.

 
Adam Wentz
Ranch Hand
Posts: 79
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Ryan McGuire wrote:

Adam Wentz wrote:I am getting a syntax error in my SQL and, for the life of me, I cannot identify the issue. Does anybody see something I am overlooking?



Thanks for any help you can offer on this!



A few things:
1. Ugh... executing SQL that was made by a bunch of string concatenations?  Depending on where the variable values are coming from, you may be opening yourself up to a SQL Injection attack.  
2. Try printing the command strings to the console and executing those in the DB IDE (e.g. SQL Server Management Studio).  Do the SQL statements work there?
3. Try a very small, simple command, like "select 'Adam'" or "Create table #Adam (ID int)", from your Java program.  Does that work?
4. If possible try putting your SQL operations in a stored procedure and then just call the stored proc from your Java program.



I am using SQLite in Android. Fields are first defined in a contract class, and then a db helper class is created that contains the SQL used to create the db, tables, and relevant fields.
 
Tim Holloway
Saloon Keeper
Posts: 22260
151
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Adam Wentz wrote:
I am using SQLite in Android. Fields are first defined in a contract class, and then a db helper class is created that contains the SQL used to create the db, tables, and relevant fields.



For those who are wondering:

I think it's related to how Android defines application resources. As I said, it has been a while so I don't remember why, but this horrible mess is actually accepted usage on that platform. And, alas, I don't believe that SQLite supports stored procedures either. One of the ways they kept it "lite".
 
Norm Radder
Master Rancher
Posts: 3862
35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you print the content of the Strings out and paste it here so the contents of the variables will be shown and the paring of delimiters checked.
 
Adam Wentz
Ranch Hand
Posts: 79
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Norm Radder wrote:Can you print the content of the Strings out and paste it here so the contents of the variables will be shown and the paring of delimiters checked.



Those are contained in the contract class, here:

 
Norm Radder
Master Rancher
Posts: 3862
35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you print the contents of the Strings:  CREATE_SCHEDULES_TABLE and CREATE_CURRENT_STATES_TABLE?
So we can see the full value of the Strings at execution.
 
Adam Wentz
Ranch Hand
Posts: 79
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Norm Radder wrote:Can you print the contents of the Strings:  CREATE_SCHEDULES_TABLE and CREATE_CURRENT_STATES_TABLE?
So we can see the full value of the Strings at execution.



I shared those in my OP. Are you suggesting that you want to see the containing method in its entirety?
 
Norm Radder
Master Rancher
Posts: 3862
35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

I shared those in my OP.


Sorry, I only see source code there.  What I'm looking for is the contents of the Strings after those statements are executed.

Add something like:
and execute it.
 
Adam Wentz
Ranch Hand
Posts: 79
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Norm Radder wrote:

I shared those in my OP.


Sorry, I only see source code there.  What I'm looking for is the contents of the Strings after those statements are executed.

Add something like:
and execute it.



Ah, I understand. I tried to do as you suggested, just for kicks, but it doesn't print anything because the termination happens as a result of the first string being executed.

After each of the strings there is a respective line that looks like this (the other String has one executing CREATE_CURRENT_STATES_TABLE):



That is what executes the SQL commands contained in the strings, and it is at that point that the program is terminating.
 
Tim Holloway
Saloon Keeper
Posts: 22260
151
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Norm Radder wrote:

I shared those in my OP.


Sorry, I only see source code there.  What I'm looking for is the contents of the Strings after those statements are executed.

Add something like:
and execute it.



Actually, System.out/err don't exist on many platforms, and Android is one of them. So, technically, are J2EE webapp servers, but actual implementations usually have a loophole, though it's not a good idea to exploit that. It's why we encourage using loggers, instead.

In Android, it's even worse, since Android "java" is not running on a JVM, it's running under Dalvik.

However, assuming one is developing on a desktop, you should be able to get away with this:



Put this in the class that defines the create table strings, compile it on the desktop using JDK javac, then run it using the desktop Java VM (again, JDK). That should cause the resulting SQL to print in the console window.

On a windows system this may look ugly, since Windows requires both carriage return AND linefeed to cleanly terminate a line. On MacOS/Linux, that's not a problem.

Also on Linux, SQLite3 is pre-installed, so you can test the generated SQL in its console. Making the hardest part doing the compile-and-run as a manual command line process. Because when you have code in packages, the source and class path options need attention.
 
Adam Wentz
Ranch Hand
Posts: 79
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Norm Radder wrote:

I shared those in my OP.


Sorry, I only see source code there.  What I'm looking for is the contents of the Strings after those statements are executed.

Add something like:
and execute it.



As you were... I broke up the statement for each table and applied it to respective to the first string. This is the output:



It breaks when it attempts the first, so there was no output for the second.
 
Norm Radder
Master Rancher
Posts: 3862
35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Change the order of the statements so you can print both of them before trying to use them.
 
Adam Wentz
Ranch Hand
Posts: 79
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Norm Radder wrote:Change the order of the statements so you can print both of them before trying to use them.




Here's the output:


I/System.out: Sched=CREATE TABLE schedules (_id INTEGER PRIMARY KEY AUTOINCREMENT, 0 INTEGER NOT NULL, scheduleName TEXT NOT NULL, schedulePkgs TEXT NOT NULL, 0 INTEGER NOT NULL, 0 INTEGER NOT NULL, 0 INTEGER NOT NULL, 0 INTEGER NOT NULL, 0 INTEGER NOT NULL, 0 INTEGER NOT NULL, 0 INTEGER NOT NULL, 0 INTEGER NOT NULL, 0 INTEGER NOT NULL, 0 INTEGER NOT NULL, 0 INTEGER NOT NULL, 0 INTEGER NOT NULL, 0 INTEGER NOT NULL, 0 INTEGER NOT NULL, scheduleTimestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

I/System.out: Sched=CREATE TABLE currentStates (_id INTEGER PRIMARY KEY AUTOINCREMENT, 0 INTEGER,  FOREIGN KEY (0) REFERENCES schedules(_id), 0 INTEGER NOT NULL, 0 INTEGER NOT NULL, 0 INTEGER NOT NULL, csPkgsEnabled TEXT NOT NULL, csPkgsDisabled TEXT NOT NULLcsTimestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

 
Adam Wentz
Ranch Hand
Posts: 79
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
DISREGARD MY PREVIOUS POST! THERE WAS A CHANGE I NEEDED TO IMPLEMENT BEFORE RUNNING.


Use this output instead:


I/System.out: Sched=CREATE TABLE schedules (_id INTEGER PRIMARY KEY AUTOINCREMENT, 0 INTEGER NOT NULL, scheduleName TEXT NOT NULL, scheduleStuff TEXT NOT NULL, 0 INTEGER NOT NULL, 0 INTEGER NOT NULL, 0 INTEGER NOT NULL, 0 INTEGER NOT NULL, 0 INTEGER NOT NULL, 0 INTEGER NOT NULL, 0 INTEGER NOT NULL, 0 INTEGER NOT NULL, 0 INTEGER NOT NULL, 0 INTEGER NOT NULL, 0 INTEGER NOT NULL, 0 INTEGER NOT NULL, 0 INTEGER NOT NULL, 0 INTEGER NOT NULL, scheduleTimestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
I/System.out: Sched=CREATE TABLE currentStates (_id INTEGER PRIMARY KEY AUTOINCREMENT, 0 INTEGER,  FOREIGN KEY (0) REFERENCES schedules(_id), 0 INTEGER NOT NULL, 0 INTEGER NOT NULL, 0 INTEGER NOT NULL, csEnabled TEXT NOT NULL, csDisabled TEXT NOT NULLcsTimestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

 
Norm Radder
Master Rancher
Posts: 3862
35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ok, now we need some one that knows SQL to look at those Strings to visually check them.

It breaks when it attempts the first,


That would point to the first one.
 
Adam Wentz
Ranch Hand
Posts: 79
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Norm Radder wrote:Ok, now we need some one that knows SQL to look at those Strings to visually check them.

It breaks when it attempts the first,


That would point to the first one.




Okay, so I managed to debug this one on my own. There were two issues:

1. In the constructor class, only the String type should be used. This is what the SqLiteDatabase class in Android uses to name the fields in the database. I noticed that the output showed a bunch of fields named "0" (which is an int). I have been thinking about that all day. Then it dawned on me. In the contract class, the left side of the equal sign represents the name of the Constant, whereas the right side of the equal sign is the value to be used as a field name in the database. So, I changed all of my int's in the contract to Strings with respective names, and voila. Even though many of my fields need to be int's, that is defined in the SQL commands printed in the DBHelper class. ONLY DEFINE STRINGS (for field names) in the contract.

2. I was missing a comma in the second string CREATE_CURRENT_STATES_TABLE.

Problem solved. Onward and upward!
 
Norm Radder
Master Rancher
Posts: 3862
35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Glad you figured it out and thanks for sharing.
 
Rancher
Posts: 4614
47
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Just so you know for the future the exception actually told you what was wrong and where:

Caused by: android.database.sqlite.SQLiteException: near "1":

That first bit says it encountered the value '1'  when it wasn't expecting it.
Continuing:

syntax error (code 1): , while compiling: CREATE TABLE schedules(_id INTEGER PRIMARY KEY AUTOINCREMENT, 1 INT NOT NULL, scheduleName TEXT NOT NULL, scheduleItems TEXT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, 0 INT NOT NULL, scheduleTimestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP)

That bit in bold was the issue.
Indeed, there were several of the same thing in there.
 
The harder you work, the luckier you get. This tiny ad brings luck - just not good luck or bad luck.
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic