RE: CF4.5 and CFMX Oracle ODBC Drivers
Thanks, that is exactly what we ended up doing, in conjunction with the NVL2 function to capture the nulls and provide a value for them. SELECT NVL2(Submitted_Date,TO_CHAR(Submitted_Date,'dd-Mon-'),'NA') FROM ... Thanks for you help. Ian -Original Message- From: Dave Carabetta [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 15, 2003 2:11 PM To: CF-Talk Subject: RE: CF4.5 and CFMX Oracle ODBC Drivers >Thanks for the interesting comments. What is weird about this to me, is >that I'm not INSERTing the data, I'm SELECTing the date. The date is >already in the Oracle database table. It was put into it by another tool >which assumable did so correctly, the data is there. But when I try to >SELECT the date field it sometimes throws an error. What is even weirder, >it only throws the error for some records and not others. And apparently >the offending records can be selected, if good records are selected first! >This is just bizarre to me. > >Thanks for your insights. I didn't see my reply come through in the arhives to this, so here goes one more time. Sorry if you already got this. For selecting data, I use Oracle's to_char() function to convert the date to a formatted string. This gives me the added flexibility of formatting my date in the query as opposed to using DateFormat() in CF, and I only have to go to one place to change the date mask if I need to. Here's an example: SELECT to_char(tablename.dateColumn, 'MM/DD/') AS formattedDate FROM tablename (Note that the "AS" keyword in Oracle isn't strictly necessary, but I do it for clarity and because I'm used to it.) Again, the mask that is applied can vary depending on what you want. See the Oracle docs for the multitude of options. As I mentioned in my previous post, using this approach has eliminated any of the date insert/update/select issues I used to see in CF 4.5, 5, and MX. Hope this helps, Dave. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CF4.5 and CFMX Oracle ODBC Drivers
>Thanks for the interesting comments. What is weird about this to me, is >that I'm not INSERTing the data, I'm SELECTing the date. The date is >already in the Oracle database table. It was put into it by another tool >which assumable did so correctly, the data is there. But when I try to >SELECT the date field it sometimes throws an error. What is even weirder, >it only throws the error for some records and not others. And apparently >the offending records can be selected, if good records are selected first! >This is just bizarre to me. > >Thanks for your insights. I didn't see my reply come through in the arhives to this, so here goes one more time. Sorry if you already got this. For selecting data, I use Oracle's to_char() function to convert the date to a formatted string. This gives me the added flexibility of formatting my date in the query as opposed to using DateFormat() in CF, and I only have to go to one place to change the date mask if I need to. Here's an example: SELECT to_char(tablename.dateColumn, 'MM/DD/') AS formattedDate FROM tablename (Note that the "AS" keyword in Oracle isn't strictly necessary, but I do it for clarity and because I'm used to it.) Again, the mask that is applied can vary depending on what you want. See the Oracle docs for the multitude of options. As I mentioned in my previous post, using this approach has eliminated any of the date insert/update/select issues I used to see in CF 4.5, 5, and MX. Hope this helps, Dave. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CF4.5 and CFMX Oracle ODBC Drivers
You might want to check out this TechNote: http://www.macromedia.com/v1/Handlers/index.cfm?ID=23463&Method=Full Deb -Original Message- From: Ian Skinner [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 15, 2003 3:03 PM To: CF-Talk Subject: CF4.5 and CFMX Oracle ODBC Drivers Does anybody know what differences there might be between the 4.5 and MX ODBC drivers for a Oracle database. We have a query to the Oracle database the runs just fine on a CF4.5 server, but when we migrated to new CFMX server all of a sudden the query is throwing errors. Both cold fusion servers are pointing the same Oracle database server. It seems to involve a date field. If we try to select the offending field from the MX we get following error. Error Executing Database Query. [MERANT][SequeLink JDBC Driver][ODBC Socket][Oracle][ODBC]Invalid datetime format. I'm just wondering why the version running on the 4.5 server has no trouble selecting this date field, but that the MX server does? Thanks for any information. Ian Skinner Web developer BloodSource Sacramento, CA ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CF4.5 and CFMX Oracle ODBC Drivers
The trouble is that I'm selecting the date, not inserting it. SELECT Collection_Desc, LICENSED_PRODUCTS.Collection_Type, Prod_Code, Approved_Date, Ref_No, Licensed_Status, Submitted_Date FROMINTRANET.LICENSED_PRODUCTS, INTRANET.LICENSED_COl_TYPE WHERE LOC_CODE = 'FLBC' AND ((Licensed_Status = 'ACTIVE' OR (Licensed_Status = 'PENDING')) AND LICENSED_PRODUCTS.COLLECTION_TYPE = LICENSED_COl_TYPE.COLLECTION_TYPE Sometimes throws this error: [MERANT][SequeLink JDBC Driver][ODBC Socket][Oracle][ODBC]Invalid datetime format. The offending field is Submitted_Date. If this field is selected for some records the error is thrown. For others, it is not. And apparently bad records can be selected if good records are selected first. All of this only happens on the CFMX server. The CF4.5 server runs the above query just fine in all cases. Thanks Ian -Original Message- From: Larry Hoy [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 15, 2003 11:44 AM To: CF-Talk Subject: CF4.5 and CFMX Oracle ODBC Drivers use the "CreateODBCDateTime()" function. Old ODBC drivers created date and time down to the seconds, new date times go to the 1/10 seconds. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CF4.5 and CFMX Oracle ODBC Drivers
>Does anybody know what differences there might be between the 4.5 and MX >ODBC drivers for a Oracle database. We have a query to the Oracle database >the runs just fine on a CF4.5 server, but when we migrated to new CFMX >server all of a sudden the query is throwing errors. Both cold fusion >servers are pointing the same Oracle database server. It seems to involve >a >date field. If we try to select the offending field from the MX we get >following error. > >Error Executing Database Query. >[MERANT][SequeLink JDBC Driver][ODBC Socket][Oracle][ODBC]Invalid datetime >format. > >I'm just wondering why the version running on the 4.5 server has no trouble >selecting this date field, but that the MX server does? > >Thanks for any information. I'm not sure about the specific driver differences, but I do have a good amount of experience with Oracle and finds that it's very particular about dates. To fix this problem, I just use its built in functions to take care of formatting issues. Here are some examples: 1. Instead of using #Now()# as the date to insert, try using "sysdate" instead (without the quotes). You can see sysdate's value by running a simple query: SELECT sysdate FROM dual 2. When a particular date is necessary, use the to_date() Oracle function like so: to_date(, 'mm-dd-') 3. If you need to insert a particular date *and* time, you can still use Oracle's to_date() function, like so: to_date(, 'mm-dd- HH24:MI:SS') (You can use HH12:MI:SS is you want to use the 12-hour clock instead of the 24-hour clock format) If you look in Oracle's reference docs, you'll see that there are many different masks you can apply to the to_date() function, not just 'mm-dd-', so you can change that to your liking. Again, it's not an answer to your specific question about driver differences, but using the above two hints has not caused a problem for me, whether I'm using CF 4.5., 5, or MX (and I move in and out of all three regularly). Regards, Dave. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
