V5R4 support

[travbeamo]

Using classes:

  public class AM112U {
    [Column] public int Id { get; set; }
    [Column] public string? NormalizedName { get; set; }
    [Column] public string? NormalizedEmailAddress { get; set; }
    [Column] public int? ImpAM112Id { get; set; }
    public int AM114Id { get; set; }
    public AM114U? AM114U { get; set; }
  }

  public class AM114U {
    [Column] public int Id { get; set; }
    [Column] public string? NormalizedName { get; set; }
    public List<AM112U>? AM112UList { get; set; }
  }

When I try to run code:

  var builder = new LinqToDbConnectionOptionsBuilder();
  var connString = appSettings.ConnectionStrings.TestDB2iSeries;
  var dataProvider = new DB2iSeriesDataProvider(DB2iSeriesProviderType.Odbc);
  builder.UseConnectionString(dataProvider, connString);

  var db = new DataConnection(new LinqToDbConnectionOptions(builder));

  var fmb = db.MappingSchema.GetFluentMappingBuilder();
  fmb.Entity<AM112U>().Association(x => x.AM114U, (x, y) => x.AM114Id == y.Id);
  fmb.Entity<AM114U>().Association(x => x.AM112UList, (y, x) => x.AM114Id == y.Id);

  try {
    var testName = "TEST";
    var testList = (from x in db.GetTable<AM112U>()
                    where x.AM114U.NormalizedName == testName
                    select new {
                      Id = x.Id,
                      Name = x.NormalizedName ?? string.Empty,
                      EmailAddress = x.NormalizedEmailAddress,
                    }).ToList();
  } catch(Exception ex) {
    throw new Exception(db.LastQuery + " " + ex.Message);
  }

I get exception:

Exception: 
SELECT x.Id, Coalesce(x.NormalizedName, ''), x.NormalizedEmailAddress
FROM AM112U x
LEFT JOIN AM114U T500 ON x.AM114Id = T500.Id
WHERE T500.NormalizedName = CAST(? AS NCLOB)

ERROR [42S02] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0204 - NCLOB in *LIBL type *SQLUDT not found.

It should not require any cast, just use a parameter.

Why is my string being cast as NCLOB when the variable testName is a string?

Why isn't a parameter being used for testName string variable?

[nsarris]

Casting as a quirk of i series, it requires casting parameters in most places (there is an article on IBM on that). If you just add an uncasted parameter it throws an exception. Instead of trying to determine where a cast is needed, I decided cast all parameters, and specifically to upcast all text and numeric parameters to their largest possible version (e.g. text to clob, unicode text to nclob, numbers to decimal(60,30)) so that the value can fit.

The reason I selected *LOB types for casting string parameters is that they don't require length. The previous version casted to NVarChar(8000) so if your string parameter's length was larger than that it got trimmed resulting in data loss.

What version of iSeries is this on?

[nsarris]

NCLOB is an alias for DBCLOB CCSID 1200, can you try running the SQL replacing NCLOB with that? Just use any text value instead of the parameter marker.

[travbeamo]

Unfortunately I am still stuck using iSeries version v5r4.

As for using *LOB types for casting string parameters so that they don't require length, why not just either

• cast to char which doesn’t require a length
• or cast to varchar and use the length of the parameter value being used.

[travbeamo]

