Thread: Multiple Inserts into SQLite

  1. #1
    Registered User
    Join Date
    Aug 2019
    Posts
    3

    Multiple Inserts into SQLite

    I have been given a program where the output of that program generates random results for a 100 rounds of 100 coin tosses. My aim is to get all of those H & T results (10,000) into SQLite for analysis. This will be done many times.
    Round 1: TTTTTHHTHTTHHTTTHHTTTTTTTHHTTHHHHHHTTTHTHTTHHTTTHH HHHHTHTTTTHTHHTHTTTHTHTHTHTTHHTTTTTTHTHTTHHTTTTHTH
    -
    Round 99: TTHHHTHTHHTTTHHTTHTHTHTTHHHHHTHTTTTHHHHTHTHTHTHHHH TTTTTHTTHHHTTTTHTTHHHHTTTTTTHHTHTTHTTTTHTHHTTHHTHT
    Round 100: THTHTHHHHHTTHTTTTTTTTTTTHTTHHTHHHTHHTHHHHTTHTHHTTH THTHHTTHHHTHTHHTHTTTTTHTHTTHHTHTHHHTHTHHTHTHHTTTHH
    As a beginner, I have little knowledge how to do this, so I looked into what others have done. Apparently Multi-row INSERT is not supported. Some sources say the only way to insert several rows in a batch is use a Select statement. How would I achieve this?
    Assuming the database and table is already created, what code could I use in a C program to insert all this data into SQLite?

    Thank you.


    Code:
    /*
     * PCG Random Number Generation for C.
     *
     * Copyright 2014 Melissa O'Neill <oneill@pcg-random.org>
     *
     * Licensed under the Apache License, Version 2.0 (the "License");
     * you may not use this file except in compliance with the License.
     * You may obtain a copy of the License at
     *
     *     http://www.apache.org/licenses/LICENSE-2.0
     *
     * Unless required by applicable law or agreed to in writing, software
     * distributed under the License is distributed on an "AS IS" BASIS,
     * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
     * See the License for the specific language governing permissions and
     * limitations under the License.
     *
     * For additional information about the PCG random number generation scheme,
     * including its license and other licensing options, visit
     *
     *     http://www.pcg-random.org
     */
    
    /*
     * This file was mechanically generated from tests/check-pcg32.c
     */
    
    #include <stdio.h>
    #include <stddef.h>
    #include <stdlib.h>
    #include <stdint.h>
    #include <stdbool.h>
    #include <time.h>
    #include <string.h>
    
    #include "pcg_basic.h"
    
    int main(int argc, char **argv)
    {
      // Read command-line options
    
      int rounds = 100;
      bool nondeterministic_seed = false;
      int round, i;
    
      ++argv;
      --argc;
      if (argc > 0 && strcmp(argv[0], "-r") == 0) {
        nondeterministic_seed = true;
        ++argv;
        --argc;
      }
      if (argc > 0) {
        rounds = atoi(argv[0]);
      }
      // In this version of the code, we'll use a local rng, rather than the
      // global one.
    
      pcg32_random_t rng;
    
      // You should *always* seed the RNG.  The usual time to do it is the
      // point in time when you create RNG (typically at the beginning of the
      // program).
      //
      // pcg32_srandom_r takes two 64-bit constants (the initial state, and the
      // rng sequence selector; rngs with different sequence selectors will
      // *never* have random sequences that coincide, at all) - the code below
      // shows three possible ways to do so.
    
      if (nondeterministic_seed) {
        // Seed with external entropy -- the time and some program addresses
        // (which will actually be somewhat random on most modern systems).
        // A better solution, entropy_getbytes, using /dev/random, is provided
        // in the full library.
    
        pcg32_srandom_r(&rng, time(NULL) ^ (intptr_t) & printf,
                        (intptr_t) & rounds);
      } else {
        // Seed with a fixed constant
    
        pcg32_srandom_r(&rng, 42u, 54u);
      }
    
      printf("pcg32_random_r:\n"
             "      -  result:      32-bit unsigned int (uint32_t)\n"
             "      -  period:      2^64   (* 2^63 streams)\n"
             "      -  state type:  pcg32_random_t (%zu bytes)\n"
             "      -  output func: XSH-RR\n" "\n", sizeof(pcg32_random_t));
    
      for (round = 1; round <= rounds; ++round) {
        printf("Round %d:\n", round);
        /* Make some 32-bit numbers */
        printf("  32bit:");
        for (i = 0; i < 6; ++i)
          printf(" 0x%08x", pcg32_random_r(&rng));
        printf("\n");
    
        /* Toss some coins */
        printf("  Coins: ");
        for (i = 0; i < 100; ++i)
          printf("%c", pcg32_boundedrand_r(&rng, 2) ? 'H' : 'T');
        printf("\n");
    
        printf("\n");
      }
      return0;
    }
    Last edited by Salem; 4 Weeks Ago at 10:10 PM. Reason: Removed crayola

  2. #2
    C++ Witch laserlight's Avatar
    Join Date
    Oct 2003
    Location
    Singapore
    Posts
    27,029
    Quote Originally Posted by MJM
    Some sources say the only way to insert several rows in a batch is use a Select statement.
    You misread "prepared statement" as "select statement". Read the SQLite docs on sqlite3_prepare_v2.
    Quote Originally Posted by Bjarne Stroustrup (2000-10-14)
    I get maybe two dozen requests for help with some sort of programming or design problem every day. Most have more sense than to send me hundreds of lines of code. If they do, I ask them to find the smallest example that exhibits the problem and send me that. Mostly, they then find the error themselves. "Finding the smallest program that demonstrates the error" is a powerful debugging tool.
    Look up a C++ Reference and learn How To Ask Questions The Smart Way

  3. #3
    Registered User
    Join Date
    Aug 2019
    Posts
    3
    I am clueless on how to go about this. Would it just be one line of code using prepared statements? Is that all that's required? Thanks.

  4. #4
    and the hat of int overfl Salem's Avatar
    Join Date
    Aug 2001
    Location
    The edge of the known universe
    Posts
    37,382
    It seems you need to get some sqlite programming exercises in first.
    SQLite C tutorial - SQLite programming in C

    Start with the basics of just being able to add one short row of say "THTHTHHH".
    If you dance barefoot on the broken glass of undefined behaviour, you've got to expect the occasional cut.
    If at first you don't succeed, try writing your phone number on the exam paper.

  5. #5
    Registered User
    Join Date
    Aug 2019
    Posts
    3
    Thanks for that advice.

    I am using Xcode for C on a Mac, but the tutorial uses C99
    . Must this tutorial be done inside Terminal using commands or is Xcode okay? FYI - I was able to compile the Cars example using Xcode to insert the data (but not able to retrieve it). Anyway, the tutorial is definitely of value.

    The Big Picture
    Trying to accomplish this as simply as possible for now, for that reason I am planning these high-level steps:

    1. Create the database and table outside of C program, using a database tool (e.g. DB Browser for SQLite or TablePlus). Easy.
    2. Write the code to insert multiple rows (obviously the most challenging part for me)
    3. Go to db using DB Browser and check if the data is there. Great if it is. Mission accomplished.
    4. Run queries to analyse the data using DB






  6. #6
    C++ Witch laserlight's Avatar
    Join Date
    Oct 2003
    Location
    Singapore
    Posts
    27,029
    Quote Originally Posted by MJM
    Apparently Multi-row INSERT is not supported.
    I checked the insert syntax supported by SQLite and multi-row insert is supported.

    Quote Originally Posted by MJM
    Would it just be one line of code using prepared statements?
    No, you need to:
    • Open the database with sqlite3_open
    • Prepare the statement with sqlite3_prepare_v2
    • In a loop, bind the coin toss result to the statement parameter with say, sqlite3_bind_text
    • Still in the loop, execute the statement with sqlite3_step
    • After the loop, finalise the statement with sqlite3_finalize
    • Close the database with sqlite3_close

    You might also wrap the inserts in a transaction.
    Quote Originally Posted by Bjarne Stroustrup (2000-10-14)
    I get maybe two dozen requests for help with some sort of programming or design problem every day. Most have more sense than to send me hundreds of lines of code. If they do, I ask them to find the smallest example that exhibits the problem and send me that. Mostly, they then find the error themselves. "Finding the smallest program that demonstrates the error" is a powerful debugging tool.
    Look up a C++ Reference and learn How To Ask Questions The Smart Way

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. SQLite in C
    By bluechip in forum C Programming
    Replies: 2
    Last Post: 10-14-2012, 12:32 PM
  2. MinGW and SQLite
    By CarlGB in forum C Programming
    Replies: 5
    Last Post: 02-15-2010, 10:30 PM
  3. SQLite questions
    By ac251404 in forum C++ Programming
    Replies: 6
    Last Post: 08-22-2006, 11:00 AM
  4. Using SQLite?
    By IM! in forum C++ Programming
    Replies: 18
    Last Post: 03-16-2005, 04:00 AM
  5. quicker for inserts
    By the Wookie in forum C++ Programming
    Replies: 2
    Last Post: 07-10-2003, 12:16 PM

Tags for this Thread