Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

DB2 Oracle incompatibility

 
tushar bhasme
Ranch Hand
Posts: 50
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Our application supports 3 types of databases- oracle, db2 and sql.

I have an sql statement that was written keeping oracle as the main database-



Idea was to select blank in the rcrd_dt (column is of type Date) column since it was not required to be displayed on the page (cannot remove it from the query either due to dependency on framework).
While it works perfectly fine with oracle database, it is issuing a char to date conversion error in DB2 env. The query itself runs fine when its run in the DB2 server but looks error comes up during the java conversion of the blank date field.

Any idea how to get this type of code working in all types of databases?
Thanks in advance...!!!
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I suppose you need to consider why your application needs a blank value in a particular field. If its not using it, then why query for it?

The conversion error (I'm guessing) occurs in the driver? You might try a different version of the DB2 driver, but it does sound like what that driver is doing could be considered corred - after all a blank character is not a date.
 
Matthew Brown
Bartender
Posts: 4568
9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you select NULL instead of ''?
 
tushar bhasme
Ranch Hand
Posts: 50
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
well, as stated before, i cannot remove any field from the query... the page needs to have all the attributes in the query even if its not to be displayed...
I tried setting it to null... it again works fine for Oracle but not db2...
 
Jan Cumps
Bartender
Posts: 2608
14
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you show us the complete query where you tried the null option?
And can you show us the java code block where the error happens, and the exact error message?
 
tushar bhasme
Ranch Hand
Posts: 50
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
this is the running query:


SELECT ap_bkwld_hst_dbal.rcrd_dt AS rcrd_dt,
'' AS frm_rec_dt,
'' AS to_rec_dt,
ap_bkwld_hst_dbal.fund_cd AS fund_cd,
ap_bkwld_hst_dbal.sfund_cd AS sfund_cd,
( CASE
WHEN ap_bkwld_hst_dbal.sfund_cd IS NOT NULL THEN sf.sfund_sh_nm
ELSE f.fund_sh_nm
END ) AS fund_sfund_sh_nm,
ap_bkwld_hst_dbal.bsa_cd AS bsa_cd,
ap_bkwld_hst_dbal.sbsa_cd AS sbsa_cd,
( CASE
WHEN ap_bkwld_hst_dbal.sbsa_cd IS NOT NULL THEN sb.sbsa_sh_nm
ELSE b.bsa_sh_nm
END ) AS bsa_sbsa_sh_nm,
MIN(ap_bkwld_hst_dbal.fy) AS fy,
SUM(ap_bkwld_hst_dbal.whld_rfnd_am) AS whld_rfnd_am,
SUM(ap_bkwld_hst_dbal.can_remt_am) AS can_remt_am,
SUM(whld_rfnd_am - can_remt_am) AS bkp_whld_bal_am
FROM ap_bkwld_hst_dbal ap_bkwld_hst_dbal
LEFT OUTER JOIN r_fund f
ON ( ap_bkwld_hst_dbal.fy = f.fy
AND ap_bkwld_hst_dbal.fund_cd = f.fund_cd )
LEFT OUTER JOIN r_bsa b
ON ( ap_bkwld_hst_dbal.fy = b.fy
AND ap_bkwld_hst_dbal.bsa_cd = b.bsa_cd )
LEFT OUTER JOIN r_sbsa sb
ON ( ap_bkwld_hst_dbal.fy = sb.fy
AND ap_bkwld_hst_dbal.bsa_cd = sb.bsa_cd
AND ap_bkwld_hst_dbal.sbsa_cd = sb.sbsa_cd )
LEFT OUTER JOIN r_sfund sf
ON ( ap_bkwld_hst_dbal.fy = sf.fy
AND ap_bkwld_hst_dbal.fund_cd = sf.fund_cd
AND ap_bkwld_hst_dbal.sfund_cd = sf.sfund_cd )
GROUP BY ap_bkwld_hst_dbal.rcrd_dt,
ap_bkwld_hst_dbal.fund_cd,
ap_bkwld_hst_dbal.sfund_cd,
( CASE
WHEN ap_bkwld_hst_dbal.sfund_cd IS NOT NULL THEN sf.sfund_sh_nm
ELSE f.fund_sh_nm
END ),
ap_bkwld_hst_dbal.bsa_cd,
ap_bkwld_hst_dbal.sbsa_cd,
( CASE
WHEN ap_bkwld_hst_dbal.sbsa_cd IS NOT NULL THEN sb.sbsa_sh_nm
ELSE b.bsa_sh_nm
END )



And this is the error from the logs:
Error: [reason=An error occurred while converting the varchar value to JDBC data type DATE.
] [Column=.] [id=0

This error is coming from the driver during converting the result from jdbc to java...

I have tried this-
CAST( NULL AS DATE ) AS FRM_REC_DT ... and looks like its working but still need to make sure... DB2 seems to need an explicit cast unlike oracle where it casts automatically for '' AS FRM_REC_DT...

I would need to keep a check of database type for such kind of an sql....
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The reason it worked in Oracle is that Oracle does not have a concept of zero-length string. Zero-length String, both in a variable and as a literal ('') is equivalent to NULL in Oracle, therefore the database has returned NULL and the driver accepted NULL for a date.

The other databases consider '' as a zero-length String (a varchar type) and need to convert it, which (of course) fails. I don't know why other databases need to cast the NULL as DATE, as I don't have any experience with them.

Consolidating syntactic and semantic differences (like for example the zero-length string) of different databases is not going to be easy. However, you could hide the syntactic differences of the three databases behind a view, which would be maintained for each type of database separately.

If you need the date just for display and nothing else (no manipulations), maybe you could also format the date in the database and return it as a String, in that case '' should work for any of the databases. However the view solution is better in my opinion.

Database independence is not a good decision, in my opinion. You will constantly run into issues like this, and it will prevent you from using any advanced features that are not supported by all of the databases.
 
tushar bhasme
Ranch Hand
Posts: 50
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the suggestion Martin. Since this is an ERP that has to support all 3 different types of databases, we try to code in a way compatible in all 3 of them. In this case, CAST(NULL AS <type>) works fine for all 3 and thus solves my problem. In case of cases where the query has to be different for each, we keep a check in the code for the database type, and create a query keeping the case in mind.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
tushar bhasme wrote:Thanks for the suggestion Martin. Since this is an ERP that has to support all 3 different types of databases, we try to code in a way compatible in all 3 of them. In this case, CAST(NULL AS <type>) works fine for all 3 and thus solves my problem. In case of cases where the query has to be different for each, we keep a check in the code for the database type, and create a query keeping the case in mind.


If it has to support three different types of database, would an ORM not be a good idea?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic