/*
 * InsertStoredProcApplet.cpp 
 * Copyright (c) 2009 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>

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(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(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)
{
    try {

        InsertStoredProcApplet InsertStoredProcApplet(argc, argv);

        InsertStoredProcApplet.start();

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