Re: [sqlite] How to create and uses a stored procedure for Sqlite
Steve Frierdich said:
> Anyone know of any links or have example on how to create a stored
> procedure and how to use that stored procedure in code on a sqlite
> database?
Steve,
We're happy to help people out here, but we would appreciate it if you
could read some of our responses before re-asking the same question.
As to how to store your scripts in a file and make use of them in sqlite,
the following code is close, although not fully checked.
char* buffer;
struct stat sb;
FILE* in;
char* sql;
sqlite3_stmt* stmt;
sqlite3* db;
if (stat("file.sql", )) return EXIT_FAILURE;
in = fopen("file.sql", "r");
if (!in) return EXIT_FAILURE;
buffer = (char*)calloc(1, sb.st_size + 1);
fread((void*)buffer, 1, sb.st_size, in);
fclose(in);
sql = buffer;
while(sql) {
/* check the parameter order on this, I'm winging it */
sqlite3_prepare(db, sql, strlen(sql), , );
while(sqlite3_step(stmt) != SQLITE_DONE);
sqlite3_finalize(stmt);
}
free(buffer);
This is completely off the top of my head, and of course may not address
your particular needs. But you've got the file reading and the continuous
processing of the SQL statements until they're completed. That should be
a good start to get you going. If you're using another language you'll
have to do the translation yourself.
Clay Dowling
--
Lazarus Notes from Lazarus Internet Development
http://www.lazarusid.com/notes/
Articles, Reviews and Commentary on web development
Re: [sqlite] How to create and uses a stored procedure for Sqlite
Steve: You really should point out if you're familiar with other environments, and ask questions that represent your eventual goals. You're fishing right now, and your questions are so loaded it's not even funny. I'm going on a hunch here, but I suspect you might be familiar with [a particular vendor] SQL server because you keep mentioning triggers and stored procedures for things that I understand aren't easy to do _without_ triggers and stored procedures on that system. SQLite is _not_ a replacement for that package. SQLite is a way for programs that contain most of their business logic _outside_ the SQL server to carry a full relational system and use it for _storage only_. SQLite is a replacement (or possibly a supplement) for systems like MySQL or Postgres which are attracting _new users_ to store data, and possibly constraints for that data inside this remote, blackbox, thing they keep calling "SQL Server". But like most MySQL and Postgres applications, the business logic for SQLite applications is NOT going to exist in the SQL server. It's going to exist [almost entirely] inside your application. This is why people keep mentioning TCL, or C [or perl, or python or ruby]- that's where your business logic has to go. I suggested you use the SQLite "REPLACE" command for something that you might've just used: BEGIN;SELECT ; UPDATE ; COMMIT Both are _almost_ the same on SQLite, and most SQL-beginners have no problem using that (if they don't know about REPLACE), but you said a _single_statement_. A single_statement has meaning on that other SQL server, but not here. When using SQLite I even recommend that you use multiple queries (but not a BEGIN-SELECT-UPDATE-COMMIT in a tight loop) as the query analyzer/optimizer in SQLite still leaves much to be desired. And should: SQLite is designed to be small and fast in "most cases". Programs that use SQLite typically have less than 10% of their source being SQL. The remainder "business logic" gets profiled. Rarely is it one of that 10% of SQL, but then, 10 minutes of thought for hand SQL optimization isn't so bad when you get to use a _normal_ profiler for the other 90% of your code. If you're looking for a way to recode business logic written in some stored procedure-language, you really need to let the list know this. There are quite a few really smart people on this list that are actually familiar with your systems and have probably even done something quite similar to what you're doing now. Please take a moment to explain what your current project is so that we can help you better. On Tue, 2005-01-25 at 17:11 -0500, Steve Frierdich wrote: > Anyone know of any links or have example on how to create a stored > procedure and how to use that stored procedure in code on a sqlite database? > Thanks > Steve > > > > >
Re: [sqlite] How to create and uses a stored procedure for Sqlite
Dennis Do you have an example on how to do this? Steve Dennis Cote wrote: Steve Frierdich wrote: Anyone know of any links or have example on how to create a stored procedure and how to use that stored procedure in code on a sqlite database? Thanks Steve As someone pointed out earlier, SQLite does not support stored procedures. You can store SQL scripts in text files, or in a table in the database. Your application can then retrieve the scripts and execute them whenever necessary. Its not the same as a stored procedure, but it is often all that is needed. HTH Dennis Cote
