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 Ribi Roland <Ro...@braunvieh.ch> on 2004/03/17 07:43:20 UTC
RE: mssql: sqlexception
Hi
I had also a problem with java.util.Date on a Oracle DB. I had to change the
types in my classes to java.sql.Date and it works fine now.
An other way could be a conversion-class for java.util.Date (like
Integer2IntegerFieldConversion) which creates a java.sql.Date from
java.util.Date.
Let me now if it solves your problem.
Roland Ribi
> -----Original Message-----
> From: Pedro Salgado [mailto:salgado.pc@04web.com]
> Sent: Thursday, January 01, 1970 2:47 AM
> To: OJB Users List
> Subject: mssql: sqlexception
>
>
>
> For several reasons, I have been using a OJB/MySQL
> development platform
> and everything was going ok. The problem was when I chose to
> test the same
> application with a OJB/MSSQL platform.
>
>
> Apart of some minor changes, I made it all work except for
> a class (the
> source and OJB description is below) that has a
> TIMESTAMP/DATETIME mapping
> and that also could be NULL (also tried with NOT NULL <=>
> nullable="false"
> and didn't work either).
> I tried Google and it seems a MSSQL JDBC driver bug (don't
> know if it
> could be a bug in OJB) but I wonder if anyone has came across
> with this
> problem and solved this or found a way around (please don't
> tell me I have
> to store the day-month-year on 3 database fields for each
> date :( or change
> it to a char/varchar field).
>
> Below is the error (log file) the DAO, OJB descriptor and
> SQL for the
> class in question.
>
>
> All help/ideas are welcome,
>
>
> Pedro Salgado
>
>
>
> --- LOG FILE
>
> 12:46:34,503 - DEBUG
> org.apache.ojb.broker.accesslayer.StatementManager -
> closeResources was called
>
>
> 12:46:34,503 - DEBUG
> org.apache.ojb.broker.accesslayer.JdbcAccessImpl -
> executeInsert : pkg.pkg.dao.Revision@144b9ff
>
>
> 12:46:34,503 - DEBUG
> org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl
> - SQL:INSERT
> INTO REVISIONS
> (id,document,title,edition,date_implementation,date_issued,dat
> e_approval,dat
> e_approval_qa,date_authorization,date_cancelled,date_deadline,
> originator,app
> rover,approver_qa,authorizer,canceller,obs,state,sys_extension) VALUES
> (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
>
>
> 12:46:34,519 - ERROR
> org.apache.ojb.broker.accesslayer.JdbcAccessImpl -
> SQLException during the execution of the insert (for a
> pkg.pkg.dao.Revision): [Microsoft][SQLServer 2000 Driver for
> JDBC]Unable to
> determine the type of the specified object.
>
>
> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
> JDBC]Unable to
> determine the type of the specified object.
> at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown
> Source)
> at
> com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
> at com.microsoft.jdbc.base.BasePreparedStatement.setObject(Unknown
> Source)
> at
> org.apache.ojb.broker.platforms.PlatformDefaultImpl.setObjectF
> orStatement(Un
> known Source)
> at
> org.apache.ojb.broker.accesslayer.StatementManager.bindInsert(Unknown
> Source)
> at
> org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeInsert(Unknown
> Source)
> at
> org.apache.ojb.broker.core.PersistenceBrokerImpl.storeToDb(Unknown
> Source)
>
>
> --- Java class
> (Date => java.util.Date)
>
> private int id = 0;
> private int document = 0;
> private String Title = null;
> private String Edition = null;
> private Date dateImplementation = null;
> private Date dateIssued = null;
> private Date dateApproval = null;
> private Date dateApprovalQA = null;
> private Date dateAuthorization = null;
> private Date dateCancelled = null;
> private Date dateDeadline = null;
> private int originator = 0;
> private int approver = 0;
> private int approverQA = 0;
> private int authorizer = 0;
> private int canceller = 0;
> private String comment = null;
> private int State = 0;
> private String fileExtension = null;
>
>
>
> --- MSSQL SQL
>
>
> IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND
> name='REVISIONS_FK_1')
> ALTER TABLE REVISIONS DROP CONSTRAINT REVISIONS_FK_1;
> IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name
> = 'REVISIONS')
> BEGIN
> DECLARE @reftable_7 nvarchar(60), @constraintname_7 nvarchar(60)
> DECLARE refcursor CURSOR FOR
> select reftables.name tablename, cons.name constraintname
> from sysobjects tables,
> sysobjects reftables,
> sysobjects cons,
> sysreferences ref
> where tables.id = ref.rkeyid
> and cons.id = ref.constid
> and reftables.id = ref.fkeyid
> and tables.name = 'REVISIONS'
> OPEN refcursor
> FETCH NEXT from refcursor into @reftable_7, @constraintname_7
> while @@FETCH_STATUS = 0
> BEGIN
> exec ('alter table '+@reftable_7+' drop constraint
> '+@constraintname_7)
> FETCH NEXT from refcursor into @reftable_7, @constraintname_7
> END
> CLOSE refcursor
> DEALLOCATE refcursor
> DROP TABLE REVISIONS
> END
>
>
> CREATE TABLE REVISIONS
> (
> id INT NOT NULL,
> document INT NOT NULL,
> title VARCHAR (200) NOT NULL,
> edition CHAR (9) NULL,
> date_implementation DATETIME NULL,
> date_issued DATETIME NULL,
> date_approval DATETIME NULL,
> date_approval_qa DATETIME NULL,
> date_authorization DATETIME NULL,
> date_cancelled DATETIME NULL,
> date_deadline DATETIME NULL,
> originator INT NULL,
> approver INT NULL,
> approver_qa INT NULL,
> authorizer INT NULL,
> canceller INT NULL,
> obs VARCHAR (200) NULL,
> state INT NOT NULL,
> sys_extension CHAR (4) NULL,
>
> CONSTRAINT REVISIONS_PK PRIMARY KEY(id)
>
> );
>
>
>
>
>
>
> --- OJB class descriptor
>
>
> <class-descriptor
> class="pkg.pkg.dao.Revision"
> table="REVISIONS"
> >
>
> <field-descriptor
> id="1"
> name="id"
> column="id"
> jdbc-type="INTEGER"
> nullable="false"
> primarykey="true"
> autoincrement="true" />
>
>
> <field-descriptor
> id="2"
> name="document"
> column="document"
> jdbc-type="INTEGER"
> nullable="false" />
>
>
> <field-descriptor
> id="3"
> name="Title"
> column="title"
> jdbc-type="VARCHAR"
> nullable="false" />
>
>
> <field-descriptor
> id="4"
> name="Edition"
> column="edition"
> jdbc-type="CHAR" />
>
>
> <field-descriptor
> id="5"
> name="dateImplementation"
> column="date_implementation"
> jdbc-type="TIMESTAMP" />
>
>
> <field-descriptor
> id="6"
> name="dateIssued"
> column="date_issued"
> jdbc-type="TIMESTAMP" />
>
>
> <field-descriptor
> id="7"
> name="dateApproval"
> column="date_approval"
> jdbc-type="TIMESTAMP" />
>
>
> <field-descriptor
> id="8"
> name="dateApprovalQA"
> column="date_approval_qa"
> jdbc-type="TIMESTAMP" />
>
>
> <field-descriptor
> id="9"
> name="dateAuthorization"
> column="date_authorization"
> jdbc-type="TIMESTAMP"/>
>
>
> <field-descriptor
> id="10"
> name="dateCancelled"
> column="date_cancelled"
> jdbc-type="TIMESTAMP" />
>
>
> <field-descriptor
> id="11"
> name="dateDeadline"
> column="date_deadline"
> jdbc-type="TIMESTAMP" />
>
>
> <field-descriptor
> id="12"
> name="originator"
> column="originator"
> jdbc-type="INTEGER" />
>
>
>
> <field-descriptor
> id="13"
> name="approver"
> column="approver"
> jdbc-type="INTEGER "/>
>
>
> <field-descriptor
> id="14"
> name="approverQA"
> column="approver_qa"
> jdbc-type="INTEGER" />
>
>
> <field-descriptor
> id="15"
> name="authorizer"
> column="authorizer"
> jdbc-type="INTEGER" />
>
>
>
> <field-descriptor
> id="16"
> name="canceller"
> column="canceller"
> jdbc-type="INTEGER" />
>
>
> <field-descriptor
> id="17"
> name="comment"
> column="obs"
> jdbc-type="VARCHAR" />
>
>
> <field-descriptor
> id="18"
> name="State"
> column="state"
> jdbc-type="INTEGER"
> nullable="false" />
>
>
> <field-descriptor
> id="19"
> name="fileExtension"
> column="sys_extension"
> jdbc-type="CHAR" />
>
> </class-descriptor>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
RE: mssql: sqlexception
Posted by sa...@04web.com.
> Hi
>
> I had also a problem with java.util.Date on a Oracle DB. I had to change
> the types in my classes to java.sql.Date and it works fine now.
>
> An other way could be a conversion-class for java.util.Date (like
> Integer2IntegerFieldConversion) which creates a java.sql.Date from
> java.util.Date.
>
> Let me now if it solves your problem.
>
Thank you Roland, I will try this and send a reply to the newsletter.
Pedro Salgado
> Roland Ribi
>
>
>> -----Original Message-----
>> From: Pedro Salgado [mailto:salgado.pc@04web.com]
>> Sent: Thursday, January 01, 1970 2:47 AM
>> To: OJB Users List
>> Subject: mssql: sqlexception
>>
>>
>>
>> For several reasons, I have been using a OJB/MySQL
>> development platform
>> and everything was going ok. The problem was when I chose to
>> test the same
>> application with a OJB/MSSQL platform.
>>
>>
>> Apart of some minor changes, I made it all work except for
>> a class (the
>> source and OJB description is below) that has a
>> TIMESTAMP/DATETIME mapping
>> and that also could be NULL (also tried with NOT NULL <=>
>> nullable="false"
>> and didn't work either).
>> I tried Google and it seems a MSSQL JDBC driver bug (don't
>> know if it
>> could be a bug in OJB) but I wonder if anyone has came across
>> with this
>> problem and solved this or found a way around (please don't
>> tell me I have
>> to store the day-month-year on 3 database fields for each
>> date :( or change
>> it to a char/varchar field).
>>
>> Below is the error (log file) the DAO, OJB descriptor and
>> SQL for the
>> class in question.
>>
>>
>> All help/ideas are welcome,
>>
>>
>> Pedro Salgado
>>
>>
>>
>> --- LOG FILE
>>
>> 12:46:34,503 - DEBUG
>> org.apache.ojb.broker.accesslayer.StatementManager -
>> closeResources was called
>>
>>
>> 12:46:34,503 - DEBUG
>> org.apache.ojb.broker.accesslayer.JdbcAccessImpl -
>> executeInsert : pkg.pkg.dao.Revision@144b9ff
>>
>>
>> 12:46:34,503 - DEBUG
>> org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl
>> - SQL:INSERT
>> INTO REVISIONS
>> (id,document,title,edition,date_implementation,date_issued,dat
>> e_approval,dat
>> e_approval_qa,date_authorization,date_cancelled,date_deadline,
>> originator,app
>> rover,approver_qa,authorizer,canceller,obs,state,sys_extension) VALUES
>> (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
>>
>>
>> 12:46:34,519 - ERROR
>> org.apache.ojb.broker.accesslayer.JdbcAccessImpl -
>> SQLException during the execution of the insert (for a
>> pkg.pkg.dao.Revision): [Microsoft][SQLServer 2000 Driver for
>> JDBC]Unable to
>> determine the type of the specified object.
>>
>>
>> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
>> JDBC]Unable to
>> determine the type of the specified object.
>> at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown
>> Source)
>> at
>> com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
>> at com.microsoft.jdbc.base.BasePreparedStatement.setObject(Unknown
>> Source)
>> at
>> org.apache.ojb.broker.platforms.PlatformDefaultImpl.setObjectF
>> orStatement(Un
>> known Source)
>> at
>> org.apache.ojb.broker.accesslayer.StatementManager.bindInsert(Unknown
>> Source)
>> at
>> org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeInsert(Unknown
>> Source)
>> at
>> org.apache.ojb.broker.core.PersistenceBrokerImpl.storeToDb(Unknown
>> Source)
>>
>>
>> --- Java class
>> (Date => java.util.Date)
>>
>> private int id = 0;
>> private int document = 0;
>> private String Title = null;
>> private String Edition = null;
>> private Date dateImplementation = null;
>> private Date dateIssued = null;
>> private Date dateApproval = null;
>> private Date dateApprovalQA = null;
>> private Date dateAuthorization = null;
>> private Date dateCancelled = null;
>> private Date dateDeadline = null;
>> private int originator = 0;
>> private int approver = 0;
>> private int approverQA = 0;
>> private int authorizer = 0;
>> private int canceller = 0;
>> private String comment = null;
>> private int State = 0;
>> private String fileExtension = null;
>>
>>
>>
>> --- MSSQL SQL
>>
>>
>> IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND
>> name='REVISIONS_FK_1')
>> ALTER TABLE REVISIONS DROP CONSTRAINT REVISIONS_FK_1;
>> IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name
>> = 'REVISIONS')
>> BEGIN
>> DECLARE @reftable_7 nvarchar(60), @constraintname_7 nvarchar(60)
>> DECLARE refcursor CURSOR FOR
>> select reftables.name tablename, cons.name constraintname
>> from sysobjects tables,
>> sysobjects reftables,
>> sysobjects cons,
>> sysreferences ref
>> where tables.id = ref.rkeyid
>> and cons.id = ref.constid
>> and reftables.id = ref.fkeyid
>> and tables.name = 'REVISIONS'
>> OPEN refcursor
>> FETCH NEXT from refcursor into @reftable_7, @constraintname_7
>> while @@FETCH_STATUS = 0
>> BEGIN
>> exec ('alter table '+@reftable_7+' drop constraint
>> '+@constraintname_7)
>> FETCH NEXT from refcursor into @reftable_7, @constraintname_7
>> END
>> CLOSE refcursor
>> DEALLOCATE refcursor
>> DROP TABLE REVISIONS
>> END
>>
>>
>> CREATE TABLE REVISIONS
>> (
>> id INT NOT NULL,
>> document INT NOT NULL,
>> title VARCHAR (200) NOT NULL,
>> edition CHAR (9) NULL,
>> date_implementation DATETIME NULL,
>> date_issued DATETIME NULL,
>> date_approval DATETIME NULL,
>> date_approval_qa DATETIME NULL,
>> date_authorization DATETIME NULL,
>> date_cancelled DATETIME NULL,
>> date_deadline DATETIME NULL,
>> originator INT NULL,
>> approver INT NULL,
>> approver_qa INT NULL,
>> authorizer INT NULL,
>> canceller INT NULL,
>> obs VARCHAR (200) NULL,
>> state INT NOT NULL,
>> sys_extension CHAR (4) NULL,
>>
>> CONSTRAINT REVISIONS_PK PRIMARY KEY(id)
>>
>> );
>>
>>
>>
>>
>>
>>
>> --- OJB class descriptor
>>
>>
>> <class-descriptor
>> class="pkg.pkg.dao.Revision"
>> table="REVISIONS"
>> >
>>
>> <field-descriptor
>> id="1"
>> name="id"
>> column="id"
>> jdbc-type="INTEGER"
>> nullable="false"
>> primarykey="true"
>> autoincrement="true" />
>>
>>
>> <field-descriptor
>> id="2"
>> name="document"
>> column="document"
>> jdbc-type="INTEGER"
>> nullable="false" />
>>
>>
>> <field-descriptor
>> id="3"
>> name="Title"
>> column="title"
>> jdbc-type="VARCHAR"
>> nullable="false" />
>>
>>
>> <field-descriptor
>> id="4"
>> name="Edition"
>> column="edition"
>> jdbc-type="CHAR" />
>>
>>
>> <field-descriptor
>> id="5"
>> name="dateImplementation"
>> column="date_implementation"
>> jdbc-type="TIMESTAMP" />
>>
>>
>> <field-descriptor
>> id="6"
>> name="dateIssued"
>> column="date_issued"
>> jdbc-type="TIMESTAMP" />
>>
>>
>> <field-descriptor
>> id="7"
>> name="dateApproval"
>> column="date_approval"
>> jdbc-type="TIMESTAMP" />
>>
>>
>> <field-descriptor
>> id="8"
>> name="dateApprovalQA"
>> column="date_approval_qa"
>> jdbc-type="TIMESTAMP" />
>>
>>
>> <field-descriptor
>> id="9"
>> name="dateAuthorization"
>> column="date_authorization"
>> jdbc-type="TIMESTAMP"/>
>>
>>
>> <field-descriptor
>> id="10"
>> name="dateCancelled"
>> column="date_cancelled"
>> jdbc-type="TIMESTAMP" />
>>
>>
>> <field-descriptor
>> id="11"
>> name="dateDeadline"
>> column="date_deadline"
>> jdbc-type="TIMESTAMP" />
>>
>>
>> <field-descriptor
>> id="12"
>> name="originator"
>> column="originator"
>> jdbc-type="INTEGER" />
>>
>>
>>
>> <field-descriptor
>> id="13"
>> name="approver"
>> column="approver"
>> jdbc-type="INTEGER "/>
>>
>>
>> <field-descriptor
>> id="14"
>> name="approverQA"
>> column="approver_qa"
>> jdbc-type="INTEGER" />
>>
>>
>> <field-descriptor
>> id="15"
>> name="authorizer"
>> column="authorizer"
>> jdbc-type="INTEGER" />
>>
>>
>>
>> <field-descriptor
>> id="16"
>> name="canceller"
>> column="canceller"
>> jdbc-type="INTEGER" />
>>
>>
>> <field-descriptor
>> id="17"
>> name="comment"
>> column="obs"
>> jdbc-type="VARCHAR" />
>>
>>
>> <field-descriptor
>> id="18"
>> name="State"
>> column="state"
>> jdbc-type="INTEGER"
>> nullable="false" />
>>
>>
>> <field-descriptor
>> id="19"
>> name="fileExtension"
>> column="sys_extension"
>> jdbc-type="CHAR" />
>>
>> </class-descriptor>
>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>
> --------------------------------------------------------------------- To
> unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org