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";
break;
case 4 : retvalue = retvalue + " hh:mm:ss";
break;
case 6 : retvalue = retvalue + ":mm:ss"; break;
case 8 : retvalue = retvalue + ":ss"; break;
default : break;
} break;
case 11 :
switch(hexvaltoint) {
case 0 : retvalue = retvalue + "-MM-DD
hh:mm:ss.#"; break;
case 2 : retvalue = retvalue + "-DD hh:mm:ss.#";
break;
case 4 : retvalue = retvalue + " hh:mm:ss.#";
break;
case 6 : retvalue = retvalue + ":mm:ss.#";
break;
case 8 : retvalue = retvalue + ":ss.#"; break;
case 10: retvalue = retvalue + ".#"; break;
default : break;
} break;
case 12 :
switch(hexvaltoint) {
case 0 : retvalue = retvalue + "-MM-DD
hh:mm:ss.##"; break;
case 2 : retvalue = retvalue + "-DD hh:mm:ss.##";
break;
case 4 : retvalue = retvalue + " hh:mm:ss.##";
break;
case 6 : retvalue = retvalue + ":mm:ss.##";
break;
case 8 : retvalue = retvalue + ":ss.##"; break;
case 10: retvalue = retvalue + ".##"; break;
case 11: retvalue = retvalue + "#"; break;
default : break;
} break;
case 13 :
switch(hexvaltoint) {
case 0 : retvalue = retvalue + "-MM-DD
hh:mm:ss.###"; break;
case 2 : retvalue = retvalue + "-DD hh:mm:ss.###";
break;
case 4 : retvalue = retvalue + " hh:mm:ss.###";
break;
case 6 : retvalue = retvalue + ":mm:ss.###";
break;
case 8 : retvalue = retvalue + ":ss.###"; break;
case 10: retvalue = retvalue + ".###"; break;
case 11: retvalue = retvalue + "##"; break;
case 12: retvalue = retvalue + "#"; break;
default : break;
} break;
case 14 :
switch(hexvaltoint) {
case 0 : retvalue = retvalue + "-MM-DD
hh:mm:ss.####"; break;
case 2 : retvalue = retvalue + "-DD
hh:mm:ss.####"; break;
case 4 : retvalue = retvalue + " hh:mm:ss.####";
break;
case 6 : retvalue = retvalue + ":mm:ss.####";
break;
case 8 : retvalue = retvalue + ":ss.####";
break;
case 10: retvalue = retvalue + ".####"; break;
case 11: retvalue = retvalue + "###"; break;
case 12: retvalue = retvalue + "##"; break;
case 13: retvalue = retvalue + "#"; break;
default : break;
} break;
case 15 :
switch(hexvaltoint) {
case 0 : retvalue = retvalue + "-MM-DD
hh:mm:ss.#####"; break;
case 2 : retvalue = retvalue + "-DD
hh:mm:ss.#####"; break;
case 4 : retvalue = retvalue + " hh:mm:ss.#####";
break;
case 6 : retvalue = retvalue + ":mm:ss.#####";
break;
case 8 : retvalue = retvalue + ":ss.#####";
break;
case 10: retvalue = retvalue + ".#####"; break;
case 11: retvalue = retvalue + "####"; break;
case 12: retvalue = retvalue + "###"; break;
case 13: retvalue = retvalue + "##"; break;
case 14: retvalue = retvalue + "#"; break;
default : break;
} 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;

}

}



Rivka Shisman schreef:

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 (AT) iiug (DOT) org [mailtowner-informix-list (AT) iiug (DOT) org]
On Behalf Of Superboer
Sent: Thursday, August 25, 2005 7:46 AM
To: informix-list (AT) iiug (DOT) 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 = 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";
break;
case 4 : retvalue = retvalue + " hh:mm:ss";
break;
case 6 : retvalue = retvalue + ":mm:ss"; break;
case 8 : retvalue = retvalue + ":ss"; break;
default : break;
} break;
case 11 :
switch(hexvaltoint) {
case 0 : retvalue = retvalue + "-MM-DD
hh:mm:ss.#"; break;
case 2 : retvalue = retvalue + "-DD hh:mm:ss.#";
break;
case 4 : retvalue = retvalue + " hh:mm:ss.#";
break;
case 6 : retvalue = retvalue + ":mm:ss.#";
break;
case 8 : retvalue = retvalue + ":ss.#"; break;
case 10: retvalue = retvalue + ".#"; break;
default : break;
} break;
case 12 :
switch(hexvaltoint) {
case 0 : retvalue = retvalue + "-MM-DD
hh:mm:ss.##"; break;
case 2 : retvalue = retvalue + "-DD hh:mm:ss.##";
break;
case 4 : retvalue = retvalue + " hh:mm:ss.##";
break;
case 6 : retvalue = retvalue + ":mm:ss.##";
break;
case 8 : retvalue = retvalue + ":ss.##"; break;
case 10: retvalue = retvalue + ".##"; break;
case 11: retvalue = retvalue + "#"; break;
default : break;
} break;
case 13 :
switch(hexvaltoint) {
case 0 : retvalue = retvalue + "-MM-DD
hh:mm:ss.###"; break;
case 2 : retvalue = retvalue + "-DD hh:mm:ss.###";
break;
case 4 : retvalue = retvalue + " hh:mm:ss.###";
break;
case 6 : retvalue = retvalue + ":mm:ss.###";
break;
case 8 : retvalue = retvalue + ":ss.###"; break;
case 10: retvalue = retvalue + ".###"; break;
case 11: retvalue = retvalue + "##"; break;
case 12: retvalue = retvalue + "#"; break;
default : break;
} break;
case 14 :
switch(hexvaltoint) {
case 0 : retvalue = retvalue + "-MM-DD
hh:mm:ss.####"; break;
case 2 : retvalue = retvalue + "-DD
hh:mm:ss.####"; break;
case 4 : retvalue = retvalue + " hh:mm:ss.####";
break;
case 6 : retvalue = retvalue + ":mm:ss.####";
break;
case 8 : retvalue = retvalue + ":ss.####";
break;
case 10: retvalue = retvalue + ".####"; break;
case 11: retvalue = retvalue + "###"; break;
case 12: retvalue = retvalue + "##"; break;
case 13: retvalue = retvalue + "#"; break;
default : break;
} break;
case 15 :
switch(hexvaltoint) {
case 0 : retvalue = retvalue + "-MM-DD
hh:mm:ss.#####"; break;
case 2 : retvalue = retvalue + "-DD
hh:mm:ss.#####"; break;
case 4 : retvalue = retvalue + " hh:mm:ss.#####";
break;
case 6 : retvalue = retvalue + ":mm:ss.#####";
break;
case 8 : retvalue = retvalue + ":ss.#####";
break;
case 10: retvalue = retvalue + ".#####"; break;
case 11: retvalue = retvalue + "####"; break;
case 12: retvalue = retvalue + "###"; break;
case 13: retvalue = retvalue + "##"; break;
case 14: retvalue = retvalue + "#"; break;
default : break;
} 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;

}

}



Rivka Shisman schreef:

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