Thread: SQLSyntaxErrorException When Following JDBC Tutorial

I am following the tutorial here: Getting Started (The Java™ Tutorials > JDBC(TM) Database Access > JDBC Basics)

I can get to the very bottom of that page, where I use ant to compile the examples, then run setup to create the tables, but when I try to run any of the examples that deal with that table data, I get a SQLSyntaxErrorException saying that 'SHOW_SUPPLIERS' does not exist.

I'm not sure whether the setup command is working correctly- it doesn't error out, but it also says "0 of 0 SQL statements executed successfully" which seems strange. I suspect something is going wrong on this step, but I'm not sure what.

I've been googling all evening trying to figure out what I'm doing wrong, but I can't see anything. I'm sure it's something stupid, but I'm an absolute novice with database stuff.

Here's the full log of me following the command line instructions from the tutorial, with resulting error messages:

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
 
C:\Documents and Settings\kworkman>cd Desktop
 
C:\Documents and Settings\kworkman\Desktop>cd "JDBC Tutorials"
 
C:\Documents and Settings\kworkman\Desktop\JDBC Tutorials>cd JDBCTutorial
 
C:\Documents and Settings\kworkman\Desktop\JDBC Tutorials\JDBCTutorial>ant jar
Buildfile: C:\Documents and Settings\kworkman\Desktop\JDBC Tutorials\JDBCTutorial\build.xml
 
compile:
     [echo] JDBC Tutorial is using C:/Program Files/Java/jdk1.6.0_29/bin/javac as Java compiler
    [javac] C:\Documents and Settings\kworkman\Desktop\JDBC Tutorials\JDBCTutorial\build.xml:59: warning: 'includeantruntime' was not set, defaulting to build.sysclasspath=last; set to false for repeatable builds
 
jar:
 
BUILD SUCCESSFUL
Total time: 0 seconds
 
C:\Documents and Settings\kworkman\Desktop\JDBC Tutorials\JDBCTutorial>ant setup
 
Buildfile: C:\Documents and Settings\kworkman\Desktop\JDBC Tutorials\JDBCTutorial\build.xml
 
setup:
 
drop-tables:
      [sql] Executing resource: C:\Documents and Settings\kworkman\Desktop\JDBCTutorials\JDBCTutorial\sql\derby\drop-tables.sql
      [sql] 0 of 0 SQL statements executed successfully
 
build-tables:
      [sql] Executing resource: C:\Documents and Settings\kworkman\Desktop\JDBCTutorials\JDBCTutorial\sql\derby\create-tables.sql
      [sql] 0 of 0 SQL statements executed successfully
 
populate-tables:
      [sql] Executing resource: C:\Documents and Settings\kworkman\Desktop\JDBCTutorials\JDBCTutorial\sql\derby\populate-tables.sql
      [sql] 0 of 0 SQL statements executed successfully
 
BUILD SUCCESSFUL
Total time: 0 seconds
 
C:\Documents and Settings\kworkman\Desktop\JDBC Tutorials\JDBCTutorial>ant javadb-create-procedure
Buildfile: C:\Documents and Settings\kworkman\Desktop\JDBC Tutorials\JDBCTutorial\build.xml
 
javadb-create-procedure:
      [sql] Executing commands
      [sql] Failed to execute:  drop procedure SHOW_SUPPLIERS
 
