FileIO extension
(1) By anonymous on 2021-06-05 02:04:13 [link] [source]
I have been using SQLite3 (3.12.1) on my Mac for a while using only basic database functions. I now want to use the writefile command to write blob data directly to a file. From my research, I understand that I need to use the FileIO.c extension. The DB Browser only accepts .dylib, .so and .dll files. I have tried using terminal to no avail. Can someone please advise:
How to load the extension through DB Browser or provide me with the exact code for loading through terminal;
Whether the extension will still be recognised if I copy the database to my PC or to another Mac, or whether I will need to set the extension up each time.
This is the first time that I have ever asked a question in a forum, so I apologise if I have the wrong forum or if the question is too basic. I am not technically trained, so I would appreciate it if any response could be written in as simple a way as possible.
Thanks K
(2) By anonymous on 2021-06-05 04:32:48 in reply to 1 [link] [source]
To begin with, you need a C compiler. Check whether one is installed by opening a terminal window and running this command:
cc -v
(3) By anonymous on 2021-06-05 10:30:38 in reply to 2 [link] [source]
Thanks for your help.
I used the command in the terminal window and the response is that I have Apple Clang versions 12.0.0 (clang-1200.0.32.2) Target: x86_64-apple-darwin19.6.0 Thread model: posix followed by an installed directory
(4.1) By Warren Young (wyoung) on 2021-06-05 11:04:29 edited from 4.0 in reply to 1 [source]
I have been using SQLite3 (3.12.1) on my Mac
I doubt that. I'd bet you've actually been using DB Browser for SQLite 3.12.1 (DB4S). Note the five-year span in release history and the difference in providers.
Beware that your question teeters on the edge of being off-topic here: we don't provide DB4S. It's only the fact that there's a way to answer your question using only SQLite that leads me to not brush you off and tell you to go ask the DB4S people, that being the software you're actually using and having trouble with, not SQLite proper.
I have tried using terminal to no avail.
One wonders why you didn't find these generic instructions, or if you did, why you didn't show those commands in your question and show what they gave you, so we'd have a leg up on helping you.
To compile this particular extension from within the SQLite source code tree, the generic instructions translate to this specific command on macOS:
$ gcc -g -fPIC -dynamiclib -I. ext/misc/fileio.c -o ~/Desktop/fileio.dylib
The generic instructions don't tell you about the -I., which you need in this case to ensure you get the right version of sqlite3.h, since the system version is found first otherwise, and it doesn't have all of the necessary definitions to compile against current SQLite.
How to load the extension through DB Browser
Select the Tools → Load Extension... menu item once you've got your DB open.
The limitation of needing to have a DB open before you can load an extension is from DB4S, not something SQLite itself imposes, so I've filed a bug for that weakness for you.
provide me with the exact code for loading through terminal;
That's covered in the SQLite documentation link above.
Whether the extension will still be recognised if I copy the database to my PC or to another Mac
A compiled loadable extension is platform-specific by its nature, so yes, you'll need to have a built copy of this extension for each platform you use, if the DB schema or the commands you give to SQLite uses features from the extension.
If you want a given extension to be built statically into SQLite, you'll have to build your own SQLite binaries, which are also be platform-specific.
The DB4S project provides instructions for doing the equivalent work in their docs, though for some reason their macOS guide is currently unwritten. Pursuing all of that is off-topic here.
whether I will need to set the extension up each time.
For SQLite proper, you can list commands that occur on launch of the sqlite3 shell by putting them in ~/.sqliterc. (Not sure what the Windows equivalent is.) I have no idea if DB4S runs the .sqliterc file or if they have a similar but differently-designed alternative. Solving that isn't on-topic here.
I am not technically trained
You wouldn't be the first person to learn some C simply in order to get some actual work done.
(5) By anonymous on 2021-06-05 23:59:41 in reply to 4.1 [link] [source]
Warren,
thank you for taking the time to provide your response. I apologise again for my lack of knowledge in this area - please be assured that I had seen the generic instructions and used these for my initial attempts in the terminal window to no avail.
Based on the information that you provided and some further investigation, I had another go - commands shown below. Using just the command line that you provided, I received the error 'no such file was found'. I downloaded the fileio.c file and attempted to run based on a known path, but then the error was for the use of an undeclared identifier in the code.
I understand that I am showing my ignorance again, but would appreciate your further advice on this. Please note that I am using clang rather than gcc, which I believe is needed as a result of the updated OS X.
COMMAND Lines and responses
k@111-111-1-111~ % clang -g -fPIC -dynamiclib -I. ext/misc/fileio.c -o ~/Desktop/fileio.dylib
clang: error: no such file or directory: 'ext/misc/fileio.c'
clang: error: no input files
k@111-111-1-111~ % clang -g -fPIC -dynamiclib -I. fileio.c -o ~/Desktop/fileio.dylib
clang: error: no such file or directory: 'fileio.c'
clang: error: no input files
k@111-111-1-111~ % clang -g -fPIC -dynamiclib -I. ~/Downloads/fileio.c -o ~/Desktop/fileio.dylib
/Users/k/Downloads/fileio.c:590:29: error: use of undeclared identifier 'SQLITE_VTAB_DIRECTONLY' sqlite3_vtab_config(db, SQLITE_VTAB_DIRECTONLY);
/Users/k/Downloads/fileio.c:985:44: error: use of undeclared identifier 'SQLITE_DIRECTONLY' SQLITE_UTF8|SQLITE_DIRECTONLY, 0,
/Users/k/Downloads/fileio.c:989:46: error: use of undeclared identifier 'SQLITE_DIRECTONLY' SQLITE_UTF8|SQLITE_DIRECTONLY, 0,
3 errors generated.
(6) By Warren Young (wyoung) on 2021-06-06 04:08:20 in reply to 5 [link] [source]
You’ve ignored my “from within the SQLite source code tree” qualifier and the link from that to the page that will answer your question.