You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by Prestige Spas <sp...@yahoo.com.au> on 2009/02/12 23:54:00 UTC
Re: sql statements execution error
Hello All,
I have searched the archives and not seen an answer to this, but I am having the
same trouble with the torque "insert-sql" task, as called from the OJB build.xml.
I have attached an old post that shows the exact same problem I am seeing here.
My project is assembled from ojb-blank.jar and my settings are similar to what is
reported below, except that I am not running inside of tomcat, simply trying to
develop in eclipse at this stage and run from there.
"ant setup-db" always returns BUILD SUCESSFUL but the ant task prints all the
sql statements together and says 0 of 2 statements executed sucessfully, the
problem being a MySQLSyntaxErrorException thrown when torque is trying to
create the OJB_HL_SEQ table. I have tried this in ojb-blank.jar also and the
same problem happened from day one.
I am running OJB 1.0.4 with mysql 5.0.51a and version 5.0.8 of the mysql JDBC
driver. Java version is 1.5.0_16. If anyone out there is able to shed any light on
this I would appreciate it, as I have been tearing my hair out for days on this now
and I cannot think what is wrong. Obviously ojb-blank should just work out of the
box with MySQL, and I think i have set everything up correctly (profile=mysql,
edit the profile/mysql.profile, torque.database=mysql, dbmsName=MySQL). I have
tested that the driver works with SQuirreL and mysql-query-browser is able to
execute the generated SQL statements *individually* from
build/database/project-schema.sql (but not if i simply paste the whole file).
TIA
-Brian
>Hai,
>
>I have a problem in doing an ant build
>
>The sql statements throw
>
>[torque-sql-exec] com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error
>in your SQL syntax; check the manual that corresponds to your MySQL server version for the
>right syntax to use near 'CREATE TABLE Product ( id INTEGER NOT NULL, name VARCHAR (100),
>price FLOAT, sto' at line 3
>
>I want the ant script to create the database "productmanager" at runtime. I'm using MySQL
>Server version 5.1.6
>
>Below shown is my build.properties file:
>
>jcdAlias=default
>databaseName=productmanager
>databaseUser=root
>databasePassword=root
>
>tomcat.home=${env.CATALINA_HOME}
>tomcat.url=http://localhost:8080
>tomcat.manager.url=${tomcat.url}/manager
># This user should be present in the $CATALINA_HOME/conf/tomcat-users.xml file with the role
>'manager':
>tomcat.username=tomcat
>tomcat.password=tomcat
>
>embeddedDatabase=false
>
>dbmsName=MySQL
>jdbcLevel=3.0
>jdbcRuntimeDriver=com.mysql.jdbc.Driver
>urlProtocol=jdbc
>urlSubprotocol=mysql
>urlDbalias=//localhost:3306/${databaseName}
>
>torque.database=mysql
>
>torque.database.createUrl=${urlProtocol}:${urlSubprotocol}://localhost:3306/${databaseName}
>
>torque.database.driver=${jdbcRuntimeDriver}
>
>torque.database.user=${databaseUser}
>
>torque.database.password=${databasePassword}
>
>torque.database.buildUrl=${torque.database.createUrl}
>
>source.dir=src
>source.java.dir=${source.dir}/java
>source.resource.dir=${source.dir}/resources
>source.test.dir=${source.dir}/test
>source.schema.dir=${source.dir}/schema
>source.webapp.dir=${source.dir}/webapp
>
>build.dir=build
>lib.dir=lib
>lib.webapp.excludes=xdoclet*.jar,xjavadoc*.jar,torque*.jar,velocity*.jar
>
>target.dir=target
>
>servlet.lib=${tomcat.home}/common/lib/servlet-api.jar
>jsp.lib=${tomcat.home}/common/lib/jsp-api.jar
>deploy.dir=${tomcat.home}/webapps
>
>embedded.database.excludes=*schema.xml,*.sql,*.sql.generation,sqldb.map
>
>
>
>Below shown is the log details: (see the highlighted text in red color)
>
>C:\Users\karthiksg\Desktop\webapp-sample\webapp-sample>ant
>Buildfile: build.xml
>
>compile:
>
>xdoclet:
>[ojbdoclet] 2008-11-09 17:51:00,188 [main] INFO xdoclet.XDocletMain.start - Run
>ning <ojbrepository/>
>[ojbdoclet] 2008-11-09 17:51:00,783 [main] INFO xdoclet.XDocletMain.start - Run
>ning <torqueschema/>
>
>war:
>
>compile:
>
>xdoclet:
>[ojbdoclet] 2008-11-09 17:51:02,105 [main] INFO xdoclet.XDocletMain.start - Run
>ning <ojbrepository/>
>[ojbdoclet] 2008-11-09 17:51:02,604 [main] INFO xdoclet.XDocletMain.start - Run
>ning <torqueschema/>
>
>setup-db:
>
>check-use-classpath:
>
>check-run-only-on-schema-change:
>
>sql-check:
>
>sql:
>[echo] +------------------------------------------+
>[echo] | |
>[echo] | Generating SQL for YOUR Torque project! |
>[echo] | |
>[echo] +------------------------------------------+
>
>sql-classpath:
>[echo] loading templates from classpath
>[torque-sql] Using contextProperties file: C:\Users\karthiksg\Desktop\webapp-sam
>ple\webapp-sample\build.properties
>[torque-sql] Using classpath
>[torque-sql] Generating to file C:\Users\karthiksg\Desktop\webapp-sample\webapp-
>sample\build\database\report.productmanager.sql.generation
>[torque-sql] 2008-11-09 17:51:04,499 [main] INFO org.apache.torque.engine.datab
>ase.transform.XmlToAppData - Parsing file: 'ojbcore-schema.xml'
>[torque-sql] 2008-11-09 17:51:04,522 [main] INFO org.apache.torque.engine.datab
>ase.transform.DTDResolver - Resolver: used database.dtd from 'org.apache.torque.
>engine.database.transform' package
>[torque-sql] 2008-11-09 17:51:04,595 [main] INFO org.apache.torque.engine.datab
>ase.transform.XmlToAppData - Parsing file: 'project-schema.xml'
>[torque-sql] 2008-11-09 17:51:04,600 [main] INFO org.apache.torque.engine.datab
>ase.transform.DTDResolver - Resolver: used 'http://db.apache.org/torque/dtd/data
>base_3_0_1.dtd'
>
>sql-template:
>
>create-db-check:
>
>create-db:
>[torque-data-model] Using classpath
>[torque-data-model] Generating to file C:\Users\karthiksg\Desktop\webapp-sample\
>webapp-sample\build\database\create-db.sql
>[torque-data-model] 2008-11-09 17:51:25,378 [main] INFO org.apache.torque.engin
>e.database.transform.XmlToAppData - Parsing file: 'ojbcore-schema.xml'
>[torque-data-model] 2008-11-09 17:51:25,392 [main] INFO org.apache.torque.engin
>e.database.transform.DTDResolver - Resolver: used database.dtd from 'org.apache.
>torque.engine.database.transform' package
>[torque-data-model] 2008-11-09 17:51:25,457 [main] INFO org.apache.torque.engin
>e.database.transform.XmlToAppData - Parsing file: 'project-schema.xml'
>[torque-data-model] 2008-11-09 17:51:25,463 [main] INFO org.apache.torque.engin
>e.database.transform.DTDResolver - Resolver: used 'http://db.apache.org/torque/d
>td/database_3_0_1.dtd'
>
>shutdown-check:
>
>append-shutdown-sql:
>[echo]
>[echo] Executing the create-db.sql script ...
>[echo]
>[sql] Executing resource: C:\Users\karthiksg\Desktop\webapp-sample\webapp-
>sample\build\database\create-db.sql
>[sql] 2 of 2 SQL statements executed successfully
>
>insert-sql:
>
>shutdown-check:
>
>add-shutdown-sql-file:
>[torque-sql-exec] Our new url -> jdbc:mysql://localhost:3306/productmanager
>[torque-sql-exec] Executing file: C:\Users\karthiksg\Desktop\webapp-sample\webap
>p-sample\build\database\project-schema.sql
>[torque-sql-exec] Failed to execute: # -----------------------------------------
>------------------------------
>[torque-sql-exec] # Product # -------------------------------------------------
>----------------------
>[torque-sql-exec] drop table if exists Product; CREATE TABLE Product ( id INTEG
>ER NOT NULL, name VARCHAR (100), price FLOAT, stock INTEGER, PRIMARY KEY(id) );
>[torque-sql-exec] com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You
>have an error in your SQL syntax; check the manual that corresponds to your MyS
>QL server version for the right syntax to use near 'CREATE TABLE Product ( id IN
>TEGER NOT NULL, name VARCHAR (100), price FLOAT, sto' at line 3
>[torque-sql-exec] Executing file: C:\Users\karthiksg\Desktop\webapp-sample\webap
>p-sample\build\database\ojbcore-schema.sql
>[torque-sql-exec] Failed to execute: # -----------------------------------------
>------------------------------
>[torque-sql-exec] # OJB_HL_SEQ # ----------------------------------------------
>-------------------------
>[torque-sql-exec] drop table if exists OJB_HL_SEQ; CREATE TABLE OJB_HL_SEQ ( TA
>BLENAME VARCHAR (250) NOT NULL, MAX_KEY BIGINT, GRAB_SIZE INTEGER, VERSION INTEG
>ER, PRIMARY KEY(TABLENAME) ); # ------------------------------------------------
>-----------------------
>[torque-sql-exec] # OJB_NRM # -------------------------------------------------
>----------------------
>[torque-sql-exec] drop table if exists OJB_NRM; CREATE TABLE OJB_NRM ( NAME VAR
>CHAR (250) NOT NULL, OID_ LONGBLOB, PRIMARY KEY(NAME) ); # ---------------------
>--------------------------------------------------
>[torque-sql-exec] # OJB_DLIST # -----------------------------------------------
>------------------------
>[torque-sql-exec] drop table if exists OJB_DLIST; CREATE TABLE OJB_DLIST ( ID I
>NTEGER NOT NULL, SIZE_ INTEGER, PRIMARY KEY(ID) ); # ---------------------------
>--------------------------------------------
>[torque-sql-exec] # OJB_DLIST_ENTRIES # ---------------------------------------
>--------------------------------
>[torque-sql-exec] drop table if exists OJB_DLIST_ENTRIES; CREATE TABLE OJB_DLIS
>T_ENTRIES ( ID INTEGER NOT NULL, DLIST_ID INTEGER NOT NULL, POSITION_ INTEGER, O
>ID_ LONGBLOB, PRIMARY KEY(ID) ); # ---------------------------------------------
>--------------------------
>[torque-sql-exec] # OJB_DSET # ------------------------------------------------
>-----------------------
>[torque-sql-exec] drop table if exists OJB_DSET; CREATE TABLE OJB_DSET ( ID INT
>EGER NOT NULL, SIZE_ INTEGER, PRIMARY KEY(ID) ); # -----------------------------
>------------------------------------------
>[torque-sql-exec] # OJB_DSET_ENTRIES # ----------------------------------------
>-------------------------------
>[torque-sql-exec] drop table if exists OJB_DSET_ENTRIES; CREATE TABLE OJB_DSET_
>ENTRIES ( ID INTEGER NOT NULL, DLIST_ID INTEGER NOT NULL, POSITION_ INTEGER, OID
>_ LONGBLOB, PRIMARY KEY(ID) ); # -----------------------------------------------
>------------------------
>[torque-sql-exec] # OJB_DMAP # ------------------------------------------------
>-----------------------
>[torque-sql-exec] drop table if exists OJB_DMAP; CREATE TABLE OJB_DMAP ( ID INT
>EGER NOT NULL, SIZE_ INTEGER, PRIMARY KEY(ID) ); # -----------------------------
>------------------------------------------
>[torque-sql-exec] # OJB_DMAP_ENTRIES # ----------------------------------------
>-------------------------------
>[torque-sql-exec] drop table if exists OJB_DMAP_ENTRIES; CREATE TABLE OJB_DMAP_
>ENTRIES ( ID INTEGER NOT NULL, DMAP_ID INTEGER NOT NULL, KEY_OID LONGBLOB, VALUE
>_OID LONGBLOB, PRIMARY KEY(ID) );
>[torque-sql-exec] com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You
>have an error in your SQL syntax; check the manual that corresponds to your MyS
>QL server version for the right syntax to use near 'CREATE TABLE OJB_HL_SEQ ( TA
>BLENAME VARCHAR (250) NOT NULL, MAX_KEY BIGINT, GRAB' at line 3
>[torque-sql-exec] 0 of 2 SQL statements executed successfully
>
>copy-database-files:
>
>BUILD SUCCESSFUL
>Total time: 46 seconds
>
>I can see the files build\database\createdb.sql ,
>build\database\ojbcore-schema.sql ,
>build\database\project-schema.sql
>
>I can copy the statements from the .sql listed above and execute successfully in mysql command
>window.
>
>kindly do the needfull.
Make Yahoo!7 your homepage and win a trip to the Quiksilver Pro. Find out more
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: sql statements execution error
Posted by Prestige Spas <sp...@yahoo.com.au>.
Some more info on this issue for anyone that might be able to help:
(Sorry for the size of the post, this gets more interesting around point 6, but i wanted to be complete)
1. I have started a brand new project from ojb-blank.jar in order to
diagnose what is going wrong here.
2. Followed the getting-started guide here:
http://db.apache.org/ojb/docu/getting-started.html
as closely as possible to setup for mysql on a remote server
(yes, this server works fine for everything else, same user etc.)
3. Extracted Tutorial 1 jar into the src/java directory
4. Imported project into eclipse
5. Changed build.properties as follows:
USER AND PASSWORD INFO - ALREADY WORKING USER SETUP ON SERVER
jcdAlias=default
databaseName=jerc
databaseUser=jerc
databasePassword=********
MYSQL SETTINGS - ADDED urlMachine for reference in torque.createUrl
dbmsName=MySQL
jdbcLevel=3.0
jdbcRuntimeDriver=com.mysql.jdbc.Driver
urlProtocol=jdbc
urlSubprotocol=mysql
urlMachine=//obelix:3306/
urlDbalias=${urlMachine}${databaseName}
CHANGED CREATEURL - THIS WORKS
torque.database=mysql
torque.database.createUrl=${urlProtocol}:${urlSubprotocol}:${urlMachine}
torque.database.buildUrl=${urlProtocol}:${urlSubprotocol}:${urlDbalias}
jar.name=ojb-blank.jar
ALL OTHER SETTINGS ARE UNCHANGED
6. Setup and Run ant tasks from eclispe
6a. build task output:
Buildfile: /home/brian/Workspace/ojb-blank/build.xml
compile:
[javac] Compiling 9 source files to /home/brian/Workspace/ojb-blank/build/classes
[javac] ----------
[javac] 1. WARNING in /home/brian/Workspace/ojb-blank/src/java/org/apache/ojb/tutorial1/Product.java (at line 23)
[javac] public class Product implements java.io.Serializable
[javac] ^^^^^^^
[javac] The serializable class Product does not declare a static final serialVersionUID field of type long
[javac] ----------
[javac] 1 problem (1 warning)
xdoclet:
[ojbdoclet] 2009-02-20 15:01:55,564 [Thread-37] INFO xdoclet.XDocletMain.start - Running <ojbrepository/>
[ojbdoclet] Generating ojb repository descriptor (build/repository_user.xml)
[ojbdoclet] Type org.apache.ojb.tutorial1.Product
[ojbdoclet] Processed 1 types
[ojbdoclet] Processed 1 types
[ojbdoclet] 2009-02-20 15:01:58,376 [Thread-37] INFO xdoclet.XDocletMain.start - Running <torqueschema/>
[ojbdoclet] Generating torque schema (build/database/project-schema.xml)
[ojbdoclet] Processed 1 types
build:
[copy] Copying 7 files to /home/brian/Workspace/ojb-blank/build
[mkdir] Created dir: /home/brian/Workspace/ojb-blank/build/lib
[copy] Copying 14 files to /home/brian/Workspace/ojb-blank/build/lib
build_if_with_mainclass:
BUILD SUCCESSFUL
Total time: 7 seconds
6b setup-db task output:
Buildfile: /home/brian/Workspace/ojb-blank/build.xml
compile:
xdoclet:
[ojbdoclet] 2009-02-20 15:03:01,742 [Thread-38] INFO xdoclet.XDocletMain.start - Running <ojbrepository/>
[ojbdoclet] 2009-02-20 15:03:03,401 [Thread-38] INFO xdoclet.XDocletMain.start - Running <torqueschema/>
setup-db:
[copy] Copying 1 file to /home/brian/Workspace/ojb-blank/build/database
BUILD FAILED
/home/brian/Workspace/ojb-blank/build.xml:185: The following error occurred while executing this line:
/home/brian/Workspace/ojb-blank/src/schema/build-torque.xml:97: taskdef A class needed by class org.apache.torque.task.TorqueJDBCTransformTask cannot be found: org.apache.torque.task.TorqueJDBCTransformTask
Total time: 6 seconds
7. Added torque-gen.jar containing TorqueJDBCTransformTask to the eclipse build path, no good.
8. Copied lib dir into src/schema also (eclipse complained about that not existing)
9. Warning for JDBCTransformTask did not go away - all the other warnings did
10. SAME RESULT as 6b
11. Ran "ant setup-db" externally from ~/Workspace/ojb-blank/ in a console:
brian@asterix:~/Workspace/ojb-blank$ ant setup-db
Buildfile: build.xml
compile:
xdoclet:
[ojbdoclet] 2009-02-20 15:06:01,383 [main] INFO xdoclet.XDocletMain.start - Running <ojbrepository/>
[ojbdoclet] 2009-02-20 15:06:01,626 [main] INFO xdoclet.XDocletMain.start - Running <torqueschema/>
setup-db:
check-use-classpath:
check-run-only-on-schema-change:
sql-check:
sql:
[echo] +------------------------------------------+
[echo] | |
[echo] | Generating SQL for YOUR Torque project! |
[echo] | |
[echo] +------------------------------------------+
sql-classpath:
[echo] loading templates from classpath
[torque-sql] Using contextProperties file: /home/brian/Workspace/ojb-blank/build.properties
[torque-sql] Using classpath
[torque-sql] Generating to file /home/brian/Workspace/ojb-blank/build/database/report.jerc.sql.generation
[torque-sql] 2009-02-20 15:06:02,719 [main] INFO org.apache.torque.engine.database.transform.XmlToAppData - Parsing file: 'ojbcore-schema.xml'
[torque-sql] 2009-02-20 15:06:02,749 [main] INFO org.apache.torque.engine.database.transform.DTDResolver - Resolver: used database.dtd from 'org.apache.torque.engine.database.transform' package
[torque-sql] 2009-02-20 15:06:02,801 [main] INFO org.apache.torque.engine.database.transform.XmlToAppData - Parsing file: 'project-schema.xml'
[torque-sql] 2009-02-20 15:06:02,802 [main] INFO org.apache.torque.engine.database.transform.DTDResolver - Resolver: used database.dtd from 'org.apache.torque.engine.database.transform' package
sql-template:
create-db-check:
create-db:
[torque-data-model] Using classpath
[torque-data-model] Generating to file /home/brian/Workspace/ojb-blank/build/database/create-db.sql
[torque-data-model] 2009-02-20 15:06:04,049 [main] INFO org.apache.torque.engine.database.transform.XmlToAppData - Parsing file: 'ojbcore-schema.xml'
[torque-data-model] 2009-02-20 15:06:04,052 [main] INFO org.apache.torque.engine.database.transform.DTDResolver - Resolver: used database.dtd from 'org.apache.torque.engine.database.transform' package
[torque-data-model] 2009-02-20 15:06:04,131 [main] INFO org.apache.torque.engine.database.transform.XmlToAppData - Parsing file: 'project-schema.xml'
[torque-data-model] 2009-02-20 15:06:04,132 [main] INFO org.apache.torque.engine.database.transform.DTDResolver - Resolver: used database.dtd from 'org.apache.torque.engine.database.transform' package
shutdown-check:
append-shutdown-sql:
[echo]
[echo] Executing the create-db.sql script ...
[echo]
[sql] Executing resource: /home/brian/Workspace/ojb-blank/build/database/create-db.sql
[sql] 2 of 2 SQL statements executed successfully
insert-sql:
shutdown-check:
add-shutdown-sql-file:
[torque-sql-exec] Executing file: /home/brian/Workspace/ojb-blank/build/database/project-schema.sql
[torque-sql-exec] Failed to execute: # -----------------------------------------------------------------------
[torque-sql-exec] # Product # -----------------------------------------------------------------------
[torque-sql-exec] drop table if exists Product; CREATE TABLE Product ( id INTEGER NOT NULL, name VARCHAR (100), price FLOAT, stock INTEGER, PRIMARY KEY(id) );
[torque-sql-exec] com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; CREATE TABLE Product ( id INTEGER NOT NULL, name VARCHAR (100), price FLOAT, s' at line 3
[torque-sql-exec] Executing file: /home/brian/Workspace/ojb-blank/build/database/ojbcore-schema.sql
[torque-sql-exec] Failed to execute: # -----------------------------------------------------------------------
[torque-sql-exec] # OJB_HL_SEQ # -----------------------------------------------------------------------
[torque-sql-exec] drop table if exists OJB_HL_SEQ; CREATE TABLE OJB_HL_SEQ ( TABLENAME VARCHAR (250) NOT NULL, MAX_KEY BIGINT, GRAB_SIZE INTEGER, VERSION INTEGER, PRIMARY KEY(TABLENAME) ); # -----------------------------------------------------------------------
[torque-sql-exec] # OJB_NRM # -----------------------------------------------------------------------
[torque-sql-exec] drop table if exists OJB_NRM; CREATE TABLE OJB_NRM ( NAME VARCHAR (250) NOT NULL, OID_ LONGBLOB, PRIMARY KEY(NAME) ); # -----------------------------------------------------------------------
[torque-sql-exec] # OJB_DLIST # -----------------------------------------------------------------------
[torque-sql-exec] drop table if exists OJB_DLIST; CREATE TABLE OJB_DLIST ( ID INTEGER NOT NULL, SIZE_ INTEGER, PRIMARY KEY(ID) ); # -----------------------------------------------------------------------
[torque-sql-exec] # OJB_DLIST_ENTRIES # -----------------------------------------------------------------------
[torque-sql-exec] drop table if exists OJB_DLIST_ENTRIES; CREATE TABLE OJB_DLIST_ENTRIES ( ID INTEGER NOT NULL, DLIST_ID INTEGER NOT NULL, POSITION_ INTEGER, OID_ LONGBLOB, PRIMARY KEY(ID) ); # -----------------------------------------------------------------------
[torque-sql-exec] # OJB_DSET # -----------------------------------------------------------------------
[torque-sql-exec] drop table if exists OJB_DSET; CREATE TABLE OJB_DSET ( ID INTEGER NOT NULL, SIZE_ INTEGER, PRIMARY KEY(ID) ); # -----------------------------------------------------------------------
[torque-sql-exec] # OJB_DSET_ENTRIES # -----------------------------------------------------------------------
[torque-sql-exec] drop table if exists OJB_DSET_ENTRIES; CREATE TABLE OJB_DSET_ENTRIES ( ID INTEGER NOT NULL, DLIST_ID INTEGER NOT NULL, POSITION_ INTEGER, OID_ LONGBLOB, PRIMARY KEY(ID) ); # -----------------------------------------------------------------------
[torque-sql-exec] # OJB_DMAP # -----------------------------------------------------------------------
[torque-sql-exec] drop table if exists OJB_DMAP; CREATE TABLE OJB_DMAP ( ID INTEGER NOT NULL, SIZE_ INTEGER, PRIMARY KEY(ID) ); # -----------------------------------------------------------------------
[torque-sql-exec] # OJB_DMAP_ENTRIES # -----------------------------------------------------------------------
[torque-sql-exec] drop table if exists OJB_DMAP_ENTRIES; CREATE TABLE OJB_DMAP_ENTRIES ( ID INTEGER NOT NULL, DMAP_ID INTEGER NOT NULL, KEY_OID LONGBLOB, VALUE_OID LONGBLOB, PRIMARY KEY(ID) );
[torque-sql-exec] com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; CREATE TABLE OJB_HL_SEQ ( TABLENAME VARCHAR (250) NOT NULL, MAX_KEY BIGINT, GR' at line 3
[torque-sql-exec] 0 of 2 SQL statements executed successfully
BUILD SUCCESSFUL
Total time: 4 seconds
Still no luck, and now we see the same problem crop up yet again. I tried the following to debug this:
1. Create an ant task insert-sql in build.xml that directly calls the insert-sql task in build-torque.xml:
<target name="insert-sql">
<property name="torque.buildFile"
value="${source.schema.dir}/build-torque.xml"/>
<property name="torque.project"
value="${databaseName}"/>
<property name="torque.schema.dir"
value="${build.dir}/database"/>
<property name="torque.output.dir"
value="${build.dir}/database"/>
<property name="torque.sql.dir"
value="${torque.output.dir}"/>
<property name="torque.useClasspath"
value="true"/>
<property name="torque.database.driver"
value="${jdbcRuntimeDriver}"/>
<property name="torque.database.user"
value="${databaseUser}"/>
<property name="torque.database.password"
value="${databasePassword}"/>
<ant dir="."
antfile="${torque.buildFile}"
target="insert-sql">
</ant>
</target>
2. Edit the generated project-schema.sql and ojbcore-schema.sql and run "ant insert-sql"
I found that if there is only 1 SQL statement in each .sql file this works!
For example if project-schema.sql reads:
# -----------------------------------------------------------------------
# Product
# -----------------------------------------------------------------------
drop table if exists Product;
CREATE TABLE Product
(
id INTEGER NOT NULL,
name VARCHAR (100),
price FLOAT,
stock INTEGER,
PRIMARY KEY(id)
);
it fails. if i remove everything before CREATE it works, but if I leave the drop table it fails.
I can leave the comments in and it succeeds.
So my question is - how do I get torque to automatically set up the DB for me? I thought this
would be working out of the box? It is not convinient for me to browse the .sql files
to snip each one out into mysql manually every time i change the schema.
Anyone??? I Really need help on this one.
Thanks,
--
Brian
________________________________
From: Prestige Spas <sp...@yahoo.com.au>
To: ojb-user@db.apache.org
Sent: Friday, 13 February, 2009 9:54:00 AM
Subject: Re: sql statements execution error
Hello All,
I have searched the archives and not seen an answer to this, but I am having the
same trouble with the torque "insert-sql" task, as called from the OJB build.xml.
I have attached an old post that shows the exact same problem I am seeing here.
My project is assembled from ojb-blank.jar and my settings are similar to what is
reported below, except that I am not running inside of tomcat, simply trying to
develop in eclipse at this stage and run from there.
"ant setup-db" always returns BUILD SUCESSFUL but the ant task prints all the
sql statements together and says 0 of 2 statements executed sucessfully, the
problem being a MySQLSyntaxErrorException thrown when torque is trying to
create the OJB_HL_SEQ table. I have tried this in ojb-blank.jar also and the
same problem happened from day one.
I am running OJB 1.0.4 with mysql 5.0.51a and version 5.0.8 of the mysql JDBC
driver. Java version is 1.5.0_16. If anyone out there is able to shed any light on
this I would appreciate it, as I have been tearing my hair out for days on this now
and I cannot think what is wrong. Obviously ojb-blank should just work out of the
box with MySQL, and I think i have set everything up correctly (profile=mysql,
edit the profile/mysql.profile, torque.database=mysql, dbmsName=MySQL). I have
tested that the driver works with SQuirreL and mysql-query-browser is able to
execute the generated SQL statements *individually* from
build/database/project-schema.sql (but not if i simply paste the whole file).
TIA
-Brian
>Hai,
>
>I have a problem in doing an ant build
>
>The sql statements throw
>
>[torque-sql-exec] com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error
>in your SQL syntax; check the manual that corresponds to your MySQL server version for the
>right syntax to use near 'CREATE TABLE Product ( id INTEGER NOT NULL, name VARCHAR (100),
>price FLOAT, sto' at line 3
>
>I want the ant script to create the database "productmanager" at runtime. I'm using MySQL
>Server version 5.1.6
>
>Below shown is my build.properties file:
>
>jcdAlias=default
>databaseName=productmanager
>databaseUser=root
>databasePassword=root
>
>tomcat.home=${env.CATALINA_HOME}
>tomcat.url=http://localhost:8080
>tomcat.manager.url=${tomcat.url}/manager
># This user should be present in the $CATALINA_HOME/conf/tomcat-users.xml file with the role
>'manager':
>tomcat.username=tomcat
>tomcat.password=tomcat
>
>embeddedDatabase=false
>
>dbmsName=MySQL
>jdbcLevel=3.0
>jdbcRuntimeDriver=com.mysql.jdbc.Driver
>urlProtocol=jdbc
>urlSubprotocol=mysql
>urlDbalias=//localhost:3306/${databaseName}
>
>torque.database=mysql
>
>torque.database.createUrl=${urlProtocol}:${urlSubprotocol}://localhost:3306/${databaseName}
>
>torque.database.driver=${jdbcRuntimeDriver}
>
>torque.database.user=${databaseUser}
>
>torque.database.password=${databasePassword}
>
>torque.database.buildUrl=${torque.database.createUrl}
>
>source.dir=src
>source.java.dir=${source.dir}/java
>source.resource.dir=${source.dir}/resources
>source.test.dir=${source.dir}/test
>source.schema.dir=${source.dir}/schema
>source.webapp.dir=${source.dir}/webapp
>
>build.dir=build
>lib.dir=lib
>lib.webapp.excludes=xdoclet*.jar,xjavadoc*.jar,torque*.jar,velocity*.jar
>
>target.dir=target
>
>servlet.lib=${tomcat.home}/common/lib/servlet-api.jar
>jsp.lib=${tomcat.home}/common/lib/jsp-api.jar
>deploy.dir=${tomcat.home}/webapps
>
>embedded.database.excludes=*schema.xml,*.sql,*.sql.generation,sqldb.map
>
>
>
>Below shown is the log details: (see the highlighted text in red color)
>
>C:\Users\karthiksg\Desktop\webapp-sample\webapp-sample>ant
>Buildfile: build.xml
>
>compile:
>
>xdoclet:
>[ojbdoclet] 2008-11-09 17:51:00,188 [main] INFO xdoclet.XDocletMain.start - Run
>ning <ojbrepository/>
>[ojbdoclet] 2008-11-09 17:51:00,783 [main] INFO xdoclet.XDocletMain.start - Run
>ning <torqueschema/>
>
>war:
>
>compile:
>
>xdoclet:
>[ojbdoclet] 2008-11-09 17:51:02,105 [main] INFO xdoclet.XDocletMain.start - Run
>ning <ojbrepository/>
>[ojbdoclet] 2008-11-09 17:51:02,604 [main] INFO xdoclet.XDocletMain.start - Run
>ning <torqueschema/>
>
>setup-db:
>
>check-use-classpath:
>
>check-run-only-on-schema-change:
>
>sql-check:
>
>sql:
>[echo] +------------------------------------------+
>[echo] | |
>[echo] | Generating SQL for YOUR Torque project! |
>[echo] | |
>[echo] +------------------------------------------+
>
>sql-classpath:
>[echo] loading templates from classpath
>[torque-sql] Using contextProperties file: C:\Users\karthiksg\Desktop\webapp-sam
>ple\webapp-sample\build.properties
>[torque-sql] Using classpath
>[torque-sql] Generating to file C:\Users\karthiksg\Desktop\webapp-sample\webapp-
>sample\build\database\report.productmanager.sql.generation
>[torque-sql] 2008-11-09 17:51:04,499 [main] INFO org.apache.torque.engine.datab
>ase.transform.XmlToAppData - Parsing file: 'ojbcore-schema.xml'
>[torque-sql] 2008-11-09 17:51:04,522 [main] INFO org.apache.torque.engine.datab
>ase.transform.DTDResolver - Resolver: used database.dtd from 'org.apache.torque.
>engine.database.transform' package
>[torque-sql] 2008-11-09 17:51:04,595 [main] INFO org.apache.torque.engine.datab
>ase.transform.XmlToAppData - Parsing file: 'project-schema.xml'
>[torque-sql] 2008-11-09 17:51:04,600 [main] INFO org.apache.torque.engine.datab
>ase.transform.DTDResolver - Resolver: used 'http://db.apache.org/torque/dtd/data
>base_3_0_1.dtd'
>
>sql-template:
>
>create-db-check:
>
>create-db:
>[torque-data-model] Using classpath
>[torque-data-model] Generating to file C:\Users\karthiksg\Desktop\webapp-sample\
>webapp-sample\build\database\create-db.sql
>[torque-data-model] 2008-11-09 17:51:25,378 [main] INFO org.apache.torque.engin
>e.database.transform.XmlToAppData - Parsing file: 'ojbcore-schema.xml'
>[torque-data-model] 2008-11-09 17:51:25,392 [main] INFO org.apache.torque.engin
>e.database.transform.DTDResolver - Resolver: used database.dtd from 'org.apache.
>torque.engine.database.transform' package
>[torque-data-model] 2008-11-09 17:51:25,457 [main] INFO org.apache.torque.engin
>e.database.transform.XmlToAppData - Parsing file: 'project-schema.xml'
>[torque-data-model] 2008-11-09 17:51:25,463 [main] INFO org.apache.torque.engin
>e.database.transform.DTDResolver - Resolver: used 'http://db.apache.org/torque/d
>td/database_3_0_1.dtd'
>
>shutdown-check:
>
>append-shutdown-sql:
>[echo]
>[echo] Executing the create-db.sql script ...
>[echo]
>[sql] Executing resource: C:\Users\karthiksg\Desktop\webapp-sample\webapp-
>sample\build\database\create-db.sql
>[sql] 2 of 2 SQL statements executed successfully
>
>insert-sql:
>
>shutdown-check:
>
>add-shutdown-sql-file:
>[torque-sql-exec] Our new url -> jdbc:mysql://localhost:3306/productmanager
>[torque-sql-exec] Executing file: C:\Users\karthiksg\Desktop\webapp-sample\webap
>p-sample\build\database\project-schema.sql
>[torque-sql-exec] Failed to execute: # -----------------------------------------
>------------------------------
>[torque-sql-exec] # Product # -------------------------------------------------
>----------------------
>[torque-sql-exec] drop table if exists Product; CREATE TABLE Product ( id INTEG
>ER NOT NULL, name VARCHAR (100), price FLOAT, stock INTEGER, PRIMARY KEY(id) );
>[torque-sql-exec] com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You
>have an error in your SQL syntax; check the manual that corresponds to your MyS
>QL server version for the right syntax to use near 'CREATE TABLE Product ( id IN
>TEGER NOT NULL, name VARCHAR (100), price FLOAT, sto' at line 3
>[torque-sql-exec] Executing file: C:\Users\karthiksg\Desktop\webapp-sample\webap
>p-sample\build\database\ojbcore-schema.sql
>[torque-sql-exec] Failed to execute: # -----------------------------------------
>------------------------------
>[torque-sql-exec] # OJB_HL_SEQ # ----------------------------------------------
>-------------------------
>[torque-sql-exec] drop table if exists OJB_HL_SEQ; CREATE TABLE OJB_HL_SEQ ( TA
>BLENAME VARCHAR (250) NOT NULL, MAX_KEY BIGINT, GRAB_SIZE INTEGER, VERSION INTEG
>ER, PRIMARY KEY(TABLENAME) ); # ------------------------------------------------
>-----------------------
>[torque-sql-exec] # OJB_NRM # -------------------------------------------------
>----------------------
>[torque-sql-exec] drop table if exists OJB_NRM; CREATE TABLE OJB_NRM ( NAME VAR
>CHAR (250) NOT NULL, OID_ LONGBLOB, PRIMARY KEY(NAME) ); # ---------------------
>--------------------------------------------------
>[torque-sql-exec] # OJB_DLIST # -----------------------------------------------
>------------------------
>[torque-sql-exec] drop table if exists OJB_DLIST; CREATE TABLE OJB_DLIST ( ID I
>NTEGER NOT NULL, SIZE_ INTEGER, PRIMARY KEY(ID) ); # ---------------------------
>--------------------------------------------
>[torque-sql-exec] # OJB_DLIST_ENTRIES # ---------------------------------------
>--------------------------------
>[torque-sql-exec] drop table if exists OJB_DLIST_ENTRIES; CREATE TABLE OJB_DLIS
>T_ENTRIES ( ID INTEGER NOT NULL, DLIST_ID INTEGER NOT NULL, POSITION_ INTEGER, O
>ID_ LONGBLOB, PRIMARY KEY(ID) ); # ---------------------------------------------
>--------------------------
>[torque-sql-exec] # OJB_DSET # ------------------------------------------------
>-----------------------
>[torque-sql-exec] drop table if exists OJB_DSET; CREATE TABLE OJB_DSET ( ID INT
>EGER NOT NULL, SIZE_ INTEGER, PRIMARY KEY(ID) ); # -----------------------------
>------------------------------------------
>[torque-sql-exec] # OJB_DSET_ENTRIES # ----------------------------------------
>-------------------------------
>[torque-sql-exec] drop table if exists OJB_DSET_ENTRIES; CREATE TABLE OJB_DSET_
>ENTRIES ( ID INTEGER NOT NULL, DLIST_ID INTEGER NOT NULL, POSITION_ INTEGER, OID
>_ LONGBLOB, PRIMARY KEY(ID) ); # -----------------------------------------------
>------------------------
>[torque-sql-exec] # OJB_DMAP # ------------------------------------------------
>-----------------------
>[torque-sql-exec] drop table if exists OJB_DMAP; CREATE TABLE OJB_DMAP ( ID INT
>EGER NOT NULL, SIZE_ INTEGER, PRIMARY KEY(ID) ); # -----------------------------
>------------------------------------------
>[torque-sql-exec] # OJB_DMAP_ENTRIES # ----------------------------------------
>-------------------------------
>[torque-sql-exec] drop table if exists OJB_DMAP_ENTRIES; CREATE TABLE OJB_DMAP_
>ENTRIES ( ID INTEGER NOT NULL, DMAP_ID INTEGER NOT NULL, KEY_OID LONGBLOB, VALUE
>_OID LONGBLOB, PRIMARY KEY(ID) );
>[torque-sql-exec] com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You
>have an error in your SQL syntax; check the manual that corresponds to your MyS
>QL server version for the right syntax to use near 'CREATE TABLE OJB_HL_SEQ ( TA
>BLENAME VARCHAR (250) NOT NULL, MAX_KEY BIGINT, GRAB' at line 3
>[torque-sql-exec] 0 of 2 SQL statements executed successfully
>
>copy-database-files:
>
>BUILD SUCCESSFUL
>Total time: 46 seconds
>
>I can see the files build\database\createdb.sql ,
>build\database\ojbcore-schema.sql ,
>build\database\project-schema.sql
>
>I can copy the statements from the .sql listed above and execute successfully in mysql command
>window.
>
>kindly do the needfull.
Make Yahoo!7 your homepage and win a trip to the Quiksilver Pro. Find out more
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org