BUILD FAILED
C:\Documents and Settings\kworkman\Desktop\JDBC Tutorials\JDBCTutorial\build.xml:108: java.sql.SQLSyntaxErrorException: 'DROP PROCEDURE' cannot be performed on'SHOW_SUPPLIERS' because it does not exist.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
        at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
        at org.apache.tools.ant.taskdefs.SQLExec.execSQL(SQLExec.java:775)
        at org.apache.tools.ant.taskdefs.SQLExec.runStatements(SQLExec.java:745)
 
        at org.apache.tools.ant.taskdefs.SQLExec$Transaction.runTransaction(SQLExec.java:1043)
        at org.apache.tools.ant.taskdefs.SQLExec$Transaction.access$000(SQLExec.java:985)
        at org.apache.tools.ant.taskdefs.SQLExec.execute(SQLExec.java:653)
        at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:291)
        at sun.reflect.GeneratedMethodAccessor4.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        at java.lang.reflect.Method.invoke(Unknown Source)
        at org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:106)
        at org.apache.tools.ant.Task.perform(Task.java:348)
        at org.apache.tools.ant.Target.execute(Target.java:390)
        at org.apache.tools.ant.Target.performTasks(Target.java:411)
        at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1399)
        at org.apache.tools.ant.Project.executeTarget(Project.java:1368)
        at org.apache.tools.ant.helper.DefaultExecutor.executeTargets(DefaultExecutor.java:41)
        at org.apache.tools.ant.Project.executeTargets(Project.java:1251)
        at org.apache.tools.ant.Main.runBuild(Main.java:809)
        at org.apache.tools.ant.Main.startAnt(Main.java:217)
        at org.apache.tools.ant.launch.Launcher.run(Launcher.java:280)
        at org.apache.tools.ant.launch.Launcher.main(Launcher.java:109)
Caused by: java.sql.SQLException: 'DROP PROCEDURE' cannot be performed on 'SHOW_SUPPLIERS' because it does not exist.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
        ... 29 more
Caused by: ERROR 42Y55: 'DROP PROCEDURE' cannot be performed on 'SHOW_SUPPLIERS' because it does not exist.
        at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
        at org.apache.derby.impl.sql.compile.DropAliasNode.bindStatement(Unknown Source)
        at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
 
        at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
        at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepa
reInternalStatement(Unknown Source)
        ... 23 more
 
Total time: 0 seconds

Never been through this tutorial myself, but just took a quick look. Don't have time right now to test, but try the following: in the build.xml file, within the code:

<target name="javadb-create-procedure">
//yadayadayada
</target>

Change all of the onerror="abort" to onerror="continue" and see if this helps. Reasoning (again on quick review): the ant task is trying to drop the procedures, and if they don't exist it exits with an error - but they can't exist unless they have been created in the first place. Note that with this change, if the build is run again and the procedures are created but an error occurs on the drop the script will continue (possible resulting in another error down the line)

Thanks for your time. I did a replace-all of every onerror="abort" with onerror="contine", and I redid everything, but I now get a similar but slightly different error message:

C:\Documents and Settings\kworkman\Desktop\JDBC Tutorials\JDBCTutorial>ant javadb-create-procedure
Buildfile: C:\Documents and Settings\kworkman\Desktop\JDBC Tutorials\JDBCTutorial\build.xml
 
javadb-create-procedure:
      [sql] Executing commands
      [sql] Failed to execute:  drop procedure SHOW_SUPPLIERS
      [sql] java.sql.SQLSyntaxErrorException: 'DROP PROCEDURE' cannot be performed on 'SHOW_SUPPLIERS' because it does not exist.
      [sql] 0 of 1 SQL statements executed successfully
      [sql] Executing commands
      [sql] Failed to execute:  drop procedure GET_SUPPLIER_OF_COFFEE
      [sql] java.sql.SQLSyntaxErrorException: 'DROP PROCEDURE' cannot be performed on 'GET_SUPPLIER_OF_COFFEE' because it does not exist.
      [sql] 0 of 1 SQL statements executed successfully
      [sql] Executing commands
      [sql] Failed to execute:  drop procedure RAISE_PRICE
      [sql] java.sql.SQLSyntaxErrorException: 'DROP PROCEDURE' cannot be performed on 'RAISE_PRICE' because it does not exist.
      [sql] 0 of 1 SQL statements executed successfully
      [sql] Executing resource: C:\Documents and Settings\kworkman\Desktop\JDBCTutorials\JDBCTutorial\sql\derby\create-procedures.sql
      [sql] 0 of 0 SQL statements executed successfully
 
BUILD SUCCESSFUL
Total time: 0 seconds

Anyway, thanks again for your time. I have to stop banging my head against this for the night though.

