Restrict specified number of charecters in each row of Sqlite Db

[nagabathula]

Hello , every one i am stuck with a problem which i am not able to clear at all.. I have some continuous data which i am receiving on RS232 terminal. I wanted to save exactly 2944 chars in each row of the db.. This is what i tried first but this does not give the right result..

QString sq("INSERT INTO Bulkdb values(%1, %2)");
			sq = sq.arg(rcount)
              		.arg("'" +  (stfram.length()>2944)?stfram.left(2944):stfram  + "'");
					//.arg("'" + stfram + "'");

QString sq("INSERT INTO Bulkdb values(%1, %2)");
			sq = sq.arg(rcount)
              		.arg("'" +  (stfram.length()>2944)?stfram.left(2944):stfram  + "'");
					//.arg("'" + stfram + "'");

To copy to clipboard, switch view to plain text mode 

i even tried it this way also but only the first row of data is saved which is exactly 2944 char's but it is empty from the second row.. What is the right way of pushing only so many chars of data in each row.. Pls help me out i have been stuck with this problem past 3 days don't know how to go ahead..

if(stfram.length()>2944)
newstr.append(stfram.left(2944)
QString sq("INSERT INTO Bulkdb values(%1, %2)");
				sq = sq.arg(rcount)
				     .arg("'" + newstr + "'");

if(stfram.length()>2944)
newstr.append(stfram.left(2944)
QString sq("INSERT INTO Bulkdb values(%1, %2)");
				sq = sq.arg(rcount)
				     .arg("'" + newstr + "'");

To copy to clipboard, switch view to plain text mode 

thank you

[ChrisW67]

You only get the first block of data because you don't iterate to get the remainder of the data.

You should also get out of the habit of using strings to build SQL statements because the approach is fragile (What happens if the data contains a single quote?) and open to deliberate abuse (look up SQL Injection).

Something like this (all error checking omitted):

QByteArray stfram;  // I assume this
const int blockLength = 2944;
 
QSqlQuery qry;
qry.prepare("insert into bulkdb (col1, col2) values (?, ?)");
 
for (int pos = 0; pos < stfram.length(); pos += blockLength) {
  qry.bindValue(0, stfram.mid(pos, blockLength));
  qry.bindValue(1, /* some value for col2 */);
  qry.exec();
}

QByteArray stfram;  // I assume this
const int blockLength = 2944;

QSqlQuery qry;
qry.prepare("insert into bulkdb (col1, col2) values (?, ?)");

for (int pos = 0; pos < stfram.length(); pos += blockLength) {
  qry.bindValue(0, stfram.mid(pos, blockLength));
  qry.bindValue(1, /* some value for col2 */);
  qry.exec();
}

To copy to clipboard, switch view to plain text mode 

[nagabathula]

Hello sir thank you so much for the help.. i tried the code sir this is what i did,
Here also i have exactly 2944 chars in the first frame, in case i don't clear stfram after a few rows the data is disturbed sir , its not exactly 2944 chars after a few rows so i thought i will remove 2944 chars every iteration and intialize pos =0; Is it right sir..

// Data is appending continuously to stfram 
QSqlQuery qry;
qry.prepare("insert into Bulkdb (Time_Interval, ChannelData) values (?, ?)");
		for (pos = 0; pos < stfram.length(); pos += blockLength){
		qry.bindValue(0, rcount);
		qry.bindValue(1, stfram.mid(pos, blockLength));
		qry.exec();
		rcount++;
               	stfram.remove(0, blockLength); // trying to clear 2944 length from stfram
		pos=0;
}

// Data is appending continuously to stfram 
QSqlQuery qry;
qry.prepare("insert into Bulkdb (Time_Interval, ChannelData) values (?, ?)");
		for (pos = 0; pos < stfram.length(); pos += blockLength){
		qry.bindValue(0, rcount);
		qry.bindValue(1, stfram.mid(pos, blockLength));
		qry.exec();
		rcount++;
               	stfram.remove(0, blockLength); // trying to clear 2944 length from stfram
		pos=0;
}

To copy to clipboard, switch view to plain text mode 

i don't exactly get 2944 chars in every frame sir which is the right place to clear this container .. I am calling this function every 5 milli secs.
thank you

[ChrisW67]

How is data being continuously appended to strfram? This changes the situation quite a lot.

[nagabathula]

Hello sir this is what i am doing calling this function every 5 milli secs.

void datareceiver::savetodb()
if(port->bytesAvailable())
	{
	    numBytes = port->bytesAvailable();
	if(numBytes > sizeof(buff))
	numBytes = sizeof(buff);
       i = port->read(buff,numBytes);
       QByteArray data = (QByteArray::fromRawData(buff,numBytes));
	quint16 r;
//****************** Bit Wise Shift Left and ORing of two Bytes **/
   	   for (int i = 0; i < data.size(); i+=2)
	     {          
		  r = (((quint16) data.at(i) << 5) |(data.at(i+1)));
        	  char *ptrR = (char*) &r;
		  result.append(ptrR[1]); 
		  result.append(ptrR[0]);
    	 }
	    oldat.append(result.toHex());
 
 //******************search for First frame ID and save to db from there*********************/
		if(datrow ==0)
 
		{			
		   	QByteArray pattern("03b702200000");
			QByteArrayMatcher  matcher(pattern);matcher(pattern);
		     pos = matcher.indexIn(oldat, pos);		
                 datrow++;
		}
			 for(np = pos; np<oldat.size(); np++)
			 {
                             stfram.append(oldat.at(np));
		             pos++;
			 }
 
/********** Saving to db every 2944 i.e one Master Frame**************/
	if((stfram.length()>2944) && (stfram.startsWith("03b702200000")))
			{
				QSqlQuery qry;
				qry.prepare("insert into Bulkdb (Time_Interval, ChannelData) values (?, ?)");
				for (npos = 0; npos < stfram.length(); npos += blockLength){
				qry.bindValue(0, rcount);
				qry.bindValue(1, stfram.mid(npos, blockLength));
				qry.exec();
				rcount++;
					}
			}
}
stfram.clear();
 npos=0;
}

void datareceiver::savetodb()
if(port->bytesAvailable())
	{
	    numBytes = port->bytesAvailable();
	if(numBytes > sizeof(buff))
	numBytes = sizeof(buff);
       i = port->read(buff,numBytes);
       QByteArray data = (QByteArray::fromRawData(buff,numBytes));
	quint16 r;
//****************** Bit Wise Shift Left and ORing of two Bytes **/
   	   for (int i = 0; i < data.size(); i+=2)
	     {          
		  r = (((quint16) data.at(i) << 5) |(data.at(i+1)));
        	  char *ptrR = (char*) &r;
		  result.append(ptrR[1]); 
		  result.append(ptrR[0]);
    	 }
	    oldat.append(result.toHex());

 //******************search for First frame ID and save to db from there*********************/
		if(datrow ==0)

		{			
		   	QByteArray pattern("03b702200000");
			QByteArrayMatcher  matcher(pattern);matcher(pattern);
		     pos = matcher.indexIn(oldat, pos);		
                 datrow++;
		}
			 for(np = pos; np<oldat.size(); np++)
			 {
                             stfram.append(oldat.at(np));
		             pos++;
			 }
                  
/********** Saving to db every 2944 i.e one Master Frame**************/
	if((stfram.length()>2944) && (stfram.startsWith("03b702200000")))
			{
				QSqlQuery qry;
				qry.prepare("insert into Bulkdb (Time_Interval, ChannelData) values (?, ?)");
				for (npos = 0; npos < stfram.length(); npos += blockLength){
				qry.bindValue(0, rcount);
				qry.bindValue(1, stfram.mid(npos, blockLength));
				qry.exec();
				rcount++;
					}
			}
}
stfram.clear();
 npos=0;
}

