Thread: 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 <[email protected]>
 *
 * 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;
}

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.

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

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.

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.