I'd ignore those error messages for the time being. Oftentimes when building a database schema the build script will check if a table or procedure exists, and if it does the script drops it before recreating it. In this case however, it does not check if it exists - just tries to drop it. Given it does not exists (it actually can't until you pass that portion of the build script), an error arises. Changing the onerror to continue lets the script continue through those errors and create those procedures (if you run it again, you shouldn't see the errors, but might want to change the onerror back to abort)

I just tried it again, but I continue to get the same error message as before, even if I run it multiple times. I'm not sure where to go from here. I would expect a ton of people to have the same problem (it's the first tutorial, I expect it to either work or break in a pretty predictable way), but my googling has come up pretty empty-handed.

Originally Posted by KevinWorkman

I just tried it again, but I continue to get the same error message as before, even if I run it multiple times. I'm not sure where to go from here. I would expect a ton of people to have the same problem (it's the first tutorial, I expect it to either work or break in a pretty predictable way), but my googling has come up pretty empty-handed.

Just ran it myself, and it ran smoothly on my system. You may want to verify all the properties are correct in the build and sample properties files.

Edit: The tutorial for the setup calls 'ant jar' and 'ant setup', but I see no suggestion to explicitly call 'ant javadb-create-procedure'

Originally Posted by copeg

Just ran it myself, and it ran smoothly on my system. You may want to verify all the properties are correct in the build and sample properties files.

All of the properties appear to be correct, but just to be sure, I just did everything from scratch - redownloaded the tutorial, made the few changes it asks for. I get the same errors.

Originally Posted by copeg

Edit: The tutorial for the setup calls 'ant jar' and 'ant setup', but I see no suggestion to explicitly call 'ant javadb-create-procedure'

At the very bottom, it says to run the samples. There are quite a few, I'm just running the first one. I get similar errors on all of them though.

Would it be too much trouble for you to paste the output of your "ant jar", "ant setup", and one of the sample commands? I think my "ant setup" step looks suspect, but I have no idea what the output is supposed to look like.

Originally Posted by KevinWorkman

All of the properties appear to be correct, but just to be sure, I just did everything from scratch - redownloaded the tutorial, made the few changes it asks for. I get the same errors.



At the very bottom, it says to run the samples. There are quite a few, I'm just running the first one. I get similar errors on all of them though.

Would it be too much trouble for you to paste the output of your "ant jar", "ant setup", and one of the sample commands? I think my "ant setup" step looks suspect, but I have no idea what the output is supposed to look like.

Ok, see them now.

Here's my console output from jar and setup:

U:\JDBC\JDBCTutorial>ant  jar
Buildfile: U:\JDBC\JDBCTutorial\build.xml
 
compile:
     [echo] JDBC Tutorial is using C:\\Program Files\\Java\\jdk1.6.0_19\\bin\\javac as Java compiler
    [javac] U:\JDBC\JDBCTutorial\build.xml:59: warning: 'includeantruntime' was not set, defaulting to build.sysclasspat
h=last; set to false for repeatable builds
 
jar:
      [jar] Building jar: U:\JDBC\JDBCTutorial\lib\JDBCTutorial.jar
 
BUILD SUCCESSFUL
Total time: 1 second
 
U:\JDBC\JDBCTutorial>ant setup
Buildfile: U:\JDBC\JDBCTutorial\build.xml
 
setup:
 
drop-tables:
      [sql] Executing resource: U:\JDBC\JDBCTutorial\sql\javadb\drop-tables.sql
      [sql] Failed to execute:  DROP TABLE COFFEE_DESCRIPTIONS
      [sql] java.sql.SQLSyntaxErrorException: 'DROP TABLE' cannot be performed on 'COFFEE_DESCRIPTIONS' because it does
not exist.
      [sql] Failed to execute:  DROP TABLE COFFEE_HOUSES
      [sql] java.sql.SQLSyntaxErrorException: 'DROP TABLE' cannot be performed on 'COFFEE_HOUSES' because it does not e
ist.
      [sql] Failed to execute:  DROP TABLE MERCH_INVENTORY
      [sql] java.sql.SQLSyntaxErrorException: 'DROP TABLE' cannot be performed on 'MERCH_INVENTORY' because it does not
