Ingres Dates using jdbc

[Ingres Forums]

I am new to Ingres, I am sure this must be an old issue but I am havin
trouble finding a solution.

Using a 9.2 Legacy Ingres DB, I cannot get Squirrel to load 'blank
dates as anything other than the epoch value '1-JAN-1970....'.

I have updated the iijdbc.properties in the ingresII/ingres/file
directory.
ingres.jdbc.date.empty=null

I have validated that the classpath is correct and according to an
information I have read, it is.

Is there something else I need to correct in order to read blankdates a
null

--
wolaniuk
-----------------------------------------------------------------------
wolaniukm's Profile: http://community.actian.com/forum/member.php?userid=9886
View this thread: http://community.actian.com/forum/showthread.php?t=1419

[Ingres Forums]

What JDBC driver version are you using (run 'java JdbcInfo' i
$II_SYSTEM/ingres/lib)

--
thogo0
-----------------------------------------------------------------------
thogo01's Profile: http://community.actian.com/forum/member.php?userid=499
View this thread: http://community.actian.com/forum/showthread.php?t=1419

[Ingres Forums]

Although I have tried several the current version of the driver that
am using is 3.4.1

--
wolaniuk
-----------------------------------------------------------------------
wolaniukm's Profile: http://community.actian.com/forum/member.php?userid=9886
View this thread: http://community.actian.com/forum/showthread.php?t=1419

[Kristoff]

On Jan 31, 9:56 pm, Ingres Forums <info-

I am new to Ingres, I am sure this must be an old issue but I am having
trouble finding a solution.
Using a 9.2 Legacy Ingres DB, I cannot get Squirrel to load 'blank'
dates as anything other than the epoch value '1-JAN-1970....'.
I have updated the iijdbc.properties in the ingresII/ingres/files
directory.
ingres.jdbc.date.empty=null
I have validated that the classpath is correct and according to any
information I have read, it is.
Is there something else I need to correct in order to read blankdates as
null?
--
wolaniukm
------------------------------------------------------------------------
wolaniukm's Profile:http://community.actian.com/forum/member.php?userid=98861
View this thread:http://community.actian.com/forum/showthread.php?t=14193