To copy to clipboard, switch view to plain text mode 

Thank you sir

[ChrisW67]

OK, so extra data is coming in frequently and possibly asynchronously (another thread).

// Data is appending continuously to stfram 
QSqlQuery qry;
qry.prepare("insert into Bulkdb (Time_Interval, ChannelData) values (?, ?)");
		for (pos = 0; pos < stfram.length(); pos += blockLength){
		qry.bindValue(0, rcount);
		qry.bindValue(1, stfram.mid(pos, blockLength));
		qry.exec();
		rcount++;
               	stfram.remove(0, blockLength); // trying to clear 2944 length from stfram
		pos=0;
}

// Data is appending continuously to stfram 
QSqlQuery qry;
qry.prepare("insert into Bulkdb (Time_Interval, ChannelData) values (?, ?)");
		for (pos = 0; pos < stfram.length(); pos += blockLength){
		qry.bindValue(0, rcount);
		qry.bindValue(1, stfram.mid(pos, blockLength));
		qry.exec();
		rcount++;
               	stfram.remove(0, blockLength); // trying to clear 2944 length from stfram
		pos=0;
}

To copy to clipboard, switch view to plain text mode 

Resetting pos results in an endless for loop if data keeps arriving (you'd be better off using a while loop). If the data provider is in the same thread then it will not run until the event loop is reached. The for loop will terminate when the buffer is empty (and potentially a partial block is written to the database) and return to the event loop.

If the data provider is in another thread then you have other issues with synchronisation of access and the possibility below.

If there is any chance that more data can arrive in the buffer between line 6 and line 9 then this approach will fail. To see why:

• Imagine that the stfram contains 1000 bytes at the start of the for loop
• At line 6 and 7 up to blockLength bytes are copied into the database. In this case 1000 bytes.
• Before line 9 is reached more data arrives so the buffer contain 1200 bytes.
• At line 9 up to blockLength bytes are removed. The buffer is now empty but the last 200 bytes have been lost.

[nagabathula]

Sir i was thinking if i stop the timer before i start saving the data to db and then start the timer1 again after saving the data and clearing all the containers..
Can you pls tell me what is the best approach sir so that i don't lose any data and i am able to save every frame in the db.? I have been trying many methods past week to get the data right but i am going wrong some wer so i am missing some data in btw.i will use a while loop instead and try sir,

if((stfram.length()>2944) && (stfram.startsWith("03b702200000")))
			{
                                 timer1->stop();
				QSqlQuery qry;
				qry.prepare("insert into Bulkdb (Time_Interval, ChannelData) values (?, ?)");
				for (npos = 0; npos < stfram.length(); npos += blockLength){
				qry.bindValue(0, rcount);
				qry.bindValue(1, stfram.mid(npos, blockLength));
				qry.exec();
				rcount++;
					}
                               timer1->start(20);
			}
}
stfram.clear();
 npos=0;
}

