C++ mysql create store procedure and call sp

//Create Store Procedure via below sql statement
//

//Create Procedure Sp_InsertIntoBookTable6( bId varchar(40), bAuthor varchar(40), bTitle varchar(40), bISBN varchar(40), bComment longblob) BEGIN insert into BookTable1(BookId,BookAuthor,BookTitle,BookISBN,BookComment) VALUES (bId,bAuthor,bTitle,bISBN,bComment); END";

#include <iostream>
#include <stdlib.h>
#include <stdio.h>
#include <mysql_connection.h>
#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
#include <cppconn/statement.h>
#include <mysql_driver.h>
#include <cppconn/prepared_statement.h>
#include <uuid/uuid.h>
#include <sstream>

void createSQL()
{
    try
    {
        sql::Connection *conn;
        sql::Statement *stmt; 
        string tableName = "BookTable1";
        conn = getMySQLConn();
        conn->setSchema("MyDB"); 

        string createSQL = "create table  " + tableName +
                           "(ID int primary key auto_increment,"
                           "BookId varchar(40) not null,"
                           "BookAuthor varchar(40),"
                           "BookTitle varchar(40),"
                           "BookISBN varchar (40),"
                           "BookComment longblob);";

        
        string createSP="Create Procedure Sp_InsertIntoBookTable6( bId varchar(40), bAuthor varchar(40), bTitle varchar(40), bISBN varchar(40), bComment longblob) BEGIN insert into BookTable1(BookId,BookAuthor,BookTitle,BookISBN,BookComment) VALUES (bId,bAuthor,bTitle,bISBN,bComment); END";

        stmt = conn->createStatement();
        bool isCreated = stmt->execute(createSP);
        sleep(1); 
        delete stmt; 
        delete conn;
    }
    catch (const sql::SQLException &e)
    {
        cout << "#ERR:SQLException in " << __FILE__ << ",function in " << __FUNCTION__ << " on line " << __LINE__ << endl;
        cout << "#ERR:" << e.what() << endl;
        cout << "(MySQL error code: " << e.getErrorCode() << endl;
        cout << "SQLState:" << e.getSQLState() << endl;
    }
}

sql::Connection *getMySQLConn()
{
    sql::mysql::MySQL_Driver *driver;
    sql::Connection *conn;
    driver = sql::mysql::get_mysql_driver_instance();
    conn = driver->connect("tcp://127.0.0.1:3306", "username", "password");
    return conn;
}

int main()
{
     createSQL();
}

Compile

g++ -g -std=c++11 -I .../include -L .../lib64 h1.cpp -lmysqlcppconn -luuid  -o h1

Run ./h1

Run the below sql statement and show result:

show procedure status where db='MyDB';

 

 

 

 

The Part 2 will call customized  store procedure with parameters;

void sp7()
{
sql::Connection *conn = getMySQLConn();
sql::PreparedStatement *prepStmt;
string tableName = "BookTable1";
conn->setSchema("MyDB");
prepStmt=conn->prepareStatement("CALL Sp_InsertIntoBookTable6(?,?,?,?,?)");
struct bookStruct *p;
p = bsArrayP5();
std::stringstream ss;
for (int i = 0; i < 100; i++)
{
prepStmt->setString(1,(p+i)->BookId);
prepStmt->setString(2,(p+i)->BookAuthor);
prepStmt->setString(3,(p+i)->BookTitle);
prepStmt->setString(4,(p+i)->BookISBN);
ss=stringstream((p+i)->BookComment);
prepStmt->setBlob(5,&ss);
prepStmt->execute();
ss=stringstream();
}
}

 

#include <iostream>
#include <stdlib.h>
#include <stdio.h>
#include <mysql_connection.h>
#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
#include <cppconn/statement.h>
#include <mysql_driver.h>
#include <cppconn/prepared_statement.h>
#include <uuid/uuid.h>
#include <sstream>

using namespace std;

struct bookStruct
{
    char *BookId;
    char *BookAuthor;
    char *BookISBN;
    char *BookTitle;
    char *BookComment;
};


void retrieveUuid(char *uuidValue)
{
    uuid_t newUUID;
    uuid_generate(newUUID);
    uuid_unparse(newUUID, uuidValue);
}

sql::Connection *getMySQLConn()
{
    sql::mysql::MySQL_Driver *driver;
    sql::Connection *conn;
    driver = sql::mysql::get_mysql_driver_instance();
    conn = driver->connect("tcp://127.0.0.1:3306", "username", "password");
    return conn;
}


struct bookStruct *bsArrayP5()
{
    static struct bookStruct arr[100];
    for (int i = 0; i < 100; i++)
    {
        struct bookStruct bs;
        bs.BookId = (char *)malloc(40);
        retrieveUuid(bs.BookId);
        bs.BookAuthor = (char *)malloc(40);
        retrieveUuid(bs.BookAuthor);
        bs.BookTitle = (char *)malloc(40);
        retrieveUuid(bs.BookTitle);
        bs.BookISBN = (char *)malloc(40);
        retrieveUuid(bs.BookISBN);
        bs.BookComment = (char *)malloc(40);
        retrieveUuid(bs.BookComment);
        arr[i] = bs;
    }

    struct bookStruct *p;
    p = arr;
    return p;
}


void sp7()
{
    sql::Connection *conn = getMySQLConn();
    sql::PreparedStatement *prepStmt;
    string tableName = "BookTable1";
    conn->setSchema("MyDB");
    prepStmt=conn->prepareStatement("CALL Sp_InsertIntoBookTable6(?,?,?,?,?)");
     struct bookStruct *p;
    p = bsArrayP5();
    std::stringstream ss;
    for (int i = 0; i < 100; i++)
    {
        prepStmt->setString(1,(p+i)->BookId);
        prepStmt->setString(2,(p+i)->BookAuthor);
        prepStmt->setString(3,(p+i)->BookTitle);
        prepStmt->setString(4,(p+i)->BookISBN);        
        ss=stringstream((p+i)->BookComment);
        prepStmt->setBlob(5,&ss);
        prepStmt->execute();
        ss=stringstream();
    }
}


int main()
{
    sp7();
    return 0;
}

Compile and run

g++ -g -std=c++11 -I .../include -L .../lib64 h1.cpp -lmysqlcppconn -luuid  -o h1

./h1