Recently IBM has announced .NET 7 support for Linux on Power. Thus, you can now run .NET applications on IBM Power. In this blog, we explain how to connect a sample .NET 7 application (that runs on Power) to MariaDB using ODBC connector.
For the purpose of this blog, we have taken an example of the .NET 7 application that connects to MariaDB server running on the same local host. However, you can modify this sample application to connect to a remote server as well. This application has been verified on a Power virtual machine (VM) running RHEL version 8.7, but it should work on RHEL version 9.1 as well.
Connecting a .NET application to MariaDB using an ODBC connector involves the following steps:
· Setting up the MariaDB server
· Installing the ODBC driver and connector
· Installing .NET 7
· Creating a .NET 7 Console application
Each of the above steps is explained in detail in the following sections.
Setting up the MariaDB server
Complete the following steps to set up a MariaDB server on your local system:
1. Install the MariaDB server using dnf/yum (software package manager).
#install MariaDb Server
dnf install -y mariadb-server
2. Start the MariaDB service.
#start MariaDB service
systemctl start mariadb.service
3. Enable the MariaDB service to start at boot.
systemctl enable mariadb.service
4. Create a database for testing purpose using the command line tool.
# Invoke mysql client from command line to get MariaDB command prompt
mysql
# Create database
CREATE DATABASE <Your Database Name>;
# Switch to newly created database
USE <YourDatabaseName>;
# Create table in database with different columns
CREATE TABLE <YourTableName> ( <ColoumnName1> <datatype1>, <ColoumnName2> <datatype2>, ..);
# Insert some dummy data into table
INSERT INTO <YourTableName> VALUES (<DummyData1>, DummyData1>, ...);
# View table
SELECT * FROM <YourTableName> ;
#exit from MariaDB client prmot
quit
5. The output for the test database and table that you created in step 4 should look as follows.
[~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.35-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> CREATE DATABASE TestDB;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> USE TestDB;
Database changed
MariaDB [TestDB]> CREATE TABLE Persons ( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) );
Query OK, 0 rows affected (0.003 sec)
MariaDB [TestDB]> INSERT INTO Persons VALUES (102, 'LMN', 'PQR', 'abc road', 'xyz city');
Query OK, 1 row affected (0.001 sec)
MariaDB [TestDB]> INSERT INTO Persons VALUES (101, 'ABC', 'DEF', 'xyz road', 'Pune');
Query OK, 1 row affected (0.001 sec)
MariaDB [TestDB]> SELECT * FROM Persons ;
+----------+----------+-----------+----------+----------+
| PersonID | LastName | FirstName | Address | City |
+----------+----------+-----------+----------+----------+
| 102 | LMN | PQR | abc road | xyz city |
| 101 | ABC | DEF | xyz road | Pune |
+----------+----------+-----------+----------+----------+
2 rows in set (0.000 sec)
MariaDB [TestDB]> quit
Bye
Installing the ODBC driver and connector
Complete the following steps to install the ODBC driver and connector:
1. Install the unixODBC driver manager and MariaDB ODBC connector using dfn/yum
yum install -y unixODBC.ppc64le unixODBC-devel.ppc64le unixODBC-debuginfo.ppc64le unixODBC-debugsource.ppc64le
yum install -y mariadb-connector-odbc.ppc64le mariadb-connector-odbc-debuginfo.ppc64le mariadb-connector-odbc-debugsource.ppc64le
2. Verify your set up by running the odbcinst commands as follows:
# Print the current configuration of unixODBC
odbcinst -j
# Query a list of drivers in /etc/odbcinst.ini
odbcinst -j -d
# Print details of MariaDB driver
odbcinst -q -d -n MariaDB
3. Ensure that the output that is displayed is as follows:
# odbcinst -j
unixODBC 2.3.7
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
# odbcinst -q -d
[PostgreSQL]
[MySQL]
[FreeTDS]
[MariaDB]
# odbcinst -q -d -n MariaDB
[MariaDB]
Description=ODBC for MariaDB
Driver=/usr/lib/libmaodbc.so
Driver64=/usr/lib64/libmaodbc.so
FileUsage=1
# ll /usr/lib64/libmaodbc.so
-rwxr-xr-x. 1 root root 410712 Apr 23 2021 /usr/lib64/libmaodbc.s
Installing .NET 7
Install .NET 7 using yum
yum install dotnet
Creating a .NET 7 console application
Complete the following steps to create a .NET 7 console application:
1. Create a console application using the dotnet new command.
dotnet new console -o odbc_sample
2. Ensure that the output that is displayed is as follows:
# dotnet new console -o odbc_sample
The template "Console App" was created successfully.
Processing post-creation actions...
Restoring odbc_sample.csproj:
Determining projects to restore...
Restored odbc_sample.csproj (in 234 ms).
Restore succeeded.
3. Add the System.Data.Odbc package using the dotnet add package command. This command adds a package reference in the csproj file.
# cd odbc_sample
[odbc_sample]# dotnet add package System.Data.Odbc
[odbc_sample]# cat odbc_sample.csproj
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>net7.0</TargetFramework>
<ImplicitUsings>enable</ImplicitUsings>
<Nullable>enable</Nullable>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="System.Data.Odbc" Version="7.0.0" />
</ItemGroup>
</Project>
4. Copy the following code in the Program.cs file. This sample application is used to connect to the YourTestDatabase on the local MariaDB server and to read data from all rows and columns from the YourTestTable, and to display the same on console output.
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Odbc;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
string MyConString = "DRIVER={MariaDB}; " +
"SERVER=localhost; " +
"DATABASE=<YourDatabaseName>; " +
"UID=root; " +
"PWD=;";
OdbcConnection DbConnection = new OdbcConnection(MyConString);
DbConnection.Open();
OdbcCommand DbCommand = DbConnection.CreateCommand();
DbCommand.CommandText = "SELECT * FROM <YourTableName>";
OdbcDataReader DbReader = DbCommand.ExecuteReader();
int fCount = DbReader.FieldCount;
Console.Write( ":" );
for ( int i = 0; i < fCount; i ++ )
{
String fName = DbReader.GetName(i);
Console.Write( fName + ":" );
}
Console.WriteLine();
while( DbReader.Read())
{
Console.Write( ":" );
for (int i = 0; i < fCount; i++)
{
String col = DbReader.GetString(i);
Console.Write(col + ":");
}
Console.WriteLine();
}
DbReader.Close();
DbCommand.Dispose();
DbConnection.Close();
}
}
}
5. Build the project using the dotnet build command.
[odbc_sample]# dotnet build
MSBuild version 17.4.0+18d5aef85 for .NET
Determining projects to restore...
Restored /root/odbc_sample/odbc_sample.csproj (in 475 ms).
odbc_sample -> /root/odbc_sample/bin/Debug/net7.0/odbc_sample.dll
Build succeeded.
0 Warning(s)
0 Error(s)
Time Elapsed 00:00:07.78
6. Use the dotnet run command to run this application. Run command fails with error “System.Data.ODBC is not supported on this platform.”
[odbc_sample]# dotnet run
Unhandled Exception:
System.PlatformNotSupportedException: System.Data.ODBC is not supported on this platform.
at System.Data.Odbc.OdbcConnection..ctor(String connectionString)
at ConsoleApplication1.Program.Main(String[] args) in /root/odbc_sample/Program.cs:line 18
[ERROR] FATAL UNHANDLED EXCEPTION: System.PlatformNotSupportedException: System.Data.ODBC is not supported on this platform.
at System.Data.Odbc.OdbcConnection..ctor(String connectionString)
at ConsoleApplication1.Program.Main(String[] args) in /root/odbc_sample/Program.cs:line 18
Note: This issue is due to wrong RID (Runtime Identifier) fallback. The native RID "rhel.8-ppc64le" should fall back to Linux. But this fallback is not working as expected. Issue is already opened here. Fix is available for this and should be available in next .NET7 service release.
7. As a workaround for this error, you have to add the runtimes section at the end of /usr/lib64/dotnet/shared/Microsoft.NETCore.App/7.0.0/Microsoft.NETCore.App.deps.json as shown below:
diff --git a/Microsoft.NETCore.App.deps.json b/Microsoft.NETCore.App.deps.json.orig
index 75a6e51..eba6595 100644
--- a/Microsoft.NETCore.App.deps.json.orig
+++ b/Microsoft.NETCore.App.deps.json
@@ -733,18 +733,4 @@
"sha512": "",
"path": "microsoft.netcore.app.runtime.rhel.8-ppc64le/7.0.0"
}
+ },
+ "runtimes": {
+ "rhel.8-ppc64le": [
+ "rhel.8",
+ "rhel-ppc64le",
+ "rhel",
+ "linux-ppc64le",
+ "linux",
+ "unix-ppc64le",
+ "unix",
+ "any",
+ "base"
+ ]
+ }
}
}
8. Rebuild and run the application as below
# Rebuild application
dotnet build
# Run application
Dotnet run
The application displays content of <YourTableName> in <YourDatabaseName> database as follows:
[odbc_sample]# dotnet build
MSBuild version 17.4.0+18d5aef85 for .NET
Determining projects to restore...
Restored /root/odbc_sample/odbc_sample.csproj (in 475 ms).
odbc_sample -> /root/odbc_sample/bin/Debug/net7.0/odbc_sample.dll
Build succeeded.
0 Warning(s)
0 Error(s)
Time Elapsed 00:00:07.78
[odbc_sample]# dotnet run
:PersonID:LastName:FirstName:Address:City:
:102:LMN:PQR:abc road:xyz city:
:101:ABC:DEF:xyz road:Pune:
Thus, in this blog you have learned to connect a .NET application to MariaDB using an ODBC connector.
References