Executing SQL Statements

An os_db_statement object encasulates a single SQL statement. Before an SQL statement can be executed a database connection must be provided to it. The basic outline of using os_db_statement objects is as follows.

  • Construct an os_db_statement object for an SQL statement.
  • Attach the os_db_statement object to an existing database connection, an os_db_connection object.
  • Bind any data or parameters, if necessary.
  • Execute the statement.
  • Process the results of the statement, if necessary.
  • Detach from the os_db_connection object.

    • Executing Statements

    The excerpt below illustrates os_db_statement object being constructed for the "SELECT * FROM Supplier" SQL statement and then attached to a connection via attach() and finally executed with execute(). Any resulting data avaliable (rows) that is not processed will be cancelled during destruction. Detaching from the connection is also handled automatically during destruction in this example.

    Excerpt from <ospace/database/examples/getdata1.cpp>
      // ...

  try
    {
    os_ip_address iaddr( target_hostname );
    os_db_connection connection( os_socket_address(iaddr, 3006),
                                 dsn,
                                 user,
                                 auth );
    os_db_statement stmt( "SELECT * FROM Supplier" );

    stmt.attach( connection );
    stmt.execute();
    }

  // ...

  return 0;
  }

    Navigating Results and Retreiving Data With get_data()

    Several SQL statements, such as SELECT statements, return results from the database. After execute() is called any results are available row-by-row using fetch() to access the data of the first (or next) row. Data can be retrieved from the active row with the get_data() method. When no more data is avaliable fetch() returns false.

    The following example illustrates using get_data() to print the results of colums 1, 2, and 9 for each row returned from executing the "SELECT * FROM Supplier" SQL statement.

    Example <ospace/database/examples/getdata1.cpp>
    #include <ospace/std/iostream>
#include <ospace/network.h>
#include <ospace/stream.h>
#include <ospace/database.h>

// ... 

int main(int argc, char** argv)
  {
  os_network_toolkit init_network;
  os_streaming_toolkit init_streaming;
  os_database_toolkit init_database;

  os_string target_hostname;
  os_string dsn;      // dsn name
  os_string user;     // user name
  os_string auth;     // auth info

  // Use local host if none specified on command line
  (argc > 1) ? target_hostname = argv[1] : target_hostname = os_host::my_host().name();
  (argc > 2) ? dsn  = argv[2] : dsn  = "dbtoolkit";
  (argc > 3) ? user = argv[3] : user = "guest";
  (argc > 4) ? auth = argv[4] : auth = "";

  try
    {
    os_ip_address iaddr( target_hostname );
    os_db_connection connection( os_socket_address(iaddr, 3006),
                                 dsn,
                                 user,
                                 auth );
    os_db_statement stmt( "SELECT * FROM Supplier" );

    stmt.attach( connection );
    stmt.execute();

    while (stmt.fetch())    // Get each valid row
      {
      const int NAME_LEN = 50+1;
      const int PHONE_LEN = 20+1;
      char supplier_name[NAME_LEN], supplier_phone[PHONE_LEN];
      unsigned long supplier_id;

      // Display columns 1,2, and 9 for this row
      stmt.get_data(1, supplier_id);
      stmt.get_data(2, (char *)supplier_name, sizeof(supplier_name));
      stmt.get_data(9, (char *)supplier_phone, sizeof(supplier_phone));

      os_stringstream oss;          
      oss << supplier_id
          << ":"
          << supplier_name
          << ":"
          << supplier_phone;
      cout << oss.str() << endl;
      }
    }

  // ...

  return 0;
  }


1:Active Outdoors:503 555-9931
2:Triumph:313 555-5735
3:Guardian:81 3 3555-5011
4:InFlux:81 6 431-7877
5:Craze:604 681 3435
6:Roadster:44 171 555-2222
7:Vesper:514 555-9022

    Retrieving Data By Binding Data Buffers With bind()

    Another method for retrieving data from a database is to bind data buffers for columns of interest. Then when a successful call to fetch() is made the data will automatically be placed in all of the bound buffers.

    The following example illustrates binding data buffers with bind() before calling fetch().

    Example <ospace/database/examples/bind1.cpp>
    #include <ospace/std/iostream>
#include <ospace/network.h>
#include <ospace/stream.h>
#include <ospace/database.h>

// ...

int main(int argc, char** argv)
  {
  os_network_toolkit init_network;
  os_streaming_toolkit init_streaming;
  os_database_toolkit init_database;

  os_string target_hostname;
  os_string dsn;      // dsn name
  os_string user;     // user name
  os_string auth;     // auth info

  // Use local host if none specified on command line
  (argc > 1) ? target_hostname = argv[1] : target_hostname = os_host::my_host().name();
  (argc > 2) ? dsn  = argv[2] : dsn  = "dbtoolkit";
  (argc > 3) ? user = argv[3] : user = "guest";
  (argc > 4) ? auth = argv[4] : auth = "";

  try
    {
    os_ip_address iaddr( target_hostname );
    os_db_connection connection( os_socket_address(iaddr, 3006),
                                 dsn,
                                 user,
                                 auth );
    os_db_statement stmt( "SELECT * FROM Supplier" );

    stmt.attach( connection );
    stmt.execute();

    const int NAME_LEN = 50+1;
    const int PHONE_LEN = 20+1;
    char supplier_name[NAME_LEN], supplier_phone[PHONE_LEN];
    unsigned long supplier_id;

    stmt.bind(1, supplier_id);
    stmt.bind(2, (char *)supplier_name, sizeof(supplier_name));
    stmt.bind(9, (char *)supplier_phone, sizeof(supplier_phone));

    while (stmt.fetch())    // Get each row
      {
      os_stringstream oss;          
      oss << supplier_id
          << ":"
          << supplier_name
          << ":"
          << supplier_phone;
      cout << oss.str() << endl;
      }
    }

  // ...

  return 0;
  }