According to the sql reference for iSeries v5r4 (ftp://public.dhe.ibm.com/systems/power/docs/systemi/v5r4/en_US/rbafzmst.pdf), the VarChar scalar function doesn’t even need a length to be specified:

“If the second argument is not specified or DEFAULT is specified:

• If the character-expression is an empty string constant, the length attribute of the result is 1.
• Otherwise, the length attribute of the result is the same as the length attribute of the first argument.”

[nsarris]

Unfortunately where typed parameter markers are required the scalar casting functions don't work, it requires an explcit cast. In that context you can't cast to char/varchar without specifying the length, and because of linq2db query caching the length of the parameter is not fixed. Casting to char without length equals casting to char(1). So this only leaves casting to *LOB as the only universal solution for parameter markers.

I can provide a patch that casts to DBCLOB CCSID 1200 instead of NCLOB, and that will work for your problem but I'd rather support 5.4 formally if possible. I can help you setup and run the tests so we can support 5.4 if you would like to assist.

[nsarris]

Looking in the whats new sections of 6.1 to 7.4, it seems 5.4 doesn't support MERGE statements (new in 7,1), so besides the MERGE api, upserts won't work because they are performed with MERGE. Documentation also states that scalar functions are supported from 6.1, but I don't know if that affects us. We'll have to run the tests to make sure nothing else is broken.

[travbeamo]

@nsarris I've tried running the SQL replacing NCLOB with DBCLOB and can confirm it worked so that patch you mentioned would be greatly appreciated.

As for formally supporting v5r4, I'm happy to assist where I can, just let me know.

[nsarris]

Just to clarify , you tested by replacing NCLOB with DBCLOB CCSID 1200 , not just DBCLOB right?

I will prepare a branch for V5.4 support soon and let you know when it's ready. In essence I will fallback to default linq2db implementation for merge and upsert, which throws an exception, and will use DBCLOB CCSID 1200 instead of NCLOB when casting.

You can assist by running the tests and checking out what breaks. To do that you will need to:

1. Have a schema ready to run the tests in. If journaling is not enabled 4 Transaction tests will fail, that is acceptable.
2. Copy the DataProviders.json file to UserDataProviders.json and fill in the missing connection string details. This file is git ignored for security reasons.
3. Setup the providers you want to run. I assume in your case you can only allow "DB2.iSeries.ODBC.54". Carefull there, invalid settings will break test discovery.
4. Run the tests. Sometimes tests will break due to all tests using the same db and conurrency issues. You can run the data base creation test again and then run all failed tests again to make sure.

[travbeamo]

Ok just tested SQL below and had it worked with no issues.

SELECT x.Id, Coalesce(x.NormalizedName, ''), x.NormalizedEmailAddress
FROM AM112U x
LEFT JOIN AM114U T500 ON x.AM114Id = T500.Id
WHERE T500.NormalizedName = CAST('test' AS DBCLOB CCSID 1200)

[travbeamo]

Yes casting to DBCLOB CCSID 1200 worked but I still do not see why the need to cast parameters at all.

All of the code below executed without exception using:

• positional parameters for OdbcConnection, OleDbConnection &
• named parameters for iDB2Connection.

var testName = "test";

var sqlNamedParams = @"
SELECT x.Id, Coalesce(x.NormalizedName, '') As Name, x.NormalizedEmailAddress
FROM AM112U x
LEFT JOIN AM114U T500 ON x.AM114Id = T500.Id
WHERE T500.NormalizedName = @name
";

var sqlPositionalParams = sqlNamedParams.Replace("@name", "?");

using(var conn = new OdbcConnection(appSettings.ConnectionStrings.DB2i_Odbc_V1)) {
    conn.Open();
    using(var cmd = new OdbcCommand(sqlPositionalParams, conn)) {
      cmd.Parameters.AddWithValue(nameof(testName), testName);
      var result = cmd.ExecuteNonQuery();
    }
    conn.Close();
}

using(var conn = new OleDbConnection(appSettings.ConnectionStrings.DB2i_OleDb_V1)) {
    conn.Open();
    using(var cmd = new OleDbCommand(sqlPositionalParams, conn)) {
      cmd.Parameters.AddWithValue(nameof(testName), testName);
      var result = cmd.ExecuteNonQuery();
    }
    conn.Close();
}

using(var conn = new iDB2Connection(appSettings.ConnectionStrings.DB2i_IBM_V1)) {
    conn.Open();
    using(var cmd = new iDB2Command(sqlNamedParams, conn)) {
      cmd.Parameters.AddWithValue(nameof(testName), testName);
      var result = cmd.ExecuteNonQuery();
    }
    conn.Close();
}

Even when using the underlying OdbcConnection in the DataConnection created earlier .

using(var cmd = db.Connection.CreateCommand()) {
    cmd.CommandText = sqlPositionalParams;
    var p = cmd.CreateParameter();
    p.ParameterName = nameof(testName);
    p.Value = testName;
    cmd.Parameters.Add(p);
    var result = cmd.ExecuteNonQuery();
}

[travbeamo]

As for testing I'm happy to help,

Which branch should I be testing?

Are you going to create a new branch with the patched code or just test the master?

[travbeamo]

FYI not sure if you are aware, but there is a free (for everyone) and public server running IBM i 7.4 that can be used for testing.

https://www.pub400.com

• You can create your own user
• You have 300MB of disk storage and two private libraries
• You can program in CL, RPG, COBOL,
• Create your own programming projects, you can even use node.js or other web technologies

[travbeamo]

I have created a fork and a branch called support-v5r4 to do some tests

https://github.com/X10sions/Linq2DB4iSeries/tree/iseries_V5R4_support

I will let you know when all tests are passing.

[nsarris]

@travbeamo I have uploaded a branch called iseries_V5R4_support, use this one to run tests.

Copy the DataProviders.json to UserDataProviders.json and at the end remove all providers except the ones for ODBC/OleDb 5.4

Make sure you use the latest version of the driver from IBM.

There seems to be a concurrency issue when dropping and creating tables and thus some tests will fail with a "System Error". Try rerunning all failed tests to clear those cases out.

There is also one more known issue with the ODBC driver only, where TRUNCATE IMMEDIATE succeedes on the iSeries side but an overflow exception is produced from the reader when trying to retrive the result, so ignore those (I will patch those tests so they dont run in a future version).

You should have no other failures. Let me know how it goes.

[travbeamo]

Initial testing is failing as data type DECFLOAT is not supported in V5R4 and was introduced in V6R1.