Qt to Sqlite data insertion doubt.

[rex]

Hello every one.

i have a doubt , how can to insert data which is in a QList<QString> into a database.
i have created the table and the columns for it in connection.h file.. i need to insert the continuous data in the string for 500 columns with a time stamp in the end.. but it is not working what i am doing wrong here. pls help me out with it.

for(int p = 0; p < rdata.count();p++)  // rdata is QList<QString>
	{
		 lstdata = rdata.at(p);               // lstdata is a QString
	}
	QString sp("INSERT INTO thdata values (%1)");
				sp = sp.arg(datacount)
					.arg("'" + lstdata + "'");
					m_query.exec(sp);
					datacount++;
					lstdata.clear();

for(int p = 0; p < rdata.count();p++)  // rdata is QList<QString>
	{
		 lstdata = rdata.at(p);               // lstdata is a QString
	}
	QString sp("INSERT INTO thdata values (%1)");
				sp = sp.arg(datacount)
					.arg("'" + lstdata + "'");
					m_query.exec(sp);
					datacount++;
					lstdata.clear();

To copy to clipboard, switch view to plain text mode 

thank you

[stampede]

for(int p = 0; p < rdata.count();p++)  // rdata is QList<QString>
	{
		 lstdata = rdata.at(p);               // lstdata is a QString
	}

for(int p = 0; p < rdata.count();p++)  // rdata is QList<QString>
	{
		 lstdata = rdata.at(p);               // lstdata is a QString
	}

To copy to clipboard, switch view to plain text mode 

This code does nothing more than assign values from rdata list into lstdata. In the end you are inserting only the last item from list. You have to put the database insertion code into the loop as well

Also use the QSqlQuery::lastError() method after executing query to check status.

[rex]

hello thanks for the reply, i am actually calling this function every sec using a QTimer.
this is what i have done in connection.h to create 500 columns for the table "thdata" i just need to insert the continuous data which is present in lstdata into these columns.

