Hi, We are using jconn4.jar and are facing a strange issue around midnight when the timestamp returned in the output parameters is off by an hour.

Please let me know in case you have faced a similar issue and have a solution for the same.

asked 07 Jul, 06:41

skatiyar's gravatar image

skatiyar
112
accept rate: 0%

1

I'd like to help you, but I do not know:
1. The SA version;
2. The library version;
3. The stored procedure, how to get the timestamp;
4. The table schema and sample data used in the stored procedure above;
5. The SA time zone & localization settings;
6. The application time zone & localization settings;
7. How you compare the values to find a one hour difference.

If you want, you can log the timestamp in the stored procedure using MESSAGE to determine a temporary timestamp value.

You see, at least I have tried.

(08 Jul, 07:23) Vlad
Replies hidden

Hi Vlad,

I am just hitting a stored proc that takes as an input parameter a timsstamp and returns a result set and an output paramter. The result set has the correct time stamp however the output parameter shows timestamp off by an hour.

If the input timestamp is "2017-06-27 00:54:41.913" The output parameter gives : 23:54:41.913 The result set gives : "2017-06-27 00:54:41.913"

I am using a simple public static void main method to test it. Let me know if you need any other info.It works perfectly fine with ojdbc jar.

public static void main(String[] args)throws SQLException { try {

        Class.forName("com.sybase.jdbc4.jdbc.SybDriver");
        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection conn = DriverManager.getConnection("jdbc:sybase:Tds:xx.xxx.xx.xx:3009", "user", "pass");
        System.out.println("---------------------------------------------------------------------------");
        DatabaseMetaData metaData = conn.getMetaData();
        System.out.println(metaData.getDatabaseProductName() + "-" + metaData.getDatabaseProductVersion());
        System.out.println(metaData.getDriverName());
        System.out.println("---------------------------------------------------------------------------");

        String query = "{ call  ABC.dbo.dbCalcWrapper(?,?,?,?,?,?,?,?,?,?,?, ?, ?, ?)}";
        CallableStatement stmt = conn.prepareCall(query);

        stmt.setString(1, "119");
        stmt.setString(2, "32");
        stmt.setString(3, "x11103603");
        stmt.setString(4, "x11103603");
        stmt.setInt(5, 1);
        stmt.setString(6, "MODELER_NEW");
        stmt.setTimestamp(7, null);
        stmt.setString(8, null);
        stmt.setTimestamp(9, Timestamp.valueOf("2017-06-27 00:54:41.913"));
        stmt.registerOutParameter(9, java.sql.Types.TIMESTAMP);
        stmt.setString(10, null);
        stmt.setTimestamp(11,null);
        stmt.registerOutParameter(11, java.sql.Types.TIMESTAMP);
        stmt.registerOutParameter(12, java.sql.Types.VARCHAR);
        stmt.registerOutParameter(13, java.sql.Types.VARCHAR);
        stmt.registerOutParameter(14, java.sql.Types.VARCHAR);
        executeQuery(stmt);
    } catch (Exception e) {
        System.out.println(e.getLocalizedMessage());
        e.printStackTrace();
        if(e.getCause() != null)
            e.getCause().printStackTrace();
    }
}
(10 Jul, 07:42) skatiyar

Are the client calling the proc and the database server on the same or on different machines? Are they using different time zones/locales?

What is the definition of your procedure, as you tell of input/output parameters and result sets?


Yes, I'm basically re-asking for the answers to Vlad's questions 3, 5 and 6, and the first two are also left unanswered...

(10 Jul, 08:11) Volker Barth

1) The client calling the proc and the database are on different machines. 2)They are using the same locales and time zones (IE) 3)Reading the output param with statement System.out.println(((CallableStatement)stmt).getTimestamp(9));

4) What is SA that you are referring to?How do I get its timezone?

Please find below the remaining code.

private static void executeQuery(Statement stmt) {
try{

        System.out.println("Executing...");
        if(stmt instanceof CallableStatement)
            ((CallableStatement)stmt).execute();
        ResultSet rs = stmt.getResultSet();

        displayRS(rs);

        boolean more = stmt.getMoreResults();
        int updCount = stmt.getUpdateCount();


        while (more || (updCount != -1)) {
            if ((rs = stmt.getResultSet()) != null) {
                System.out.println("More..................................");
                displayRS(rs);
            }
            more = stmt.getMoreResults();
            updCount = stmt.getUpdateCount();
        } // while

        System.out.println(((CallableStatement)stmt).getTimestamp(9));
        System.out.println("Executed with warnings ... " + ((CallableStatement)stmt).getWarnings());

    } catch (Exception e) {
        System.out.println(e.getLocalizedMessage());
        e.printStackTrace();
        if(e.getCause() != null)
            e.getCause().printStackTrace();
    }
}

public static void displayRS(ResultSet rs) {

    if (rs == null) {
        return;
    }
    try {
        //System.out.println("1 : " + rs.getObject(1)); 
        // Get result set meta data
        ResultSetMetaData rsmd = rs.getMetaData();
        int numColumns = rsmd.getColumnCount();

        String[] columnLabels = new String[numColumns + 1];
        // Get the column names; column indices start from 1
        for (int i = 1; i < numColumns + 1; i++) {
            String columnName = rsmd.getColumnLabel(i); // Get the name of
                                                        // the column's
                                                        // table name
            columnLabels[i] = columnName;
            String tableName = rsmd.getTableName(i);
            String type = rsmd.getColumnTypeName(i);

            System.out.println(columnName + " -> " + type + " -> "
                    + tableName);
        }

        while (rs.next()) {
            System.out
                    .println("\n##############################################");
            for (int i = 1; i <= numColumns; i++) {
                try {

                    System.out.println(columnLabels[i] + " : "
                            + rs.getObject(i));
                } catch (SQLException e) {

                    System.out.println("throw e");
                    throw e;
                }
            }
            System.out
                    .println("##############################################\n");
        }
    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

}
(10 Jul, 08:25) skatiyar
Replies hidden

SA means SQL Anywhere, the database system this forum is focussed on. Or do you use SAP Sybase ASE?

We would be more interested in the stored procedure's code (as that evaluates the timestamp on the server's side), not the calling Java code...

As to the time zones, the following query displays the time zone adjustment for the database server vs. the client/current connection:

select property('TimeZoneAdjustment'), connection_property('TimeZoneAdjustment');

If they are different, the database server and the client application may treat non-UTC timestamp values differently.

(10 Jul, 09:12) Volker Barth

Volker , The proc code on the data base side is almost 5 years old.We are able to retrieve the correct time stamp from the output parameters using ojdbc. I don't have the stored proc's code as that is maintained by a third party.

(10 Jul, 09:17) skatiyar

I meant with jtds it works.

(11 Jul, 05:34) skatiyar
Replies hidden

So do you use Sybase/SAP ASE or SQL Anywhere?

FWIW, for jConnect there seems to have been a bug fix w.r.t. timestamps and particular "time lags" around midnight, in case that applies to you (which I can't tell), see that older FAQ...

(11 Jul, 06:01) Volker Barth

Hm. I have read this thread carefully, and I still doubt that my questions have been answered. We don't even know the server name/version, as well as the library version.

(11 Jul, 18:07) Vlad

I agree...

(11 Jul, 18:41) Volker Barth
showing 4 of 10 show all flat view
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×33

question asked: 07 Jul, 06:41

question was seen: 161 times

last updated: 11 Jul, 18:41