SOL9 Sample: InsertStoredProcApplet

SOL9 2.0 Samples

1 Screenshot


2 Source code

/*
 * InsertStoredProcApplet.cpp 
 * Copyright (c) 2015 Antillia.com TOSHIYUKI ARAI. ALL RIGHTS RESERVED. 
 */


// SOL9
// 2009/05/27
// Assumes that environment of SQL Server 2008 and SQLClient1.0 
// Please create a database 'Sample' for this example

#include <sol/sql/ADOApplet.h>
#include <sol/Locale.h>

namespace SOL {

class InsertStoredProcApplet: public ADOApplet {

public:
  /**
   * Constructor
   */
  InsertStoredProcApplet(int argc, const TCHAR** argv)
    :ADOApplet(argc, argv)
  {
  }

public:
  ~InsertStoredProcApplet()
  {
  }

public:

  void executeProc(__in ADOConnection& connection, __in _bstr_t& storedProcName)
  {
    //printf("1 executeProc start\n");
    int i = 0;
    for (i = 0; i< 10; i++) {
    
      ADOCommand command;
      command.createInstance(); 
      command.putActiveConnection(connection.getConnectionPtr());

      command.putCommandType(ADODB::adCmdStoredProc) ;

      command.putCommandText(storedProcName); 
      command.putPrepared(VARIANT_TRUE);
      int id = 1000 + i;
      command.appendParameter("@userid", ADODB::adInteger, ADODB::adParamInput, 
          sizeof(int), _variant_t(id));
      char name[255];
      sprintf_s(name, SizeOf(name), "Test user No.%d", id);
      command.appendParameter("@name", ADODB::adVarChar, ADODB::adParamInput, 
          strlen(name)+1, _variant_t("Test user"));

      int age = (int)(rand() % 70) + 20;

      command.appendParameter("@age", ADODB::adInteger, ADODB::adParamInput, 
          sizeof(int), _variant_t(age));

      char email[255];
      sprintf_s(email, SizeOf(email), "user_%d@antillia.com@earth.sol", id);

      command.appendParameter("@email", ADODB::adVarChar, ADODB::adParamInput, 
          strlen(email)+1, _variant_t(email));
  
    //  ADOParameters params;
    //  command.getParameters(params);
    //  params.dump();

      ADORecordset recordset;
      //long recordsAffected = 
      command.execute(recordset, 
        NULL, ADODB::adCmdStoredProc);
    }
    printf("OK, executeProc: command.execute(adCmdStoredProc): count=%d \n", i);

  }


private:
  void executeSql(__in ADOConnection& connection, __in _bstr_t& sql)
  {
    try {      

      ADORecordset recordset;
      connection.execute(sql, recordset);
      printf("OK, executeSql: connection.execute(\"%S\")\n", (const wchar_t*)sql);
    } catch (_com_error& e) {
      COMError error(e);
      error.dump();
    }
  }

private:
  void executeSql(__in ADOConnection& connection, __in _bstr_t& sql, __out ADORecordset& recordset)
  {
    try {      
      connection.execute(sql, recordset);
      printf("OK, executeSql: connection.execute(\"%S\")\n", (const wchar_t*)sql);
    } catch (_com_error& e) {
      COMError error(e);
      error.dump();
    }
  }

public:
  /**
   * InsertStoredProcApplet main procedure
   */
  virtual void run()
  {
    printf("1 run(): Start\n");

    ADOConnection connection = getConnection();

    // 1 Drop an existing table SolTester.
    _bstr_t dropTable = "Drop Table SolTester";

    executeSql(connection, dropTable);

    // 2 Create a new table SolTester
    _bstr_t createTable = "Create Table SolTester(\n"
        " UserID int not NULL primary key,\n"
        " Name  varchar(255) not NULL,\n"
        " Age   int,\n"
        " Birthday  Date,\n"
        " Email varchar(255),\n"
        " Telephone varchar(128))";

    executeSql(connection, createTable);

    // 3 Drop an existing storedProcedure InsertStoredProc
    _bstr_t dropStoredProc = "Drop Procedure InsertStoredProc";
    executeSql(connection, dropStoredProc);
      
  
    // 4 Create a new storedProcedure InsertStoredProc
    _bstr_t createStoredProc = "Create Procedure InsertStoredProc(\n"
        " @userid int,\n"
        " @name varchar(255),\n"
        " @age int,\n"
        " @email varchar(255))\n"
        " As Insert into SolTester(UserID, Name, Age, Email)\n"
        " Values(@userid, @name, @age, @email)";

    executeSql(connection, createStoredProc);


    _bstr_t storedProcName = "InsertStoredProc";
    executeProc(connection, storedProcName);

    _bstr_t query = "Select * from SolTester where Age<50";
    ADORecordset result;
    executeSql(connection, query, result);

    printf("2 Try ADORecorset dump()\n");
    result.dump();

    printf("3 run(): End\n");

  }
};

}


// Console application starts here.
void _tmain(int argc, const TCHAR** argv)
{
  Locale locale;
  try {

    InsertStoredProcApplet InsertStoredProcApplet(argc, argv);

    InsertStoredProcApplet.start();

  } catch(Exception& ex){
    ex.dump();
  } catch(...){
    printf("Exception:Unknown\n");
  }
}

Last modified: 2 May 2016

Copyright (c) 2016 Antillia.com ALL RIGHTS RESERVED.