MYSQL insert

[allensr]

I am wanting to insert into my database STL vector data types. My first question is: Is this possible??

Test code looks like this:

bool createConnection()
{
sVector3<double> pos(0.0,0.0,0.0);
sVector3<double> orient(0.0,0.0,0.0);
sVector3<double> vel(0.0,0.0,0.0);
...
QSqlTableModel * model = new QSqlTableModel;
model->setTable("Missile1");

QSqlRecord dataRecord;

for(int i=0; i <= 180; ++i)
{
//made up values
pos[0] = i+100;
pos[1] = i+10;
pos[2] = i;
dataRecord.setValue(QString("Position"),pos);

bool insert = model->insertRecord(-1,dataRecord);
bool submit = model->submit();
}
}


The dataRecord.setValue(QString("Position"),pos); statement is the one I know is wrong but don't know how to fix it. I eventually will have a combination of several vectors like this and scalars that I will be placing into this table at a high rate.

Any help would be greatly appreciated.

[niko]

you can't save STL vectors directly using the setValue-method, as it accepts only QVairant-values.
what mysql-type has the Position-field?
if it is a string you have to convert the vectors into a QString.
afaik there is no vector-field-type in mysql...

niko

[allensr]

Ok, thanks.

I have "fixed" my error. Next question, is there a better/more efficient method of placing this data in (i.e. 1) insertRow() ,2) setData() 3) submitAll())?? Code below.

Also, is there a maximum number of columns?? I see where there is a max limit on the length of the column but not on the number of columns.

bool createConnection()
{
   sVector3<double> pos(0.0,0.0,0.0);
   sVector3<double> orient(0.0,0.0,0.0);
   sVector3<double> vel(0.0,0.0,0.0);
 
   ...
   QSqlQuery query;
 
   //Delete old table if it exists, if not no harm no foul
   query.exec("drop table Missile1");
   query.exec("create table Missile1 ( "
              "PositionX double, PositionY double, PositionZ double,"
              "OrientationX double, OrientationY double, OrientationZ double,"
              "VelocityX double, VelocityY double, VelocityZ double)");
 
   QSqlTableModel * model = new QSqlTableModel;
   model->setTable("Missile1");
 
   QSqlRecord dataRecord = model->record();
 
   int row = 0;
   for(int i=0; i <= 150; ++i)
   {
     //made up values
      pos[0] = i+100;
      pos[1] = i+10;
      pos[2] = i;
      orient[0] = i+50;
      orient[1] = i+5;
      orient[2] = i;
      vel[0] += 25.123;
      vel[1] += 2.456;
      vel[2] += 0.7890123;
 
      row = model->rowCount();
      model->insertRow(row);
      model->setData(model->index(row,0),pos[0]);
      model->setData(model->index(row,1),pos[1]);
      model->setData(model->index(row,2),pos[2]);
      model->setData(model->index(row,3),orient[0]);
      model->setData(model->index(row,4),orient[1]);
      model->setData(model->index(row,5),orient[2]);
      model->setData(model->index(row,6),vel[0]);
      model->setData(model->index(row,7),vel[1]);
      model->setData(model->index(row,8),vel[2]);
 
      bool submit = model->submitAll();
 
      if(!submit)
         cerr << "Record not submited " << endl;
   }
   ...
   return true;
}

bool createConnection()
{
   sVector3<double> pos(0.0,0.0,0.0);
   sVector3<double> orient(0.0,0.0,0.0);
   sVector3<double> vel(0.0,0.0,0.0);

   ...
   QSqlQuery query;

   //Delete old table if it exists, if not no harm no foul
   query.exec("drop table Missile1");
   query.exec("create table Missile1 ( "
              "PositionX double, PositionY double, PositionZ double,"
              "OrientationX double, OrientationY double, OrientationZ double,"
              "VelocityX double, VelocityY double, VelocityZ double)");

   QSqlTableModel * model = new QSqlTableModel;
   model->setTable("Missile1");

   QSqlRecord dataRecord = model->record();

   int row = 0;
   for(int i=0; i <= 150; ++i)
   {
     //made up values
      pos[0] = i+100;
      pos[1] = i+10;
      pos[2] = i;
      orient[0] = i+50;
      orient[1] = i+5;
      orient[2] = i;
      vel[0] += 25.123;
      vel[1] += 2.456;
      vel[2] += 0.7890123;

      row = model->rowCount();
      model->insertRow(row);
      model->setData(model->index(row,0),pos[0]);
      model->setData(model->index(row,1),pos[1]);
      model->setData(model->index(row,2),pos[2]);
      model->setData(model->index(row,3),orient[0]);
      model->setData(model->index(row,4),orient[1]);
      model->setData(model->index(row,5),orient[2]);
      model->setData(model->index(row,6),vel[0]);
      model->setData(model->index(row,7),vel[1]);
      model->setData(model->index(row,8),vel[2]);

      bool submit = model->submitAll();

      if(!submit)
         cerr << "Record not submited " << endl;
   }
   ...
   return true;
}

To copy to clipboard, switch view to plain text mode 

Thanks.

[niko]

Also, is there a maximum number of columns?? I see where there is a max limit on the length of the column but not on the number of columns.

i guess the only limitation is by MySQL - and here it is normally the datatype of the primary key. if the primary key is an INT the limit will be at 2.147.483.647 rows - but in your case you don't have a primary key anyway (i would suggest you add one...)

QSqlTAbleModel is maily useful to provide a data-class for eg. QTableView. In your case you could use QSqlQuery to execute the INSERT-query.

