» Documentation

Connectivity

Connecting to distributed database

Connection string attributes:

  • [Server] - the name(s) or ip(s) of db nodes.
  • [Port] - db node(s) listening IP port. Default (999).
  • [Priority] - query priority value [1,100], default (100). Read more...
  • [Database] - database name.
  • [Username] - if database secure, the login username.
  • [Password] - Users' password.

Windows integrated security

V4.* supports windows integrated security using SSPI.

It allows to authenticate either a single windows user or the group (organizational unit).

For example, create a database user [mydomain\usergroup1]. Then, the connecting to db user, who is part of the [usergroup1] group, will inherit security permissions of the database user: [mydomain\usergroup1]. It works somewhat similiar to SQL Server.

string ConnectionString = "Server=localhost; Port=999; Database=northwind;Trusted_Connection=true" 

or

string ConnectionString = "Server=localhost; Port=999; Database=northwind;Username=SSPI" 

Single node

string ConnectionString = "Server=localhost; Port=999; Database=northwind"

string ConnectionString = "Server=localhost; Port=999; Database=northwind; Priority=70"

string ConnectionString = "Server=localhost; Port=999; Database=northwind; Username=myuser; Password=pwd" 

Multi nodes

Allow balance connections over multiple database nodes.

string ConnectionString = "Server=10.0.0.2&10.0.0.3; Port=999; Database=Northwind" 

In this case, first connection goes to 10.0.0.2, second to 10.0.0.3, third to 10.0.0.2, and so on.

To specify IP and Port for each node:

string ConnectionString = "Server=10.0.0.2:999&10.0.0.3:1000; Database=Northwind" 

Connecting to embedded database

Connection string attributes:

  • [Data Source] - embedded database root folder.
  • [Open Mode] - values: [Shared] (default) - embedded database opened in shared mode and can be accessed by multiple processes, [Exclusive] - single process access; the database will be loaded inside calling process.
  • [Max Buffer Size] - Number of database cache pages (each 8Kb pages), default (3000).
  • [Max Connections] - Maximum allowed number of connections, default (8).
  • [Max Locks] - Number of maximum row-level locks, default(100K)
  • [Database] - database name. embedded database supports multiple databases.
  • [Username] - if database secure, the login username.
  • [Password] - Users' password.

Connecting with connection string

string ConnectionString = "Data Source=d:\\MyDBRoot; Database=MyDB; Open Mode=Shared";

string ConnectionString = "Data Source=d:\\MyDBRoot; Database=MyDB; Open Mode=Exclusive;Max Connections=16"; 

Connecting with ScimoreEmbedded API

Connection strings is not the only way to connect to a Scimore Embedded Database. With .NET providers ScimoreEmbedded class you can create databases and open embedded databases. For example:

ScimoreEmbedded em = new ScimoreEmbedded();
em.MaxConnections=16; //optional property
em.MemoryPages = 3000 //optional
em.MaxLocks = 1000000 //optional
em.Create(@"c:\database\sample1");
em.Open(@"c:\database\sample1") 

This will launch the database as an external process, what we call "out of process". The ScimoreEmbedded [em] variable should be global and reused for each thread needed a connection.

Multiple application using the same Create and Open path, will share the same database instance, all connecting the started embedded database process. The embedded process will shutdown automatically, 1 minute after the last application disconnects.

ScimoreEmbedded em = new ScimoreEmbedded();
em.Create(@"c:\database\sample1");
//em.MaxConnections=16; -- optional property
//em.MemoryPages = 3000 - optional
//em.MaxLocks = 1000000 -optional
em.OpenInProcess(@"c:\database\sample1"); 

This will load the database inside the calling process, and enforce exclusive database access by the current processed. This mean you won't be able access the database from other applications, e.g. the manager - while your application is running.

Next, create connection:

ScimoreConnection conn = em.CreateConnection("Database=mydb;") 

For secure database:

ScimoreConnection conn = em.CreateConnection("Database=mydb;Username=myuser;Password=mypwd") 

C++ client

In order to use Scimore native client in your applications, you need to have the native client SDK available to the compiler and linker. Client SDK contains header files, lib file and dll.

Download ZIP file containing Client SDK from:

Product

x86

x64

Embedded 3.0

http://scimore.com/download/release/3.0/latest/ScimoreDB.zip

http://scimore.com/download/release/3.0/latest/ScimoreDB_x64.zip

Distributed 4.0

http://scimore.com/download/release/4.0/latest/ScimoreDB.zip

http://scimore.com/download/release/4.0/latest/ScimoreDB_x64.zip

In Microsoft Visual Studio, Linker Input under "Additional Dependencies" add "Scimore.Data.ScimoreClient.lib". In the project, include header "scdriver.h".

Simple Query Example

