I've tackled this problem a few ways; *Method 1* The one I usually go with (Old habits die hard, and I'm freak'n lazy when it comes to one or two statements in the whole application) is just put the SQL string right into the code. That way it doesn't 'get lost', I don't have to rely on external sources when trying to troubleshoot problems, and when I make a change directly in the source code and I'm on my merry way after a recompile, and the new code gets put into VC when I submit the change.
*Method 2* Although I've not employed this method often, I'll keep a separate unit containing constant strings of all queries to be used for this application. Within the code, I'd use the querys constant name I've defined in the unit. However, I've found that it becomes a bit difficult to maintain, BUT, run time is fast as the compiler will essentially replace the constant name with the string and there is exactly zero look up time. If push came to shove, I could easily write an application that runs at pre-compile time that'd generate the unit based on that SQL code managers KEY=VALUE export, but, that'd be just one more thing to troubleshoot if something along the line failed. *Method 3* Another way is the same method that James mentions in that he uses external text files and builds them into his EXE. I've done this as well, and it works, although, handling simple resources like that is a bit annoying for me coding wise and organizational wise (Having several notepads or tabs in PSPad isn't ideal for me), especially when I might need tens to hundreds of individual queries. I could improve the code so it is less transparent, but, I've since moved onto this next method. *Method 4* The last, and most recent way that I work for hard-core applications dealing this particular problem is once I realized that I've used the same kind of SQL code and database structures (User Authentication mechanisms, or, tables with KEY=VALUE pairs, etc) across different projects, I wrote a program that manages a central repository of all my SQL table schemas and queries. On a new project, I tell this application where to either create, recreate, or add into a database file that will be used by the application the required table and view schemas, and then include sample or default data if required. It will also create another SQL database file that includes a table of KEY=VALUE of all queries that should be available to the program. At compile time, I build in the KEY=VALUE database into a resource file. At runtime, I create a globally accessible object that reads in this KEY=VALUE database and relevant table and puts the results into a tStringList, then my application refers to the object that contains the tStringList. When done, I drop the SQLite database from memory.[1] If, during development, I make changes to the KEY=VALUE table/database through my favorite SQLite3 database manager, I can have this application scan all KEY=VALUE databases it knows of, and import the changes and keep revisions available for other applications if needed. On export I can tell the application exactly which version of the query I want.[2] [1]=I haven't done tests to compare the speed of doing multiple SELECT queries to get a result versus just using a tStringList.Values['SQLStringName'] and get a result. In other words, I don't know if Pascal/Delphi tStringList.Values[] is quicker to do this kind of look up compared to creating a new table instance, generating the query, and getting the string result. As always, with C/C++/OC, YMMV. [2]=This application I wrote is seldom used because I'm not changing details all that much. It is basically used when I create new projects, do the exporting, and such, and then when I want to validate the existing KEY=VALUE pairs out 'in the field' in my development directory on my computer. On Fri, Oct 30, 2015 at 2:08 PM, Ramar Collins <ramar.collins at gmail.com> wrote: > Hi Guys, > > I'm working on a static site generator and want to use SQLite to store > metadata. I'm using C and a small library to get the majority of the work > done. My question is, do you have any suggestions or know where to find > more lore on how to nicely embed SQL in a program like this? > > Here is an example: > char query[1024]; > char *val1, *val2, *qs = "INSERT INTO posts VALUES ( NULL, '%s', '%s' ); > /* initialize val1 and val2... */ > snprintf(query, 1023, qs, val1, val2); > /* open database, bind statement, etc. */ > sqlite3_exec(query); > > > The example is not nearly complete, but I'm almost certain there's a > cleaner way to seperate the SQL from the rest of the code. Before I go > come up with my own thing, I wanted to see if there perhaps some better > solutions already out there. > > Antonio R. Collins II > ramar.collins at gmail.com > http://ramarcollins.com > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