QSqlQuery query;
        query.prepare("INSERT INTO person (id, forename, surname) "
                      "VALUES (:id, :forename, :surname)");
        query.bindValue(":id", 1001);
        query.bindValue(":forename", "Bart");
        query.bindValue(":surname", "Simpson");
        query.exec();

QSqlQuery query;
        query.prepare("INSERT INTO person (id, forename, surname) "
                      "VALUES (:id, :forename, :surname)");
        query.bindValue(":id", 1001);
        query.bindValue(":forename", "Bart");
        query.bindValue(":surname", "Simpson");
        query.exec();

To copy to clipboard, switch view to plain text mode 

niko

[allensr]

Thanks again, niko. Using your technique for insertion is MUCH MUCH faster!! To put this issue to bed, here is what I ended up doing:

bool createConnection()
{
   sVector3<double> pos(0.0,0.0,0.0);
   sVector3<double> orient(0.0,0.0,0.0);
   sVector3<double> vel(0.0,0.0,0.0);
   double time(0.0),sltRngToGo(0.0),fuel(0.0),seekerEl(0.0),seekerAz(0.0);
...
   //Set up MySQL template query for insertion
   QSqlQuery query;
   //Delete old table if it exists, if not no harm no foul
   query.exec("drop table Missile2");
   query.exec("create table Missile2 ( "
              "Time double,"
              "PositionX double, PositionY double, PositionZ double,"
              "OrientationX double, OrientationY double, OrientationZ double,"
              "VelocityX double, VelocityY double, VelocityZ double,"
              "SltRngToGo double, Fuel double, SeekerAcq smallint, SeekerEl double,"
              "SeekerAz double)"
              "ENGINE = MyISAM ");
 
   query.prepare("insert into missile2 (Time,PositionX, PositionY, PositionZ,"
                 "OrientationX, OrientationY, OrientationZ,"
                 "VelocityX, VelocityY, VelocityZ, SltRngToGo, Fuel, "
                 "SeekerAcq, SeekerEl, SeekerAz)"
                 "Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
 
      for(int i=0; i <= 10000; ++i)
      {
        //made up values
         time = i;
         pos[0] = i+100;
         pos[1] = i+10;
         pos[2] = i;
         orient[0] = i+50;
         orient[1] = i+5;
         orient[2] = i;
         vel[0] += 25.123;
         vel[1] += 2.456;
         vel[2] += 0.7890123;
         sltRngToGo = sltRngToGo+1.2345;
         fuel = fuel+0.567;
         seekerAcq = true;
         seekerEl = 2.2;
         seekerAz = 3.45;
 
         query.addBindValue(time);
         query.addBindValue(pos[0]);
         query.addBindValue(pos[1]);
         query.addBindValue(pos[2]);
         query.addBindValue(orient[0]);
         query.addBindValue(orient[1]);
         query.addBindValue(orient[2]);
         query.addBindValue(vel[0]);
         query.addBindValue(vel[1]);
         query.addBindValue(vel[2]);
         query.addBindValue(sltRngToGo);
         query.addBindValue(fuel);
         query.addBindValue(seekerAcq);
         query.addBindValue(seekerEl);
         query.addBindValue(seekerAz);
         query.exec();
   }
   return true;
}

bool createConnection()
{
   sVector3<double> pos(0.0,0.0,0.0);
   sVector3<double> orient(0.0,0.0,0.0);
   sVector3<double> vel(0.0,0.0,0.0);
   double time(0.0),sltRngToGo(0.0),fuel(0.0),seekerEl(0.0),seekerAz(0.0);
...
   //Set up MySQL template query for insertion
   QSqlQuery query;
   //Delete old table if it exists, if not no harm no foul
   query.exec("drop table Missile2");
   query.exec("create table Missile2 ( "
              "Time double,"
              "PositionX double, PositionY double, PositionZ double,"
              "OrientationX double, OrientationY double, OrientationZ double,"
              "VelocityX double, VelocityY double, VelocityZ double,"
              "SltRngToGo double, Fuel double, SeekerAcq smallint, SeekerEl double,"
              "SeekerAz double)"
              "ENGINE = MyISAM ");

   query.prepare("insert into missile2 (Time,PositionX, PositionY, PositionZ,"
                 "OrientationX, OrientationY, OrientationZ,"
                 "VelocityX, VelocityY, VelocityZ, SltRngToGo, Fuel, "
                 "SeekerAcq, SeekerEl, SeekerAz)"
                 "Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

      for(int i=0; i <= 10000; ++i)
      {
        //made up values
         time = i;
         pos[0] = i+100;
         pos[1] = i+10;
         pos[2] = i;
         orient[0] = i+50;
         orient[1] = i+5;
         orient[2] = i;
         vel[0] += 25.123;
         vel[1] += 2.456;
         vel[2] += 0.7890123;
         sltRngToGo = sltRngToGo+1.2345;
         fuel = fuel+0.567;
         seekerAcq = true;
         seekerEl = 2.2;
         seekerAz = 3.45;
         
         query.addBindValue(time);
         query.addBindValue(pos[0]);
         query.addBindValue(pos[1]);
         query.addBindValue(pos[2]);
         query.addBindValue(orient[0]);
         query.addBindValue(orient[1]);
         query.addBindValue(orient[2]);
         query.addBindValue(vel[0]);
         query.addBindValue(vel[1]);
         query.addBindValue(vel[2]);
         query.addBindValue(sltRngToGo);
         query.addBindValue(fuel);
         query.addBindValue(seekerAcq);
         query.addBindValue(seekerEl);
         query.addBindValue(seekerAz);
         query.exec();
   }
   return true;
}

To copy to clipboard, switch view to plain text mode