db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Donald Rosengartner" <DROSENGART...@kc.rr.com>
Subject Schema producing invalid SQL
Date Fri, 03 Jan 2003 03:15:00 GMT
I've attached my relevant schema and output from the torque build (ant -f build-torque.xml
insert-sql)
The database type is mysql.

To clarify the question...
<column name="CC_TYPE" size="10" type="VARCHAR"/>
in the schema is producing SQL...
CC_TYPE DATE (10), 

while other occurrences of varchar are working properly. AND
ARTICLE_TYPE VARCHAR (40),  has ' required="true" '   option, but is not generating the appropriate
SQL and thus throwing this error(below). I can easily rewrite the SQL and manually create
these tables, but thought it might make for good bug hunting. If my own error, I humbly accept
defeat. 

I'll be glad to share the properties files I'm using on request.

PS - I know this was working flawlessly at an earlier date. I've since made changes to the
properties file to include reference to datasource stuff, but thought this was not applicable.
I could have made other changes, like adding tables to the schema, but have cropped out all
tables except the dependencies of FK/PK. and have no recollection of other changes. Thanks
in advance for your help.
***********************************************


<?xml version="1.0" encoding="ISO-8859-1" standalone="no" ?>
<!DOCTYPE app-data SYSTEM
          "http://jakarta.apache.org/turbine/dtd/database.dtd">

<!-- ==================================================================== -->
<!--                                                                      -->
<!-- T U R B I N E  P R O J E C T  S C H E M A                            -->
<!--                                                                      -->
<!-- ==================================================================== -->
<!-- This is an example Turbine project schema.                           -->
<!-- ==================================================================== -->

<app-data>

<database name="fishing">
  <table name="CUSTOMER">
    <column name="CUSTOMER_ID" required="true" autoIncrement="true"
            primaryKey="true" type="INTEGER"/>
    <column name="CUSTOMER_NAME" required="true" size="50" type="VARCHAR"/>
    <column name="RATING" type="INTEGER"/>
    <column name="ADDED" type="DATE"/>
  </table>
  
  <table name="GFEUSER">
    <column name="USER_NAME" required="true"
            primaryKey="true" size="50" type="VARCHAR"/>
    <column name="USER_PWD" required="true" size="50" type="VARCHAR"/>
    <column name="EMAIL_TXT" required="true" size="50" type="VARCHAR"/>
    <column name="USER_RIGHTS" required="true" size="50" type="VARCHAR" default="user"/>
    <column name="GFE_POINTS" type="INTEGER"/>
  </table>
  

  <table name="ORDERS">
    <column name="ORDER_ID" required="true" autoIncrement="true"
         primaryKey="true" type="INTEGER"/>
    <column name="ORDER_DATE" required="true" type="DATE"/>
    <column name="PRIORITY" size="5" type="VARCHAR"/> 
    <column name="AUTOBILL" type="TINYINT"/>    
    <column name="TERM" type="INTEGER" default="-1"/>
    <column name="BEGIN_DATE" type="DATE"/>
    <column name="CC_TYPE" size="10" type="VARCHAR"/>
    <column name="CC_NUM" type="BIGINT"/>
    <column name="CC_EXPIRE" type="DATE"/>
    <column name="CC_NAME" size="75" type="VARCHAR"/>
    <column name="FKCUSTOMER_ID" type="INTEGER"/>
    <index name="ORDER_CUST_IDX">
         <index-column name="FKCUSTOMER_ID"/>
    </index>
    <foreign-key foreignTable="CUSTOMER">
            <reference local="FKCUSTOMER_ID" foreign="CUSTOMER_ID"/>
    </foreign-key>
  </table>
  
  <table name="MISC_ARTICLE">
    <column name="ARTICLE_ID" required="true" autoIncrement="true"
         primaryKey="true" type="INTEGER"/>
    <column name="ARTICLE_TYPE" required="true" 
         primaryKey="true" size="40" type="VARCHAR"/>
    <column name="P_1" required="true" size="250" type="VARCHAR" description="paragraph
