SQLite Forum


8 forum posts by user scottdoctor

08:57 Post: Easy way to scroll through table (artifact: cb920590ff user: scottdoctor)

Given a table with a bunch of rows in it. I am writing a windows program for displaying a row's data on a GUI form. I am trying to figure out a simple way to know the row-id of the next/previous row in the table when sorted by a Julian date column. The idea is to have a simple NEXT and PREVIOUS button on the screen to scroll through the rows showing the current row's data in the form window. Although the primary key is unique, rows were deleted and inserted after editing so the row-id when sorted on the Julian date column are not necessarily sequential, and gaps in the sequence exist due to deleting some rows.

Is there a SQLite function or SQL command to determine the next/previous rowid when sorted on a column?

I am thinking doing a SELECT with a SORT and populate a temporary table with the row-id in the order they come out. However the database has several thousand rows and this seems an inefficient way of doing it.

23:17 Reply: Confused about blobs (artifact: d1f39753ef user: scottdoctor)

Actually I advocated for months to use fossil. I even set up an example repository. But the powers-that-be decided no, mostly because they are not computer people. But they did like my example demonstration of using a database file instead. Such as why I am going this route. Realistically a modifiable and searchable database is actually what we need while generating the data.

The main issue in this discussion is that I have sets of data that get plotted to graphs. The graphs must go along with the data as part of the documentation package that follows along with the machine. A typical completed set of data and documents comprises about 100 megabytes of information.

23:07 Reply: Confused about blobs (artifact: 6961c0e99d user: scottdoctor)

This will probably get mangled by email systems line wrapping everything, but it looks like on the forum it keeps the format.

Below is the C code I wrote for basic access of my database. If you have any suggestions, or looks like I missed something, feel free to comment. There is a header file not included here that has an enumeration of error codes, but they are easily replaced and obvious.

/* ********************** */
/* SQLite Access routines */
/* ********************** */

#include <stdio.h>
#include <ctype.h>
#include <errno.h>
#include <math.h>
#include <stdlib.h>
#include <string.h>
#include <mem.h>
#include <io.h>
#include <fcntl.h>
#include <sys/types.h>
#include <sys/stat.h>

#include "sqlite3.h"

#define SQLMAXSIZE BUFSIZE /* max number of bytes in a sql statement */

typedef struct
  char fn[MAXFILENAMESIZE + 1]; /* name of the database */
  char isopen;                  /* flag whether the database is open */
  char exists;                  /* flag set if database exists on disk */
  char sql[SQLMAXSIZE];         /* the sql string */
  int sqllen;                   /* length of the sql string */
  sqlite3 *db;                  /* the opaque sqlite database structure */
  sqlite3_stmt *stmt;           /* the prepared statement */
  int rows;                     /* number of rows returned by statement */
  } TXTDB;

TXTDB txtdb; /* the database */

char txtdbtemp[SQLMAXSIZE + 2]; /* temp string */

/* ********************************************************** */
/* open txtdb and init variables. called at start of program */
/* returns 0 if everything OK */
/* returns 1 if file did not exist and was created */
/* this means the database tables need to be created */
/* ********************************************************** */

int txtdb_open(void)
  int i;
  int f;
  char cflag; /* create flag if set then file does not exist */

  if (txtdb.isopen)
    write_errfile("Attempt to open txtdb which is already open");

  if (txtdb.db)
    write_errfile("txtdb.db not null in txtdb_open");

  /* first try opening the file to see if it exists */

  cflag = 0;
  f = open(txtdb.fn, O_RDWR | O_BINARY, S_IREAD | S_IWRITE);
  if (f == -1)
    /* if an error occurs then cannot proceed, so write error message */
    switch (errno)
    case EACCES:
      write_errfile("txtdb Open Error: Permission Denied");
    case EINVACC:
      write_errfile("txtdb Open Error: Invalid Access Code");
    case EMFILE:
      write_errfile("txtdb Open Error: Too Many Files Open");
    case ENOENT:
      write_errfile("txtdb file does not exist. Creating txtdb");
      cflag = 1; /* set flag indicating that file is being created */
      write_errfile("txtdb Open Error: Unknown Error");
  close(f); /* close the handle */

  i = sqlite3_open(txtdb.fn, &txtdb.db); /* open the database */
  if (i != SQLITE_OK)
    sprintf(txtdbtemp, "txtdb Open Error: error during database open %d", i);
  if (!txtdb.db)
    write_errfile("txtdb Open Error: txtdb is null after open");

  txtdb.isopen = 1;     /* set open flag */
  txtdb.exists = cflag; /* save the create flag */
  return cflag;

/* ************************************ */
/* Close the previously opened database */
/* ************************************ */

void txtdb_close(void)
  int i;

  if (!txtdb.isopen)
    if (txtdb.db)
      write_errfile("txtdb Close Error: txtdb.db not null and not flagged as open");
    write_errfile("txtdb Close Error: txtdb.db is null and not flagged as open");

    i = sqlite3_close(txtdb.db);
    if ((i != SQLITE_BUSY) && (i != SQLITE_OK))
      sprintf(txtdbtemp, "txtdb Close Error: error during database Close %d", i);
  while (i == SQLITE_BUSY);

  txtdb.isopen = 0;
  txtdb.db = 0;
  txtdb.exists = 0;

