Database access samples on IBMi - ADO.Net

[richardschoen]

I downloaded and installed the binary today. Seems to work nicely with the simple hello.cs.

Do you have any database samples yet using ADO.Net ?

Not sure if there is any database drivers that will work on IBMi yet with this port ?

Please let me know.

Thanks Richard

[NattyNarwhal]

Any ADO.NET provider and consuming code that doesn't call into native code unsupported on PASE should work. Note that if code depends on a native library that is supported, but doesn't load the library, then the DllMap may need to be adjusted. I think Mono comes with an MS SQL, Oracle, Unix DB2, and SQLite provider out of the box.

As mentioned in the README, the IBM-supplied DB2/400 provider won't work due to requiring an ODBC provider only available as a Win32 DLL. Working on cross-platform (including from i) ways to access DB2/400 in .NET is an eventual goal of the project, however.

[richardschoen]

Got it. I'll do a little test to see if I can get SQL Server driver to work.

[richardschoen]

What level of .Net is supported ? I might be able to tweak my XMLSERVICE wrapper code to work since that uses HTTP.

[NattyNarwhal]

The MS SQL driver changed quite a bit in newer Mono; if authentication doesn't work, let us know, as we may have to integrate Kerberos stuff inside. (I'll ask upstream to clarify the situation further, if needed.)

The latest .NET Framework should be supported; there's no need to use Core binaries.

[richardschoen]

Tried to use the System.Data namespace.

Should that be part of the base Mono build or is there something I need to include on the mcs command line ? I simply added the following line to the hello.cs program: System.Data.Datatable dt = new SYstem.Data.DataTable();

Got following compile error: CS0234: The type or namespace Data does not exist in the namespace System. Are you missing 'System.Data' namespace reference ?

My first time using command line compile with Mono :-)

[NattyNarwhal]

-r:System.Data should do it. Alternatively, you can compile software from another system (including compiled with Visual Studio) and it should work.

[richardschoen]

Yep. If I get SQL Server connection working I'll do a little speed test.

[richardschoen]

ConsoleApp2SqlClient.cs.txt

Very simple DB console app attached.

Throw following error when called from PASE on IBMi:

Error:Snix_Connect (provider: SNI_PN7, error: 25 - SNI_ERROR_25) Snix_Connect (provider: SNI_PN7, error: 25 - SNI_ERROR_25)

Used old style SQL Server connection string syntax.

I'll play some more, but it appears that something with DB access isn't quite right yet.

[NattyNarwhal]

We'll try to get a test environment set up; I myself am not familar with MS SQL, so it might take some poking once it happens. Some quick ideas:

• The server looks like SQL Server Express based on the name - is this licensed or configured to listen on the network?

• The connection string looks right to us. Might be worth reviewing anyways.

• You're using the "sa" user, which may be treated special.

• Could be a bug in Mono?

[richardschoen]

Yep the sql server is fine and talking to the network.

I'm betting on a bug in mono.

Stack trace in .net gave no detail.

Let me know if there's a secret to tracing these comms in Mono.

[glrodgers]

Yeah, I'm personally inclined to think it might be a Mono bug as well. Do you happen to know which version of SQL Server Express it was, just so we can replicate your test case?

[NattyNarwhal]

I do know that Mono has changed over to the CoreFX implementation of the MS SQL client, which IIRC, requires GSS libraries we don't include/use with the build. I'll have to look into seeing if those work, and what can be done to package them.

[glrodgers]

That would make a lot of sense, now that I think of it. Looking at the error, I can't help but think "SNI" in the output might be a reference to TLS Server Name Indication.

I did a bit of digging, this seems like it might be relevant: https://forum.unity.com/threads/unity-2018-using-entity-framework.512675/

