Update Table Example

There are those times when tables need to be updated.  Here is an example of the necessary steps to sucessfully update database tables.

The following describes the table members of the dataset “lk2″.

[codesyntax lang=”mysql”]

--
-- Table structure for table `lk2`
--

DROP TABLE IF EXISTS `lk2`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `lk2` (
  `idlk2` int NOT NULL AUTO_INCREMENT,
  `idlk1` int NOT NULL,
  `lkn` varchar(500) NOT NULL,
  `lnk` varchar(500) NOT NULL,
  `ddes` mediumtext NOT NULL,
  PRIMARY KEY (`idlk2`)
) ENGINE=InnoDB AUTO_INCREMENT=697 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

[/codesyntax]

Here we have the definition of the interface to allow access to the table class.

[codesyntax lang=”cpp”]

bcswebtools_lk2_io li;

[/codesyntax]

The following describes the devinition of the data class.

[codesyntax lang=”cpp”]

#ifndef LK2_IO_H
#define LK2_IO_H
#include <string>
#include <cppconn/driver.h>
#include <cppconn/resultset.h>
#include <cppconn/prepared_statement.h>
#include "mysql_connection.h"

using namespace std;

const int rmaxRecs = 1000;

struct lk2_area
{
    int idlk2;
    int idlk1;
    string lkn;
    string lnk;
    string ddes;
};

struct db_conn_area_bcswebtools_lk2
{
    sql::Driver *driver;
    sql::Connection *con;
    sql::Statement *stmt;
    sql::ResultSet *res;
    sql::SQLString server = "tcp://127.0.0.1:3306";
    sql::SQLString user  = "bcs";
    sql::SQLString pwd = "Peace007!amb";
    sql::SQLString catalog = "bcswebtools";
    sql::SQLString qry = "SELECT `id`, `cust` from `cust` order by `cust`";
    sql::ResultSet *rs;
    sql::ResultSetMetaData *res_meta;
};

class bcswebtools_lk2_io

{

//bcswebtools_lk2_io lk2_ut

public:
    lk2_area tab_lk2;
    lk2_area lk2a [rmaxRecs];
    db_conn_area_bcswebtools_lk2 lk2_dbc;

    void lk2_create();
    size_t lk2_read();
    void lk2_update();
    void lk2_delete();
    uint64_t lk2_last(string cmd);
    string catNo;
    string mySqlTwo(string zcmd);
    string lnkNo = "";
    string cat = "";
    bool oneItem;
    bool lk2All;

protected:

private:

};

#endif // LK2_IO_H

[/codesyntax]

The following identifies the code required to read and store every data element in the table.

[codesyntax lang=”cpp”]

size_t bcswebtools_lk2_io::lk2_read()
{
    int ii = 0;
    size_t numRecs = 0;
    lnk_dbc.driver = get_driver_instance();
    lnk_dbc.con = lnk_dbc.driver->connect(lnk_dbc.server, lnk_dbc.user, lnk_dbc.pwd);
    lnk_dbc.con->setSchema(lnk_dbc.catalog);
    lnk_dbc.stmt = lnk_dbc.con->createStatement();
    string cmd;
    cmd = "SELECT idlk2,idlk1,lkn,lnk,ddes FROM lk2 where idlk1 = " + catNo + " order by lkn";
    if (lnkNo.length() > 0)
    {
        cmd = "SELECT idlk2,idlk1,lkn,lnk,ddes FROM lk2 where idlk2 = " + lnkNo;
    }
    if (cat.length() > 0)
    {
        cmd = "SELECT idlk2,idlk1,lkn,lnk,ddes FROM lk2 where lkn like '%" + cat + "%' order by lkn";
    }
    if (oneItem)
    {
        cmd = "SELECT idlk2,idlk1,lkn,lnk,ddes FROM lk2 where idlk2 = " + cat;
    }

    if (lk2All)
    {
        cmd = "SELECT idlk2,idlk1,lkn,lnk,ddes FROM lk2 ";
    }

    lnk_dbc.res = lnk_dbc.stmt->executeQuery(cmd.c_str());
    numRecs = lnk_dbc.res->rowsCount();
    if (numRecs < rmaxRecs)
    {
        while (lnk_dbc.res->next())
        {
            lk2a[ii].idlk2 = lnk_dbc.res->getInt("idlk2");
            lk2a[ii].idlk1 = lnk_dbc.res->getInt("idlk1");
            lk2a[ii].lkn = lnk_dbc.res->getString("lkn");
            lk2a[ii].lnk = lnk_dbc.res->getString("lnk");
            lk2a[ii].ddes = lnk_dbc.res->getString("ddes");
            ii++;
        }
    }
    delete lnk_dbc.res;
    delete lnk_dbc.stmt;
    delete lnk_dbc.con;
    lnkNo = "";
    return numRecs;

}

[/codesyntax]

The following depicts the code reauired to update the data table “lk2″,

[codesyntax lang=”cpp”]

void DlgTstFrame::OnmenUpTabSelected(wxCommandEvent& event)
{
    li.lk2All = true;
    li.lnkNo = "";
    li.oneItem = false;
    size_t lRec;
    lRec = li.lk2_read();
    size_t cnt = 0;
    while (cnt < lRec-1)
    {
//        li.lk2a[i]
        if (li.lk2a[cnt].lnk.find("archman") > 0)
        {
            boost::replace_all(li.lk2a[cnt].lnk, "archman", "archbrooks");
            li.tab_lk2.idlk1 = li.lk2a[cnt].idlk1;
            li.tab_lk2.idlk2 = li.lk2a[cnt].idlk2;
            li.tab_lk2.ddes = li.lk2a[cnt].ddes;
            li.tab_lk2.lkn = li.lk2a[cnt].lkn;
            li.tab_lk2.lnk = li.lk2a[cnt].lnk;
            li.lk2_update();
        }
        cnt++;
    }
    li.lk2a[1].lnk = li.lk2a[1].lnk;
    lRec = lRec;
    li.lk2All = false;
}

[/codesyntax]

Mr. Arch Brooks, Software Engineer, Brooks Computing Systems, LLC authored this article.