/* ***************************************************** */
/* does the sqlite3_prepare_v2() with error checking */
/* returns SQLITE_OK if ready for a step. */
/* writes to errfile, but does not exit if sql too long */
/* if sql is too long then returns SQLITE_ERROR */
/* any other error, the error code is returned */
/* **************************************************** */

int txtdb_prepare(char *sql)
  int i;

  txtdb.sqllen = strlen(sql); /* get and check length of sql */
  if (txtdb.sqllen > SQLMAXSIZE - 1)
    write_errfile("SQL Too Long:");
    return SQLITE_ERROR;
  strcpy(txtdb.sql, sql); /* save the sql into the structure */
  if (!txtdb.isopen)
    write_errfile("txtdb prepare without database open");
  if (txtdb.db == 0)
    write_errfile("txtdb prepare with null database handle");

  i = sqlite3_prepare_v2(txtdb.db, sql, (txtdb.sqllen + 1), &txtdb.stmt, 0);

  return i;

22:23 Reply: Confused about blobs (artifact: ca97eeb401 user: scottdoctor)
"Keep the images in filesystem, store their path in db. Why bother to stuff it into blob, then unpack just to reference it as a file again?"

That defeats the purpose, and that is what we currently are doing. 

The idea is that ALL information for a specific machine is in a single file that can travel with the machine. The devices are required to have an extensive testing and documentation log in accordance with ISO 9002 and regulations from the US government and other regulators in various countries. 

It has become untenable gathering all the data for one machine, let alone trying to find anything, like looking for trends over many machines. By everything being in one database file per machine it becomes easy to search and extract various data, along with being able to store a history of the machine.
20:30 Reply: Confused about blobs (artifact: 67ba33f18d user: scottdoctor)

I think I get it now.

What I have been doing is using sprintf() to assemble a sql statement. So bind is sort of like doing a sprintf. A base string with variables that get replaced by some value using bind. So several calls to sqlite3 routines to build the statement sql.

Is there an advantage to using several bind calls versus using a single sprintf call? or is just another way of doing the same thing?

09:50 Reply: Confused about blobs (artifact: b66d53e3c3 user: scottdoctor)

What I do not understand is why bind exists at all.

Why is it not something as simple as:

INSERT INTO blobfield (pointer, bytes)

I do not understand the concept of bind. Never used it and do not get why this different mechanism from INSERT exists.

08:09 Post: Confused about blobs (artifact: 319b5244e6 user: scottdoctor)

I am confused about how to deal with blobs. Reading through the documentation I find references to operations like sqlite3_open_blob, sqlite3_write_blob,... and examples that use bind like sqlite3_bind_blob.

My question is which technique should I use, bind or open/write/read? the documentation about blobs seems to favor bind, but open write, read seems more intuitive.

What I am doing is writing a C program for work to replace our manual paper test reports with a sqlite database that has the same, and all related data, in one sqlite file. Some of the data is auto generated by a C program that analyzes the data. Most of the fields are normal integer, reals, text, of which I have no issues.

One part of the processing program generates some graphs of the test data. The graphs are converted into a PNG image which I want to put into a blob that is part of the record for that data set. Currently we have data and graphs spread out over dozens of folders. One machine can generate more than a hundred images and has become a nightmarish task to find stuff. Such is why I am trying to convert our manual system to a sqlite database file that contains all information for a specific machine.

I just cannot seem to get working with blobs to work. Using a browser such as DB Browser for sqlite, I can copy/paste an image into a blob field. But I want to do this programmatically (in my C program that created the data) without the need for one of the technicians to open a program and copy/paste.

So assume a C function being passed a pointer to an array of bytes that is the image I want to put into the current record blob field. The database is already open and is an external variable. Assume the db has a field called imageblob that I want to put the bytes of the image into.

sqlite3 *db;

int myfunction_writeblob(char *image, long bytes_in_image) { ... }

What is the recommended technique for putting the bytes into the blob (and the converse reading them out)?

One problem with the documentation is that there is so much information it is hard to tell what is important and what is not. Would be nice to have a brief how-to C example for doing basic blob handling in the documentation., or if it already exists, make it easier to find.

21:27 Post: How to use BLOB in C program (artifact: d127181b91 user: scottdoctor)

A project I am working on involves automatically logging data from a test setup by reading some instrumentation during very long tests. That part works fine. I am adding sqlite to the data collection program (written in C) to use sqlite as "the file format" for the massive amount of data collected. Currently the system simply writes a massive csv file that is then imported into excel. ugh. Literally hours spent after each test manually processing data. So I wrote a C program that talks to the equipment, snags the data, then organizes it into a sqlite database. Much easier to find and grab data of interest after the test saving hours of work.

Handling numbers and text is no problem, works fine. During the post processing, my program creates and displays some graphs of the data. The current process involves literal cut and paste of the graphs. Screen shot, print out, cut out graph, tape into log book. So I am trying to make this program into a digital log book where all the data and graphs are in a single sqlite file.

I can programmatically convert the graphs in the post processing into a PNG or JPEG image file. I want to save that image as a BLOB in the table with the test data.

I never used BLOB before and confused how to insert and extract data using a BLOB. The syntax is confusing from the few examples I found on the internet which seem to use BIND to do the task.

Assume I have the image in memory as a char array of bytes.

I need an example in C of an INSERT statement how I put a binary block of memory into a BLOB, and conversely extracting the bytes from the BLOB into a memory array.