Multi Column selection from a Sqlite table

[nagabathula]

hello .. I have a problem with multiple column selection, This is what i am doing to access a single column.. How could i select only the column names i.e( Channels ) which i have selected i have appended the list of selected column names in a QVector.. How can i use the column names from this vector in the query to access only those channel data.
" QSqlQuery sq1("select Thermo2 from thdata where rowid = (" + QString::number(rowcnt) + ")");" Instead of a single column Thermo2 i need to pass how many ever channels were selected.. I have created a column for each channel in the db,say i have 300 columns for 300 channels.. In case i select only 100 columns in random i sud be able to retrieve only those columns of data.. I have all the selected channel names in a QVector called columnnames but don't know how to pass multiple columns to the Query . I am calling this function with a QTimer every 500 milli sec.

QSqlQuery sq1("select Thermo2 from thdata where rowid = (" + QString::number(rowcnt) + ")");
	if(sq1.exec())
	{
		if (sq1.first())
		{
			do
			{
				for (int i = 0; i < tepdata.size(); ++i)
				{
					tepdata.push_back(sq1.value(i).toString());
					rowcnt++;
 
				}
			} while(sq1.next());
		}
//qDebug()<< tepdata

QSqlQuery sq1("select Thermo2 from thdata where rowid = (" + QString::number(rowcnt) + ")");
	if(sq1.exec())
	{
		if (sq1.first())
		{
			do
			{
				for (int i = 0; i < tepdata.size(); ++i)
				{
					tepdata.push_back(sq1.value(i).toString());
					rowcnt++;
					
				}
			} while(sq1.next());
		}
//qDebug()<< tepdata

To copy to clipboard, switch view to plain text mode 

;
Pls help me ,i will be really great full this is very important for me.. i have been trying to get a solution past 2 days but have no answer, i searched a lot din't find any much help.

thanks a lot.

[Lykurg]

Hi,

I don't think that that is a question for the Qt Programming forum, since it is basic and pure SQL!

Anyway, make yourself familiar with QSqlQuery::prepare() which can help you in future and grap any book/online resource on SQL syntax:

SELECT column1, column2, columnX, columnY, columnZ FROM myDatabase LIMIT 2;

SELECT column1, column2, columnX, columnY, columnZ FROM myDatabase LIMIT 2;

To copy to clipboard, switch view to plain text mode 

And if you want to call that statement every 1/2 second, which sounds crazy for me, you should consider storing the constructed SQL statement.

[nagabathula]

hello thank you for help sir.. Until here i knew, i din know how to access only the column names which i have selected this can vary every time.. so i can't give a fixed column names..

SELECT column1, column2, columnX, columnY, columnZ FROM myDatabase LIMIT 2;

i i have a QVector in which has all the selected column names are there, which i need to pass in place of the column name in the query..
i don't know the syntax to use a QVector to give the column names instead of a fixed name.

SELECT channelnames.at(i) FROM myDatabase LIMIT 2; // QVectorString channelnames;

i don't know if this syntax is right.. what is the right way to do it sir.

thank you

[Lykurg]

Well I hope you now the basic of C++ and know how to concat strings. If not read the docs about QStirng. Pay attention to th += operator or the function append().

[nagabathula]

Yes sir i know how to concat a string..
this is what i did to insert some data from a Vector to a database column.

QString qc("insert into tempchnames values(%1)");		
		qc = qc.arg("'" + technames.at(i) + "'")

QString qc("insert into tempchnames values(%1)");		
		qc = qc.arg("'" + technames.at(i) + "'")

To copy to clipboard, switch view to plain text mode 

should i use it in the same way for multiple column name selection also sir. ?

for(int i=0;i <channelnames.size(); i++)
QSqlQuery sq1("SELECT ""+channelnames.at(i)+"" FROM myDatabase rowid = (" + QString::number(rowcnt) + ")");// QVectorString channelnames;

for(int i=0;i <channelnames.size(); i++)
QSqlQuery sq1("SELECT ""+channelnames.at(i)+"" FROM myDatabase rowid = (" + QString::number(rowcnt) + ")");// QVectorString channelnames;

To copy to clipboard, switch view to plain text mode 

thank you sir

[BalaQT]

hi,

should i use it in the same way for multiple column name selection also sir. ?

yes , u can use.

do u want to access the exact column name from the table????

can u explain ur need clearly?
Bala

[nagabathula]

thanks a lot for the reply sir.. I actually have some 300 channels of data which i receive on rs232.. i am processing them and inserting them into the data base with 300 columns, column one is channel1 and so on till column 300. Now i need to display a few selected channels in QTable View so i am trying to retrieve data for only those channels from the db table. The user selects the required channels from a window so i have the channel names saved into a QVector.The user can select any channel so the column name selection has to be dynamic I have named the column names also same as the channel names so that i can access the columns with respect to the selected channel names..

thanks a lot.

[BalaQT]

thanks a lot for the reply sir.. I actually have some 300 channels of data which i receive on rs232.. i am processing them and inserting them into the data base with 300 columns, column one is channel1 and so on till column 300. Now i need to display a few selected channels in QTable View so i am trying to retrieve data for only those channels from the db table. The user selects the required channels from a window so i have the channel names saved into a QVector.The user can select any channel so the column name selection has to be dynamic I have named the column names also same as the channel names so that i can access the columns with respect to the selected channel names..

thanks a lot.

here is a quick fix, if u want to select 100 columns

QString colNames;
for(int i=0;i <channelnames.size(); i++){
colNames=colNames + channelnames.at(i) + ",";
}
colNames=colNames.left(colNames.length()-1);  //removing last comma
 
QSqlQuery sq1("SELECT ""+ colNames +"" FROM myDatabase rowid = (" + QString::number(rowcnt) + ")");

QString colNames;
for(int i=0;i <channelnames.size(); i++){
colNames=colNames + channelnames.at(i) + ",";
}
colNames=colNames.left(colNames.length()-1);  //removing last comma

QSqlQuery sq1("SELECT ""+ colNames +"" FROM myDatabase rowid = (" + QString::number(rowcnt) + ")");

To copy to clipboard, switch view to plain text mode 

this will take all the columns specified in the vector for rowid = rowcnt;

hope this is helpful

Bala

[nagabathula]

thanks a lot sir for your time and help.. i will try this now..
sir what comma are is been removed in this code..

colNames=colNames.left(colNames.length()-1); //removing last comma

regards

[BalaQT]

for ex:
vector[0] =col1
vector[1] =col2
vector[2] =col3

after the loop, colNames string will be
colNames=col1,col2,col3,;

we are concatenating column names from vector and we are using the colName string in select Query.

if i pass "select col1,col2,col3, from Table" we will get a syntax error.
so we need to remove the last ','.
"select col1,col2,col3 from Table"

hope it helps
Bala

[nagabathula]

sir i understood now.. you are trying to remove the last comma from the string names which will be appended..
i am still compiling the code..

[BalaQT]

sir i understood now.. you are trying to remove the last comma from the string names which will be appended..
i am still compiling the code..

hi,
I strongly recommend u to use QStringList instead of QVector for this purpose.
then code will be like the following

QStringList  channalNames;
     channalNames << "col1" << "col2" << "col3" << "col4";  //ur channels
     QString colName= channalNames.join(",");  // now colName will have col1,col2,col3,col4
     QSqlQuery sq1("SELECT ""+ colName +"" FROM myDatabase rowid = (" + QString::number(rowcnt) + ")");

QStringList  channalNames;
     channalNames << "col1" << "col2" << "col3" << "col4";  //ur channels
     QString colName= channalNames.join(",");  // now colName will have col1,col2,col3,col4
     QSqlQuery sq1("SELECT ""+ colName +"" FROM myDatabase rowid = (" + QString::number(rowcnt) + ")");

To copy to clipboard, switch view to plain text mode 

there is no need to remove last comma ,etc.
QStringList is simple and effective

Hope it helps
Bala

[nagabathula]

sir i have an error it tells

"No query Unable to fetch row"

this is the code i put in my program sir..

QString colNames;
		for(int i=0;i <indexCount.size(); i++){
		colNames=colNames + indexCount.at(i) + ",";
		}
		colNames=colNames.left(colNames.length()-1);  //removing last comma
 
QSqlQuery sq1("SELECT ""+ colNames +"" FROM myDatabase rowid = (" + QString::number(rowcnt) + ")");
  if(sq1.exec())
	{
		if (sq1.first())
		{
			do
			{
				for (int i = 0; i < tepdata.size(); ++i)
				{
					tepdata.push_back(sq1.value(i).toString());
 
				}
			} while(sq1.next());
		}
 
	}
		if(tepdata.count()!= 0)
		{
			rowcnt++;
		}
			qDebug() << sq1.lastError().text(); 
		qDebug() << colNames;

QString colNames;
		for(int i=0;i <indexCount.size(); i++){
		colNames=colNames + indexCount.at(i) + ",";
		}
		colNames=colNames.left(colNames.length()-1);  //removing last comma

QSqlQuery sq1("SELECT ""+ colNames +"" FROM myDatabase rowid = (" + QString::number(rowcnt) + ")");
  if(sq1.exec())
	{
		if (sq1.first())
		{
			do
			{
				for (int i = 0; i < tepdata.size(); ++i)
				{
					tepdata.push_back(sq1.value(i).toString());
					
				}
			} while(sq1.next());
		}
		
	}
		if(tepdata.count()!= 0)
		{
			rowcnt++;
		}
			qDebug() << sq1.lastError().text(); 
		qDebug() << colNames;

To copy to clipboard, switch view to plain text mode 

sir i am able to see the selected column names in the debug but the values are not retrieved from the database sir.. i get an query error in debug.

thank you

[BalaQT]

hi , try the following code using while.
u r querying in a complex way i guess.

QString colNames;
		for(int i=0;i <indexCount.size(); i++){
		colNames=colNames + indexCount.at(i) + ",";
		}
		colNames=colNames.left(colNames.length()-1);  //removing last comma
 
QSqlQuery sq1("SELECT ""+ colNames +"" FROM myDatabase where rowid = " + QString::number(rowcnt));
if(!sq1.exec())
{
//error
}
  while(sq1.next())
	{
		for (int i = 0; i < tepdata.size(); ++i)
		{
		       tepdata.push_back(sq1.value(i).toString());					
	         }
 
	}
		if(tepdata.count()!= 0)
		{
			rowcnt++;
		}
			qDebug() << sq1.lastError().text(); 
		qDebug() << colNames;

QString colNames;
		for(int i=0;i <indexCount.size(); i++){
		colNames=colNames + indexCount.at(i) + ",";
		}
		colNames=colNames.left(colNames.length()-1);  //removing last comma

QSqlQuery sq1("SELECT ""+ colNames +"" FROM myDatabase where rowid = " + QString::number(rowcnt));
if(!sq1.exec())
{
//error
}
  while(sq1.next())
	{
		for (int i = 0; i < tepdata.size(); ++i)
		{
		       tepdata.push_back(sq1.value(i).toString());					
	         }
		
	}
		if(tepdata.count()!= 0)
		{
			rowcnt++;
		}
			qDebug() << sq1.lastError().text(); 
		qDebug() << colNames;

To copy to clipboard, switch view to plain text mode 

in ur query , u have missed WHERE

QSqlQuery sq1("SELECT ""+ colNames +"" FROM myDatabase rowid = (" + QString::number(rowcnt) + ")");

() is not needed near rowid =

hope it helps

Bala

[Lykurg]

Seriously, how long have you learned C++ and/or is that a homework you have to do? I am curios, because your problems are so basic. Not that we won't help you because you are a beginner.

It is just because I would like to remind, that we do not solve any homework here (and thus we normally also do not provide full examples/solutions!).

[nagabathula]

hello lykurg sir.. i am sorry i know its annoying to get noob questions. I jus joined as a intern finished my engineering. I have no guidance here. Qt Assistant and the Forum are the only Teacher for me. I am getting the understanding of programming. I am reading a lot of Qt Assistant to learn more..

---

Added after 23 minutes:

hi bala sir.. i tried the code i did every possible thing before i could post again..I checked the table name,it is right there is data in the table also which i am trying to retrieve. I am still getting the same error... tempchnames is the Table name. And i have initialized rowcnt =0; at the start of the program. There is data in the table also sir
but as soon as i open the dialog box i get the error from QMessageBox we have put in the if(!sql1.exec()) condition.

QSqlQuery sq1("SELECT ""+ colNames +"" FROM tempchnames where rowid = ""+ QString::number(rowcnt) + "" ");
if(!sq1.exec())
{
   QMessageBox::information(this, "Test","SQL Query Error");
}
  while(sq1.next())
	{
		for (int i = 0; i < tepdata.size(); i++)
		{
		       tepdata.push_back(sq1.value(i).toString());					
	    }
 
	}
		if(tepdata.count()!= 0)
		{
			rowcnt++;
		}
			qDebug() << sq1.lastError().text(); 
                         qDebug() << colNames();

QSqlQuery sq1("SELECT ""+ colNames +"" FROM tempchnames where rowid = ""+ QString::number(rowcnt) + "" ");
if(!sq1.exec())
{
   QMessageBox::information(this, "Test","SQL Query Error");
}
  while(sq1.next())
	{
		for (int i = 0; i < tepdata.size(); i++)
		{
		       tepdata.push_back(sq1.value(i).toString());					
	    }
		
	}
		if(tepdata.count()!= 0)
		{
			rowcnt++;
		}
			qDebug() << sq1.lastError().text(); 
                         qDebug() << colNames();

To copy to clipboard, switch view to plain text mode 

this is the Debug result which i got from the last result sir.. The selected Channel names are present but the Query is not fetching any data from the Table.. Am i still doing something wrong in the Select Query sir. ?

No query Unable to fetch row"
"Thermo12,Thermo13,Thermo14,Thermo15,Thermo16,Ther mo17,Thermo18,Thermo19,Thermo20,Thermo21,Thermo22"

sir and i have put this part of the code in main part of the program.
thank you

[Lykurg]

Please debug the string sql. Have a look at the whole syntax. The error is because you have too much quotation marks and you might add a space after the commas.

[nagabathula]

oki sir i will do the debugging. Thank you so much for your time in guiding me..

Regards

[Lykurg]

Thank you so much for your time in guiding me..

No problem, that's why we are here

(Only a full working example is from a padagogigal point of view nonsense, so I'll just push you in the "right" direction. You must do the work, but in the end you hopfully have understand what you have done, so that you can solve your problems later on by yourown.)

But now it is time for gift giving, so Merry Christmas!

[nagabathula]

Sorry for off topic

Merry Christmas to you too Sir may you have a Happy time ahead.. That post made me feel very Happy.. What you tell is perfectly right. Only if a person does some reading and debugging he will come across many things and learn stuff. only then even i will also be able to help anyone else also some time in the future in this forum..

Thank You