Hi,
I'm using CPLEX 20.1 and connect my model to MSSQL with ODBC Connection. I've tested this connection and it works great.
Here is the problem.

I have a table called Test with 2 columns. The data types of this table in SQL are as follows:
ID int
Name nvarchar(50)

I've create a tuple in OPL as follows:
tuple test{
int ID;
string Name;
}
{test} Tests= ...;

I get the following error because the data type of Name column in my table in SQL is nvarchar.
Exception from IBM ILOG Concert: Cannot handle column type -9.

When I change the data type to varchar, I do not get an error.

Version 12.5 and 12.10 did not have such a problem. What should be done to assign the nvarchar data type to a string? Does anyone have any idea about this topic?

Regards

------------------------------
Sabri Suyunu
------------------------------

#DecisionOptimization

Hi Sabri,

nvarchar is not supported 

In 12.10, there was no database support and the only way to import data was using Java + JDBC and there was some implicit conversion in them.
If you want to assign the nvarchar data to a string, you will need to cast it in the SQL.

SELECT CAST(yourcolumn AS varchar(10)) AS yourcolumn FROM yourtable

------------------------------
Viu Long Kong
------------------------------

Original Message:
Sent: Tue March 30, 2021 01:27 PM
From: Sabri Suyunu
Subject: CPLEX and MSSQL Connection nvarchar problem

Hi,
I'm using CPLEX 20.1 and connect my model to MSSQL with ODBC Connection. I've tested this connection and it works great.
Here is the problem.

I have a table called Test with 2 columns. The data types of this table in SQL are as follows:
ID int
Name nvarchar(50)

I've create a tuple in OPL as follows:
tuple test{
int ID;
string Name;
}
{test} Tests= ...;

I get the following error because the data type of Name column in my table in SQL is nvarchar.
Exception from IBM ILOG Concert: Cannot handle column type -9.

When I change the data type to varchar, I do not get an error.

Version 12.5 and 12.10 did not have such a problem. What should be done to assign the nvarchar data type to a string? Does anyone have any idea about this topic?

Regards

------------------------------
Sabri Suyunu
------------------------------

#DecisionOptimization

Hi Viu,
Thanks for your message. As you mention, importing data with Java + JDBC connector allows to use nvarchar from SQL Database. But there were lots of restriction for SQL Query (working in order, performance issue, etc.)
Your solution is a good alternative. But we will change our data type nvarchar to varchar. Cast function may use up our SQL sources with bulk data.
I also want to thank you for developing the new ODBC connection string. I will write a blog post for Turkish CPLEX user for more information.
Regards

------------------------------
Sabri Suyunu
------------------------------

Original Message:
Sent: Wed March 31, 2021 04:17 AM
From: Viu Long Kong
Subject: CPLEX and MSSQL Connection nvarchar problem

Hi Sabri,

nvarchar is not supported 

In 12.10, there was no database support and the only way to import data was using Java + JDBC and there was some implicit conversion in them.
If you want to assign the nvarchar data to a string, you will need to cast it in the SQL.

SELECT CAST(yourcolumn AS varchar(10)) AS yourcolumn FROM yourtable

------------------------------
Viu Long Kong

Original Message:
Sent: Tue March 30, 2021 01:27 PM
From: Sabri Suyunu
Subject: CPLEX and MSSQL Connection nvarchar problem

Hi,
I'm using CPLEX 20.1 and connect my model to MSSQL with ODBC Connection. I've tested this connection and it works great.
Here is the problem.

I have a table called Test with 2 columns. The data types of this table in SQL are as follows:
ID int
Name nvarchar(50)

I've create a tuple in OPL as follows:
tuple test{
int ID;
string Name;
}
{test} Tests= ...;

I get the following error because the data type of Name column in my table in SQL is nvarchar.
Exception from IBM ILOG Concert: Cannot handle column type -9.

When I change the data type to varchar, I do not get an error.

Version 12.5 and 12.10 did not have such a problem. What should be done to assign the nvarchar data type to a string? Does anyone have any idea about this topic?

Regards

------------------------------
Sabri Suyunu
------------------------------

#DecisionOptimization

Hi Sabri, there is no need to change the field format of your table, you just need to change the SQL server in your data file. Example if
tuple tup_data {int a; string b;} ; {set_data} S= …;  in your .mod. If the Set S is populated from a table T (Column a is int and b is nvarchar(100)), then the SQL in the .dat  will be "select a, cast(b as varchar(100)) from T". You can also save the SQL select a, cast(b as varchar(100)) from T in a stored procedure SP and then read the data by executing the stored procedure SP, e.g. "exec SP". That's how I would have done it. Best regards,

------------------------------
Nourredine Hail
------------------------------

Original Message:
Sent: Wed March 31, 2021 07:43 AM
From: Sabri Suyunu
Subject: CPLEX and MSSQL Connection nvarchar problem

Hi Viu,
Thanks for your message. As you mention, importing data with Java + JDBC connector allows to use nvarchar from SQL Database. But there were lots of restriction for SQL Query (working in order, performance issue, etc.)
Your solution is a good alternative. But we will change our data type nvarchar to varchar. Cast function may use up our SQL sources with bulk data.
I also want to thank you for developing the new ODBC connection string. I will write a blog post for Turkish CPLEX user for more information.
Regards

