(part 20) Han from China answers your C questions

[Nomen Nescio]

SQLITE Blob writing error

wizard said:

database.
I've a problem to read from blob struct called 'Points'
Any help would be appreciated

Yeah, no problem. We love SQLITE around here.

Reagards
Greg

#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include <assert.h>
#include "sqlite3.h"

You may need to include the header for unlink(). On a POSIX system,
this would be unistd.h.

typedef unsigned char uchar;

char dbname[1000];
static sqlite3 *db1;

void
exec_simple (char *stm)
{
int rc;
char *errMsg = 0;

rc = sqlite3_exec (db1, stm, NULL, 0, &errMsg);
if (rc != SQLITE_OK)
{
fprintf (stderr, "SQL error: %s\n", errMsg);
}

}

typedef struct points
{
long numPoints;
double *x;

}Points;

int
main (int argc, char **argv)
{
char *errMsg = 0;
int rc;
sqlite3_stmt *stmt = NULL;
Points *pointsP;
long numPoints;
long size;
int iii;
char text[32];
uchar *pabyRec;
int nRecordSize;
int num_punkt;
int nOffset;

strcpy(dbname,".\\test.db");

unlink (dbname);

rc = sqlite3_open (dbname, &db1);

if (rc)
{
fprintf (stderr, "Can't open database: %s\n", sqlite3_errmsg (db1));
exit (1);
}

exec_simple ("PRAGMA page_size=8192");
exec_simple ("PRAGMA synchronous = OFF");
exec_simple ("PRAGMA count_changes = OFF");
exec_simple ("PRAGMA temp_store = MEMORY");

exec_simple ("CREATE TABLE punkty(id INTEGER PRIMARY KEY, value BLOB)");

// write record

numPoints=5;

size = numPoints * 1 * sizeof(double) + sizeof(int);

I think you want sizeof(long).

pointsP = (Points *) malloc(size);

Casting the return from malloc() is optional. Also, you are allocating more
memory than is indicated by the declaration of the Points structure. This
appears to be intentional, but I would advise you to seek a much cleaner
solution.

FYI: Only the address returned by malloc() is suitably aligned for
any data type you need. I predict that at some point (not in the code
you've posted so far) you will assume that malloc_address + sizeof(long)
is suitably aligned for a double type. This results in undefined behavior.

memcpy( pointsP, &numPoints, sizeof(int));

If you really must do this, change it to

memcpy(..., sizeof(long));

Otherwise, simply do

pointsP->numPoints = numPoints;

The address of the first structure member, pointsP->numPoints,
should be equal to pointsP.

nRecordSize = 4;

Size assumption of sizeof(long) = 4.

>
pointsP->x = (double *) calloc(numPoints, sizeof(double));

Cast is again optional.

for( iii = 0; iii < numPoints; iii++ )
{
pointsP->x[iii] = (double)iii*100+0.01;
}

This appears correct.

for( iii = 0; iii < numPoints; iii++ )
{
memcpy( pointsP + nRecordSize, pointsP->x + iii, sizeof(double));

The behavior you probably wanted would be achieved by

memcpy((char *)pointsP + nRecordSize, ...);

since pointsP + nRecordSize = (char *)pointsP + nRecordSize*sizeof(Points)

However, "fixing" the code would, subject to internal structure padding,
trash pointsP->x.

You have conflicting data structures. On the one hand, you have a structure
that, ignoring internal padding, looks like this in memory:

pointsP
------------ <--/
| long |
------------ --------------------------
| double * | ----|double|double|...|double|
------------ --------------------------

Then you've gone ahead and modified the above to this:
pointsP
--------------- <--/
| int / long |
---------------
| double |
---------------
| double |
---------------
| ... |
---------------
| double |
---------------

Yet you still expect that double pointer to be there. What's
there instead is a junk pointer that is causing you to read
doubles from some other place in memory.

nRecordSize += 1 * 8;

Size assumption of sizeof(double) = 8.

}

rc = sqlite3_prepare(db1, "insert into punkty values (1,?);", -1, &stmt,
NULL);

if (rc)
{
fprintf (stderr, "ERORR: %s\n", sqlite3_errmsg (db1));
exit (1);
}

rc =sqlite3_bind_blob(stmt, 1, pointsP, size, NULL);
if (rc)
{
fprintf (stderr, "ERORR: %s\n", sqlite3_errmsg (db1));
exit (1);
}

sqlite3_step(stmt);

rc =sqlite3_finalize(stmt);
if (rc)
{
fprintf (stderr, "ERORR: %s\n", sqlite3_errmsg (db1));
exit (1);
}

if( pointsP->x != NULL )
{
free( pointsP->x );
pointsP->x=NULL;
}

free(pointsP);
pointsP=NULL;

// read record
rc = sqlite3_prepare (db1, "select *from punkty", -1, &stmt, NULL);

if (rc != SQLITE_OK)
{
fprintf (stderr, " %s\n", sqlite3_errmsg (db1));
}

while (sqlite3_step (stmt) == SQLITE_ROW)
{

if (rc != SQLITE_OK)
{
fprintf (stderr, "SQL error: %s\n", errMsg);
}

size = sqlite3_column_bytes(stmt, 1);

pointsP= (Points *)malloc(size);

Cast is optional.

>
memcpy(pointsP,sqlite3_column_blob(stmt, 1),size);

Probably not a good thing.

printf("size=%ld\n",size);

memcpy( &num_punkt, pointsP, 4 );

What you want is

num_punkt = pointsP->numPoints;

Note also that num_punkt is an int, whereas pointsP->numPoints
is a long.

printf("pointsP->numPoints=%ld\n",num_punkt);

nOffset = 4;

Another size assumption.

>
pointsP->x = (double *) calloc(num_punkt, sizeof(double));

Cast is optional.

>
for( iii = 0; iii < num_punkt; iii++ )
{
memcpy(pointsP->x + iii, pointsP + nOffset + iii * 8, 8); //maybe here
is a fault
}

Yes, see the above data structure diagrams. This time you appear to have
trashed in reverse.

>
for(iii=0;iii<num_punkt;iii++)
{
printf("x=%.2f\n",pointsP->x[iii]); // here prints rubbish
}

printf("ID=%ld\n", sqlite3_column_int(stmt,0));

}
sqlite3_finalize (stmt);
}

I'm not sure whether I have read your code correctly. A lot of
people here have killfiled me, so don't take no replies to my
post to be an indication of the correctness of my post. Read
over what I've said, and see whether it makes sense.
Yours,
Han from China