(It's for the Unity game engine, but I know that uses Mono underneath the covers)

After that i think errors you linked started popping up only because Mono System.Data.SqlClient can't parse all of the connection string formats so i replaced my connection string i usually use in EF Core with a simpler older format: Data Source={SQL_INSTANCE_NAME};user id={SQL_USERNAME};password={SQL_PASS};Initial Catalog={DB:NAME};

@NattyNarwhal I'm going to install the latest MSSQL Express on a box on the same network as our build host tonight and see if I can get it working.

[richardschoen]

@glrodgers I am using 2014 Express. Make sure to enable TCP when you configure the SQL Server since that's not enabled by default.

[richardschoen]

Hey guys just checking in for status on this ? Now that you're getting some press the first thing people will be looking for is some level of database access. I'll test my XMLSERVICE wrapper for IBMi in the next week or so, but SQL Server and other DB types would be useful as well.

[NattyNarwhal]

Real life gets in the way, but a libdb400-based connector for fast local access got bumped up on my priority list. XMLSERVICE is all managed code, so it should work in the interim.

[richardschoen]

Just wanted to let you guys know I tested my IBM i XMLSERVICE wrapper and it seems to work fine with no special changes. Was able to run a remote command on the IBM i and query the database to a JSON file successfully and it was quick. I just created a couple of small console apps and run them against my pre-compiled DLL and it worked perfectly. Nice work so far. Db access to other platforms still needed but for me this should cover the immediate IBM i data access needs since PHP and Node rely on the same components for data access.

[NattyNarwhal]

Having XMLSERVICE working is good to hear. I had just started work on a from-scratch object-oriented libdb400 wrapper, but I still have the nagging thought in my mind to just refactor an LUW binding and get it up and running faster.

[richardschoen]

I might sound a little ignorant, but can you tell me what an LUW is ? :-)

On another note I took some time to refactor one of my other PASE wrappers to create a CL command called MONO which front ends the mono environment setup and program calls for classic IBM i developers to use from a CL or RPG program. The command runs the .Net app, pipelines the console output/stdout back into the IFS, physical file, IBM i primary job log or a spool file so that it can be consumed from a traditional/classic IBM i RPG or CL program. It also catches any failures on the call to Mono. I ended up calling the PASE program via QSHELL because QP2SHELL doesn't deal well with calling a multi-threaded env directly. Apparently in V7R3 (not sure about earlier yet) you can call PASE via QSHELL and QSHELL has always had some functionality to be called as multi-threaded from a traditional IBM i job. Hope that made sense.

I'll post the IBM i wrapper code on a repo soon. It will work out of the box with the environment you've set up.

One last note. How production ready do you think the mono code is currently ? (sans more expansive database support of course) Seemed stable from my initial tests, but needs more exercising.

Thanks again for your hard work on porting the mono code to IBM i. Not many would take that technical challenge on :-)

[NattyNarwhal]

LUW = DB2 on Linux/Unix/Windows. The SQLI/CLI API is 99% similar between LUW and i, hence why DB2 Connect works (it basically adapts DB2 on i to speak the DB2 LUW network protocol, AFAIK) if you have that installed. I tried pointing modified LUW clients at libdb400, but it's not 100%; it'll need modification, and might be easier than writing bindings from scratch. (SQL is a little outside my comfort zone, so I'm approaching the from-scratch bindings carefully.)

Green screen and particularily ILE integration is definitely something I want to look at.

Mono works, but it's definitely pre-release quality, and your call on how stable it is. The PPC JIT is a free-time passion thing from the guy who maintains it upstream, there may be AIX API/ABI quirks lurking I haven't squashed, sorting out ASP.NET, and deal with the some of the depency chains; but it's surprisingly stable for what it is. CI runs constantly and is able to pass a majority of the tests.

[richardschoen]

I was able to get the older TDS style SQL Server connections to work against SQL Server 2014 from the Mono.Data.Tds.dll library. System.Data.SqlClient still not working right, but was able to use the Mono.Data.Tds.Protocol.Tds80 object to connect and query data from a SQL Server 2014 database. Super speedy.

[NattyNarwhal]

Nice. I suspect the JIT is surprisingly speedy despite the fact it doesn't apply optimizations beyond POWER5; there's some stuff about an IR form and optimizing that I don't know the specifics of. I've been meaning to try some benchmarks against J9.

Fixing up System.Data's MS SQL driver is on my TODO list, but good to know there's a workaround replacement library. As I got the notification for this though, I actually just got a hacked up DB2 LUW library to work against libdb400 for a super simple test case - more advanced ones shouldn't be too bad.

[richardschoen]

Nice. Once you get it to working order with a sample I'm happy to test it out. I'll plan to do some tests with the SQLite, MySQL and Postgres drivers after vaca.

[NattyNarwhal]

SQLite I think I tested to be working; you might just need to adjust the DllMap entries.