在 SQLite 中插入 PDF

Question

我正在开发 iPhone 应用程序的离线实现。 我无法将 pdf 文件插入 sqlite 数据库。 谁能帮我将 pdf/xls 插入 sqlite 数据库中。 提前致谢！

Answer 1

我认为更好的方法是将 PDF 存储在文件系统中，并将路径存储在数据库中。

Answer 2

为此，您需要使用正在使用的 sqlite API 的占位符机制：

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

在执行该查询之前，您需要将 :_pdf 绑定到 PDF 二进制数据（伪代码如下）：

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

这可能会给出更多见解：

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

Answer 3

一般来说，您希望将 blob（也称为二进制大对象）插入数据库。 SQLite 提供了一种方法来做到这一点，但它有点棘手。我的 Squidoo 页面 上有一个很好的示例。此处复制了插​​入和提取 blob（用 C 语言）的代码示例：

/* 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;

}