exist.
      [sql] Failed to execute:  DROP TABLE COF_INVENTORY
      [sql] java.sql.SQLSyntaxErrorException: 'DROP TABLE' cannot be performed on 'COF_INVENTORY' because it does not e
ist.
      [sql] Failed to execute:  DROP TABLE RSS_FEEDS
      [sql] java.sql.SQLSyntaxErrorException: 'DROP TABLE' cannot be performed on 'RSS_FEEDS' because it does not exist
 
      [sql] Failed to execute:  DROP TABLE COFFEES
      [sql] java.sql.SQLSyntaxErrorException: 'DROP TABLE' cannot be performed on 'COFFEES' because it does not exist.
      [sql] Failed to execute:  DROP TABLE SUPPLIERS
      [sql] java.sql.SQLSyntaxErrorException: 'DROP TABLE' cannot be performed on 'SUPPLIERS' because it does not exist
 
      [sql] Failed to execute:  DROP TABLE DATA_REPOSITORY
      [sql] java.sql.SQLSyntaxErrorException: 'DROP TABLE' cannot be performed on 'DATA_REPOSITORY' because it does not
exist.
      [sql] 0 of 8 SQL statements executed successfully
 
build-tables:
      [sql] Executing resource: U:\JDBC\JDBCTutorial\sql\javadb\create-tables.sql
      [sql] 8 of 8 SQL statements executed successfully
 
populate-tables:
      [sql] Executing resource: U:\JDBC\JDBCTutorial\sql\javadb\populate-tables.sql
      [sql] 40 of 40 SQL statements executed successfully
 
BUILD SUCCESSFUL
Total time: 7 seconds

Here's running javadb-create-procedure with onerror set to abort

U:\JDBC\JDBCTutorial>ant javadb-create-procedure
Buildfile: U:\JDBC\JDBCTutorial\build.xml
 
javadb-create-procedure:
      [sql] Executing commands
      [sql] Failed to execute:  drop procedure SHOW_SUPPLIERS
 
BUILD FAILED
U:\JDBC\JDBCTutorial\build.xml:108: java.sql.SQLSyntaxErrorException: 'DROP PROCEDURE' cannot be performed on 'SHOW_S
LIERS' because it does not exist.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
        at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
        at org.apache.tools.ant.taskdefs.SQLExec.execSQL(SQLExec.java:775)
        at org.apache.tools.ant.taskdefs.SQLExec.runStatements(SQLExec.java:745)
        at org.apache.tools.ant.taskdefs.SQLExec$Transaction.runTransaction(SQLExec.java:1043)
        at org.apache.tools.ant.taskdefs.SQLExec$Transaction.access$000(SQLExec.java:985)
        at org.apache.tools.ant.taskdefs.SQLExec.execute(SQLExec.java:653)
        at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:291)
        at sun.reflect.GeneratedMethodAccessor4.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:106)
        at org.apache.tools.ant.Task.perform(Task.java:348)
        at org.apache.tools.ant.Target.execute(Target.java:390)
        at org.apache.tools.ant.Target.performTasks(Target.java:411)
        at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1399)
        at org.apache.tools.ant.Project.executeTarget(Project.java:1368)
        at org.apache.tools.ant.helper.DefaultExecutor.executeTargets(DefaultExecutor.java:41)
        at org.apache.tools.ant.Project.executeTargets(Project.java:1251)
        at org.apache.tools.ant.Main.runBuild(Main.java:809)
        at org.apache.tools.ant.Main.startAnt(Main.java:217)
        at org.apache.tools.ant.launch.Launcher.run(Launcher.java:280)
        at org.apache.tools.ant.launch.Launcher.main(Launcher.java:109)
Caused by: java.sql.SQLException: 'DROP PROCEDURE' cannot be performed on 'SHOW_SUPPLIERS' because it does not exist.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
        ... 29 more
Caused by: ERROR 42Y55: 'DROP PROCEDURE' cannot be performed on 'SHOW_SUPPLIERS' because it does not exist.
        at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
        at org.apache.derby.impl.sql.compile.DropAliasNode.bindStatement(Unknown Source)
        at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
        at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
        at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
        ... 23 more
 
