informix jdbc and date fields
hello friends i work with Informix 9.4 and JDBC V3.0. My CLIENT_LOCALE and DB_LOCALE env variables are set to iw_il-8859-8. I would like to know if I can hold a date field as String - for example I have a VO called Student that has a property - String birth_date. In the DB the field is datetime (from year to month). then in my DAO I have an insert statement like this: PreparedStatement pstmt = conn.prepareStatement("insert into students values (?, ?, ?)"); pstmt.setString(3, birth_date); ResultSet r = pstmt.executeQuery(); it fails on the set string with the below message: -79786 Unable to build a Date object based on localized date string representation but when I change my string to a date object - as shown below - it works fine: GregorianCalendar gc = new GregorianCalendar(1998, 7, 10); java.sql.Date dateObj = new java.sql.Date(gc.getTime().getTime()); pstmt.setDate(1, dateObj); my question is: is there a way that i can use String with no conversions to date? is it at all possible to use setString (and not set date)? thanks Rivka sending to informix-list
afaik datetime year to month are not supported...check the rel notes..
ahum because there is no datatype to map it to???...see also the
info for interval datatypes...
GRMBLLL lazy ^&*^*&^(&*(*&&( jdbc driver programmers...
since you are on V9xx and that supports casting
see below for my ugly hack to map stuff.
usage is: pass in the type and encoded length to be found with
metadata;
( getEncodedLength(colno) and getIfxColumnType(colno))
pass it in getIfxcast to obtain the real datatype use this to cast.
ex table:
create table mytab
( col1 char(10),
datetimecol datetime year to month)eq build statement like
myssql = "insert into mytab(col1, datetimecol ) values (?, ? :: "+
getIfxcast(theencodedlen, thecoltype)+")";
Prepare it and execute it; pass in strings
Superboer.
----------------
package superboer;
import com.informix.jdbc.*;
import com.informix.lang.*;
import java.*;
import java.util.*;
import java.math.*;
import java.lang.*;
public abstract class convIfxDaTeInVa{
public static String getIfxcast(int enclendb , int coltype) {
String valueinhex;
String retvalue;
String valueneeded;
int hexvaltoint=0;
switch( coltype) {
case IfxTypes.IFX_TYPE_INTERVAL : retvalue = "interval "; break;
case IfxTypes.IFX_TYPE_DATETIME : retvalue = "datetime "; break;
default : retvalue = null ; return retvalue;
}
valueinhex = Integer.toHexString(enclendb);
valueneeded = ""+valueinhex.charAt((valueinhex.length() - 2));
try
{
hexvaltoint = Integer.parseInt(valueneeded,16);
} catch (Exception e) {
hexvaltoint = 666;
};
// First bit....
switch (hexvaltoint) {
case 0 : retvalue = retvalue +" year to "; break;
case 2 : retvalue = retvalue +" month to "; break;
case 4 : retvalue = retvalue +" day to "; break;
case 6 : retvalue = retvalue +" hour to "; break;
case 8 : retvalue = retvalue +" minute to "; break;
case 10 : retvalue = retvalue +" second to "; break;
case 12 : retvalue = retvalue +" fraction to "; break;
default : retvalue = " Syntax Error"; return retvalue;
}
valueneeded = ""+valueinhex.charAt((valueinhex.length() - 1));
try
{
hexvaltoint = Integer.parseInt(valueneeded,16);
} catch (Exception e) {
hexvaltoint = 666;
};
// Second bit....
switch (hexvaltoint) {
case 0 : retvalue = retvalue + "year "; break;
case 2 : retvalue = retvalue + "month "; break;
case 4 : retvalue = retvalue + "day "; break;
case 6 : retvalue = retvalue + "hour "; break;
case 8 : retvalue = retvalue + "minute "; break;
case 10 : retvalue = retvalue + "second "; break;
case 11 : retvalue = retvalue + "fraction(1) "; break;
case 12 : retvalue = retvalue + "fraction(2) "; break;
case 13 : retvalue = retvalue + "fraction(3) "; break;
case 14 : retvalue = retvalue + "fraction(4) "; break;
case 15 : retvalue = retvalue + "fraction(5) "; break;
default : retvalue = " Syntax Error"; return retvalue;
}
/* i do realize that i need to add the value found for the first
bit and the second
bit and check this against the third last and fourth last
value/position
the sum of (1) and (2) should be the value represented by this
may do so later.
*/
return retvalue;
}
public static String getIfxPicture(int enclendb , int coltype) {
String valueinhex;
String valueinhex2;
String retvalue;
String valueneeded;
int hexvaltoint=0;
int hexvaltoint2=0;
switch( coltype) {
case IfxTypes.IFX_TYPE_INTERVAL : retvalue = "interval picture
is:"; break;
case IfxTypes.IFX_TYPE_DATETIME : retvalue = "datetime picture
is:"; break;
default : retvalue = null ; return retvalue;
}
valueinhex = Integer.toHexString(enclendb);
valueneeded = ""+valueinhex.charAt((valueinhex.length() - 2));
try
{
hexvaltoint = Integer.parseInt(valueneeded,16);
} catch (Exception e) {
hexvaltoint = 666;
};
// First bit....
switch (hexvaltoint) {
case 0 : retvalue = retvalue +"YYYY"; break;
case 2 : retvalue = retvalue +"MM"; break;
case 4 : retvalue = retvalue +"DD"; break;
case 6 : retvalue = retvalue +"hh"; break;
case 8 : retvalue = retvalue +"mm"; break;
case 10 : retvalue = retvalue +"ss"; break;
case 12 : retvalue = retvalue +"#"; break;
default : retvalue = " Syntax Error"; return retvalue;
}
valueneeded = ""+valueinhex.charAt((valueinhex.length() - 1));
try
{
hexvaltoint2 = Integer.parseInt(valueneeded,16);
} catch (Exception e) {
hexvaltoint2 = 666;
};
// Second bit....
switch (hexvaltoint2) {
case 0 : break;
case 2 :
switch(hexvaltoint) {
case 0 : retvalue = retvalue + "-MM"; break;
default : break;
} break;
case 4 :
switch(hexvaltoint) {
case 0 : retvalue = retvalue + "-MM-DD"; break;
case 2 : retvalue = retvalue + "-DD"; break;
default : break;
} break;
case 6 :
switch(hexvaltoint) {
case 0 : retvalue = retvalue + "-MM-DD hh";
break;
case 2 : retvalue = retvalue + "-DD hh"; break;
case 4 : retvalue = retvalue + " hh"; break;
default : break;
} break;
case 8 :
switch(hexvaltoint) {
case 0 : retvalue = retvalue + "-MM-DD hh:mm";
break;
case 2 : retvalue = retvalue + "-DD hh:mm";
break;
case 4 : retvalue = retvalue + " hh:mm"; break;
case 6 : retvalue = retvalue + ":mm"; break;
default : break;
} break;
case 10 :
switch(hexvaltoint) {
case 0 : retvalue = retvalue + "-MM-DD hh:mm:ss";
break;
case 2 : retvalue = retvalue + "-DD hh:mm:ss@@D