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

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 2

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

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;

}

Answer 4

Thank you all. Got an overall idea now only.Now got another question!!

The app displays the pdf file's content in UIWebView(when connected to internet). how to get the content into DB from UIWebView?

(I am searching to change the content into binary to store it),

and "how to display the contents of PDF(which is stored in SQLite DB as binary ) in offline mode? using webview again.. or ?!!

Any other efficient ways to do W/O sqlite?

How much memory is allowed to store in the phone? for SQLite DB?

Help this newbie!!

@Jay Godse: Thanks a lot !! It is really nice!!