if((stfram.length()>2944) && (stfram.startsWith("03b702200000")))
			{
                                 timer1->stop();
				QSqlQuery qry;
				qry.prepare("insert into Bulkdb (Time_Interval, ChannelData) values (?, ?)");
				for (npos = 0; npos < stfram.length(); npos += blockLength){
				qry.bindValue(0, rcount);
				qry.bindValue(1, stfram.mid(npos, blockLength));
				qry.exec();
				rcount++;
					}
                               timer1->start(20);
			}
}
stfram.clear();
 npos=0;
}

To copy to clipboard, switch view to plain text mode 

sir and instead of using stfram.remove(0, blockLength); i am using stfram.clear(); now , but that wud empty the whole container no sir. and sud't i be making pos =0; when i clear the stfram cause the container will be empty then no sir and the pos should also be in initial condition zero.?

If the data provider is in the same thread then it will not run until the event loop is reached

yes sir the data provider is in the same thread but what is event loop sir i din't understand that line.

[ChrisW67]

The event loop is the mechanism that orchestrates the event-driven GUI. It is where the "idle" program spends its time. QTimer timeout() signals are not sent until the program reaches the "idle" state. If the timer is in the same thread no data can be added to the buffer while your program is in a loop writing to the database.

If you only want to save whole blocks in each record of the database, and leave any partial block in the buffer, then it really is as simple as this:

QSqlQuery qry;
qry.prepare("insert into Bulkdb (Time_Interval, ChannelData) values (?, ?)");
 
while (stfram.length() >= blockLength) {
  qry.bindValue(0, rcount++);
  qry.bindValue(1, stfram.left(blockLength));
  qry.exec();
  stfram = stfram.mid(blockLength);
}