text"/>
    <column name="P_2" size="250" type="VARCHAR"/>
    <column name="P_3" size="250" type="VARCHAR"/>
    <column name="P_4" size="250" type="VARCHAR"/>
    <column name="P_5" size="250" type="VARCHAR"/>
    <column name="IMAGE_DIR" required="true" size="70" type="VARCHAR"/>
    <column name="IMAGE_1" size="30" type="VARCHAR"/>
    <column name="IMAGE_2" size="30" type="VARCHAR"/>
    <column name="IMAGE_3" size="30" type="VARCHAR"/>
    <column name="BG_IMAGE" size="30" type="VARCHAR"/>
    <column name="LINK_1" size="150" type="VARCHAR"/>
    <column name="APPROVED" type="TINYINT"/>    
    <column name="ADDED" type="DATE" description="use for VERIFICATION and AUDITS"/>
    <column name="FKPOSTED_BY" required="true" size="50" type="VARCHAR"/>
    <index name="MISC_POSTED_IDX">
        <index-column name="FKPOSTED_BY"/>
    </index>
    <foreign-key foreignTable="GFEUSER">
        <reference local="FKPOSTED_BY" foreign="USER_NAME"/>
    </foreign-key>
  </table>
  
  
</database>

</app-data>





***********************************************
insert-sql:
[torque-sql-exec] Our new url -> jdbc:mysql://localhost:3306/fishing
[torque-sql-exec] Executing file: C:\projects\torque-3.0-rc2\src\sql\id-table-schema.sql
[torque-sql-exec] Executing file: C:\projects\torque-3.0-rc2\src\sql\project-schema.sql
[torque-sql-exec] Failed to execute: CREATE TABLE ORDERS ( ORDER_ID INTEGER NOT NULL AUTO_INCRE
MENT, ORDER_DATE DATE NOT NULL, PRIORITY VARCHAR (5), AUTOBILL INTEGER, TERM INTEGER default
-1
, BEGIN_DATE DATE NOT NULL, CC_TYPE DATE (10), CC_NUM BIGINT, CC_EXPIRE DATE, CC_NAME VARCHAR
(
75), FKCUSTOMER_ID INTEGER, PRIMARY KEY(ORDER_ID), FOREIGN KEY (FKCUSTOMER_ID) REFERENCES
CUSTO
MER (CUSTOMER_ID) )
[torque-sql-exec] java.sql.SQLException: Syntax error or access violation: You have an error
in
 your SQL syntax near '(10), CC_NUM BIGINT, CC_EXPIRE DATE, CC_NAME VARCHAR (75), FKCUSTOMER_ID
 INTEGER' at line 1
[torque-sql-exec] Failed to execute: CREATE TABLE MISC_ARTICLE ( ARTICLE_ID INTEGER NOT NULL
AU
TO_INCREMENT, ARTICLE_TYPE VARCHAR (40), P_1 VARCHAR (250) NOT NULL, P_2 VARCHAR (250), P_3
VAR
CHAR (250), P_4 VARCHAR (250), P_5 VARCHAR (250), IMAGE_DIR VARCHAR (70) NOT NULL, IMAGE_1
VARC
HAR (30), IMAGE_2 VARCHAR (30), IMAGE_3 VARCHAR (30), BG_IMAGE VARCHAR (30), LINK_1 VARCHAR
(15
0), APPROVED INTEGER, ADDED DATE, FKPOSTED_BY VARCHAR (50) NOT NULL, PRIMARY KEY(ARTICLE_ID,ART
ICLE_TYPE), FOREIGN KEY (FKPOSTED_BY) REFERENCES GFEUSER (USER_NAME) )
[torque-sql-exec] java.sql.SQLException: General error: All parts of a PRIMARY KEY must be
NOT
NULL;  If you need NULL in a key, use UNIQUE instead
[torque-sql-exec] 66 of 68 SQL statements executed successfully
[torque-sql-exec] Our new url -> jdbc:mysql://localhost:3306/fishing
[torque-sql-exec] Executing file: C:\projects\torque-3.0-rc2\src\sql\fishing-schema.sql
[torque-sql-exec] Executing file: C:\projects\torque-3.0-rc2\src\sql\fishing-schema-idtable-ini
t.sql
[torque-sql-exec] 78 of 80 SQL statements executed successfully
Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message