1:Active Outdoors:503 555-9931
2:Triumph:313 555-5735
3:Guardian:81 3 3555-5011
4:InFlux:81 6 431-7877
5:Craze:604 681 3435
6:Roadster:44 171 555-2222
7:Vesper:514 555-9022

    Sending Data by Binding Parameters With bind_param()

    Sending data for an SQL statement is accomplished by binding each SQL parameter to a data buffer that will be sent for that parameter. An SQL parameter is indicated by ? in the SQL statement. For the example below, the statement "UPDATE Purchases SET ReorderLevel = ? WHERE ProductID = ?" has two parameters.

    The actual value for each bound paramter must be correct at the time execute() is called since it automatically sends the values of parameters bound with bind_param(). In the example below the value for reorder_level is set to 112 for each of the product_id in the range 7401-7404. The example also illustrates using prepare() which can be used when multiple executions of the statement will be made.

    Example <ospace/database/examples/bindparam1.cpp>
    #include <ospace/std/iostream>
#include <ospace/network.h>
#include <ospace/stream.h>
#include <ospace/database.h>

// ...

int main(int argc, char** argv)
  {
  os_network_toolkit init_network;
  os_streaming_toolkit init_streaming;
  os_database_toolkit init_database;

  os_string target_hostname;
  os_string dsn;      // dsn name
  os_string user;     // user name
  os_string auth;     // auth info

  // Use local host if none specified on command line
  (argc > 1) ? target_hostname = argv[1] : target_hostname = os_host::my_host().name();
  (argc > 2) ? dsn  = argv[2] : dsn  = "dbtoolkit";
  (argc > 3) ? user = argv[3] : user = "guest";
  (argc > 4) ? auth = argv[4] : auth = "";


  try
    {
    os_ip_address iaddr( target_hostname );
    os_db_connection connection( os_socket_address(iaddr, 3006),
                                 dsn,
                                 user,
                                 auth );
    os_db_statement stmt( "UPDATE Purchases SET ReorderLevel = ? WHERE ProductID = ?" );

    stmt.attach( connection );
    stmt.prepare();

    unsigned long reorder_level;
    unsigned long product_id;

    stmt.bind_param(1, reorder_level, OSSQL_INTEGER);
    stmt.bind_param(2, product_id, OSSQL_INTEGER);

    long updated_rows = 0;

    reorder_level = 112;
    for (product_id = 7401; product_id <= 7404; product_id++)
      {
      stmt.execute();  // Automatically sends the bound parameters
      updated_rows += stmt.get_num_rows_affected();
      }
    cout << "Total updated rows = " << updated_rows << endl;
    }

  // ...

  return 0;
  }


Total updated rows = 4

    Sending Data by Binding Parameters With bind_param_exec()

    It is also possible to send parameters manually using put_data() if the parameter is bound with bind_param_exec(). This method is often used with long data that is sent in parts by repeated calls to put_data().

    Any parameters bound with bind_param_exec() are not sent automatically when calling execute(). After execution the code must call need_data() to determine which parameter to send and then to send it with put_data().

    For bind_param_exec() buffers the actual value contained within need only be valid at the time put_data() is called since the parameter data is not sent when execute() is called. In the example below the same SQL statement as the previous example is executed with the parameters bound via bind_param_exec(). The value for reorder_level is set to 222 for the product_id 7404.

    Example <ospace/database/examples/bindparam2.cpp>
    #include <ospace/std/iostream>
#include <ospace/network.h>
#include <ospace/stream.h>
#include <ospace/database.h>

// ...

int main(int argc, char** argv)
  {
  os_network_toolkit init_network;
  os_streaming_toolkit init_streaming;
  os_database_toolkit init_database;

  os_string target_hostname;
  os_string dsn;      // dsn name
  os_string user;     // user name
  os_string auth;     // auth info

  // Use local host if none specified on command line
  (argc > 1) ? target_hostname = argv[1] : target_hostname = os_host::my_host().name();
  (argc > 2) ? dsn  = argv[2] : dsn  = "dbtoolkit";
  (argc > 3) ? user = argv[3] : user = "guest";
  (argc > 4) ? auth = argv[4] : auth = "";

  try
    {
    os_ip_address iaddr( target_hostname );
    os_db_connection connection( os_socket_address(iaddr, 3006),
                                 dsn,
                                 user,
                                 auth );
    os_db_statement stmt( "UPDATE Purchases SET ReorderLevel = ? WHERE ProductID = ?" );

    stmt.attach( connection );
    stmt.prepare();

    unsigned long reorder_level;
    unsigned long product_id;

    stmt.bind_param_exec(1, reorder_level, OSSQL_INTEGER);
    stmt.bind_param_exec(2, product_id, OSSQL_INTEGER);

    stmt.execute();

    unsigned short param;
    while (stmt.need_data(param))
      {
      switch (param)
        {
        case 1:
          {
          // Send all the data for param 1.
          // Can call multiple times for large data
          reorder_level = 222;
          stmt.put_data(reorder_level);
          break;
          }

        case 2:
          {
          // Send all the data for param 2.
          // Can call multiple times for large data
          product_id = 7404;
          stmt.put_data(product_id);
          break;
          }

        default:
          // need_param() will only return parameter numbers specified
          // in bind_param_exec() calls
          break;
        }
      }

    cout << "Total updated rows = " << stmt.get_num_rows_affected() << endl;
    }

  // ...

  return 0;
  }


Total updated rows = 1

    Copyright©2005-2026 Recursion Software LLC
    All Rights Reserved - For use by licensed users only.