Total time: 4 seconds

Running it after changing to onerror="continue" in only the drop procedure SQL commands of this task (the last call builds the procedures)

U:\JDBC\JDBCTutorial>ant javadb-create-procedure
Buildfile: U:\JDBC\JDBCTutorial\build.xml
 
javadb-create-procedure:
      [sql] Executing commands
      [sql] Failed to execute:  drop procedure SHOW_SUPPLIERS
      [sql] java.sql.SQLSyntaxErrorException: 'DROP PROCEDURE' cannot be performed on 'SHOW_SUPPLIERS' because it does
ot exist.
      [sql] 0 of 1 SQL statements executed successfully
      [sql] Executing commands
      [sql] Failed to execute:  drop procedure GET_SUPPLIER_OF_COFFEE
      [sql] java.sql.SQLSyntaxErrorException: 'DROP PROCEDURE' cannot be performed on 'GET_SUPPLIER_OF_COFFEE' because
t does not exist.
      [sql] 0 of 1 SQL statements executed successfully
      [sql] Executing commands
      [sql] Failed to execute:  drop procedure RAISE_PRICE
      [sql] java.sql.SQLSyntaxErrorException: 'DROP PROCEDURE' cannot be performed on 'RAISE_PRICE' because it does not
exist.
      [sql] 0 of 1 SQL statements executed successfully
      [sql] Executing resource: U:\JDBC\JDBCTutorial\sql\javadb\create-procedures.sql
      [sql] 3 of 3 SQL statements executed successfully
 
BUILD SUCCESSFUL
Total time: 3 seconds
 
U:\JDBC\JDBCTutorial>ant javadb-create-procedure
Buildfile: U:\JDBC\JDBCTutorial\build.xml
 
javadb-create-procedure:
      [sql] Executing commands
      [sql] 1 of 1 SQL statements executed successfully
      [sql] Executing commands
      [sql] 1 of 1 SQL statements executed successfully
      [sql] Executing commands
      [sql] 1 of 1 SQL statements executed successfully
      [sql] Executing resource: U:\JDBC\JDBCTutorial\sql\javadb\create-procedures.sql
      [sql] 3 of 3 SQL statements executed successfully
 
BUILD SUCCESSFUL
Total time: 3 seconds
 
U:\JDBC\JDBCTutorial>

Note there are 2 calls above - the first results in the errors as I would expect. The second no errors because the procedures now exist (same result when onerror is set back to abort)

Note, check the path of your SQL files. 0 of 0 executed == not good - meaning the files are empty or do not exist (I would presume ant would warn you if they don't exist, perhaps not?). Check the following in your build-properties file:
<property name="DB.VENDOR" value="javadb"/>
make sure the value is the name of the folder in your sql directory (in your setup, it appears the value may be set to derby).

Okay, I finally feel like I'm getting somewhere! Thanks so much. For the record, here's what I did:

tl;dr version: The tutorial says to use a value of derby for the DB.VENDOR property, but I should have been using a value of javadb!

I noticed that your "ant setup" output looks different from mine, which is what I expected. Taking a closer look, this is the first difference I see.

Mine: Executing resource: C:\Documents and Settings\kworkman\Desktop\JDBCTutorials\JDBCTutori al\sql\derby\drop-tables.sql
Yours: Executing resource: U:\JDBC\JDBCTutorial\sql\javadb\drop-tables.sql

Upon further inspection, there is no JDBCTutorial\sql\derby directory! The tutorial says to use derby as the vendor and dbms, but is that wrong? I changed both of them to javadb, and I got the same output as you for the setup step.

After that, and changing the correct onerror="abort" to onerror="continue", everything seemed to be working better. But I was still getting errors on the samples! So after mostly experimental poking in the properties file, I changed the dbms property file back to derby. And everything seems to be working now!

It looks to me like there's a typo or an outdated reference in that tutorial. Gah! Anyway, it works now, thanks so much for your time.

Edit- Yeah, you got it. I should have just hit refresh!