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

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
View this thread: http://community.actian.com/forum/showthread.php?t=14193

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

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
View this thread: http://community.actian.com/forum/showthread.php?t=14193

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
------------------------------------------------------------------------
wolaniukm's Profile: http://community.actian.com/forum/member.php?userid=98861
View this thread: http://community.actian.com/forum/showthread.php?t=14193

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

2012/2/2 Ingres Forums <info-ing...@kettleriverconsulting.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
------------------------------------------------------------------------
thogo01's Profile: http://community.actian.com/forum/member.php?userid=4992
View this thread: http://community.actian.com/forum/showthread.php?t=14193

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

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
------------------------------------------------------------------------
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 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
View this thread: http://community.actian.com/forum/showthread.php?t=14193

* 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
View this thread: http://community.actian.com/forum/showthread.php?t=14193

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
View this thread: http://community.actian.com/forum/showthread.php?t=14193

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

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