jt400 cannot query USER column CCSID 37

[cwg999]

So I encountered an interesting issue, and am looking to gather feedback if it occurs with anyone else.

I added a table in DB2 for i 7.1 that uses the Column name (and System name) "USER".
IBM Data Studio, i Series Navigator can both query that column with a WHERE USER = 'user', however a connection pool query (via Connection) cannot. The query will return all the results (including the USER column) just fine without the WHERE clause.

At this point am unsure if this is an open-jt400 issue or node-jt400 issue.

-Cody

[bergur]

Hi Cody and thanks for the feedback.

Skimming veeery quickly over the node-jt400 code I can't find any reason why this shouldn't work, but like I said I just glanced.

I did a little test using SQL Workbench, where I added the JT400.jar from the node-jt400 project as a driver.

create table bergurtest (
  user char(25),
  myuser char(25)
)

insert into QGPL.bergurtest(user, myser) values('bergur1', 'bergur1')
insert into QGPL.bergurtest(user, myser) values('bergur2', 'bergur2')

select * from QGPL.bergurtest

This returns
user | myuser
bergur1 | bergur1
bergur2 | bergur2

I than ran the following tests:

select * from QGPL.bergurtest2 where myser = 'bergur1'`

Returns

user | myuser
bergur1 | bergur1

select * from QGPL.bergurtest2 where user = 'bergur1'`

Returns

user | myuser
|

(empty)

So SQL Workbench with the JT400.jar does not seems to recognize the user column.

However, when doing the following.

create table bergurtest1 (
  "user" char(25),
  "myuser" char(25)
)

insert into QGPL.bergurtest1("user", "myuser") values('bergur1', 'bergur1')
insert into QGPL.bergurtest1("user", "myuser") values('bergur2', 'bergur2')

select * from QGPL.bergurtest1 where "user" = 'bergur1'

Returns

user | myuser
bergur1 | bergur1

So it seems that unless the column name is escaped on creation the JT400.jar doesn't handle it. One possible next step would be to write a small java snippet.

[cwg999]

I'm not entirely sure if this is what you meant by snippet, haha.

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

// import com.ibm.as400.access.*;
import com.ibm.as400.access.AS400; 
import com.ibm.as400.access.AS400JDBCDataSource;


public class <YOURFILENAME> {
  public static void main(String[] args) {
    AS400 as400 = new AS400("POWER7", "<USER>", "<PASSWORD>");
      // Create a data source for making the connection.
    AS400JDBCDataSource datasource = new AS400JDBCDataSource(as400);
    // Create a database connection to the system.
    Statement stmt = null;
    String query = "SELECT * FROM TABLE WHERE USER = '<USER>'";
    try {
      Connection conn = datasource.getConnection();
      stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery(query);
      while(rs.next()){
        String user = rs.getString("USER").trim();
        System.out.println("User:"+user);
      }
    } catch (SQLException e2) {
      e2.printStackTrace();
    } finally {
      if (stmt != null){
        try {stmt.close();} catch (SQLException e) { e.printStackTrace();}
      }
    }

    as400.disconnectAllServices();
    System.exit(0);
  }
}

This seems to work

[milesje]

Could this be an issue where the systems CCSID is different than what is being used by jt400 library? I know I had an issue a while back with the CCSID used by the library vs what was used on our system. I wrote a PR but it has not been merged... mainly because I need to do some cleanup on it before it can be merged. I don't remember of hand but I think my fix was specific the IFS Write methods so the code fix I wrote may not work without some research and updates to other parts of the code.

[bergur]

Hi @milesje, sorry I missed your comment.

You are correct, there was a problem with the IFS and default encoding, but that has been fixed, thanks to you :) I merged your pull request sometimes in may and made some changes. See commit: 0ac5f50

We also had similar issues with programs where encoding was also hard code. We have fixed that as well.

So...this could be something similar, like you suggest.

We will happily accept pull requests or if find that hard-coding value I will happily make it optional and default to the system's value.