Hi Mark, > From: Mark Wong [mailto:[EMAIL PROTECTED]] > > I'm having trouble using DATE_STRUCT, I'm hoping someone can offer a > tip. > > Here's the small stored procedure I have: > > create dbproc datetest(out i_pub_date date) as > begin > select i_pub_date > into :i_pub_date > from tpcw.item > where i_id = 1; > end; > [snip ODBC code] > Here's the troublesome error I get: > > SQLSTATE 22008 > [SAP AG][LIBSQLOD SO]Datetime field overflow.
The problem is exactly the same as the one of Thomas Cataldo. Marco answered it yesterday (see below). Regards Thomas ---------------------------------------------- Dr. Thomas K�tter SAP DB, SAP Labs Berlin SAP DB is open source. Get it! www.sapdb.org -----Original Message----- From: Paskamp, Marco Sent: Donnerstag, 28. Februar 2002 15:03 To: 'Thomas Cataldo'; sapdb.general Subject: RE: jdbc and stored procs Hello, the reason for the ConversionExceptionSapDB is a bug in the database kernel. DBProcedures return DATE and TIMESTAMP values always in internal format. But the JDBC-driver expects the ANSI format and throws an exception. Until the bug is fixed the following DBProcedure shows a workaround for the error: CREATE DBPROC protocole_get_date_invent(IN protocole INTEGER, OUT dateinvent VARCHAR(10)) AS SELECT MAX(CHAR(dateinvent,ISO)) FROM DUMMY.DBProcGetDate where protocole=:protocole; FETCH INTO :dateinvent; Now the DBProcedure has a VARCHAR(10) output value instead of a DATE. The function CHAR(date,ISO) converts the date value into a String representing the date value in ANSI-format. You do not have to change the JAVA-code, the getDate() in your code snippet below works also with the modified DBProcedure. Regards, Marco ---------------------------------------------- Marco PASKAMP SAP DB, SAP Labs Berlin > -----Original Message----- > From: Thomas Cataldo [mailto:[EMAIL PROTECTED]] > Sent: Mittwoch, 27. Februar 2002 11:03 > To: sapdb.general > Subject: jdbc and stored procs > > > Hi, > > I've got this simple stored proc: > > CREATE DBPROC protocole_get_date_invent(IN protocole INTEGER, OUT > dateinvent DATE) AS > > SELECT MAX(dateinvent) FROM DUMMY.inventaire where > protocole=:protocole; > FETCH INTO :dateinvent; > > > And this method in java: > > public Date getDateInvent() throws RemoteException { > try { > makeConnection(); > } catch (Exception ex) { > throw new EJBException("Unable to connect to database. " + > ex.getMessage()); > } > try { > CallableStatement cs = con.prepareCall("CALL > protocole_get_date_invent(?, ?)"); > cs.registerOutParameter(2, Types.DATE); > cs.setInt(1, id.intValue()); > System.out.println("Running get_date_invent..."); > cs.execute(); > System.out.println("[OK]"); > con.commit(); > // get return value from stored proc > return cs.getDate(2); > } catch (Exception e) { > throw new RemoteException(e.toString()); > } finally { > try { con.close(); } catch (Exception e) { } > } > } > > > I get the following exeception: > Exception in thread "main" java.rmi.ServerException: RemoteException > occurred in server thread; nested exception is: > javax.transaction.TransactionRolledbackException: > com.sap.dbtech.jdbc.translators.ConversionExceptionSapDB: > Cannot convert > '20020210' to Date; nested exception is: > java.rmi.RemoteException: > com.sap.dbtech.jdbc.translators.ConversionExceptionSapDB: > Cannot convert > '20020210' to Date > > > 20020210 is the value I'm expecting. dateinvent is an sql date in the > inventaire table. > > Thanks in advance, > Tom. > > _______________________________________________ > sapdb.general mailing list > [EMAIL PROTECTED] > http://listserv.sap.com/mailman/listinfo/sapdb.general > _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
