IBM.Data.DB2.iSeries DLL

[tb-mtg]

It would be nice if this could support the DB2 for iSeries dll available from https://www.nuget.org/packages/IBM.Data.DB2.iSeries/ in your redist folder https://github.com/linq2db/linq2db.LINQPad/tree/master/Redist/IBM

[ili]

What about PR? Have no possibility to test anything with iSeries :(((

[kpgmr72]

hmmm. This is exactly what i am trying to do as well, and i DO have access to test enviroments for the i. My thought was this... starting with the linq2db.LINQPad project, add the driver from https://github.com/LinqToDB4iSeries/Linq2DB4iSeries into the project to have native iseries access. Please forgive me if i sound naive. I am only a budding C# programmer (spend most of my time doing iSeries stuff) and do not know if this is even possible. One thing i do know is that IBM is charging to use the IBM.Data.DB2 assembly with iSeries based db2 and they do not charge to use IBM.Data.DB2.iSeries.

[sdanyliv]

I’m working on support iSeries in LINQPad. It will be released after 2.0. Since we have no access to iSeries testing environment, your help in testing will be appreciated.

[tb-mtg]

The link below can help you setup a free testing environment:

https://www.nicklitten.com/free-ibm-i-system-for-developers

[abgenullt]

Maybe you can make the supported drivers configurable through linqpad or a config file, if possible. So that you can add any custom drivers not only the Linq2DB4iSeries.

[sdanyliv]

Will add support of iSeries soon. I have done preparations for that. But i need your help in testing because we do not have environment for this database.

[abgenullt]

That would be great. If you provide an alpha version or anything like that, we could test a few things. But we only have a productive environment, so we could test only the basics.

[sdanyliv]

You just have to ensure that provider loaded and queries work. @RoyC-Retail-Assist, can you update iSeries provider to latest linq2db release?

[abgenullt]

Sure, that would be no problem.

[RoyC-Retail-Assist]

It works with V2.0 except for CTE. I will try to test with 2.1 this weekend.

There is a RC nuget https://www.nuget.org/packages/linq2db4iSeries/2.0.0-rc1

[sdanyliv]

@RoyC-Retail-Assist, deploying your library with LINQPad will be enough if client libraries are installed?

[RoyC-Retail-Assist]

I've not used LinqPad and was rather looking forwards to see how it is done.

In .Net code I usually create a DataContext thus.

    public class DBConnection : LinqToDB.Data.DataConnection
    {
        public DBConnection(string conn) : base(new LinqToDB.DataProvider.DB2iSeries.DB2iSeriesDataProvider(), conn)
        {
        }

        public ITable<MyFileFile> TestTable { get { return GetTable<MyFile>(); } }
    }

ProviderFactory methods should also work.

[RoyC-Retail-Assist]

In addition to above. The iSeries provider requires the installation of IBM iNavigtor which has the ADO.Net provider and also installs the COM objects that it needs. Unfortuantly this is only availble from IBM and is not, as far as I know, a public download.

[sdanyliv]

@RoyC-Retail-Assist, looks like you have implemented four DataProviders for iSeries. But ProviderDetector is not complete. You have to detect version based on opened connection. In this case i can add just one item in LINQPad for iSeries and ProviderDetector should return correct DataProvider for opened connection. Check SqlServerTools for sample.

[RoyC-Retail-Assist]

I've had a look at ProviderDetector and can see what needs doing. I'm on vacation from Thursday so may not be able to work on this for two weeks.

[sdanyliv]

NP, have a good vacation ;)

[RoyC-Retail-Assist]

I've pushed branch V2.1 of the iSeries DataProvider. The provider detector should return the correct provider based upon the connection string.

If you need a nuget package for this I can sort out a RC version.

If you let me have a copy of the LinqPad driver I will do some testing with it.

[sdanyliv]

@RoyC-Retail-Assist, i have expected that you open connection and ask server for version (some select), not just parsing connections string. Or it is not possible with IBM products? This code just opens and do not use connection.

using (var conn = connectionCreator(cs))
{
    conn.Open();

    return GetDataProvider(css.Name, connectionString);
}

[RoyC-Retail-Assist]

For the server version this could be done as the iDB2Connection object has a ServerVersion property but the mapping of Guids as strings is something that is done by the provider not by the database hence having to flag it this way. Also by specifying the server version this way I am able to test the provider code works for both methods against a single v7.3 server which is important for us as we have code running against V7.1 even though we develop against v7.3.

I suspect that when I get time to test CTEs being able to target specific versions may become more helpful as I know that the CTE capability of newer versions is better. My unit tests run against all combinations of these flags so will hopefully pick up any issues.

[sdanyliv]

@RoyC-Retail-Assist, not in this place. Here, if you have connection string and specified "DB2.iSeries" provider name and AutoDetectProvider is true - you have to connect to the server and get version from server. How it works with SQL Server: https://github.com/linq2db/linq2db/blob/master/Source/LinqToDB/DataProvider/SqlServer/SqlServerTools.cs#L92

[RoyC-Retail-Assist]

So if AutoDetectProvider = true it is expecting to get the provider based upon retrieving the details from the server? This could be done for the version but I would still need to have some way for the user to specify the MapGuidAsString option and the connection string would seem to be the only way of doing this.

The MinVer defaults to the lowest version so would be "safe" (the SQL generated would work even if not the most efficient) if it failed to get set correctly. MapGuidAsString affects how Guids are stored/retrieved so there isn't a "default" setting for it as having the wrong setting will cause errors.

Will how I have coded the ProviderDetector work as is? If so can we work to getting something that I can do some testing with as I have a gut feeling that there could be other issues that might need fixing e.g. SchemaProvider isn't something that we are using here so may possibly have bugs.

Do you have a sample test that I can use on the provider detector so that I can run through some scenarios?

[sdanyliv]

You can get branch iseries_support install LINQPad 4 - it has trial license. And just run in debug. Add connection, and select LINQ To DB from top list box.

[RoyC-Retail-Assist]

I've get the iseries_suport branch but running in VS debug cannot start class library. I guess that the VS Start Action isn't saved into the git as it is a user setting. Can you let me know what it should be please.

I'm presuming that it should work with LinqPad 5 as I already have a licensed version of that.

[sdanyliv]

Debug Start external program: C:\Program Files (x86)\LINQPad4\LINQPad.exe

[RoyC-Retail-Assist]

I have connection and the tables are populated but if I try to run table.Dump() on any of them I get a System.MissingMethodException Constructor on type 'UserQuery' not found. This is with LinqPad 5

[sdanyliv]

Try to run in expression mode just SomeTable. Will check what happened in version 5. Maybe Dump requires something.

[sdanyliv]

@RoyC-Retail-Assist, i have same problem. Fixing...

[sdanyliv]

I have updated branch. It was regression.

[RoyC-Retail-Assist]

I've got it running and it seems to work but the ProviderDetector doesn't seem to work at all so I'm going to try to get things bottomed out with that.

I'm trying to write out some debug data from the detector but neither Console.WriteLine nor Debug.WriteLine or Trace.WriteLine seem to output anything. Do you have any suggestions?

[sdanyliv]

@RoyC-Retail-Assist, maybe because you have already registered dataprovider

DataConnection.AddDataProvider(DB2iSeriesProviderName.DB2, _db2iDataProvider);

Remove this line and give control for ProviderDetector. And test using the following command:

DataConnection.GetDataProvider(DB2iSeriesProviderName.DB2, connectionString);

[RoyC-Retail-Assist]

I removed all the AddDataProvider statements and the ProviderDetector looks to be run. I'll do more testing on it tomorrow. Thanks for your help.

[sdanyliv]

It is not needed to remove all, but only this one.

[RoyC-Retail-Assist]

I've managed to find out how to get the necessary details from the server to determine which version it needs to use and have tided the ProviderDetector accordingly. I'm still unsure how we might allow the selection of the GuidAsString option. Changes pushed.

What would be the effort involved to add another string field into the Connection Options panel just below the Friendly Name that could be used to supply the necessary setting? The user would then be able to add any provider specific option that may be available.

[sdanyliv]

@RoyC-Retail-Assist, let's solve problems one by one :) Currently we will use default setting.

[RoyC-Retail-Assist]

Thinking about it - this is just showing "raw" data so isn't really a problem until someone wants to use their own objects. If we can get the conditional mapping stuff working then it may probably eliminate the necessity for provider option as the appropriate mapping could, hopefully, be determined from the target property.

[sdanyliv]

I have idea to provide some meta information for LINQ Pad driver to build more informative connection screen. Also i'm thinking about improving whole options model because currently it is static which is bad.

[sdanyliv]

@RoyC-Retail-Assist, so which status of your part? Can we deliver new LINQPad version?

[RoyC-Retail-Assist]

I pushed a branch https://github.com/LinqToDB4iSeries/Linq2DB4iSeries/tree/V2.2 that is compatible with Linq2DB V2.2.0 and is good to go.

I'd like to be able to provide the MapGuidAsString option but that could be for a later release.

[RoyC-Retail-Assist]

I've just released a NuGet package v2.2.0.3 which may be better to use.

[abgenullt]

While populating, I get the error: Object name SYSTABLES is not valid.

But I can successfully perform a SELECT * FROM SYSTABLES directly on the iSeries with the same user...

[RoyC-Retail-Assist]

I'm guessing that it your library list setting on your connection string. You will need to have the Library List parameter in your connection string start with a comma so that the specified libraries are appended to the user's default libraries.

Will the connection string allow you to access SYSTABLES if used in C# code?

[abgenullt]

You are right, I can't access SYSTABLES in C#. Only, if use QSYS2.SYSTABLES. Somehow , the LibraryList parameter doesn't work. If I add it, I can't even connect with the message "At least one library has to be added to the library list".

My connection string looks like that: DataSource=...; LibraryList=,QSYS2; Password=...; UserID=...;

[RoyC-Retail-Assist]

The provider needs to have the libraries explicitly provided in the library list so that it can determine which tables to use. So if you want to see tables from the FOO and BAR libraries you will need to set Library List = ,FOO,BAR .

[abgenullt]

I provide working libraries but I still get the this error, if I want to add a new connection:

[abgenullt]

It works! I added the parameter Naming=SYSTEM to the connection string. Now, the populating works...

[abgenullt]

Ok, I've tested insert, select, update and delete statements. Everything without any error. Thank you very much.

[RoyC-Retail-Assist]

@sdanyliv this doesn't seem to have been released yet. Do you have an ETA ?

[sdanyliv]

We will try to do that tomorrow. PR needs review.

[sdanyliv]

@RoyC-Retail-Assist, we plan to release linq2db this weekend. And possible with new LINQPad. Could you please check that LINQPad connects to iSeries successfully? Just build from master.

[RoyC-Retail-Assist]

Sorry I was on vacation last week.

It looks to work OK however it is using v2.1.0.0 of the DB2iSeries provider. It would be better to use the 2.2.0.3 package from NuGet https://www.nuget.org/packages/linq2db4iSeries/

If there is a new version of Linq2DB (presumably 2.4.0) that will be included with this then, ideally, I would like to tests the DB2iSeries provider against this new version and release an update if necessary before the LinqPad update is released.