ODBC to Oracle Dates with EP / Help

[Nick Matteucci]

Having lots of trouble with Dates in Oracle.

Basically Oracle stores dates 12-OCT-99 and the DLL / ODBC displays in edit as 10/12/99 so when you edit there are errors.

We have Oracle 8.0.5 / MS Oracle ODBC driver / ASPdb EP and everything else seems to work fine.

Here is the simple EP demo using the DEMO database with Oracle 8 / Personal Oracle. If you go to edit and try to add the date as it is displayed, it will error. You have to change all the dates to DD-MMM-YY to even get it to work.

Please help asap as we have a large client and are delivering the solution to them this week.

Nick

Demo Code you can easily run:


<CENTER>Oracle Date Test. Go to Update and enter a date in standard format (12/12/99) to see the Oracle error. You must change the date to DD-MM-YY to get it to work.<P>
<%Set Mydb = Server.CreateObject(&#34;aspDB.EP&#34
MyDb.dbDSN=&#34;DSN=local; UID=scott; PWD=tiger;&#34;
Mydb.dbDbType=&#34;Oracle&#34;
MyDb.dbUnit = 1
MyDb.DBColor = &#34;4,auto,lightblue&#34;
MyDb.dbImageDir=&#34;images/&#34;
MyDb.dbGridTableTag = &#34;border=3 cellspacing=3 cellpadding=3&#34;
MyDb.DbMode = &#34;Both&#34;
MyDb.dbSQL = &#34;($~)SELECT * FROM SCOTT.EMP&#34;
MyDb.dbNavigation=&#34;top&#34;
MyDb.dbNavigationItem=&#34;top, bottom, next, prev, update,&#34;
MyDb.dbNavigationIcon=&#34;std&#34;

MyDb.dbEditParams=&#34;TableName=SCOTT.EMP,BookMar kFlds=0,TableTag=BORDER=2&#34;
MyDb.dbEditFlds=&#34;1,2,3,4,5,6,7&#34; &#39; Note: You CANNOT include the AutoNumbered UniqueID field!

MyDb.aspDBEP
%>

[Nick Matteucci]

Here is the fix. You need the latest copy of EP and some Oracle PL/SQL to do it.

Look especially at the SQL statment and the silentcmdtext

Nick Matteucci

<%
Set Mydb = Server.CreateObject(&#34;aspDB.EP&#34
Mydb.dbSilentCmdText=&#34;Alter Session Set NLS_DATE_FORMAT=&#39;MM/DD/YYYY&#39;&#34;
MyDb.dbDSN=&#34;DSN=local; UID=scott; PWD=tiger;&#34;
Mydb.dbDbType=&#34;Oracle&#34;
MyDb.dbUnit = 1
MyDb.DBColor = &#34;4,auto,lightblue&#34;
MyDb.dbImageDir=&#34;images/&#34;
MyDb.dbGridTableTag = &#34;border=3 cellspacing=3 cellpadding=3&#34;
MyDb.DbMode = &#34;Both&#34;
MyDb.dbSQL = &#34;($~)SELECT EMP.EMPNO, EMP.ENAME, EMP.JOB, EMP.MGR, TO_CHAR(EMP.HIREDATE, &#39;MM/DD/YYYY&#39 HIREDATE, EMP.SAL, EMP.COMM, EMP.DEPTNO FROM EMP ORDER BY EMP.EMPNO&#34;
MyDb.dbNavigation=&#34;top&#34;
MyDb.dbNavigationItem=&#34;top, bottom, next, prev, update,&#34;
MyDb.dbNavigationIcon=&#34;std&#34;
Mydb.dboptions=&#34;DateFormat=DD-MMM-YY&#34;
MyDb.dbEditParams=&#34;TableName=SCOTT.EMP,BookMar kFlds=0,TableTag=BORDER=2,&#34;
MyDb.dbEditFlds=&#34;1,2,3,4,5,6,7&#34; &#39; Note: You CANNOT include the AutoNumbered UniqueID field!

MyDb.aspDBEP
%>

------------
Nick Matteucci at 12/2/99 12:37:18 AM

Having lots of trouble with Dates in Oracle.

Basically Oracle stores dates 12-OCT-99 and the DLL / ODBC displays in edit as 10/12/99 so when you edit there are errors.

We have Oracle 8.0.5 / MS Oracle ODBC driver / ASPdb EP and everything else seems to work fine.

Here is the simple EP demo using the DEMO database with Oracle 8 / Personal Oracle. If you go to edit and try to add the date as it is displayed, it will error. You have to change all the dates to DD-MMM-YY to even get it to work.

Please help asap as we have a large client and are delivering the solution to them this week.

Nick

Demo Code you can easily run:


<CENTER>Oracle Date Test. Go to Update and enter a date in standard format (12/12/99) to see the Oracle error. You must change the date to DD-MM-YY to get it to work.<P>
<%Set Mydb = Server.CreateObject(&#34;aspDB.EP&#34
MyDb.dbDSN=&#34;DSN=local; UID=scott; PWD=tiger;&#34;
Mydb.dbDbType=&#34;Oracle&#34;
MyDb.dbUnit = 1
MyDb.DBColor = &#34;4,auto,lightblue&#34;
MyDb.dbImageDir=&#34;images/&#34;
MyDb.dbGridTableTag = &#34;border=3 cellspacing=3 cellpadding=3&#34;
MyDb.DbMode = &#34;Both&#34;
MyDb.dbSQL = &#34;($~)SELECT * FROM SCOTT.EMP&#34;
MyDb.dbNavigation=&#34;top&#34;
MyDb.dbNavigationItem=&#34;top, bottom, next, prev, update,&#34;
MyDb.dbNavigationIcon=&#34;std&#34;

MyDb.dbEditParams=&#34;TableName=SCOTT.EMP,BookMar kFlds=0,TableTag=BORDER=2&#34;
MyDb.dbEditFlds=&#34;1,2,3,4,5,6,7&#34; &#39; Note: You CANNOT include the AutoNumbered UniqueID field!

MyDb.aspDBEP
%>

[Frank]

Nick,
Latest version (EP and Pro) takes care of ORACLE&#39;s date automatically. No need to use TO_DATE conversions which makes the code not portable. Don&#39;t even need to specify DateFormat since dbDBType=&#34;ORACLE&#34; will trigger the date conversion to ORACLE&#39;s default date for SQL and the ADO format for display and Edit input. essentially, ignore the ORACLE and treat it like ACCESS.


Frank


------------
Nick Matteucci at 12/2/99 4:38:46 PM

Here is the fix. You need the latest copy of EP and some Oracle PL/SQL to do it.

Look especially at the SQL statment and the silentcmdtext

Nick Matteucci

<%
Set Mydb = Server.CreateObject(&#34;aspDB.EP&#34
Mydb.dbSilentCmdText=&#34;Alter Session Set NLS_DATE_FORMAT=&#39;MM/DD/YYYY&#39;&#34;
MyDb.dbDSN=&#34;DSN=local; UID=scott; PWD=tiger;&#34;
Mydb.dbDbType=&#34;Oracle&#34;
MyDb.dbUnit = 1
MyDb.DBColor = &#34;4,auto,lightblue&#34;
MyDb.dbImageDir=&#34;images/&#34;
MyDb.dbGridTableTag = &#34;border=3 cellspacing=3 cellpadding=3&#34;
MyDb.DbMode = &#34;Both&#34;
MyDb.dbSQL = &#34;($~)SELECT EMP.EMPNO, EMP.ENAME, EMP.JOB, EMP.MGR, TO_CHAR(EMP.HIREDATE, &#39;MM/DD/YYYY&#39 HIREDATE, EMP.SAL, EMP.COMM, EMP.DEPTNO FROM EMP ORDER BY EMP.EMPNO&#34;
MyDb.dbNavigation=&#34;top&#34;
MyDb.dbNavigationItem=&#34;top, bottom, next, prev, update,&#34;
MyDb.dbNavigationIcon=&#34;std&#34;
Mydb.dboptions=&#34;DateFormat=DD-MMM-YY&#34;
MyDb.dbEditParams=&#34;TableName=SCOTT.EMP,BookMar kFlds=0,TableTag=BORDER=2,&#34;
MyDb.dbEditFlds=&#34;1,2,3,4,5,6,7&#34; &#39; Note: You CANNOT include the AutoNumbered UniqueID field!

MyDb.aspDBEP
%>

------------
Nick Matteucci at 12/2/99 12:37:18 AM

Having lots of trouble with Dates in Oracle.

Basically Oracle stores dates 12-OCT-99 and the DLL / ODBC displays in edit as 10/12/99 so when you edit there are errors.

We have Oracle 8.0.5 / MS Oracle ODBC driver / ASPdb EP and everything else seems to work fine.

Here is the simple EP demo using the DEMO database with Oracle 8 / Personal Oracle. If you go to edit and try to add the date as it is displayed, it will error. You have to change all the dates to DD-MMM-YY to even get it to work.

Please help asap as we have a large client and are delivering the solution to them this week.

Nick

Demo Code you can easily run:


<CENTER>Oracle Date Test. Go to Update and enter a date in standard format (12/12/99) to see the Oracle error. You must change the date to DD-MM-YY to get it to work.<P>
<%Set Mydb = Server.CreateObject(&#34;aspDB.EP&#34
MyDb.dbDSN=&#34;DSN=local; UID=scott; PWD=tiger;&#34;
Mydb.dbDbType=&#34;Oracle&#34;
MyDb.dbUnit = 1
MyDb.DBColor = &#34;4,auto,lightblue&#34;
MyDb.dbImageDir=&#34;images/&#34;
MyDb.dbGridTableTag = &#34;border=3 cellspacing=3 cellpadding=3&#34;
MyDb.DbMode = &#34;Both&#34;
MyDb.dbSQL = &#34;($~)SELECT * FROM SCOTT.EMP&#34;
MyDb.dbNavigation=&#34;top&#34;
MyDb.dbNavigationItem=&#34;top, bottom, next, prev, update,&#34;
MyDb.dbNavigationIcon=&#34;std&#34;

MyDb.dbEditParams=&#34;TableName=SCOTT.EMP,BookMar kFlds=0,TableTag=BORDER=2&#34;
MyDb.dbEditFlds=&#34;1,2,3,4,5,6,7&#34; &#39; Note: You CANNOT include the AutoNumbered UniqueID field!

MyDb.aspDBEP
%>

[Nick Matteucci]

The problem with this is that you can&#39;t get the date/timestamp to work correctly your way.

Look at the following 2 examples on your machine. You will see that there needs to be a &#34;dbMagicCellEdit&#34; so that the date / time come up when you hit update. We need to be able to force the formating in edit instead of droping the 4 digit year and the time.

The only way to make it work in view and edit (that I have found) is to redo the SQL with the TO_CHAR. There are 2 samples below. The first one works with date / time and date in EDIT and the second one (your suggestion) does not seem to handle date and time in edit mode.

------------ EXAMPLE 1 : WORKS WITH DATE AND TIME STAMPS ---------

<CENTER>Oracle Date Test.<P>
<%
Set MyDb = Server.CreateObject(&#34;AspDB.EP&#34
response.write(&#34;ASP-db Version: &#34; + Mydb.dbVersion + &#34;<BR>&#34
response.write(&#34;ASP-db License: &#34; + Mydb.dbLicense)


Set Mydb = Server.CreateObject(&#34;aspDB.EP&#34

&#39; Set the Oracle PL/SQL Date Format
Mydb.dbSilentCmdText=&#34;Alter Session Set NLS_DATE_FORMAT=&#39;MM/DD/YYYY HH24:MI:SS&#39;;&#34;

MyDb.dbDSN=&#34;DSN=local; UID=scott; PWD=tiger;&#34;
Mydb.dbDbType=&#34;Oracle&#34;
MyDb.dbUnit = 1

&#39; Set the exact date formatting for displaying the date fields
Mydb.dbMagicCell=&#34;HIREDATE,,Format=[mm/dd/yyyy HH:MM:SS];&#34;

MyDb.DBColor = &#34;4,auto,lightblue&#34;
MyDb.dbImageDir=&#34;images/&#34;
MyDb.dbGridTableTag = &#34;border=3 cellspacing=3 cellpadding=3&#34;
MyDb.DbMode = &#34;Both&#34;

&#39; Set the individual formatting for the date time for each date field. TO_CHAR(column, &#39;format&#39
MyDb.dbSQL = &#34;($~)SELECT EMP.EMPNO, EMP.ENAME, EMP.JOB, EMP.MGR, &#34; &_
&#34;TO_CHAR(EMP.HIREDATE, &#39;MM/DD/YYYY HH24:MI:SS&#39 HIREDATE, &#34; &_
&#34;EMP.SAL, EMP.COMM, EMP.DEPTNO FROM EMP ORDER BY EMP.EMPNO&#34;
MyDb.dbNavigation=&#34;top&#34;
MyDb.dbNavigationItem=&#34;top, bottom, next, prev, update,&#34;
MyDb.dbNavigationIcon=&#34;std&#34;

&#39; Set the Oracle Date/Time standards for edit and add
Mydb.dboptions=&#34;DateFormat=DD-MMM-YY HH24:MI:SS&#34;

MyDb.dbEditParams=&#34;TableName=SCOTT.EMP,BookMar kFlds=0,TableTag=BORDER=2,&#34;
MyDb.dbEditFlds=&#34;1,2,3,4,5,6,7,&#34; &#39; Note: You CANNOT include the AutoNumbered UniqueID field!

MyDb.aspDBEP
%>

------------ EXAMPLE 2 : DOES NOT WORK WITH DATE AND TIME STAMPS ---------

<CENTER>Oracle Date Test.<P>
<%
Set MyDb = Server.CreateObject(&#34;AspDB.EP&#34
response.write(&#34;ASP-db Version: &#34; + Mydb.dbVersion + &#34;<BR>&#34
response.write(&#34;ASP-db License: &#34; + Mydb.dbLicense)

Set Mydb = Server.CreateObject(&#34;aspDB.EP&#34
MyDb.dbDSN=&#34;DSN=local; UID=scott; PWD=tiger;&#34;
Mydb.dbDbType=&#34;Oracle&#34;
MyDb.dbUnit = 1
Mydb.dbMagicCell=&#34;HIREDATE,,Format=[mm/dd/yyyy HH:MM:SS];&#34;
MyDb.DBColor = &#34;4,auto,lightblue&#34;
MyDb.dbImageDir=&#34;images/&#34;
MyDb.dbGridTableTag = &#34;border=3 cellspacing=3 cellpadding=3&#34;
MyDb.DbMode = &#34;Both&#34;

MyDb.dbSQL = &#34;($~)SELECT EMP.EMPNO, EMP.ENAME, EMP.JOB, EMP.MGR, &#34; &_
&#34;EMP.HIREDATE, &#34; &_
&#34;EMP.SAL, EMP.COMM, EMP.DEPTNO FROM EMP ORDER BY EMP.EMPNO&#34;
MyDb.dbNavigation=&#34;top&#34;
MyDb.dbNavigationItem=&#34;top, bottom, next, prev, update,&#34;
MyDb.dbNavigationIcon=&#34;std&#34;

&#39; Tried with and without: Mydb.dboptions=&#34;DateFormat=DD-MMM-YY HH24:MI:SS&#34;

MyDb.dbEditParams=&#34;TableName=SCOTT.EMP,BookMar kFlds=0,TableTag=BORDER=2,&#34;
MyDb.dbEditFlds=&#34;1,2,3,4,5,6,7&#34; &#39; Note: You CANNOT include the AutoNumbered UniqueID field!

MyDb.aspDBEP
%>

------------
Frank at 12/2/99 11:58:44 PM

Nick,
Latest version (EP and Pro) takes care of ORACLE&#39;s date automatically. No need to use TO_DATE conversions which makes the code not portable. Don&#39;t even need to specify DateFormat since dbDBType=&#34;ORACLE&#34; will trigger the date conversion to ORACLE&#39;s default date for SQL and the ADO format for display and Edit input. essentially, ignore the ORACLE and treat it like ACCESS.


Frank


------------
Nick Matteucci at 12/2/99 4:38:46 PM

Here is the fix. You need the latest copy of EP and some Oracle PL/SQL to do it.

Look especially at the SQL statment and the silentcmdtext

Nick Matteucci

<%
Set Mydb = Server.CreateObject(&#34;aspDB.EP&#34
Mydb.dbSilentCmdText=&#34;Alter Session Set NLS_DATE_FORMAT=&#39;MM/DD/YYYY&#39;&#34;
MyDb.dbDSN=&#34;DSN=local; UID=scott; PWD=tiger;&#34;
Mydb.dbDbType=&#34;Oracle&#34;
MyDb.dbUnit = 1
MyDb.DBColor = &#34;4,auto,lightblue&#34;
MyDb.dbImageDir=&#34;images/&#34;
MyDb.dbGridTableTag = &#34;border=3 cellspacing=3 cellpadding=3&#34;
MyDb.DbMode = &#34;Both&#34;
MyDb.dbSQL = &#34;($~)SELECT EMP.EMPNO, EMP.ENAME, EMP.JOB, EMP.MGR, TO_CHAR(EMP.HIREDATE, &#39;MM/DD/YYYY&#39 HIREDATE, EMP.SAL, EMP.COMM, EMP.DEPTNO FROM EMP ORDER BY EMP.EMPNO&#34;
MyDb.dbNavigation=&#34;top&#34;
MyDb.dbNavigationItem=&#34;top, bottom, next, prev, update,&#34;
MyDb.dbNavigationIcon=&#34;std&#34;
Mydb.dboptions=&#34;DateFormat=DD-MMM-YY&#34;
MyDb.dbEditParams=&#34;TableName=SCOTT.EMP,BookMar kFlds=0,TableTag=BORDER=2,&#34;
MyDb.dbEditFlds=&#34;1,2,3,4,5,6,7&#34; &#39; Note: You CANNOT include the AutoNumbered UniqueID field!

MyDb.aspDBEP
%>

------------
Nick Matteucci at 12/2/99 12:37:18 AM

Having lots of trouble with Dates in Oracle.

Basically Oracle stores dates 12-OCT-99 and the DLL / ODBC displays in edit as 10/12/99 so when you edit there are errors.

We have Oracle 8.0.5 / MS Oracle ODBC driver / ASPdb EP and everything else seems to work fine.

Here is the simple EP demo using the DEMO database with Oracle 8 / Personal Oracle. If you go to edit and try to add the date as it is displayed, it will error. You have to change all the dates to DD-MMM-YY to even get it to work.

Please help asap as we have a large client and are delivering the solution to them this week.

Nick

Demo Code you can easily run:


<CENTER>Oracle Date Test. Go to Update and enter a date in standard format (12/12/99) to see the Oracle error. You must change the date to DD-MM-YY to get it to work.<P>
<%Set Mydb = Server.CreateObject(&#34;aspDB.EP&#34
MyDb.dbDSN=&#34;DSN=local; UID=scott; PWD=tiger;&#34;
Mydb.dbDbType=&#34;Oracle&#34;
MyDb.dbUnit = 1
MyDb.DBColor = &#34;4,auto,lightblue&#34;
MyDb.dbImageDir=&#34;images/&#34;
MyDb.dbGridTableTag = &#34;border=3 cellspacing=3 cellpadding=3&#34;
MyDb.DbMode = &#34;Both&#34;
MyDb.dbSQL = &#34;($~)SELECT * FROM SCOTT.EMP&#34;
MyDb.dbNavigation=&#34;top&#34;
MyDb.dbNavigationItem=&#34;top, bottom, next, prev, update,&#34;
MyDb.dbNavigationIcon=&#34;std&#34;

MyDb.dbEditParams=&#34;TableName=SCOTT.EMP,BookMar kFlds=0,TableTag=BORDER=2&#34;
MyDb.dbEditFlds=&#34;1,2,3,4,5,6,7&#34; &#39; Note: You CANNOT include the AutoNumbered UniqueID field!

MyDb.aspDBEP
%>