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

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


--
thogo01

On Jan 31, 9:56*pm, Ingres Forums <info-
ing... (AT) kettleriverconsulting (DOT) com> wrote:
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

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


--
wolaniukm

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

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-pierre.zuate (AT) lafageconseil (DOT) fr
http://lafageconseil.fr/



2012/2/2 Ingres Forums <info-ingres (AT) kettleriverconsulting (DOT) com>

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

On Feb 2, 7:12*pm, Ingres Forums <info-
ing... (AT) kettleriverconsulting (DOT) com> wrote:
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

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

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