Connects to the database, runs a query. This program will simply run the GENERATE STATISTICS SQL command.

This needs to be linked against Scimore.Data.ScimoreClient.lib and run with Scimore.Data.ScimoreClient.dll available.

#include "stdafx.h"
#include "scdriver.h"
#include <iostream>

using namespace scdriver;

int main(int argc, char** argv)
{
    CConnection conn;
    try
    {
        // Connect to the local machine on the default port
        conn.Connect("localhost",999);
        // Execute a query
        conn.Execute("GENERATE STATISTICS FOR ALL");
        // Must be called after every query to finish the query status
        conn.GetCompletionStatus();
    }
    catch (node_exception& e)
    {
        std::cerr << "Error: " << e.msg << std::endl;
        return 1;
    }
    return 0;
} 

Simple Asynchronous Querying

Connects to the database, runs a query asynchronously. A callback function is executed when the query is complete. The main thread waits for this event to be triggered.

#include "stdafx.h"

#include "scdriver.h"
#include <iostream>

using namespace scdriver;

/// Callback function
void __stdcall on_query_done(CConnection *conn, void *hQuery)
{
  std::cout << "Handling query done event" << std::endl;
  // wakeup client's thread
  SetEvent((HANDLE)hQuery);
};

int main(int argc, char** argv)
{
  CConnection conn;
  try
  {
    // Connect to the local machine on the default port
    conn.Connect("localhost",999);

    // Create an event on which the current thread will wait
    HANDLE hQuery=CreateEvent(NULL,TRUE,FALSE,NULL);

    // Set up the callback function, with the event object as user data
    conn.SetConnectionStateCallback(
            CConnection::CONNECTION_EVENT::QUERY_DONE,
            on_query_done,
            hQuery);

    // Execute a query
    conn.Execute("SELECT * FROM system.syscolumns");

    // Possibly some other processing here
    // ...

    // Wait for the callback function to signal that the query has ended
    std::cout << "Waiting for query done event" << std::endl;
    WaitForSingleObject(hQuery,INFINITE);
    std::cout << "Finished waiting for query done event" << std::endl;
    ResetEvent(hQuery);

    // Must be called after every query to finish the query status
    conn.GetCompletionStatus();

    CloseHandle(hQuery);

    // Clear the callback function
    conn.ResetConnectionStateCallback(
            CConnection::CONNECTION_EVENT::QUERY_DONE);
  }
  catch (node_exception& e)
  {
    std::cerr << "Error: " << e.msg << std::endl;
    return 1;
  }
  return 0;
} 

Simple Recordset Retrieval

This is one of the simplest examples possible with scdriver. It will connect to a database, run one query, fetching the varchar results and displaying them on the terminal output.

#include "stdafx.h"
#include <iostream>
#include <cassert>
#include "scdriver.h"

using namespace scdriver;

int main(int argc, char** argv)
{
   CConnection conn;
   try
   {
     // Initiate a connection with the default port on the current machine
     conn.Connect("localhost",999);

     // Used to collect results through
     CRecordset result;

     // Send the query to the server and set up result to handle the results
     result.Open(&conn, "SELECT c.name,t.name"
                        " FROM system.systables AS t"
                        " JOIN system.syscatalogs AS c"
                        " ON t.catalog_id=c.id");

     // Wait for the query to be complete
     conn.GetCompletionStatus();

     // While there are still rows to be read
     while (!result.IsEOF())
     {
       // On this row, for each column in the result
       for (int i=0; i<result.GetFieldCount(); ++i)
       {
         // Fetch the field information, which is also used to access the data
         FIELD_INFO* field=result.GetFieldInfo(i);

         // Check that the column is a varchar
         assert(field->type==DB_VARCHAR);

         // Get the byte width of the field. For varchar, this includes the null-terminator
         int size=field->data.GetFieldSize();

         // Allocate storage for the output
         char* temp_data=new char[size];
         if (temp_data)
         {
           // read the actual data into the temp_data array
           field->data.ReadField(temp_data,size);

           // if not the first column, output a tab to pad it
           if (i) std::cout << '\t';

           // output the data string
           std::cout << temp_data;
           delete temp_data;
         }
       }
       std::cout << std::endl;

       // move to the next result
       result.Next();
     }
     conn.Close();
   }
   catch(node_exception& e)
   {
     std::cerr << "Error: " << e.msg << std::endl;
     return 1;
   }
   return 0;
} 

Simple Transactions

This will connect to a server instance and initialize a database and table if they don't exist. Then it inserts 100 rows in a single transaction.

#include "stdafx.h"
#include "scdriver.h"
#include <iostream>

using namespace scdriver;

