# Controlling an AS/400 or System i with c#

### Introduction
Some time ago we had to develop and deploy a new system side application for our customers. As our customer base is using mainly Windows and System i, and our whole new system configurations have been developed in c# we had to decide which way we go. 
Either rewrite the current codebase in c++ and port it to System i or find another solution. As c++ is very specific on the System i, it would have been an own development on its own.

After some digging in the internet I came across some articles (sadly I haven't saved them, so I cannot reference these) which described the execution of scripts by using sql (strsql) on the System i. 

### Prerequisites
I installed the IBM i Access client solution on my dev workstation, including the ODBC-Drivers for DB2 and started up Visual Studio. As a side note: at this time there was no native .net DB2 driver and later on I never got it really running - however as we want to control the System i and not transfer insert/update many data in the database, I did not care about using ODBC.

The whole integration ended up in using:
- ODBC to access the database and execute commandos (Access to QSYS.QCMDEXC required
- The IFS (integrated file system) for transferring files by SMB/CIFS 

A specific connection string should be used, to set the required library list:
```
Driver={Client Access ODbC Driver (32-bit)};System=i5system;Uid=user;Pwd=password;DefaultLibraries=ITLIB1,ITLIB2
```

### Executing cl procedures 
By executing a normal 'non query' and [QSYS.QCMDEXC](https://www.ibm.com/docs/en/i/7.1?topic=ssw_ibm_i_71/apis/qcmdexc.htm) we can execute any command. Here we will execute a CL procedure in ITLIB2 which is called TESTCN. The CL procedure which we call will receive 2 parameters and the 'native' call on the System i is stored in the variable clCmd:
```
string clCmd = "CALL PGM(ITLIB2/TESTCN) PARM('1stParam' '2condParam')";
```

The call itself, please note that we need to escape the single quotes ' when using them in the sql query. Furthermore, QCMDEXC does require the length of the command string in 15,5 packed format.
```
 string pgmParm = $"CALL QSYS.QCMDEXC('{cmdtext.Replace("'", "''")}', {clCmd.Length.ToString("0000000000.00000").Replace(',', '.')})";
```

### Error handling
An error at calling will throw an exception (OdbcException) which can then be examined for the exact error code.

### Spool files
For reading spool files with SQL, [SYSTOOLS](https://www.ibm.com/docs/en/i/7.4?topic=systools-using) with [SPOOLED_FILE_DATA](https://www.ibm.com/docs/en/i/7.4?topic=services-spooled-file-data-table-function) is available. 

### Conclusion
Naturally, a native port would have been much better implementation BUT by creating some CL procedures and building up an architecture where the platform calls are handled by specific procedures/functions we got a fluent integration which can run from any windows operating system.