QSqlQuery qry;
qry.prepare("insert into Bulkdb (Time_Interval, ChannelData) values (?, ?)");
				
while (stfram.length() >= blockLength) {
  qry.bindValue(0, rcount++);
  qry.bindValue(1, stfram.left(blockLength));
  qry.exec();
  stfram = stfram.mid(blockLength);
}

To copy to clipboard, switch view to plain text mode 

[nagabathula]

Hello sir thank you so much for your time and help..
i don't have to clear the stfram buffer here no sir. ?

stfram = stfram.mid(blockLength);

here stfram data is replaced with data after 2944 chars right sir so i don't have to clear this buffer.. But i have a small problem sir after 3 rows the data is empty and another time when i tested it showed the right data 2944 chars for all the rows that time there were many rows more then 100 rows of right data..

sir what is the basics of clearing a container which we use in our program. I have to clear the containers right after every iteration. ? cause i have used a few more QByteArrays before this where i am retrieving and aligning the data before i plan to push it into the database.

Thank you

[ChrisW67]

I cannot tell you what the logic of your program is or should be. The buffer will be emptied of whole records by the while loop which will exit if there is not a whole record in the buffer. Presumably, when more data is added to the buffer you will call this code again. If no more data is appended to the buffer then more records will never be written to the database.

If you want to throw away the content of the buffer the the clear() method is good for that.

[nagabathula]

Hello sir i have one more doubt .. I am actually doing this to save the data into the rsdata from rs232 port

QByteArray rsdata = (QByteArray::fromRawData(buff,numBytes));

QByteArray rsdata = (QByteArray::fromRawData(buff,numBytes));

To copy to clipboard, switch view to plain text mode 

but i read the detail description of
QByteArray QByteArray::fromRawData ( const char * data, int size )

Constructs a QByteArray that uses the first size characters in the array data. The bytes in data are not copied. The caller must be able to guarantee that data will not be deleted or modified as long as the QByteArray (or an unmodified copy of it) exists.
Any attempts to modify the QByteArray or copies of it will cause it to create a deep copy of the data, ensuring that the raw data isn't modified.

but i am doing some bitwise shifting and aligining of the data so can it be the reason i am losing data sir, ?

I tried it this way i though i will append the data into an QByteArray rsdata; is this right sir it sud append the data in buff but i am not seeing the right data.

rsdata.append((QByteArray::fromRawData(buff,numBytes)).toHex());

rsdata.append((QByteArray::fromRawData(buff,numBytes)).toHex());

To copy to clipboard, switch view to plain text mode 

thank you

[ChrisW67]

I cannot tell you why you are losing data.

It is easy enough to do a simple experiment to find out if the sort of manipulations you do will cause a deep copy:

#include <QtCore>
#include <QDebug>
 
int main(int argc, char **argv)
{
    QCoreApplication app(argc, argv);
 
    char buf[] = "....";
    QByteArray a = QByteArray::fromRawData(buf, 4);
    QByteArray b = a;   // shallow copy of a 
    qDebug() << buf << a << b;  // all point to buf and show same data
 
    buf[0] = 'X';   // modify original buffer directly
    qDebug() << buf << a << b;  // all changed because the share the buffer
 
    b[0] = 'Y';     // change using the byte array interface causes deep copy 
    qDebug() << buf << a << b;  //  only b changed after deep-copy
}

#include <QtCore>
#include <QDebug>

int main(int argc, char **argv)
{
    QCoreApplication app(argc, argv);

    char buf[] = "....";
    QByteArray a = QByteArray::fromRawData(buf, 4);
    QByteArray b = a;   // shallow copy of a 
    qDebug() << buf << a << b;  // all point to buf and show same data

    buf[0] = 'X';   // modify original buffer directly
    qDebug() << buf << a << b;  // all changed because the share the buffer

    b[0] = 'Y';     // change using the byte array interface causes deep copy 
    qDebug() << buf << a << b;  //  only b changed after deep-copy
}

To copy to clipboard, switch view to plain text mode 

Output:

.... "...." "...." 
X... "X..." "X..." 
X... "X..." "Y..."

.... "...." "...." 
X... "X..." "X..." 
X... "X..." "Y..."

To copy to clipboard, switch view to plain text mode