Inserting PDFs in SQLite

Question

I am developing offline implementation of an iphone app. I cannot insert pdf files into sqlite DB. can anyone help me to insert pdf/xls into sqlite DB. Thanks in advance!

Answer 1

I think a better approach would be to store the PDFs in the file system and just store the path in the database.

Answer 2

To do that you need to use the placeholder mechanism of the sqlite API you are using:

INSERT INTO my_table (my_pdf) VALUES (:_pdf);

and before doing that query, you need to bind :_pdf to the PDF binary data (pseudocode follows):

myPdfData = //code that loads binary contents of the PDF into a variable
myQuery.bindValue(":_pdf", myPdfData);
myQuery.exec();

This might give some more insight:

• http://www.sqlite.org/c3ref/bind_blob.html

Answer 3

In general terms, you want to insert a blob (a.k.a. binary large object) into a database. SQLite provides a way to do th is, but it is a bit tricky. There is a good example of it on my Squidoo page. The code sample which inserts and extracts blobs (in C) is reproduced here:

/* Copyright 2008 Jay Godse

Licensed under the Apache License, Version 2.0 (the "License")
http://www.apache.org/licenses/LICENSE-2.0
*/

/* File name - blobExampleMain.c */
/* Purpose - The main file to show how SQLite can be used */
/* to store blobs */
/* Assumes blobTest.db already exists with a table: */
/* CREATE TABLE images (name string, image blob); */
/* Build this file (linux/gcc with the following command - */
/* gcc blobExampleMain.c -lsqlite3 -o blobExample */

#include "stdio.h"
#include "stdlib.h"
#include "string.h"
#include "sqlite3.h"

sqlite3 *db; /* global SQLite database handle */

int main (int argc, char **argv) {
int nrows, ncols, rc, i,j;
char *zErr;
char **startupConfig;

/* query strings*/
char* sqlinsert="insert into images (name, image) values (?,?);";
char* sqlselect="select image from images where name=? limit 1;";
int f1Size;
FILE * f1;
FILE * f2;
char fileToSave[200]; /* input file to save*/
char copiedFile[208]; /* file that is output*/

if (argc==2) {
    strncpy(fileToSave, (char*)argv[1], 199);
    printf("saving %s to database\n", fileToSave);
}
else {
    printf("Usage: blobExample argc=%d\n", argc);
return 0;
}

printf("Open the file %s to copy into the database\n", fileToSave);

f1 = fopen(fileToSave, "rb");

if (f1==NULL) {
    printf("%s the file does not exist\n", fileToSave);
    return 0;
}

/* get the size f1Size of the input file*/
fseek(f1, 0, SEEK_END);
f1Size=ftell(f1);
fseek(f1, 0, SEEK_SET);

char *copyBuf = (char*)malloc(f1Size+1);

printf("The size of %s is %d\n", fileToSave, f1Size);

if (f1Size != fread(copyBuf, sizeof(char), f1Size, f1)) {
    free (copyBuf);
    return -2;
}

fclose (f1);

printf("Opening the database to copy the file into it\n");
rc = sqlite3_open("./blobExample.db", &db);
sqlite3_stmt *insertstmt;
sqlite3_stmt *selectstmt;

printf ("Now doing the image insert by binding the file to the blob\n");
rc = sqlite3_prepare(db, sqlinsert, strlen(sqlinsert), &insertstmt, NULL);

sqlite3_bind_text(insertstmt, 1, fileToSave,
strlen(fileToSave), SQLITE_STATIC);
sqlite3_bind_blob(insertstmt, 2, (const void*)copyBuf, f1Size, SQLITE_STATIC);

sqlite3_step(insertstmt);

sqlite3_finalize(insertstmt);
free (copyBuf);

printf("Now doing the select and image extraction\n");
rc=sqlite3_prepare(db, sqlselect, strlen(sqlselect), &selectstmt, NULL);
sqlite3_bind_text(selectstmt, 1, fileToSave, strlen(fileToSave), SQLITE_STATIC);

ncols=sqlite3_column_count(selectstmt);
sqlite3_step(selectstmt);

sprintf (copiedFile, "copyOf__%s", fileToSave);

f2 = fopen(copiedFile, "wb");
fwrite (sqlite3_column_blob(selectstmt, 0), sqlite3_column_bytes(selectstmt, 0), 1, f2);
fclose (f2);

sqlite3_finalize(selectstmt);
return 0;

}