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