-
ODBC to Oracle Dates with EP / Help
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("aspDB.EP"
MyDb.dbDSN="DSN=local; UID=scott; PWD=tiger;"
Mydb.dbDbType="Oracle"
MyDb.dbUnit = 1
MyDb.DBColor = "4,auto,lightblue"
MyDb.dbImageDir="images/"
MyDb.dbGridTableTag = "border=3 cellspacing=3 cellpadding=3"
MyDb.DbMode = "Both"
MyDb.dbSQL = "($~)SELECT * FROM SCOTT.EMP"
MyDb.dbNavigation="top"
MyDb.dbNavigationItem="top, bottom, next, prev, update,"
MyDb.dbNavigationIcon="std"
MyDb.dbEditParams="TableName=SCOTT.EMP,BookMar kFlds=0,TableTag=BORDER=2"
MyDb.dbEditFlds="1,2,3,4,5,6,7" ' Note: You CANNOT include the AutoNumbered UniqueID field!
MyDb.aspDBEP
%>
-
ODBC to Oracle Dates with EP / Help (reply)
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("aspDB.EP"
Mydb.dbSilentCmdText="Alter Session Set NLS_DATE_FORMAT='MM/DD/YYYY'"
MyDb.dbDSN="DSN=local; UID=scott; PWD=tiger;"
Mydb.dbDbType="Oracle"
MyDb.dbUnit = 1
MyDb.DBColor = "4,auto,lightblue"
MyDb.dbImageDir="images/"
MyDb.dbGridTableTag = "border=3 cellspacing=3 cellpadding=3"
MyDb.DbMode = "Both"
MyDb.dbSQL = "($~)SELECT EMP.EMPNO, EMP.ENAME, EMP.JOB, EMP.MGR, TO_CHAR(EMP.HIREDATE, 'MM/DD/YYYY' HIREDATE, EMP.SAL, EMP.COMM, EMP.DEPTNO FROM EMP ORDER BY EMP.EMPNO"
MyDb.dbNavigation="top"
MyDb.dbNavigationItem="top, bottom, next, prev, update,"
MyDb.dbNavigationIcon="std"
Mydb.dboptions="DateFormat=DD-MMM-YY"
MyDb.dbEditParams="TableName=SCOTT.EMP,BookMar kFlds=0,TableTag=BORDER=2,"
MyDb.dbEditFlds="1,2,3,4,5,6,7" ' 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("aspDB.EP"
MyDb.dbDSN="DSN=local; UID=scott; PWD=tiger;"
Mydb.dbDbType="Oracle"
MyDb.dbUnit = 1
MyDb.DBColor = "4,auto,lightblue"
MyDb.dbImageDir="images/"
MyDb.dbGridTableTag = "border=3 cellspacing=3 cellpadding=3"
MyDb.DbMode = "Both"
MyDb.dbSQL = "($~)SELECT * FROM SCOTT.EMP"
MyDb.dbNavigation="top"
MyDb.dbNavigationItem="top, bottom, next, prev, update,"
MyDb.dbNavigationIcon="std"
MyDb.dbEditParams="TableName=SCOTT.EMP,BookMar kFlds=0,TableTag=BORDER=2"
MyDb.dbEditFlds="1,2,3,4,5,6,7" ' Note: You CANNOT include the AutoNumbered UniqueID field!
MyDb.aspDBEP
%>
-
ODBC to Oracle Dates with EP / Help (reply)
Nick,
Latest version (EP and Pro) takes care of ORACLE's date automatically. No need to use TO_DATE conversions which makes the code not portable. Don't even need to specify DateFormat since dbDBType="ORACLE" will trigger the date conversion to ORACLE'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("aspDB.EP"
Mydb.dbSilentCmdText="Alter Session Set NLS_DATE_FORMAT='MM/DD/YYYY'"
MyDb.dbDSN="DSN=local; UID=scott; PWD=tiger;"
Mydb.dbDbType="Oracle"
MyDb.dbUnit = 1
MyDb.DBColor = "4,auto,lightblue"
MyDb.dbImageDir="images/"
MyDb.dbGridTableTag = "border=3 cellspacing=3 cellpadding=3"
MyDb.DbMode = "Both"
MyDb.dbSQL = "($~)SELECT EMP.EMPNO, EMP.ENAME, EMP.JOB, EMP.MGR, TO_CHAR(EMP.HIREDATE, 'MM/DD/YYYY' HIREDATE, EMP.SAL, EMP.COMM, EMP.DEPTNO FROM EMP ORDER BY EMP.EMPNO"
MyDb.dbNavigation="top"
MyDb.dbNavigationItem="top, bottom, next, prev, update,"
MyDb.dbNavigationIcon="std"
Mydb.dboptions="DateFormat=DD-MMM-YY"
MyDb.dbEditParams="TableName=SCOTT.EMP,BookMar kFlds=0,TableTag=BORDER=2,"
MyDb.dbEditFlds="1,2,3,4,5,6,7" ' 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("aspDB.EP"
MyDb.dbDSN="DSN=local; UID=scott; PWD=tiger;"
Mydb.dbDbType="Oracle"
MyDb.dbUnit = 1
MyDb.DBColor = "4,auto,lightblue"
MyDb.dbImageDir="images/"
MyDb.dbGridTableTag = "border=3 cellspacing=3 cellpadding=3"
MyDb.DbMode = "Both"
MyDb.dbSQL = "($~)SELECT * FROM SCOTT.EMP"
MyDb.dbNavigation="top"
MyDb.dbNavigationItem="top, bottom, next, prev, update,"
MyDb.dbNavigationIcon="std"
MyDb.dbEditParams="TableName=SCOTT.EMP,BookMar kFlds=0,TableTag=BORDER=2"
MyDb.dbEditFlds="1,2,3,4,5,6,7" ' Note: You CANNOT include the AutoNumbered UniqueID field!
MyDb.aspDBEP
%>
-
ODBC to Oracle Dates with EP / Help (reply)
The problem with this is that you can'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 "dbMagicCellEdit" 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("AspDB.EP"
response.write("ASP-db Version: " + Mydb.dbVersion + "<BR>"
response.write("ASP-db License: " + Mydb.dbLicense)
Set Mydb = Server.CreateObject("aspDB.EP"
' Set the Oracle PL/SQL Date Format
Mydb.dbSilentCmdText="Alter Session Set NLS_DATE_FORMAT='MM/DD/YYYY HH24:MI:SS';"
MyDb.dbDSN="DSN=local; UID=scott; PWD=tiger;"
Mydb.dbDbType="Oracle"
MyDb.dbUnit = 1
' Set the exact date formatting for displaying the date fields
Mydb.dbMagicCell="HIREDATE,,Format=[mm/dd/yyyy HH:MM:SS];"
MyDb.DBColor = "4,auto,lightblue"
MyDb.dbImageDir="images/"
MyDb.dbGridTableTag = "border=3 cellspacing=3 cellpadding=3"
MyDb.DbMode = "Both"
' Set the individual formatting for the date time for each date field. TO_CHAR(column, 'format'
MyDb.dbSQL = "($~)SELECT EMP.EMPNO, EMP.ENAME, EMP.JOB, EMP.MGR, " &_
"TO_CHAR(EMP.HIREDATE, 'MM/DD/YYYY HH24:MI:SS' HIREDATE, " &_
"EMP.SAL, EMP.COMM, EMP.DEPTNO FROM EMP ORDER BY EMP.EMPNO"
MyDb.dbNavigation="top"
MyDb.dbNavigationItem="top, bottom, next, prev, update,"
MyDb.dbNavigationIcon="std"
' Set the Oracle Date/Time standards for edit and add
Mydb.dboptions="DateFormat=DD-MMM-YY HH24:MI:SS"
MyDb.dbEditParams="TableName=SCOTT.EMP,BookMar kFlds=0,TableTag=BORDER=2,"
MyDb.dbEditFlds="1,2,3,4,5,6,7," ' 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("AspDB.EP"
response.write("ASP-db Version: " + Mydb.dbVersion + "<BR>"
response.write("ASP-db License: " + Mydb.dbLicense)
Set Mydb = Server.CreateObject("aspDB.EP"
MyDb.dbDSN="DSN=local; UID=scott; PWD=tiger;"
Mydb.dbDbType="Oracle"
MyDb.dbUnit = 1
Mydb.dbMagicCell="HIREDATE,,Format=[mm/dd/yyyy HH:MM:SS];"
MyDb.DBColor = "4,auto,lightblue"
MyDb.dbImageDir="images/"
MyDb.dbGridTableTag = "border=3 cellspacing=3 cellpadding=3"
MyDb.DbMode = "Both"
MyDb.dbSQL = "($~)SELECT EMP.EMPNO, EMP.ENAME, EMP.JOB, EMP.MGR, " &_
"EMP.HIREDATE, " &_
"EMP.SAL, EMP.COMM, EMP.DEPTNO FROM EMP ORDER BY EMP.EMPNO"
MyDb.dbNavigation="top"
MyDb.dbNavigationItem="top, bottom, next, prev, update,"
MyDb.dbNavigationIcon="std"
' Tried with and without: Mydb.dboptions="DateFormat=DD-MMM-YY HH24:MI:SS"
MyDb.dbEditParams="TableName=SCOTT.EMP,BookMar kFlds=0,TableTag=BORDER=2,"
MyDb.dbEditFlds="1,2,3,4,5,6,7" ' 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's date automatically. No need to use TO_DATE conversions which makes the code not portable. Don't even need to specify DateFormat since dbDBType="ORACLE" will trigger the date conversion to ORACLE'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("aspDB.EP"
Mydb.dbSilentCmdText="Alter Session Set NLS_DATE_FORMAT='MM/DD/YYYY'"
MyDb.dbDSN="DSN=local; UID=scott; PWD=tiger;"
Mydb.dbDbType="Oracle"
MyDb.dbUnit = 1
MyDb.DBColor = "4,auto,lightblue"
MyDb.dbImageDir="images/"
MyDb.dbGridTableTag = "border=3 cellspacing=3 cellpadding=3"
MyDb.DbMode = "Both"
MyDb.dbSQL = "($~)SELECT EMP.EMPNO, EMP.ENAME, EMP.JOB, EMP.MGR, TO_CHAR(EMP.HIREDATE, 'MM/DD/YYYY' HIREDATE, EMP.SAL, EMP.COMM, EMP.DEPTNO FROM EMP ORDER BY EMP.EMPNO"
MyDb.dbNavigation="top"
MyDb.dbNavigationItem="top, bottom, next, prev, update,"
MyDb.dbNavigationIcon="std"
Mydb.dboptions="DateFormat=DD-MMM-YY"
MyDb.dbEditParams="TableName=SCOTT.EMP,BookMar kFlds=0,TableTag=BORDER=2,"
MyDb.dbEditFlds="1,2,3,4,5,6,7" ' 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("aspDB.EP"
MyDb.dbDSN="DSN=local; UID=scott; PWD=tiger;"
Mydb.dbDbType="Oracle"
MyDb.dbUnit = 1
MyDb.DBColor = "4,auto,lightblue"
MyDb.dbImageDir="images/"
MyDb.dbGridTableTag = "border=3 cellspacing=3 cellpadding=3"
MyDb.DbMode = "Both"
MyDb.dbSQL = "($~)SELECT * FROM SCOTT.EMP"
MyDb.dbNavigation="top"
MyDb.dbNavigationItem="top, bottom, next, prev, update,"
MyDb.dbNavigationIcon="std"
MyDb.dbEditParams="TableName=SCOTT.EMP,BookMar kFlds=0,TableTag=BORDER=2"
MyDb.dbEditFlds="1,2,3,4,5,6,7" ' Note: You CANNOT include the AutoNumbered UniqueID field!
MyDb.aspDBEP
%>
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|