We seem to have found the problem.
In ImageMapBuilder.java, the following line was added:
tMap.setPrimaryKeyMethodInfo("SEQ_IMAGE");
I'm not sure if this is something that is normally included in the
Torque generated classes, but in this particular case it wasn't there.
Thanks for your help and suggestions. Using P6Spy allowed us to track
this down.
Markus F. Hay
Software Engineer
SecureInfo Corporation
211 N. Loop 1604 E.
Suite 200
San Antonio, Texas 78232
210.445.6613 (cell)
210.403.5702 (fax)
www.secureinfo.com
This message may contain confidential and/or legally privileged
information. If it has been sent to you in error, please reply
immediately to advise the sender of the error and then destroy this
message, any copies of this message and any printout of this message. If
you are not the intended recipient of the message, any unauthorized
dissemination, distribution or copying of the material in this message,
and any attachments to the message, is strictly forbidden.
-----Original Message-----
From: Thomas Fischer [mailto:[EMAIL PROTECTED]
Sent: Friday, March 10, 2006 11:50 AM
To: Apache Torque Users List
Subject: RE: Error using Oracle + Torque 3.1 (ORA-00923)
"Hay, Markus" <[EMAIL PROTECTED]> schrieb am 10.03.2006
17:24:28:
> I did as you suggested and added in P6Spy. The second line is what
> appears to be the problem:
>
> 1142005304218|15|1|statement||SELECT IMAGE.IMAGE_ID, IMAGE.GUID,
> IMAGE.FLAVOR_ID, IMAGE.MODIFIED_DATE, IMAGE.DEPRECATED FROM IMAGE
> ORDER BY IMAGE.IMAGE_ID ASC
> 1142005304234|16|2|statement||select null.nextval from dual
>
> null.nextval should be SEQ_IMAGE.nextval, so what would cause this to
> happen?
I am not too familiar with the 3.1 code, because this changed between
Torque 3.1 and Torque 3.2.
Remote diagnosis is very difficult at this point because the "Trigger"
SQL statement seems to indicate that you have customized Torque (I did
not find the word "Trigger" in any templates for 3.1.1, and I doubt very
much that it appears in 3.1 templates). Somehow, Torque fails to
retrieve the name of the trigger; no idea why. You would need to debug
into the
BasePeer.doInsert(Criteria,connection) method in the Torque library to
find out more.
> Also, the defaultIdMethod is set to native, and from doing a little
> bit of reading, it appears that even if it is changed to sequence it
> still defaults to native. That is according to what I found here:
> -
> http://mail-archives.apache.org/mod_mbox/jakarta-turbine-user/200110.m
> bo x/[EMAIL PROTECTED]
> -
> http://mail-archives.apache.org/mod_mbox/jakarta-turbine-user/200110.m
> bo x/[EMAIL PROTECTED]
>
Yes, "sequence" and "native" are the same id method in the case of
oracle.
> I should state that I'm not too terribly familiar with how triggers
> work and our DB person is out. What I did do is compare the create
> trigger/sequence statements for the IMAGE table to that of another
> table that had content successfully imported and the syntax is
identical.
> Additionally, I modified ImageMapBuilder.java and set the primary key
> method to sequence manually to see if this would make a difference
> (tMap.setPrimaryKeyMethod(TableMap.SEQUENCE)). Same result. Any
> suggestions?
No, nothing like this will help. Torque asks the key from the sequence
the key in a separate statement, which fails and throws the exception,
which will happen even if the key is generated automatically (because
Torque does not know it is generated automatically.). What you can do is
to set the IdMethod to noIdMewthod (look into the dtd what the value for
the IdMethod
is) and rely on your trigger to generate the correct id.
Thomas
> -----Original Message-----
> From: Thomas Fischer [mailto:[EMAIL PROTECTED]
> Sent: Friday, March 10, 2006 1:41 AM
> To: Apache Torque Users List
> Subject: RE: Error using Oracle + Torque 3.1 (ORA-00923)
>
> A few things come to my mind:
> - It would be most helpful if the offending SQL statement would be
> known.
> You can e.g. use P6Spy to print out the sql statements issued by
Torque.
> See
> http://wiki.apache.org/db-torque/FrequentlyAskedQuestions#head-46f663e
> 01
> bea72682fdc5f60ae02f2a47de29fec
>
> Only if this does not help:
> - You have got a blob in your table. Blobs in oracle are not supported
> in the data access layer which is used by torque (village). There is a
> custom village library available for oracle, read the oracle howto on
> the Torque homepage.
> - I am not so sure with Torque 3.1, but looking at the trigger code,
> it does not seem to be generated by torque this way. This might also
> cause the problem. Which IdMethod are you using ?
>
> Thomas
>
> "Hay, Markus" <[EMAIL PROTECTED]> schrieb am 09.03.2006
> 23:55:22:
>
> > Hello.
> >
> > We're using Torque 3.1 with our application and the following
> > TorqueException is being thrown when we are importing application
> > content into the database (note that everything works fine when
> > using
> > MSSQL):
> >
> > [Stack_Trace] =
> > org.apache.torque.util.BasePeer.throwTorqueException(BasePeer.java:2
> > 36
> > )
> > org.apache.torque.util.BasePeer.doInsert(BasePeer.java:730)
> > com.secureinfo.flatties.om.BaseImagePeer.doInsert(BaseImagePeer.java
> > :2
> > 39
> > )
> > com.secureinfo.flatties.om.BaseImagePeer.doInsert(BaseImagePeer.java
> > :6
> > 01
> > )
> > com.secureinfo.flatties.om.BaseImage.save(BaseImage.java:644)
> > com.secureinfo.baboon.export.db.ObjectInjector.injectPrimaryObject(O
> > bj
> > ec
> > tInjector.java:628)
> > com.secureinfo.baboon.export.db.ObjectInjector.inject(ObjectInjector
> > .j
> > av
> > a:390)
> >
com.secureinfo.baboon.export.xml.DBImporter.importXML(DBImporter.java:
> > 45
> > 0)
> > com.secureinfo.baboon.export.xml.DBImporter.injectClasses(DBImporter
> > .j
> > av
> > a:337)
> >
com.secureinfo.baboon.export.xml.DBImporter.runImport(DBImporter.java:
> > 24
> > 7)
> > com.secureinfo.baboon.export.ui.ContentAdminFramework.performImport(
> > Co
> > nt
> > entAdminFramework.java:596)
> > com.secureinfo.baboon.export.ui.ContentAdminFramework.prepImport(Con
> > te
> > nt
> > AdminFramework.java:525)
> > com.secureinfo.baboon.export.ui.ContentAdminFramework.locateData(Con
> > te
> > nt
> > AdminFramework.java:298)
> > com.secureinfo.baboon.export.ui.ContentAdminFramework.postTaskConfig
> > (C
> > on
> > tentAdminFramework.java:247)
> > com.secureinfo.baboon.export.ui.ContentAdminFramework.doRun(ContentA
> > dm
> > in
> > Framework.java:175)
> > com.secureinfo.baboon.export.ui.ContentAdminFramework.run(ContentAdm
> > in
> > Fr
> > amework.java:114)
> > com.secureinfo.baboon.export.ui.DBMain.invoke(DBMain.java:157)
> > com.secureinfo.baboon.export.ui.DBMain.main(DBMain.java:262)
> > sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> > sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
> > sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
> > java.lang.reflect.Method.invoke(Unknown Source)
> > BootStrapContent.main(BootStrapContent.java:54)
> > sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> > sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
> > sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
> > java.lang.reflect.Method.invoke(Unknown Source)
> > com.zerog.lax.LAX.launch(DashoA8113)
> > com.zerog.lax.LAX.main(DashoA8113)
> > org.apache.torque.TorqueException: ORA-00923: FROM keyword not found
> > where expected
> >
> >
> > ** The following is what we are using in our Torque XML file that
> > generates the classes (for the particluar table that is causing the
> > above error):
> > <table name="IMAGE" idMethod="native">
> > <column primaryKey="true" required="true" type="INTEGER"
> > name="IMAGE_ID"/>
> > <column type="BINARY" name="DATA"/>
> > <column type="DATE" name="MODIFIED_DATE"/>
> > <column type="VARCHAR" name="MODIFIED_USER_NAME"/>
> > <column type="VARCHAR" name="MODIFIED_DOMAIN_NAME"/>
> > <column type="INTEGER" javaType="object" name="FLAVOR_ID"/>
> > <column type="VARCHAR" name="GUID"/>
> > <column type="BIT" javaType="object" name="DEPRECATED"/>
> > <column type="INTEGER" javaType="object"
> name="DEPRECATED_BY"/>
> > <column type="CHAR" name="image_hash"/>
> > <column type="VARCHAR" name="image_name"/> </table>
> >
> > ** The table is created as follows (through various scripts during
> > the
>
> > installation process):
> > CREATE TABLE IMAGE (
> > IMAGE_ID INT NOT NULL,
> > DATA BLOB NULL,
> > MODIFIED_DATE TIMESTAMP NULL,
> > MODIFIED_USER_NAME VARCHAR2 (50) NULL,
> > MODIFIED_DOMAIN_NAME VARCHAR2 (50) NULL,
> > FLAVOR_ID INT NULL,
> > GUID VARCHAR2 (256) NULL,
> > DEPRECATED SMALLINT NULL,
> > DEPRECATED_BY INT NULL,
> > image_hash char(32) null,
> > image_name varchar(50) null
> > )
> >
> > ALTER TABLE IMAGE ADD CONSTRAINT XK_IMAGE PRIMARY KEY (IMAGE_ID);
> >
> > CREATE SEQUENCE SEQ_IMAGE START WITH 1 INCREMENT BY 1;
> >
> > CREATE OR REPLACE TRIGGER IMAGE_IDENTITY BEFORE INSERT ON "IMAGE"
> > FOR EACH ROW BEGIN
> > IF :NEW.IMAGE_ID IS NULL THEN
> > SELECT NVL(:NEW.IMAGE_ID, SEQ_IMAGE.NEXTVAL)
> > INTO :NEW.IMAGE_ID
> > FROM DUAL;
> > END IF;
> > END;
> >
> > --------
> >
> > Does anyone know what the problem is and how I might fix it? I've
> > searched around but haven't run into a solution for this. Is this an
> > issue with Torque or does something need to change on our end?
> > Thanks in advance.
> >
> > Markus F. Hay
> > Configuration Manager
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
