ODBC

From Qt Wiki
Revision as of 09:21, 8 January 2018 by JoachimP (talk | contribs) (Use QStringLiteral and avoid unnecessary QString::append)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

ODBC

ODBC stands for Open Database Connectivity and provides an open API for DBMS access, where the ODBC layer acts as an transition layer. Therefor the client and the server itself will have to implement an interface to the ODBC layer.

Opening MS SQL Server Database Connection using QODBC

When trying to open a Database Connection with QODBC please ensure

  • You have QODBC driver available
  • Your server has an ODBC interface and is enabled to (this depends on your ODBC driver installations)
    • use shared memory access or
    • use TCP/IP connections or
    • use named pipe connection


All connections only require the DatabaseName to be set by calling QSqlDatabase::setDatabaseName

Open Connection using shared memory access

For this option to work you will need to have access to memory of the machine and must have permissions to access shared memory. For using a shared memory connection it is required to set lpc: in front of the Server string. Connection using the SQL Server Native Client 11 is made using these steps:

QString connectString = "Driver={SQL Server Native Client 11.0};";                     // Driver is now {SQL Server Native Client 11.0}
connectString.append("Server=lpc:"+QHostInfo::localHostName()+"\\SQLINSTANCENAME;");   // Hostname,SQL-Server Instance
connectString.append("Database=SQLDBSCHEMA;");  // Schema
connectString.append("Uid=SQLUSER;");           // User
connectString.append("Pwd=SQLPASS;");           // Pass
db.setDatabaseName(connectString);
if(db.open())
{
    ui->statusBar->showMessage("Connected");
}else{
    ui->statusBar->showMessage("Not Connected");
}

Open Connection using Named Pipe

This option requires your ODBC Connection to have a full DSN. The Server string is setup by using the Windows Computername and the Instancename of the SQL Server. The example connection will be opened using SQL Server Native Client 10.0

QString connectString = "Driver={SQL Server Native Client 10.0};"; // Driver can also be {SQL Server Native Client 11.0}
connectString.append("Server=SERVERHOSTNAME\\SQLINSTANCENAME;");   // Hostname,SQL-Server Instance
connectString.append("Database=SQLDBSCHEMA;");  // Schema
connectString.append("Uid=SQLUSER;");           // User
connectString.append("Pwd=SQLPASS;");           // Pass
db.setDatabaseName(connectString);
if(db.open())
{
    ui->statusBar->showMessage("Connected");
}else{
    ui->statusBar->showMessage("Not Connected");
}

Open Connection using TCP/IP

For opening a TCP/IP connection the server should be configured to allow connections on a fixed port, otherwise you will first have to query for the currently active port. In this example we have a fixed port at 5171. You can find an example for setting up the server to allow connections on a fixed port at [1]. For open a connection using TCP/IP use a tuple of the servers IP and Port:

QString connectString = "Driver={SQL Server};"; // Driver is now {SQL Server}
connectString.append("Server=10.1.1.15,5171;"); // IP,Port
connectString.append("Database=SQLDBSCHEMA;");  // Schema
connectString.append("Uid=SQLUSER;");           // User
connectString.append("Pwd=SQLPASS;");           // Pass
db.setDatabaseName(connectString);
if(db.open())
{
    ui->statusBar->showMessage("Connected");
}else{
    ui->statusBar->showMessage("Not Connected");
}

Opening SAP HANA Database Connection using QODBC

Please ensure that

  • You have QODBC3 driver available
  • SAP HANA ODBC driver file is available on the system (file name is libodbcHDB.so on linux)

As the HANA ODBC driver does not provide scrollable results per default, the connection has to be established using the option "SCROLLABLERESULT=TRUE".

QSqlDatabase db = QSqlDatabase::addDatabase("QODBC3");
QString connectString = QStringLiteral(
    "DRIVER=/path/to/installation/libodbcHDB.so;"
    "SERVERNODE=hostname:port;"
    "UID=USER;"
    "PWD=PASSWORD;"
    "SCROLLABLERESULT=true");
db.setDatabaseName(connectString);
if(db.open())
{
    ui->statusBar->showMessage("Connected");
}else{
    ui->statusBar->showMessage("Not Connected");
}