QString dbtot("create table if not exists thdata(Time_InterVal INTEGER, Thermo varchar(250), Thermo varchar(250),Thermo varchar(250),Thermo4 varchar(250),Thermo5 varchar(250),Thermo6 varchar(250) . . . . . . Thermo500 varchar(250)
query.exec(dbtot);

QString dbtot("create table if not exists thdata(Time_InterVal INTEGER, Thermo varchar(250), Thermo varchar(250),Thermo varchar(250),Thermo4 varchar(250),Thermo5 varchar(250),Thermo6 varchar(250) . . . . . . Thermo500 varchar(250)
query.exec(dbtot);

To copy to clipboard, switch view to plain text mode 

this is how i am trying to insert the data base into the sqlite table columns now, but the data is not been inserted into columns.

for(int p = 0; p < rdata.count();p++)
	{
	 lstdata = rawdata.at(p);
	if(lstdata.count()!=0)
		{
		QString sp("INSERT INTO thermodata values (%1)");
					sp = sp.arg(datacount)
						.arg("'" + lstdata + "'");
						m_query.exec(sp);
				        	datacount++;
		}
	}
	listdata.clear();

for(int p = 0; p < rdata.count();p++)
	{
	 lstdata = rawdata.at(p);
	if(lstdata.count()!=0)
		{
		QString sp("INSERT INTO thermodata values (%1)");
					sp = sp.arg(datacount)
						.arg("'" + lstdata + "'");
						m_query.exec(sp);
				        	datacount++;
		}
	}
	listdata.clear();

To copy to clipboard, switch view to plain text mode 

i did put it in the loop but i have no data in the columns. ..
thank you sir

[stampede]

QString dbtot("create table if not exists thdata(Time_InterVal INTEGER, Thermo varchar(250), Thermo varchar(250),Thermo varchar(250),Thermo4 varchar(250),Thermo5 varchar(250),Thermo6 varchar(250) . . . . . . Thermo500 varchar(250)

QString dbtot("create table if not exists thdata(Time_InterVal INTEGER, Thermo varchar(250), Thermo varchar(250),Thermo varchar(250),Thermo4 varchar(250),Thermo5 varchar(250),Thermo6 varchar(250) . . . . . . Thermo500 varchar(250)

To copy to clipboard, switch view to plain text mode 

Did you hand-coded all of this ? You could create statement like this using a loop.

QString thermoDbData;
for( int i=0 ; i<500 ; ++i ){
thermoDbData += QString("Thermo%1 varchar(250) ").arg(i) + (i==499 ? "" : ",");
}
QString dbtot = QString("create table if not exists thdata(Time_InterVal INTEGER, %1 );").arg(thermoDbData);

QString thermoDbData;
for( int i=0 ; i<500 ; ++i ){
thermoDbData += QString("Thermo%1 varchar(250) ").arg(i) + (i==499 ? "" : ",");
}
QString dbtot = QString("create table if not exists thdata(Time_InterVal INTEGER, %1 );").arg(thermoDbData);

To copy to clipboard, switch view to plain text mode 

Now to insert a data into 500 columns you need to create an insertion statement that looks like

insert into thdata values(/*and here goes your string: value1, value2, ..., value500*/)

insert into thdata values(/*and here goes your string: value1, value2, ..., value500*/)

To copy to clipboard, switch view to plain text mode 

Just use a loop to join all the values from list into one string ( or use QStringList::join() method ) and then insert it into database.

[rex]

thanks a lot for the help sir. Yes i actually hand- coded every thing to create 500 columns. i app the code you gave in the main program and it is working fine all the columns are created. I am actually taking four char's from a QString and appending them to a List that is on channels data , tat is the reason i stored all the values in a QList<QString>

for(int k=0; k<newdat.size(); k+=4)
	{
	    nudata.append(newdat.mid(k, 4));      // QList<QString> nudata;
	}
	//timest = QTime::currentTime().toString("hh:mm:ss");									 
	for(int p = 0; p < nudata.count();p++)
	{
		 lstdata = nudata.at(p);      // QString  lstdata;
	}
	if(lstdata.count()!=0)
		{
		//QMessageBox::information(this, "Test","Loop Position reached");
		QString sq("insert into thdata values(lstdata)");
		m_query.exec(sq);
		}

for(int k=0; k<newdat.size(); k+=4)
	{
	    nudata.append(newdat.mid(k, 4));      // QList<QString> nudata;
	}
	//timest = QTime::currentTime().toString("hh:mm:ss");									 
	for(int p = 0; p < nudata.count();p++)
	{
		 lstdata = nudata.at(p);      // QString  lstdata;
	}
	if(lstdata.count()!=0)
		{
		//QMessageBox::information(this, "Test","Loop Position reached");
		QString sq("insert into thdata values(lstdata)");
		m_query.exec(sq);
		}

To copy to clipboard, switch view to plain text mode 

but i still don't have the data in the columns. .. is this syntax right. I am calling the function every 2 sec's with a timer.

thanks you.

[stampede]

but i still don't have the data in the columns. .. is this syntax right.

Try to debug, print the value of "lstdata" variable before creating query, see what QSqlQuerry::lastError() says after executing the query ect...

QString sq("insert into thdata values(lstdata)");

QString sq("insert into thdata values(lstdata)");

To copy to clipboard, switch view to plain text mode 

This code will try to insert a string value "lstdata" rather than the value of "lstdata" variable. Try:

QString sq = QString("insert into thdata values(%1);").arg(lstdata);

QString sq = QString("insert into thdata values(%1);").arg(lstdata);

To copy to clipboard, switch view to plain text mode 

[rex]

Hello sir thanks for your time and help, This is the function i am calling every 2 seconds. The data is present in the QString just before the data insertion but its not saved into the data base.

void datareceiver::aligndata()
{
	for(int k=0; k<newdat.size(); k+=4)
	{
	    nudata.append(newdat.mid(k, 4));      
	}
 
	for(int p = 0; p < nudata.count();p++)
	{
	 lstdata = nudata.at(p);
	}
  if(lstdata!=0)
    {
        textBrowser_2->append(lstdata); \\ The data is displayed in the text browser but not in the data base.
	QString sq = QString("insert into thdata values(%1);").arg(lstdata);
	m_query.exec(sq);
    }
}

void datareceiver::aligndata()
{
	for(int k=0; k<newdat.size(); k+=4)
	{
	    nudata.append(newdat.mid(k, 4));      
	}

	for(int p = 0; p < nudata.count();p++)
	{
	 lstdata = nudata.at(p);
	}
  if(lstdata!=0)
    {
        textBrowser_2->append(lstdata); \\ The data is displayed in the text browser but not in the data base.
	QString sq = QString("insert into thdata values(%1);").arg(lstdata);
	m_query.exec(sq);
    }
}

To copy to clipboard, switch view to plain text mode 

the data is present in the String but its not updated into the db. what do you think can be going wrong.

thank you

[stampede]

Hello sir thanks for your time and help

no problem

textBrowser_2->append(lstdata); \\ The data is displayed in the text browser but not in the data base.

And does the data looks like you expect ?
You have created your table as

thdata(Time_InterVal INTEGER, /* ... and 500 varchars*/ )

thdata(Time_InterVal INTEGER, /* ... and 500 varchars*/ )

To copy to clipboard, switch view to plain text mode 

so in order to insert data into it you need to provide value for each column in your statement. Valid statements for this table looks like this:

insert into thdata values(x,'str1','str2',...,'str500');

insert into thdata values(x,'str1','str2',...,'str500');

To copy to clipboard, switch view to plain text mode 

where x is an integer value, and str1, ..., str500 are "varchars" (strings).
Note that each string is wrapped around the extra quotes.

So the value of "lstdata" should look like

x,'str1','str2',...,'str500'

x,'str1','str2',...,'str500'

To copy to clipboard, switch view to plain text mode 

in order to give you a valid statement.

I'm gonna repeat myself, please check the QSqlQuerry::lastError(), this could really give you a clue about what you are doing wrong.

[nikhilqt]

Hey!

Generally we follow this process,

1) prepare a query
2) Exec a query
3) commit it to the database

Are you doing everything ?

[rex]

hello, yes this is what i am doing right now to insert where lstdata is QString with all the data i need to insert into the columns. but it is not working , i mean the data is not updated. I am calling this function every 2 seconds.

QString sq = QString("insert into thdata values(%1);").arg(lstdata);
m_query.exec(sq);

QString sq = QString("insert into thdata values(%1);").arg(lstdata);
m_query.exec(sq);

To copy to clipboard, switch view to plain text mode 

---

Added after 10 minutes:

Hello stampede sir , Yes i am getting all the data as i expected in textBrowser.. but its not updated into the data base. . what do i have to do to insert all the data stored in a String
id on't think this approch will work for me sir cause, i am receiving data from rs 232 terminal after aligning the data the way i wanted i am finally appending tat list into the QString lstdata; each string in the list is of 4 char's. .. I just need to insert data from the QString from first position to 500th and again start from 1st column and go on till 500th column with the time stamp.

insert into thdata values(x,'str1','str2',...,'str500');

since the data is appended into the string continiously , i have to insert every consequtive string into the columns from 1 to 500 and come back to 1st column and insert to 500 and so on with the time stamp. ..?! pls help me out with this problem..
I did read about lastError() but i am using Qt integrated with Visual Studio IDE so i am not able to use QDebug here.
thanks a lot.

[nikhilqt]

hello, yes this is what i am doing right now to insert where lstdata is QString with all the data i need to insert into the columns. but it is not working , i mean the data is not updated. I am calling this function every 2 seconds.

QString sq = QString("insert into thdata values(%1);").arg(lstdata);

QString sq = QString("insert into thdata values(%1);").arg(lstdata);

To copy to clipboard, switch view to plain text mode 

.

I do not see commit statement after your exec. And also prepare statement is also missing, which ideally tells whether your query is valid or not?

I did read about lastError() but i am using Qt integrated with Visual Studio IDE so i am not able to use QDebug here.

Include the header QDebug, you can able to use. It does not matter which IDE you are using. To put it simple. Get lastError() inside the string and check its value. I assume that you are opening the database before calling all these statements.

[rex]

Hello this is what i am doing to insert the data in QString temperaturedata; into the sqlite db which has 500 columns..

if(temperaturedata.count()!=0)
	{		
		QString sq = QString("insert into thdata values(%1);").arg("'"+ temperaturedata +"'");
		 m_query.exec(sq);
		// qDebug("lastError()");
		temperaturedata.clear();
		if(!m_query.exec(sq))
		{
			qDebug() << m_query.lastError().text(); 
		}
	}

if(temperaturedata.count()!=0)
	{		
		QString sq = QString("insert into thdata values(%1);").arg("'"+ temperaturedata +"'");
		 m_query.exec(sq);
		// qDebug("lastError()");
		temperaturedata.clear();
		if(!m_query.exec(sq))
		{
			qDebug() << m_query.lastError().text(); 
		}
	}

To copy to clipboard, switch view to plain text mode 

i used the lastError() method to find whats wrong with the query.. and this is message i got in debug, but not able to figure out what is wrong.

lastError()
"table thdata has 500 columns but 1 values were supplied Unable to execute statement"

lastError()
"table thdata has 500 columns but 1 values were supplied Unable to execute statement"

To copy to clipboard, switch view to plain text mode 

pls help me out with this problem.

thank you

[stampede]

"table thdata has 500 columns but 1 values were supplied Unable to execute statement"

Your query statement is wrong.

QString("insert into thdata values(%1);").arg("'"+ temperaturedata +"'");

QString("insert into thdata values(%1);").arg("'"+ temperaturedata +"'");

To copy to clipboard, switch view to plain text mode 

You need to remove the extra quotes from around the "temperatureData", this is causing the error, because the value like this

'tempData'

'tempData'

To copy to clipboard, switch view to plain text mode 

is just one string. As I've posted before, string that goes into arg() must be like this

x,'str1','str2',...,'str500'

Can you give us an example of how "temperatureData" looks like ? But not how you want it to look like, just print the value of the string from your program and show us.

[rex]

Hello sir thanks a lot for your time and patience in helping me out with my problem, This is where i am appending strings in a list to a QString which i am converting to hex and appending to temperatureData String. This is the data which is exactly been showed in the textBrowser at that instance the data which i received from RS232 terminal was displayed like this.
every string is displayed in a new line in the textBrowser.

497
497
497
497
497
497
497
497
497
497
497
525
525
525

it is a part of the simulated data which i am receiving.

This is the code where i am appending the data from the list to the string.

for(int p = 0; p < rawdata.count();p++)
	{
			listdata = rawdata.at(p);   //  QList<QString> rawdata;  
	}
	for(int j=0; j< listdata.size();j++)         // QString listdata, str;
	{
	  temperatureData = QString::number(listdata.toInt(0,16));
	}textBrowser_2->append(temperatureData);

for(int p = 0; p < rawdata.count();p++)
	{
			listdata = rawdata.at(p);   //  QList<QString> rawdata;  
	}
	for(int j=0; j< listdata.size();j++)         // QString listdata, str;
	{
	  temperatureData = QString::number(listdata.toInt(0,16));
	}textBrowser_2->append(temperatureData);

To copy to clipboard, switch view to plain text mode 

sir i removed the extra quotes also from the code i compiled and checked it again but it does't fill any data to the columns at all.

QString sq = QString("insert into thdata values(%1);").arg(temperaturedata );
		 m_query.exec(sq);
		 temperaturedata .clear();

QString sq = QString("insert into thdata values(%1);").arg(temperaturedata );
		 m_query.exec(sq);
		 temperaturedata .clear();

To copy to clipboard, switch view to plain text mode 

i have attached the complete .cpp file sir the function where i am trying to insert data into db is aligndata().
thank you

[stampede]

Ok, but what I meant was something like:

if(temperaturedata.count()!=0)
	{
		qDebug() << temperaturedata;	//!< print the value and see if its ok
		QString sq = QString("insert into thdata values(%1);").arg(temperaturedata);
		 m_query.exec(sq);
		 temperaturedata.clear();
	}

if(temperaturedata.count()!=0)
	{
		qDebug() << temperaturedata;	//!< print the value and see if its ok
		QString sq = QString("insert into thdata values(%1);").arg(temperaturedata);
		 m_query.exec(sq);
		 temperaturedata.clear();
	}

To copy to clipboard, switch view to plain text mode 

I see that you've displayed strings before processing them into query statement.
Print the data just before inserting it into database.

[rex]

hello sir i checked using the debug the data is present it shows in the debug window.. The right data is present in the QString temperaturedata; but its not getting inserted into the data base.

"10"
"10"
"10"
"10"
"10"
"10"
The thread 'Win32 Thread' (0xef0) has exited with code 0 (0x0).
The thread 'Win32 Thread' (0x848) has exited with code 0 (0x0).

and i have two textBrowser in the program to check the incoming data and the processed data which is supposed to go into the db..
No data at all is updated not even a single row..