Page 1 of 2 12 LastLast
Results 1 to 20 of 35

Thread: Multi Column selection from a Sqlite table

  1. #1
    Join Date
    Nov 2010
    Posts
    100
    Thanks
    38
    Thanked 1 Time in 1 Post
    Qt products
    Qt3 Qt4

    Default Multi Column selection from a Sqlite table

    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.

    Qt Code:
    1. QSqlQuery sq1("select Thermo2 from thdata where rowid = (" + QString::number(rowcnt) + ")");
    2. if(sq1.exec())
    3. {
    4. if (sq1.first())
    5. {
    6. do
    7. {
    8. for (int i = 0; i < tepdata.size(); ++i)
    9. {
    10. tepdata.push_back(sq1.value(i).toString());
    11. rowcnt++;
    12.  
    13. }
    14. } while(sq1.next());
    15. }
    16. //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.
    Last edited by nagabathula; 24th December 2010 at 07:57.

  2. #2
    Join Date
    Jan 2006
    Location
    Germany
    Posts
    4,380
    Thanks
    19
    Thanked 1,004 Times in 913 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows Symbian S60
    Wiki edits
    5

    Default Re: Multi Column selection from a Sqlite table

    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:
    sql Code:
    1. 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.

  3. #3
    Join Date
    Nov 2010
    Posts
    100
    Thanks
    38
    Thanked 1 Time in 1 Post
    Qt products
    Qt3 Qt4

    Default Re: Multi Column selection from a Sqlite table

    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
    Last edited by nagabathula; 24th December 2010 at 08:41.

  4. #4
    Join Date
    Jan 2006
    Location
    Germany
    Posts
    4,380
    Thanks
    19
    Thanked 1,004 Times in 913 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows Symbian S60
    Wiki edits
    5

    Default Re: Multi Column selection from a Sqlite table

    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().

  5. #5
    Join Date
    Nov 2010
    Posts
    100
    Thanks
    38
    Thanked 1 Time in 1 Post
    Qt products
    Qt3 Qt4

    Default Re: Multi Column selection from a Sqlite table

    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.
    Qt Code:
    1. QString qc("insert into tempchnames values(%1)");
    2. 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. ?

    Qt Code:
    1. for(int i=0;i <channelnames.size(); i++)
    2. 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

  6. #6
    Join Date
    Aug 2009
    Location
    coimbatore,India
    Posts
    314
    Thanks
    37
    Thanked 47 Times in 43 Posts
    Qt products
    Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows

    Default Re: Multi Column selection from a Sqlite table

    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
    Last edited by BalaQT; 24th December 2010 at 12:06.

  7. #7
    Join Date
    Nov 2010
    Posts
    100
    Thanks
    38
    Thanked 1 Time in 1 Post
    Qt products
    Qt3 Qt4

    Default Re: Multi Column selection from a Sqlite table

    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.

  8. #8
    Join Date
    Aug 2009
    Location
    coimbatore,India
    Posts
    314
    Thanks
    37
    Thanked 47 Times in 43 Posts
    Qt products
    Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows

    Default Re: Multi Column selection from a Sqlite table

    Quote Originally Posted by nagabathula View Post
    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

    Qt Code:
    1. QString colNames;
    2. for(int i=0;i <channelnames.size(); i++){
    3. colNames=colNames + channelnames.at(i) + ",";
    4. }
    5. colNames=colNames.left(colNames.length()-1); //removing last comma
    6.  
    7. 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

  9. The following user says thank you to BalaQT for this useful post:

    nagabathula (24th December 2010)

  10. #9
    Join Date
    Nov 2010
    Posts
    100
    Thanks
    38
    Thanked 1 Time in 1 Post
    Qt products
    Qt3 Qt4

    Default Re: Multi Column selection from a Sqlite table

    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

  11. #10
    Join Date
    Aug 2009
    Location
    coimbatore,India
    Posts
    314
    Thanks
    37
    Thanked 47 Times in 43 Posts
    Qt products
    Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows

    Default Re: Multi Column selection from a Sqlite table

    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

  12. #11
    Join Date
    Nov 2010
    Posts
    100
    Thanks
    38
    Thanked 1 Time in 1 Post
    Qt products
    Qt3 Qt4

    Default Re: Multi Column selection from a Sqlite table

    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..

  13. #12
    Join Date
    Aug 2009
    Location
    coimbatore,India
    Posts
    314
    Thanks
    37
    Thanked 47 Times in 43 Posts
    Qt products
    Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows

    Default Re: Multi Column selection from a Sqlite table

    Quote Originally Posted by nagabathula View Post
    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
    Qt Code:
    1. QStringList channalNames;
    2. channalNames << "col1" << "col2" << "col3" << "col4"; //ur channels
    3. QString colName= channalNames.join(","); // now colName will have col1,col2,col3,col4
    4. 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

  14. The following user says thank you to BalaQT for this useful post:

    nagabathula (24th December 2010)

  15. #13
    Join Date
    Nov 2010
    Posts
    100
    Thanks
    38
    Thanked 1 Time in 1 Post
    Qt products
    Qt3 Qt4

    Default Re: Multi Column selection from a Sqlite table

    sir i have an error it tells

    "No query Unable to fetch row"
    this is the code i put in my program sir..
    Qt Code:
    1. QString colNames;
    2. for(int i=0;i <indexCount.size(); i++){
    3. colNames=colNames + indexCount.at(i) + ",";
    4. }
    5. colNames=colNames.left(colNames.length()-1); //removing last comma
    6.  
    7. QSqlQuery sq1("SELECT ""+ colNames +"" FROM myDatabase rowid = (" + QString::number(rowcnt) + ")");
    8. if(sq1.exec())
    9. {
    10. if (sq1.first())
    11. {
    12. do
    13. {
    14. for (int i = 0; i < tepdata.size(); ++i)
    15. {
    16. tepdata.push_back(sq1.value(i).toString());
    17.  
    18. }
    19. } while(sq1.next());
    20. }
    21.  
    22. }
    23. if(tepdata.count()!= 0)
    24. {
    25. rowcnt++;
    26. }
    27. qDebug() << sq1.lastError().text();
    28. 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

  16. #14
    Join Date
    Aug 2009
    Location
    coimbatore,India
    Posts
    314
    Thanks
    37
    Thanked 47 Times in 43 Posts
    Qt products
    Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows

    Default Re: Multi Column selection from a Sqlite table

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

    Qt Code:
    1. QString colNames;
    2. for(int i=0;i <indexCount.size(); i++){
    3. colNames=colNames + indexCount.at(i) + ",";
    4. }
    5. colNames=colNames.left(colNames.length()-1); //removing last comma
    6.  
    7. QSqlQuery sq1("SELECT ""+ colNames +"" FROM myDatabase where rowid = " + QString::number(rowcnt));
    8. if(!sq1.exec())
    9. {
    10. //error
    11. }
    12. while(sq1.next())
    13. {
    14. for (int i = 0; i < tepdata.size(); ++i)
    15. {
    16. tepdata.push_back(sq1.value(i).toString());
    17. }
    18.  
    19. }
    20. if(tepdata.count()!= 0)
    21. {
    22. rowcnt++;
    23. }
    24. qDebug() << sq1.lastError().text();
    25. 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

  17. #15
    Join Date
    Jan 2006
    Location
    Germany
    Posts
    4,380
    Thanks
    19
    Thanked 1,004 Times in 913 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows Symbian S60
    Wiki edits
    5

    Default Re: Multi Column selection from a Sqlite table

    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!).

  18. #16
    Join Date
    Nov 2010
    Posts
    100
    Thanks
    38
    Thanked 1 Time in 1 Post
    Qt products
    Qt3 Qt4

    Default Re: Multi Column selection from a Sqlite table

    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.
    Qt Code:
    1. QSqlQuery sq1("SELECT ""+ colNames +"" FROM tempchnames where rowid = ""+ QString::number(rowcnt) + "" ");
    2. if(!sq1.exec())
    3. {
    4. QMessageBox::information(this, "Test","SQL Query Error");
    5. }
    6. while(sq1.next())
    7. {
    8. for (int i = 0; i < tepdata.size(); i++)
    9. {
    10. tepdata.push_back(sq1.value(i).toString());
    11. }
    12.  
    13. }
    14. if(tepdata.count()!= 0)
    15. {
    16. rowcnt++;
    17. }
    18. qDebug() << sq1.lastError().text();
    19. 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
    Last edited by nagabathula; 24th December 2010 at 15:59.

  19. #17
    Join Date
    Jan 2006
    Location
    Germany
    Posts
    4,380
    Thanks
    19
    Thanked 1,004 Times in 913 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows Symbian S60
    Wiki edits
    5

    Default Re: Multi Column selection from a Sqlite table

    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.

  20. The following user says thank you to Lykurg for this useful post:

    nagabathula (24th December 2010)

  21. #18
    Join Date
    Nov 2010
    Posts
    100
    Thanks
    38
    Thanked 1 Time in 1 Post
    Qt products
    Qt3 Qt4

    Default Re: Multi Column selection from a Sqlite table

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

    Regards

  22. #19
    Join Date
    Jan 2006
    Location
    Germany
    Posts
    4,380
    Thanks
    19
    Thanked 1,004 Times in 913 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows Symbian S60
    Wiki edits
    5

    Default Re: Multi Column selection from a Sqlite table

    Quote Originally Posted by nagabathula View Post
    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!

  23. #20
    Join Date
    Nov 2010
    Posts
    100
    Thanks
    38
    Thanked 1 Time in 1 Post
    Qt products
    Qt3 Qt4

    Default Re: Multi Column selection from a Sqlite table

    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

Similar Threads

  1. Replies: 0
    Last Post: 5th October 2010, 07:50
  2. Replies: 0
    Last Post: 25th June 2008, 18:36
  3. Can a QListView support multi-column?
    By wesley in forum Qt Programming
    Replies: 3
    Last Post: 7th March 2008, 08:00
  4. QListWidget multi selection
    By user in forum Qt Programming
    Replies: 1
    Last Post: 6th February 2008, 07:36
  5. Multi-selection Combo Box
    By EricF in forum Qt Programming
    Replies: 7
    Last Post: 7th November 2007, 07:15

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.