Sewing DB2 JDBC under .NET
In a very strange time, we live. NET becomes cross-platform, JAVA becomes sweeter. But while we are moving together towards a common bright future, there are many inherited solutions that need to be supported. And while this is possible with a jigsaw and file ...
Currently, the driver supports only the functionality that is needed in our projects. You can always join the project Wintegra.Data
Although lately, relational databases are somehow not in the hype and give everyone CAP systems - fast, convenient, sometimes consistent and scalable. And yet, Interprize lives on old and proven solutions, one of which is the Blue Giant database — IBM and its name is DB2 .
')
Moderately fast, reliable, no worse and no better than Oracle and MS SQL. There is something, there is something, but in general SQL supports notation (although it didn’t do without offense at dogs @)
IBM supplies an ODBC driver to work with its base for .NET, which is not bad in general, but uses a small native code cart.
On the other hand, under JAVA there is a fully working driver without any use of third-party dll, its name is JCC (JDBC 4.0 Driver (db2jcc4.jar)).
And it's not clear what prevents IBM from implementing the “right” diver for .NET.
Since the .NET Core is not far off, and rewriting tons of code under JAVA with C # looks more than strange, then why not kill two birds with one stone - leave the code, and use the JDBC driver.
First we will use IKVM.NET to convert jar to dll and use it in .NET. The author claims that you can safely run Minecraft . So the JDBC driver did not cause any problems.
Create the necessary library simply by running the command:
Now all we need is to connect the created dll to our solution and use it.
However, I don’t think that .NET developers will appreciate the syntax in the form of java.lang. *, But recalling inherited projects that explicitly run at least on top of IDbConnetion and often through Dapper , our result can be interesting only as an optional entertainment.
Having prepared a jigsaw and a file, we will make our bike, as far as we need it.
It is quite enough to create a connection and work Dapper.
To bring benefits to the database, we need commands; we need to do it:
Where I had to tinker with it so Dapper.
For commands, parameters are needed:
And at the end:
Implementing multi query through JDBC.
Of course, the project is not completed yet, but in the future there is a desire to replace the shell implementation above db2jcc4.jar with a “normal” driver implementation under .NET without any extra squats.
What may be needed?
Well, firstly, you can not put the ODBC driver on the user's machine, although yes you will not win in volume.
Secondly, you can slowly move your backend to linux from mono. Which is very convenient with a little Docker whisper
And of course, not much for the fan, may the complex request come with you:
Link to the package in NuGet .
Before
Currently, the driver supports only the functionality that is needed in our projects. You can always join the project Wintegra.Data
Story
(for impatient, you can immediately go to the item Pilim )Although lately, relational databases are somehow not in the hype and give everyone CAP systems - fast, convenient, sometimes consistent and scalable. And yet, Interprize lives on old and proven solutions, one of which is the Blue Giant database — IBM and its name is DB2 .
')
Moderately fast, reliable, no worse and no better than Oracle and MS SQL. There is something, there is something, but in general SQL supports notation (although it didn’t do without offense at dogs @)
IBM supplies an ODBC driver to work with its base for .NET, which is not bad in general, but uses a small native code cart.
On the other hand, under JAVA there is a fully working driver without any use of third-party dll, its name is JCC (JDBC 4.0 Driver (db2jcc4.jar)).
And it's not clear what prevents IBM from implementing the “right” diver for .NET.
Since the .NET Core is not far off, and rewriting tons of code under JAVA with C # looks more than strange, then why not kill two birds with one stone - leave the code, and use the JDBC driver.
First we will use IKVM.NET to convert jar to dll and use it in .NET. The author claims that you can safely run Minecraft . So the JDBC driver did not cause any problems.
Create the necessary library simply by running the command:
ikvmc.exe -classloader:ikvm.runtime.AppDomainAssemblyClassLoader -target:library db2jcc4.jar db2jcc_license_cu.jar -out:db2jcc4.dll Now all we need is to connect the created dll to our solution and use it.
Java code in .NET
using System; using com.ibm.db2.jcc; using java.sql; using Thread = java.lang.Thread; using Class = java.lang.Class; using String = System.String; using Connection = java.sql.Connection; using Statement = java.sql.Statement; using DriverManager = java.sql.DriverManager; using ResultSet = java.sql.ResultSet; namespace jdbc { class Program { static void Main(string[] args) { Class.forName( typeof(com.ibm.db2.jcc.DB2Driver).AssemblyQualifiedName, true, Thread.currentThread().getContextClassLoader()); String url = "jdbc:db2://192.168.72.135:50000/DB1:user=root;password=password;"; using (Connection conn = DriverManager.getConnection(url)) { String sql = "SELECT * FROM TABLE(VALUES( CAST( :p AS VARCHAR(100)) , 'It is work')) AS T(ID, LOG)"; using (var stmt = conn.prepareCall(sql)) { stmt.setString("p","1234"); using (ResultSet rs = stmt.executeQuery()) { while (rs.next()) { //Retrieve by column name String id = rs.getString("ID"); String log = rs.getString("LOG"); global::System.Console.WriteLine("LOG: " + id + " : " + log); } } } } } } } However, I don’t think that .NET developers will appreciate the syntax in the form of java.lang. *, But recalling inherited projects that explicitly run at least on top of IDbConnetion and often through Dapper , our result can be interesting only as an optional entertainment.
Sawing
Having prepared a jigsaw and a file, we will make our bike, as far as we need it.
public sealed class Db2Connection : DbConnection, ICloneable It is quite enough to create a connection and work Dapper.
Db2connection
A bit of magic from the world of JAVA
We load the JDBC driver, almost the same as in JAVA as
Database connection - one to one of JAVA:
It should be noted that the connection string is the same as in JDBC (although, if necessary, you can use Db2ConnectionStringBuilder to set your own or ODBC-compatible parameters).
Connection to the server, port, database, scheme, login and password. For customization, you can look at the Blue Giant website URL format for the IBM Data Server Driver for JDBC and SQLJ type 4 connectivity .
Like everything that could be interesting ...
static Db2Connection() { Class.forName( typeof(com.ibm.db2.jcc.DB2Driver).AssemblyQualifiedName, true, Thread.currentThread().getContextClassLoader()); } We load the JDBC driver, almost the same as in JAVA as
Class.forName("com.ibm.db2.jcc.DB2Driver"); Database connection - one to one of JAVA:
connector = DriverManager.getConnection(_connectionString); It should be noted that the connection string is the same as in JDBC (although, if necessary, you can use Db2ConnectionStringBuilder to set your own or ODBC-compatible parameters).
jdbc:db2://192.168.72.135:50000/DB1:currentSchema=DB01;user=root;password=password;fullyMaterializeLobData=true;DB2NETNamedParam=1; Connection to the server, port, database, scheme, login and password. For customization, you can look at the Blue Giant website URL format for the IBM Data Server Driver for JDBC and SQLJ type 4 connectivity .
Like everything that could be interesting ...
To bring benefits to the database, we need commands; we need to do it:
public sealed class Db2Command : DbCommand, ICloneable Where I had to tinker with it so Dapper.
Db2command
Dapper stubbornly replaces the parameter: XML_BODY of the XmlDocument type with an indistinct (: XML_BODY1,: XML_BODY2) - I had to write a non-complex regular.
Although yes, it was possible to file TypeHandler, for the curious Dapper - ulong throwing System.ArgumentException . But first, the wrapper on ODBC already worked with an XmlDocument, and second, the use of the magic commands of the form, at the beginning of the application:
always fraught with errors.
Returns a query prepared for execution.
The explanation of using prepareCall ( CallableStatement ) in the place prepareStatement ( PreparedStatement ) deserves special attention. The use of positional parameters is simpler, but in some cases it is not convenient; the use of named parameters requires some additional work, which reduces the speed quickly and increases the complexity of the code. The preference was given to the named parameters, since with them it is easier to debug the requests going to the server and gives a bonus in the case when a multi-request is executed.
For his magic uses the other two.
Replaces positional parameters with named ones.
Dapper's little hoax cost as much as two goto. And although it was possible to do without them, the solution is simple and left as satisfactory.
ref int parameter - needed for Db2DataReader and implementation of multi queries.
Sets the parameters.
A weighty if and calling the set of methods stmt.setXXX (name,
new Regex(@"\(:(?<n>\w+)\d+,:\1\d+\)"); Although yes, it was possible to file TypeHandler, for the curious Dapper - ulong throwing System.ArgumentException . But first, the wrapper on ODBC already worked with an XmlDocument, and second, the use of the magic commands of the form, at the beginning of the application:
SqlMapper.AddTypeHandler(DapperULongHandler.Default); always fraught with errors.
Magic method number of times
internal static CallableStatement PrepareExecute(Db2Connection connection, string query, Db2ParameterCollection parameters) Returns a query prepared for execution.
The explanation of using prepareCall ( CallableStatement ) in the place prepareStatement ( PreparedStatement ) deserves special attention. The use of positional parameters is simpler, but in some cases it is not convenient; the use of named parameters requires some additional work, which reduces the speed quickly and increases the complexity of the code. The preference was given to the named parameters, since with them it is easier to debug the requests going to the server and gives a bonus in the case when a multi-request is executed.
For his magic uses the other two.
Magic method number two
internal static string PrepareCommandText(string query, Db2ParameterCollection parameters, ref int parameter) Replaces positional parameters with named ones.
Dapper's little hoax cost as much as two goto. And although it was possible to do without them, the solution is simple and left as satisfactory.
ref int parameter - needed for Db2DataReader and implementation of multi queries.
Last magic method
private static void PrepareParameters(CallableStatement stmt, Db2ParameterCollection parameters) Sets the parameters.
A weighty if and calling the set of methods stmt.setXXX (name,
For commands, parameters are needed:
public sealed class Db2ParameterCollection : DbParameterCollection, IList<Db2Parameter> public sealed class Db2Parameter : DbParameter, ICloneable Db2parameter
For Dapper, you need to conjure a little over the method:
since it is used for type casting, otherwise we implement the interface.
And you need to be very careful about the method:
As we need to create copies of parameters at multi requests.
public override DbType DbType since it is used for type casting, otherwise we implement the interface.
And you need to be very careful about the method:
public object Clone() As we need to create copies of parameters at multi requests.
Db2ParameterCollection
It deserves attention only because of the presence of a small optimization of the method:
public override int IndexOf(string parameterName) otherwise nothing substantial.And at the end:
internal sealed class Db2DataReader : DbDataReader Implementing multi query through JDBC.
Db2DataReader
Perhaps this is not the best solution, but why not.
First , you need to split the query into subqueries. No surprises simple regular expression
Secondly , it is necessary to prepare for each of the subqueries its own set of parameters. Take advantage of
Thirdly , a small hack in public override bool NextResult (), to move from one command to the next.
First , you need to split the query into subqueries. No surprises simple regular expression
new Regex(@"(?<q>[^;]+);?"); Secondly , it is necessary to prepare for each of the subqueries its own set of parameters. Take advantage of
new Regex(@"(?<n>:\w+)"); and the familiar Db2Command.PrepareCommandText method for replacing positional parameters with their named parameters (as a bonus: Dapper can properly work on named parameters in multi queries).Thirdly , a small hack in public override bool NextResult (), to move from one command to the next.
var b = _statement.getMoreResults(); if (b) { _rs = _statement.getResultSet(); } else { _statement = PrepareStatementAndResultSet(); if (_statement != null) { _rs = _statement.executeQuery(); b = true; } } Results
Of course, the project is not completed yet, but in the future there is a desire to replace the shell implementation above db2jcc4.jar with a “normal” driver implementation under .NET without any extra squats.
What may be needed?
Well, firstly, you can not put the ODBC driver on the user's machine, although yes you will not win in volume.
Secondly, you can slowly move your backend to linux from mono. Which is very convenient with a little Docker whisper
And of course, not much for the fan, may the complex request come with you:
SELECT
SELECT r.ID , r.INCOME , r.GU_CODE as GuCode , r.NO as No , r.DIVISION , d.NAME as DivisionName , u.LNAME || ' ' || u.FNAME || nvl(' ' || u.MNAME, '') as CreaterName , r.SIGNED , r.SIGNED_SYSUSER_ID as SignedID , su.LNAME || ' ' || su.FNAME || nvl(' ' || su.MNAME, '') as SignedName --, (select count(*) from PACK_ENTRY p where p.REGISTRY_ID = r.ID) as PackCount -- Use C# code --, (select count(*) -- from PACK_ENTRY p -- join FILE_ENTRY f on f.PACK_ID=p.ID -- where p.REGISTRY_ID = r.ID) as FileCount -- Use C# code -- TODO 2016-04-13 emiya: . REGISTRY_ENTRY , (select p.CLOSE_DATE from PACK_ENTRY p join REGISTRY_TO_PACK p2p on p2p.REGISTRY_ID = r.ID and p2p.PACK_ID = p.ID order by p.INCOME fetch first 1 rows only) as CloseDate , (select p.CATEGORY_ID from PACK_ENTRY p join REGISTRY_TO_PACK p2p on p2p.REGISTRY_ID = r.ID and p2p.PACK_ID = p.ID order by p.INCOME fetch first 1 rows only) as CategoryCode , (select l.SHORTNAME from PACK_ENTRY p join CATEGORY_LIST l on l.ID = p.CATEGORY_ID join REGISTRY_TO_PACK p2p on p2p.REGISTRY_ID = r.ID and p2p.PACK_ID = p.ID order by p.INCOME fetch first 1 rows only) as CategoryName , p2p.REGISTRY_ID , p.ID , p.INCOME , p.CLOSE_DATE as CloseDate , p.CATEGORY_ID as CategoryCode , l.SHORTNAME as CategoryName , p.GU_CODE as GuCode , p.NO , p.DIVISION , p.SYSUSER_ID as SysUserID , (select count(*) from FILE_ENTRY where PACK_ID = p.ID) as Count , r.SIGNED , p.PLACE , p2p.STATUS , p2p.NOTE from REGISTRY_ENTRY r join DIVISION d on d.CODE=r.DIVISION join SYSUSER u on u.ID=r.SYSUSER_ID LEFT JOIN SYSUSER su ON su.ID=r.SIGNED_SYSUSER_ID join REGISTRY_TO_PACK p2p on p2p.REGISTRY_ID = r.ID join PACK_ENTRY p on p.ID = p2p.PACK_ID join CATEGORY_LIST l on l.ID = p.CATEGORY_ID WHERE r.ID=? AND @DIVISION order by r.ID,p.GU_CODE,p.NO Link to the package in NuGet .
Source: https://habr.com/ru/post/303878/
All Articles