RE: informix jdbc and date fields
Hi Superboer
Thanks for your time
I'm not sure I understand:
When I do the insert like this:
stmtInsert = conn.prepareStatement("insert into students values ("999",
"first col", "2005-05");
it works - so I don't always need to use casting
only when I try to do it with - > stmtInsert.setString(3, "2005-05");
I get the error message.
So back to my original question:
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(?
Rivka
-----Original Message-----
From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org]
On Behalf Of Superboer
Sent: Thursday, August 25, 2005 7:46 AM
To: informix-list@iiug.org
Subject: Re: informix jdbc and date fields
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 = retv
Hello Rivka, this: "insert into students values ('999', 'first col', '2005-05')" is more or less static; the database gets the whole sql and it will do 'conversions' etc internally if needed. this: stmtInsert = conn.prepareStatement( "insert into students values (?,?.?)")' is not the database expects for each placeholder a 'correct' datatype and value. so in the above example the 3rd ? needs to be a datetime year to month... unfortunatly the jdbc driver does not support datetime year to month so in order to get that to work make the database expect a string by: stmtInsert = conn.prepareStatement( "insert into students values (?,?.? ::"+ convIfxDaTeInVa.getIfxcast(theencodedlen, thecoltype) )")' -- ahum my prev mail contained an error; above is corrected. for what it's worth my hack allows me to insert/update any datetime/interval value with any precision. i realize my explanation is a bit crummy; maybe someone else can do a better job... Jonathan please help Superboer