Results 1 to 14 of 14

Thread: Best method to insert data into 500 tables in SQLITE

  1. #1
    Join Date
    Dec 2010
    Posts
    41
    Thanks
    12

    Default Best method to insert data into 500 tables in SQLITE

    hi every one,

    i am working on database project,in which i need to create 500 different tables and update some students data into it. previously i had done a data base project but with only 50 tables now it is 500 tables..
    this is how i did previously to create 50 tables in connection.h
    Qt Code:
    1. QString dbstr("create table if not exists Data( Time_Interval INTEGER, Channel1 varchar(250),Channel2 varchar(250),Channel3 varchar(250),Channel4 varchar(250),Channel5 varchar(250),Channel6 varchar(250),Channel7 varchar(250),
    2. Channel8 varchar(250),Channel9 varchar(250),Channel10 varchar(250),Channel11 varchar(250),Channel12 varchar(250),Channel13 varchar(250),Channel14 varchar(250),Channel15 varchar(250),Channel16 varchar(250),Channel17 varchar(250),Channel18 varchar(250),Channel19 varchar(250),Channel20 varchar(250),Channel21 varchar(250),Channel22 varchar(250),Channel23 varchar(250)
    3. )");
    4. query.exec(dbstr);
    To copy to clipboard, switch view to plain text mode 

    and this part of code to update data into the tables.
    Qt Code:
    1. m_query.exec("begin transaction Trans");
    2. for (int i = 0; i < radata2.count(); ++i)
    3. {
    4. QString sQuery = "insert into Data";
    5. sQuery += " values (";for (int j = 0; j < radata2[i]->count(); j++) sQuery += QString("%1").arg("'" + radata2[i]->at(j) + "'") + ", ";sQuery += ")";
    6. q.exec(sQuery);
    7. }
    To copy to clipboard, switch view to plain text mode 
    can some one suggest me what is the best method while dealing with so many tables.

    thanks

  2. #2
    Join Date
    Jan 2006
    Location
    Belgium
    Posts
    1,938
    Thanked 268 Times in 268 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows
    Wiki edits
    20

    Default Re: Best method to insert data into 500 tables in SQLITE

    I hope this isn't a wrong question but why do you need 500 tables?

  3. #3
    Join Date
    Dec 2010
    Posts
    41
    Thanks
    12

    Default Re: Best method to insert data into 500 tables in SQLITE

    i wanted it to store some data for 500 different students .

  4. #4
    Join Date
    Jan 2006
    Location
    Belgium
    Posts
    1,938
    Thanked 268 Times in 268 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows
    Wiki edits
    20

    Default Re: Best method to insert data into 500 tables in SQLITE

    Your problem can be simplified because you will not need 500 tables.

  5. #5
    Join Date
    Dec 2010
    Posts
    41
    Thanks
    12

    Default Re: Best method to insert data into 500 tables in SQLITE

    Thanks a lot for your reply tbscope.. i have to create 500 tables for 500 different channels i am working on a student example first so i told student.. say Channel 1 to Channel 500. I have the data also for it ready i have it done. i am in a fix on how to go about inserting data for 500 Channels into 500 different tables. .

  6. #6
    Join Date
    Dec 2009
    Location
    Romania, Iasi
    Posts
    18
    Thanks
    1
    Thanked 2 Times in 2 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: Best method to insert data into 500 tables in SQLITE

    A fast way would be to put all CREATE and INSERT statements between database.transaction() and database.commit() statements (database is an QSqlDatabase object).

  7. The following user says thank you to vcernobai for this useful post:

    rex (4th December 2010)

  8. #7
    Join Date
    Dec 2010
    Posts
    41
    Thanks
    12

    Default Re: Best method to insert data into 500 tables in SQLITE

    can you pls show me a small example.. that will be of help for me.
    thank you

  9. #8
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,729
    Thanks
    13
    Thanked 1,610 Times in 1,537 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: Best method to insert data into 500 tables in SQLITE

    If your aim is to produce an independent sample data set in an Sqlite database for each of 500 students then by far the easiest way to do this is create a single sample data set in one Sqlite database and duplicate the whole database. Each student has their own data and cannot possibly stuff up any other student's data.

    A more normal design for this situation would create a single table which each records carrying a channel number column. The channel number column would contain an identifier for the channel the row's data belonged to.

    If you persist with creating 500 tables in one database then it is a trivial exercise to loop 500 times creating and executing "CREATE TABLE" statements.

  10. #9
    Join Date
    Dec 2010
    Posts
    41
    Thanks
    12

    Default Re: Best method to insert data into 500 tables in SQLITE

    Hello Sir ,
    This is what i want to achieve. I am trying to create 500 Channels from Channel 1 to Channel 500 and Time Stamp in the first column, insert data into it every second i have all the channel data processed in a QVector<QString> i need to insert it into data dase. I read in the Detailed Description of QString, how do i overcome this problem cause i will have 500 columns but here i can use only upto 99 place markers.
    Qt Code:
    1. If there is no place marker (%1, %2, etc.), a warning message is output and the result is undefined. Note that only placeholders between %1 and %99 are supported.
    To copy to clipboard, switch view to plain text mode 

    Pls give me a small hint or code Snippet for the best way to insert data into db with so many tables.

    thanks a lot

  11. #10
    Join Date
    Dec 2010
    Posts
    41
    Thanks
    12

    Default Re: Best method to insert data into 500 tables in SQLITE

    some one pls help me out with the SQLite data creation and insertion for many number of channels..

    thanks

  12. #11
    Join Date
    Jan 2006
    Location
    Belgium
    Posts
    1,938
    Thanked 268 Times in 268 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows
    Wiki edits
    20

    Default Re: Best method to insert data into 500 tables in SQLITE

    I personally think that you should start with a good book about database design.

  13. #12
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,729
    Thanks
    13
    Thanked 1,610 Times in 1,537 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: Best method to insert data into 500 tables in SQLITE

    You start the thread with:
    i need to create 500 different tables
    and then reinforce that with:
    i am in a fix on how to go about inserting data for 500 Channels into 500 different tables.
    Ultimately it seems that you want 500 columns in a single table:
    This is what i want to achieve. I am trying to create 500 Channels from Channel 1 to Channel 500 and Time Stamp in the first column, insert data into it every second i have all the channel data processed in a QVector<QString> i need to insert it into data dase. I read in the Detailed Description of QString, how do i overcome this problem cause i will have 500 columns but here i can use only upto 99 place markers.
    The obvious solution to the problem as stated is to build the row with a series of queries rather than one. You may also be able to make this work using parameterised queries and bind parameters in QSqlQuery (Sqlite limit seems to be 999 parameters by default).

    The far superior solution is to sit down and design the database properly as tbscope advises. Sqlite will allow more than 500 columns but that certainly does not make doing so a good idea. I think a table with three columns will do it and will even adapt easily to the arrival of a 501st channel.

  14. #13
    Join Date
    Dec 2010
    Posts
    41
    Thanks
    12

    Default Re: Best method to insert data into 500 tables in SQLITE

    Hello sir, thank you for your time.

    i am sorry that was a mistake. i meant 500 columns not tables.
    i am in a fix on how to go about inserting data for 500 Channels into 500 different tables.
    Qt Code:
    1. I think a table with three columns will do it and will even adapt easily to the arrival of a 501st channel
    To copy to clipboard, switch view to plain text mode 
    but i might have different data for each column for Channel , so i though i must have 500 columns to save each channels data. How could i use a table of only three columns to accommodate 500 different channels of data.?
    I did go through sqlite faq to find some answer but din't find any.

    thank you

  15. #14
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,729
    Thanks
    13
    Thanked 1,610 Times in 1,537 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: Best method to insert data into 500 tables in SQLITE

    I am with tbscope:
    Quote Originally Posted by tbscope View Post
    I personally think that you should start with a good book about database design.
    Although I suspect that even a rudimentary overview would suffice.

    Ponder this:
    Qt Code:
    1. CREATE TABLE channelData (
    2. sampletime integer,
    3. chNumber integer,
    4. chData varchar(250)
    5. );
    To copy to clipboard, switch view to plain text mode 
    Every sample:
    Qt Code:
    1. BEGIN TRANSACTION
    2. INSERT INTO channelData ( sampletime, chNumber, chData )
    3. VALUES (strftime('%s', 'now'), 0, "some ch0 data");
    4. INSERT INTO channelData ( sampletime, chNumber, chData )
    5. VALUES (strftime('%s', 'now'), 1, "some ch1 data");
    6. INSERT INTO channelData ( sampletime, chNumber, chData )
    7. VALUES (strftime('%s', 'now'), 2, "some ch2 data");
    8. ...
    9. INSERT INTO channelData ( sampletime, chNumber, chData )
    10. VALUES (strftime('%s', 'now'), 499, "some ch499 data");
    11. COMMIT;
    To copy to clipboard, switch view to plain text mode 
    and later this:
    Qt Code:
    1. SELECT * from channelData where chNumber = 2 order by sampletime;
    To copy to clipboard, switch view to plain text mode 

Similar Threads

  1. Insert unicode in SQlite
    By Kastagne in forum Qt Programming
    Replies: 3
    Last Post: 11th October 2011, 14:07
  2. How to insert row to SQLite database?
    By MIH1406 in forum Qt Programming
    Replies: 6
    Last Post: 29th May 2010, 12:22
  3. Replies: 0
    Last Post: 21st April 2010, 16:25
  4. Way to load all of data from 2 tables into one QTableView?
    By Kevin Hoang in forum Qt Programming
    Replies: 8
    Last Post: 3rd April 2010, 09:42
  5. Bulk insert into SQLite
    By munna in forum Qt Programming
    Replies: 6
    Last Post: 19th November 2007, 03:56

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.