int main(int argc, char** argv)
{
    CConnection conn;
    try
    {
        // Connect to the local machine on the default port
        conn.Connect("localhost",999);

        // Set up example database and create table
        conn.Execute("CREATE DATABASE IF NOT EXISTS scdriver_example");
        conn.GetCompletionStatus();
        conn.Execute("USE scdriver_example");
        conn.GetCompletionStatus();
        conn.Execute("CREATE TABLE IF NOT EXISTS example_run ("
                        "id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, "
                        "added DATETIME NOT NULL PARTITION, "
                        "description VARCHAR(255))");
        conn.GetCompletionStatus();

        // Begin a transaction with the default read-committed isolation level
        conn.BeginTransaction();

        // Insert a bunch of rows
        for(int i = 0; i < 100; i++)
            conn.Execute("INSERT INTO example_run VALUES "
                         "(0,GETDATE(),'Example transaction')");

        conn.CommitTransaction();

        // Must be called after every query to finish the query status
        conn.GetCompletionStatus();
    }
    catch (node_exception& e)
    {
        std::cerr << "Error: " << e.msg << std::endl;
        return 1;
    }
    return 0;
} 

Prepared Query

This will connect to the default server instance and create the example database and table, if necessary. Then it will make a prepared query to simply insert a row.

#include "stdafx.h"
#include "scdriver.h"
#include <iostream>

using namespace scdriver;

int main(int argc, char** argv)
{
    CConnection conn;
    try
    {
        // Connect to the local machine on the default port
        conn.Connect("localhost",999);

        // Set up example database and create table and procedure
        conn.Execute("CREATE DATABASE IF NOT EXISTS scdriver_example");
        conn.GetCompletionStatus();
        conn.Execute("USE scdriver_example");
        conn.GetCompletionStatus();
        conn.Execute("CREATE TABLE IF NOT EXISTS example_run ("
                        "id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, "
                        "added DATETIME NOT NULL PARTITION, "
                        "description VARCHAR(255))");
        conn.GetCompletionStatus();

        // Build the stored query for running multiple times
        CCommand prepared_query;

        // Prepare the query with the parameter
        prepared_query.PrepareText(&conn,"INSERT INTO example_run VALUES (0,getdate(),?message)");

        // Set parameter and run the query
        prepared_query.AddParameter("message",DB_VARCHAR,"One prepared query text");
        prepared_query.Execute();
        conn.GetCompletionStatus();

        // Reset parameter and run the query again
        prepared_query.AddParameter("message",DB_VARCHAR,"Another prepared query text");
        prepared_query.Execute();
        conn.GetCompletionStatus();
    }
    catch (node_exception& e)
    {
        std::cerr << "Error: " << e.msg << std::endl;
        return 1;
    }
    return 0;
} 

Stored Procedure

This will connect to the default server instance and create the example database and table, if necessary. Then it will create or recreate a procedure to insert three rows.

#include "stdafx.h"
#include "scdriver.h"
#include <iostream>

using namespace scdriver;

int main(int argc, char** argv)
{
    CConnection conn;
    try
    {
        // Connect to the local machine on the default port
        conn.Connect("localhost",999);

        // Set up example database and create table and procedure
        conn.Execute("CREATE DATABASE IF NOT EXISTS scdriver_example");
        conn.GetCompletionStatus();
        conn.Execute("USE scdriver_example");
        conn.GetCompletionStatus();
        conn.Execute("CREATE TABLE IF NOT EXISTS example_run ("
                        "id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, "
                        "added DATETIME NOT NULL PARTITION, "
                        "description VARCHAR(255))");
        conn.GetCompletionStatus();

        conn.Execute("DROP PROCEDURE IF EXISTS example_inserter");
        conn.GetCompletionStatus();
        conn.Execute("CREATE PROCEDURE example_inserter ("
                    "INSERT INTO example_run VALUES (0,getdate(),?message) "
                    "INSERT INTO example_run VALUES (0,getdate(),?message) "
                    "INSERT INTO example_run VALUES (0,getdate(),?message)"
                    ")");
        conn.GetCompletionStatus();

        // The command object for the call
        CCommand procedure_call;

        // Set up the command object to run the procedure named
        procedure_call.PrepareProcedure(&conn,"example_inserter");

        // Put in the parameter value, run the procedure and wait for it to finish
        procedure_call.AddParameter("message",DB_VARCHAR,"One message");
        procedure_call.Execute();
        conn.GetCompletionStatus();

        // Put in a different parameter value and repeat
        procedure_call.AddParameter("message",DB_VARCHAR,"Another message");
        procedure_call.Execute();
        conn.GetCompletionStatus();

        procedure_call.AddParameter("message",DB_VARCHAR,"Yet another message");
        procedure_call.Execute();
        conn.GetCompletionStatus();
    }
    catch (node_exception& e)
    {
        std::cerr << "Error: " << e.msg << std::endl;
        return 1;
    }
    return 0;
}