Ingres Dates using jdbc

[Ingres Forums]

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

[Ingres Forums]

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


--
thogo01
------------------------------------------------------------------------
thogo01's Profile: http://community.actian.com/forum/member.php?userid=4992

[Kristoff]

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

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]

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

--
wolaniukm
------------------------------------------------------------------------
wolaniukm's Profile: http://community.actian.com/forum/member.php?userid=98861

[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.

[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-pie...@lafageconseil.fr
http://lafageconseil.fr/

2012/2/2 Ingres Forums <info-...@kettleriverconsulting.com>

_______________________________________________
Info-Ingres mailing list
Info-...@kettleriverconsulting.com
http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres

[Ingres Forums]

The first thing is to confirm that empty dates can be handled outside
Squirrel. Try the test program below both with and without converting
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 to
work, but it doesn't work in Squirrel, then it could either be a
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;
}

}


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

--
thogo01
------------------------------------------------------------------------
thogo01's Profile: http://community.actian.com/forum/member.php?userid=4992

[Kristoff]

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

ing...@kettleriverconsulting.com> wrote:
> 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]

Below are the returns from the program. How can I verifiy if the driver
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>java
-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 have
anything to do with this?


Thank-you very much for the time!

--
wolaniukm
------------------------------------------------------------------------
wolaniukm's Profile: http://community.actian.com/forum/member.php?userid=98861

[Ingres Forums]

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

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

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

--
thogo01
------------------------------------------------------------------------
thogo01's Profile: http://community.actian.com/forum/member.php?userid=4992

[Ingres Forums]

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

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

--
wolaniukm
------------------------------------------------------------------------
wolaniukm's Profile: http://community.actian.com/forum/member.php?userid=98861

[Ingres Forums]

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

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

--
thogo01
------------------------------------------------------------------------
thogo01's Profile: http://community.actian.com/forum/member.php?userid=4992

[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-pie...@lafageconseil.fr
http://lafageconseil.fr/

2012/2/2 Jean-Pierre Zuate, La Fage Conseil <jean-pie...@lafageconseil.fr>

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-pie...@lafageconseil.fr
http://lafageconseil.fr/

2012/2/2 Ingres Forums <info-...@kettleriverconsulting.com>

[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" file
located?

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

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


Thx


Leo


--
leonarbe
------------------------------------------------------------------------
leonarbe's Profile: http://community.actian.com/forum/member.php?userid=98437

[Ingres Forums]

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,

[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