------------------------------
Sabri Suyunu

Original Message:
Sent: Wed March 31, 2021 04:17 AM
From: Viu Long Kong
Subject: CPLEX and MSSQL Connection nvarchar problem

Hi Sabri,

nvarchar is not supported 

In 12.10, there was no database support and the only way to import data was using Java + JDBC and there was some implicit conversion in them.
If you want to assign the nvarchar data to a string, you will need to cast it in the SQL.

SELECT CAST(yourcolumn AS varchar(10)) AS yourcolumn FROM yourtable

------------------------------
Viu Long Kong

Original Message:
Sent: Tue March 30, 2021 01:27 PM
From: Sabri Suyunu
Subject: CPLEX and MSSQL Connection nvarchar problem

Hi,
I'm using CPLEX 20.1 and connect my model to MSSQL with ODBC Connection. I've tested this connection and it works great.
Here is the problem.

I have a table called Test with 2 columns. The data types of this table in SQL are as follows:
ID int
Name nvarchar(50)

I've create a tuple in OPL as follows:
tuple test{
int ID;
string Name;
}
{test} Tests= ...;

I get the following error because the data type of Name column in my table in SQL is nvarchar.
Exception from IBM ILOG Concert: Cannot handle column type -9.

When I change the data type to varchar, I do not get an error.

Version 12.5 and 12.10 did not have such a problem. What should be done to assign the nvarchar data type to a string? Does anyone have any idea about this topic?

Regards

------------------------------
Sabri Suyunu
------------------------------

#DecisionOptimization

Hi Nourredine,
Thanks for your advice. I know cast function and also SP and the views.
The reason why I would change data type of the table is, speed.
My model has a complex structure. It is not only model, it is also a product. There are lots of intergration with ERP system. So, with the millions of data, CAST function will be problem while reading data with OBDC. I think I don't need nvarchar. So it will be better solution to change nvarchar to varchar at the beginning of 20.1 journey :)
Regards
Sabri

------------------------------
Sabri Suyunu
------------------------------

Original Message:
Sent: Wed March 31, 2021 08:25 AM
From: Nourredine Hail
Subject: CPLEX and MSSQL Connection nvarchar problem

Hi Sabri, there is no need to change the field format of your table, you just need to change the SQL server in your data file. Example if
tuple tup_data {int a; string b;} ; {set_data} S= …;  in your .mod. If the Set S is populated from a table T (Column a is int and b is nvarchar(100)), then the SQL in the .dat  will be "select a, cast(b as varchar(100)) from T". You can also save the SQL select a, cast(b as varchar(100)) from T in a stored procedure SP and then read the data by executing the stored procedure SP, e.g. "exec SP". That's how I would have done it. Best regards,

------------------------------
Nourredine Hail

Original Message:
Sent: Wed March 31, 2021 07:43 AM
From: Sabri Suyunu
Subject: CPLEX and MSSQL Connection nvarchar problem

Hi Viu,
Thanks for your message. As you mention, importing data with Java + JDBC connector allows to use nvarchar from SQL Database. But there were lots of restriction for SQL Query (working in order, performance issue, etc.)
Your solution is a good alternative. But we will change our data type nvarchar to varchar. Cast function may use up our SQL sources with bulk data.
I also want to thank you for developing the new ODBC connection string. I will write a blog post for Turkish CPLEX user for more information.
Regards

------------------------------
Sabri Suyunu

Original Message:
Sent: Wed March 31, 2021 04:17 AM
From: Viu Long Kong
Subject: CPLEX and MSSQL Connection nvarchar problem

Hi Sabri,

nvarchar is not supported 

In 12.10, there was no database support and the only way to import data was using Java + JDBC and there was some implicit conversion in them.
If you want to assign the nvarchar data to a string, you will need to cast it in the SQL.

SELECT CAST(yourcolumn AS varchar(10)) AS yourcolumn FROM yourtable

------------------------------
Viu Long Kong

Original Message:
Sent: Tue March 30, 2021 01:27 PM
From: Sabri Suyunu
Subject: CPLEX and MSSQL Connection nvarchar problem

Hi,
I'm using CPLEX 20.1 and connect my model to MSSQL with ODBC Connection. I've tested this connection and it works great.
Here is the problem.

I have a table called Test with 2 columns. The data types of this table in SQL are as follows:
ID int
Name nvarchar(50)

I've create a tuple in OPL as follows:
tuple test{
int ID;
string Name;
}
{test} Tests= ...;

I get the following error because the data type of Name column in my table in SQL is nvarchar.
Exception from IBM ILOG Concert: Cannot handle column type -9.

When I change the data type to varchar, I do not get an error.

Version 12.5 and 12.10 did not have such a problem. What should be done to assign the nvarchar data type to a string? Does anyone have any idea about this topic?

Regards

------------------------------
Sabri Suyunu
------------------------------

#DecisionOptimization