Using the basic structure from the last example where we implemented a simple query, let's have a look at how to do inserts, deletes, and updates based on the primary key.
First of all, use the same table structure as last time:
> mysql -u root test
and create the following table in the test database:
create table t1 ( a integer primary key, b integer, c char(20)) engine=ndb;
insert into t1 values(1, 1, 'hello');
insert into t1 values(2, 2, 'hello again');
Insert operation - insert into t1 values (3, 1, 'good bye');
Before we do the actual insert, let us prepare the data of the tuple we should insert:
int pk_a =3 ; //primary key
int b=1;
char * string = "good bye";
char c[20];
memset(c,32,sizeof(c)); // mysql requires chars to be space padded.
memcpy(c,string, strlen(string)); //copy string to spacepadded storage
If we do not space pad the string we want to insert, then it will be difficult to present the data in a correct way when accessing it using SQL from the MySQL server.
When we do an insert, we simply have to get an operation on the table we want to insert the tuple in and define it should be an insertTuple operation (for brewity I leave out some of the error checking, but the complete code is at the end of this) :
op->insertTuple();
After that we have to define the primary key we want to write:
op->equal("a", (const char*)&a);
and then for the rest of the non-pk columns:
op->setValue("b", (const char*)&b);
op->setValue("c", (const char*)c);
After that, we call commit the transaction as in the previous example. Quite simple!
Update operation - update t1 set b=4, c='mysql cluster rock' where a=2;
Again, we have to define the data for the tuple we want to update:
int pk_a =2; //primary key
int b=4;
char * string = "mysql cluster rocks";
char c[20];
memset(c,32,sizeof(c)); // mysql requires chars to be space padded.
memcpy(c,string, strlen(string)); //copy string to spacepadded storage
And we simple denote that this should be an update operation:
op->updateTuple();
After that we have to define the primary key of the tuple we want to update:
op->equal("a", (const char*)&a);
and then for the rest of the non-pk columns:
op->setValue("b", (const char*)&b);
op->setValue("c", (const char*)c);
And commit! Voila!
Delete operation - delete from t1 where a=1;
For the delete operation it is only necessary to give the primary key of the tuple we want to delete:
int pk_a =1; //primary key
And define that we want to do a delete operation:
op->deleteTuple();
After that we have to define the primary key of the tuple we want to update:
op->equal("a", (const char*)&a);
Commit!
Source code
(Compile and link as in previous examples)
#include
#include
#include
using namespace std;
/**
* prototypes
*/
int pk_read( Ndb * ndb);
int pk_insert( Ndb * ndb);
int pk_update( Ndb * ndb);
int pk_delete( Ndb * ndb);
int main()
{
ndb_init();
/**
* define a connect string to the management server
*/
char * connectstring = "localhost";
/**
* Create a Ndb_cluster_connection object using the connectstring
*/
Ndb_cluster_connection * conn = new Ndb_cluster_connection(connectstring);
/**
* Connect to the management server
* try 12 times, wait 5 seconds between each retry,
* and be verbose (1), if connection attempt failes
*/
if(conn->connect(12, 5, 1) != 0)
{
cout << "Unable to connect to management server." <<>wait_until_ready(30,0) <0) ndb =" new">init() != 0)
{
/**
* just exit if we can't initialize the Ndb object
*/
return -1;
}
if (pk_read( ndb ) < a="1;" pk_a =" 1;" b="0;" trans =" ndb-">startTransaction();
/**
* If there is an error then we should check the
* calling object to find out what the problem is.
* The error is stored in an NdbError struct and contains
* an error message, error code etc.
* Later on, we will do more elaborate error handling, but for
* now we will just print out the error message!
*/
if ( trans == 0)
{
NdbError err = ndb->getNdbError();
cout << "err code: " << op =" trans-">getNdbOperation("t1");
/**
* if op == 0 then we have to check the error.
* The error is stored at the parent in call stack.
* We also have to close the transaction since it is no longer
* usable!!
*/
if ( op == 0)
{
trans->close();
NdbError err = trans->getNdbError();
cout << "err code: " <<>committedRead();
/**
* Define the primary key!
*/
if (op->equal("a", (const char*)&pk_a) <>close();
NdbError err = op->getNdbError();
cout << "err code: " <<>getValue("b", (char*)&b) == 0)
{
trans->close();
NdbError err = op->getNdbError();
cout << "err code: " <<>getValue("c", c) == 0)
{
trans->close();
NdbError err = op->getNdbError();
cout << "err code: " <<>execute(NdbTransaction::Commit,
NdbTransaction::AbortOnError,
1) <>close();
/**
* If there is an error, it is stored in NdbError.
*/
NdbError err = trans->getNdbError();
cout << "err code: " <<>close();
/**
* print out the result:
*/
cout << "b= " << c= " << c << endl; return 0; } /** * This function implements * insert into t1 values (3, 1, 'good bye'); */ int pk_insert(Ndb * ndb) { /** * Define the tuple to insert: */ int pk_a =3 ; //primary key int b=1; char * string = " trans =" ndb-">startTransaction();
if ( trans == 0)
{
NdbError err = ndb->getNdbError();
cout << "err code: " << op =" trans-">getNdbOperation("t1");
if ( op == 0)
{
trans->close();
NdbError err = trans->getNdbError();
cout << "err code: " <<>insertTuple();
/**
* Define the primary key!
*/
if (op->equal("a", (const char*)&pk_a) <>close();
NdbError err = op->getNdbError();
cout << "err code: " <<>setValue("b", (const char*)&b) <>close();
NdbError err = op->getNdbError();
cout << "err code: " <<>setValue("c", (const char*)c) <>close();
NdbError err = op->getNdbError();
cout << "err code: " <<>execute(NdbTransaction::Commit,
NdbTransaction::AbortOnError,
1) <>close();
/**
* If there is an error, it is stored in NdbError.
*/
NdbError err = trans->getNdbError();
cout << "err code: " <<>close();
return 0;
}
/**
* This function implements
* update t1 set b=4, c='mysql cluster rocks' where a=2;
*/
int pk_update(Ndb * ndb)
{
/**
* Define the tuple to insert:
*/
int pk_a =2 ; //primary key
int b=4;
char * string = "mysql cluster rocks";
char c[20];
memset(c,32,sizeof(c)); // mysql requires chars to be space padded.
memcpy(c,string, strlen(string)); //copy string to spacepadded storage
NdbTransaction * trans = ndb->startTransaction();
if ( trans == 0)
{
NdbError err = ndb->getNdbError();
cout << "err code: " << op =" trans-">getNdbOperation("t1");
if ( op == 0)
{
trans->close();
NdbError err = trans->getNdbError();
cout << "err code: " <<>updateTuple();
/**
* Define the primary key!
*/
if (op->equal("a", (const char*)&pk_a) <>close();
NdbError err = op->getNdbError();
cout << "err code: " <<>setValue("b", (const char*)&b) <>close();
NdbError err = op->getNdbError();
cout << "err code: " <<>setValue("c", (const char*)c) <>close();
NdbError err = op->getNdbError();
cout << "err code: " <<>execute(NdbTransaction::Commit,
NdbTransaction::AbortOnError,
1) <>close();
/**
* If there is an error, it is stored in NdbError.
*/
NdbError err = trans->getNdbError();
cout << "err code: " <<>close();
return 0;
}
/**
* This function implements
* delete from t1 where a=1
*/
int pk_delete(Ndb * ndb)
{
/**
* Define the tuple to delete:
*/
int pk_a =1 ; //primary key
NdbTransaction * trans = ndb->startTransaction();
if ( trans == 0)
{
NdbError err = ndb->getNdbError();
cout << "err code: " << op =" trans-">getNdbOperation("t1");
if ( op == 0)
{
trans->close();
NdbError err = trans->getNdbError();
cout << "err code: " <<>deleteTuple();
/**
* Define the primary key!
*/
if (op->equal("a", (const char*)&pk_a) <>close();
NdbError err = op->getNdbError();
cout << "err code: " <<>execute(NdbTransaction::Commit,
NdbTransaction::AbortOnError,
1) <>close();
/**
* If there is an error, it is stored in NdbError.
*/
NdbError err = trans->getNdbError();
cout << "err code: " <<>close();
return 0;
}
No comments:
Post a Comment