Originally posted by: Ohseas
Here is an example that was provided by support(Great support by the way) answering an example of C#.. See below:
Also noted was the ability to connect using windows authentication on mssql 2005 -- the connection string that I found to work was : DBConnection db("oledb","//DatabaseName/ServerName");.. after this code I will post other connection to try...
There are no direct .NET methods or functions available to the OPL/CPLEX object for writing to a database. One will need to first incorporate the DBConnection/DBUpdate/DBExecute statements in the dat file itself. Since these functions in the dat file are called during post-processing of the model, one will then need to explicitly call the postProcess method of the opl object from .NET to write the results to the required table(s).
Please find a simple example below:
<mod file>
range r=1..5;
int minX[r] = ...;
dvar int X[r];
minimize sum(y in r) X[y];
subject to{
forall(y in r)
X[y]- (minX[y]*2)>=0;
}
tuple resultT{
int i;
int val;
};
{resultT} resultX = {<y,X[y]>|y in r};
execute{
writeln("resultX="+resultX);
writeln("Writing resultX to database");
}
<dat file>
minX =
1,2,3,4,5;
DBConnection db("MSSQL","<userName>/<password>/<database>/<dbServer>");
DBExecute(db,"drop table solutionX");
DBExecute(db,"create table solutionX(id int, value int)");
resultX to DBUpdate(db,"Insert into solutionX(index,value) VALUES (?,?)");
The DBConnection statement's connection string should be replaced with your MSSQL database details. Create the above two files (say test.mod and test.dat) and then place them in one of our example directory (say C:\ILOG\OPL63\examples\opl_interfaces\dotnet\x86_.net2005_8.0\CSharp\Mulprod). Once this is done, open the Mulprod (CSharp) example and modify the mulprod.cs file to the following:
>>>>>>>>>>>>>>>>>>>>>>>>>
using System;
using System.Collections;
using ILOG.Concert;
using ILOG.CPLEX;
using ILOG.OPL;
namespace Mulprod
{
class Mulprod
{
static int Main(string[] args)
{
int status = 127;
const string DATADIR = "../..";
try
{
OplFactory oplF = new OplFactory();
Cplex cplex = oplF.CreateCplex();
OplErrorHandler handler = oplF.CreateOplErrorHandler(Console.Out);
OplModelSource modelSource = oplF.CreateOplModelSource(DATADIR + "/test.mod");
OplSettings settings = oplF.CreateOplSettings(handler);
OplModelDefinition def = oplF.CreateOplModelDefinition(modelSource, settings);
OplModel opl = oplF.CreateOplModel(def, cplex);
OplDataSource dataSource = oplF.CreateOplDataSource(DATADIR + "/test.dat");
opl.AddDataSource(dataSource);
opl.Generate();
if (cplex.Solve())
{
Console.Out.WriteLine();
Console.Out.WriteLine("OBJECTIVE: " + opl.Cplex.ObjValue);
opl.PostProcess();
opl.PrintSolution(Console.Out);
status = 0;
}
else
{
Console.Out.WriteLine("No solution!");
status = 1;
}
oplF.End();
}
catch (IloException ex)
{
Console.Out.WriteLine("### exception: " + ex.Message);
status = 2;
}
catch (System.Exception ex)
{
Console.Out.WriteLine("### UNEXPECTED ERROR ..." + ex.Message);
status = 3;
}
Console.WriteLine("-Press <Enter> to exit-");
Console.ReadLine();
return status;
}
}
}
Possible other DBConnection string:
I would suggest you to try the following to
test this out:
Instead of using non-trusted connection like:
DBConnection db("MSSQL","<userName>/<password>/<database>/<dbServer>");
try using:
DBConnection db("MSSQL","//<database>/<dbServer>");
or
DBConnection db("mssql","//<database>/<dbServer>");
If you are infact trying to connect to a SQL Server database, then
please try using:
DBConnection db("OLEDB","//<database>/<dbServer>");
or
DBConnection db("oledb","//<database>/<dbServer>");
#DecisionOptimization#OPLusingCPLEXOptimizer