The directory where iijdbc.properties is stored needs to be added to
the classpath. The startup scripts for SQuirreL defines the classpath,
but instead of manipulating it here I would add the directory in the
"Extra Class Path" Tab in the Driver definition. (I wouldn't use the
ingres/files directory for that, but it doesn't matter ...)

I've tested it and it works fine.

Kristoff



Kristoff

[Ingres Forums]

I have tried the extra path option in squirrel, to no avail.
Regardless of how I set the ingres.jdbc.date.empty=null in th
properties, Squirrel will not return null for blank dates.

As I have said I am new to Ingres, could this be a DB issue rather tha
just a squirrel/jdbc issue?

Thanks very much for your time

--
wolaniuk
-----------------------------------------------------------------------
wolaniukm's Profile: http://community.actian.com/forum/member.php?userid=9886
View this thread: http://community.actian.com/forum/showthread.php?t=1419

[Jean-Pierre Zuate, La Fage Conseil]

I hall,

I have the same issue with Ingres 9.2 and dbvisualizer. It seems it is not
possible to set the parameter date.empty ...

Can someone help ?

Thx in advance,
--
Jean-Pierre Zuate
La Fage Conseil
01 55 68 12 25
jean-***@lafageconseil.fr
http://lafageconseil.fr/

Post by Ingres Forums
I have tried the extra path option in squirrel, to no avail.
Regardless of how I set the ingres.jdbc.date.empty=null in the
properties, Squirrel will not return null for blank dates.
As I have said I am new to Ingres, could this be a DB issue rather than
just a squirrel/jdbc issue?
Thanks very much for your time.
--
wolaniukm
------------------------------------------------------------------------
http://community.actian.com/forum/member.php?userid=98861
View this thread: http://community.actian.com/forum/showthread.php?t=14193
_______________________________________________
Info-Ingres mailing list
http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres

[Ingres Forums]

The first thing is to confirm that empty dates can be handled outsid
Squirrel. Try the test program below both with and without convertin
empty dates to null:

java IngEmpty
java -Dingres.jdbc.date.empty=null IngEmpty

I tried the 3.4.11 driver and it worked as expected. If you get this t
work, but it doesn't work in Squirrel, then it could either be
Squirrel problem or just that the driver isn't seeing the config.


Code
-------------------

import java.io.*;
import java.sql.*;


public class IngEmpty
{

private static String host = "localhost";
private static String port = "II7";
private static String db = "iidbdb";
private static String uid = "";
private static String pwd = "";
private static String attr = "";

public static void
main( String args[] )
throws IllegalAccessException,
ClassNotFoundException,
InstantiationException,
IOException,
SQLException
{
Connection conn;
String url = "jdbc:ingres://" + host + ":" + port + "/" + db;

if ( uid.length() > 0 ) url += ";UID=" + uid + ";PWD=" + pwd;
if ( attr.length() > 0 ) url += attr;

Class.forName("com.ingres.jdbc.IngresDriver");

try
{
if ( (conn = DriverManager.getConnection( url )) == null )
{
System.out.println( "no driver available" );
return;
}
}
catch( SQLException ex )
{
printSQLException( ex );
return;
}

try
{
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select ingresdate('')");

while( rs.next() )
{
Timestamp ts = rs.getTimestamp(1);
if ( rs.wasNull() )
System.out.println( "Date : NULL" );
else
System.out.println( "Date : " + ts.toString() );

System.out.println( "String: " + rs.getString(1) );
}

rs.close();
}
catch( SQLException ex )
{
printSQLException( ex );
}
finally
{
conn.close();
}
}

private static void
printSQLException( SQLException ex )
{
do
{

System.out.print( "SQLException: '" );
System.out.print( ex.getSQLState() );
System.out.print( "' 0x" );
System.out.print( Integer.toHexString( ex.getErrorCode() ) );
System.out.print( " -- " );
System.out.println( ex.getMessage() );

} while( (ex = ex.getNextException()) != null );

return;
}

}


-------------------

--
thogo0
-----------------------------------------------------------------------
thogo01's Profile: http://community.actian.com/forum/member.php?userid=499
View this thread: http://community.actian.com/forum/showthread.php?t=1419

[Ingres Forums]

Below are the returns from the program. How can I verifiy if the drive
is seeing the config?

C:\Program Files\Java\jdk1.6.0_26\jre\bin>java IngEmpty
Date : NULL
String: null

C:\Program Files\Java\jdk1.6.0_26\jre\bin>jav
-Dingres.jdbc.date.empty=null IngEmpty
Date : NULL
String: null

This may be an uniformed question but ....
Squirrel runs on eclipse and eclipse uses jboss. Could JBoss hav
anything to do with this?


Thank-you very much for the time

--
wolaniuk
-----------------------------------------------------------------------
wolaniukm's Profile: http://community.actian.com/forum/member.php?userid=9886
View this thread: http://community.actian.com/forum/showthread.php?t=1419

[Ingres Forums]

The output from 'java -Dingres.jdbc.date.empty=NULL IngEmpty' shows tha
the driver is turning the empty date into a null value. The fact tha
you get the same output for 'java IngEmpty' indicates that the driver
at least in a pure Java environment, is seeing the properties file wit
the empty date config.

Now you need to get the config info into the driver in the Squirre
environment. Unfortunately, I'm not familiar with that environment an
can't provide further assistance other than one of the following i
needed:

* The driver will load iijdbc.properites if it is in a directory whic
is referenced by the CLASSPATH used in the environment.
* Define the system property ingres.jdbc.property_file with the path t
the properties file.
* Define the system property ingres.jdbc.date.empty directly

--
thogo0
-----------------------------------------------------------------------
thogo01's Profile: http://community.actian.com/forum/member.php?userid=499
View this thread: http://community.actian.com/forum/showthread.php?t=1419

[Ingres Forums]

* Define the system property ingres.jdbc.property_file with the path t
the properties file.

This one confuses me, according to what I have read on the web, this i
a property contained in the properties file? Can you provide an exampl
of where/how I would set this

--
wolaniuk
-----------------------------------------------------------------------
wolaniukm's Profile: http://community.actian.com/forum/member.php?userid=9886
View this thread: http://community.actian.com/forum/showthread.php?t=1419

[Kristoff]

On Feb 2, 7:12 pm, Ingres Forums <info-

Post by Ingres Forums
I have tried the extra path option in squirrel, to no avail.
Regardless of how I set the ingres.jdbc.date.empty=null in the
properties, Squirrel will not return null for blank dates.

Try with the following "iijdbc.properties" file:
ingres.jdbc.trace.log=/tmp/jdbc.log
ingres.jdbc.trace.drv=3
ingres.jdbc.trace.ds=1
ingres.jdbc.trace.timestamp=true
ingres.jdbc.date.empty=NULL

When iijdbc.properties is picked up you will get a trace file /tmp/
jdbc.log. If there is no such file, then there is something wrong with
the classpath setting or with the filename or may be permissions to
read the file.
If the trace file is created, you should see a "Conn: date.empty=NULL"
in that file somewhere at the beginning.

Kristoff

[Ingres Forums]

Hi,

I'm using Sql Squirrel.
I'm experiencing the NULL date shown as '1970-01-01'.

My question is:
In which client's folder should be the "iijdbc.properties" fil
located?

I changed the "iijdbc.properties" in the Ingres server, restarted th
server and still I see the same 1970-01-01 date.

Please advise what else to do on the client side. Where to put th
properties file, etc.


Thx


Le

--
leonarb
-----------------------------------------------------------------------
leonarbe's Profile: http://community.actian.com/forum/member.php?userid=9843
View this thread: http://community.actian.com/forum/showthread.php?t=1419

[Ingres Forums]

In the client, I put the "iijdbc.properties" at the same location wher
I have the ingres driver (this path is already within the CLASSPATH)
I restarted Squirrel, ran the Query and still get the same date
1970-01-01', for NULL values

The property that I changed in the "iijdbc.properties" is

ingres.jdbc.date.empty=NUL

Please advise

Le

--
leonarb
-----------------------------------------------------------------------
leonarbe's Profile: http://community.actian.com/forum/member.php?userid=9843
View this thread: http://community.actian.com/forum/showthread.php?t=1419

[John Smedley]

I put the "iijdbc.properties" file which includes the line
"ingres.jdbc.date.empty=null" in the following place "C:\Program Files
(x86)\squirrel-sql-3.2.1" and my IngresDates which contain an empty
string now return <null>

Hope that helps

John

-----Original Message-----
From: info-ingres-***@kettleriverconsulting.com
[mailto:info-ingres-***@kettleriverconsulting.com] On Behalf Of
Ingres Forums
Sent: 12 June 2013 00:53
To: info-***@kettleriverconsulting.com
Subject: Re: [Info-Ingres] Ingres Dates using jdbc


In the client, I put the "iijdbc.properties" at the same location where
I have the ingres driver (this path is already within the CLASSPATH).
I restarted Squirrel, ran the Query and still get the same dates
1970-01-01', for NULL values.

The property that I changed in the "iijdbc.properties" is:

ingres.jdbc.date.empty=NULL

Please advise,


Leo


--
leonarbe
------------------------------------------------------------------------
leonarbe's Profile:
http://community.actian.com/forum/member.php?userid=98437
View this thread:
http://community.actian.com/forum/showthread.php?t=14193

[Jean-Pierre Zuate, La Fage Conseil]

I know my question was not exactly in the subject but have my reply :
*iijdbc.properties* must be located in *DB Visualizer home\resources*.
FYI
--
Jean-Pierre Zuate
La Fage Conseil
01 55 68 12 25
jean-***@lafageconseil.fr
http://lafageconseil.fr/

2012/2/2 Jean-Pierre Zuate, La Fage Conseil <

Post by Jean-Pierre Zuate, La Fage Conseil
I hall,
I have the same issue with Ingres 9.2 and dbvisualizer. It seems it is not
possible to set the parameter date.empty ...
Can someone help ?
Thx in advance,
--
Jean-Pierre Zuate
La Fage Conseil
01 55 68 12 25
http://lafageconseil.fr/

Post by Ingres Forums
I have tried the extra path option in squirrel, to no avail.
Regardless of how I set the ingres.jdbc.date.empty=null in the
properties, Squirrel will not return null for blank dates.
As I have said I am new to Ingres, could this be a DB issue rather than
just a squirrel/jdbc issue?
Thanks very much for your time.
--
wolaniukm
------------------------------------------------------------------------
http://community.actian.com/forum/member.php?userid=98861
http://community.actian.com/forum/showthread.php?t=14193
_______________________________________________
Info-Ingres mailing list
http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres

[Ingres Forums]

java -Dingres.jdbc.property_file=c:\apps\appinfo.properties MyApp

Since this property indicates the location and name of the propertie
file, it doesn't really do anything if placed in the properties fil
itself

--
thogo0
-----------------------------------------------------------------------
thogo01's Profile: http://community.actian.com/forum/member.php?userid=499
View this thread: http://community.actian.com/forum/showthread.php?t=1419