MySQL >= 5 supports stored procedures. I would be interested in an SQL
Server port. With the new .NET framework v2 you can now create
applications that support the free express version of SQL Server and
with clickonce that prerequisite can be automatically grabbed from
microsofts server and installed. Needless to say, I think there will be
a lot of hobbiest developers picking up these technology in the coming
years.
Dave
Steve Dibb wrote:
I've just barely recently started working on stored procedures, and so
far they're coming really handy. This way, instead of having to write
a class in just one language, I can put the functions in the database
so any language can use them with just a SELECT statement.
Anyway, I'm still very new at this, but I've got two for PostgreSQL
that should help out.
Both of these are the same function name "verse", but you can pass
different arguments.
The first one, you can run this:
SELECT verse('Genesis 1:1');
and it will return:
IN the beginning God created the heaven and the earth.
Here's the SQL to run to create it in your database:
CREATE OR REPLACE FUNCTION "verse" (character varying) RETURNS text AS
'SELECT verse_scripture FROM lds_scriptures_verses WHERE
LOWER(verse_title) = TRIM(LOWER($1)) OR LOWER(verse_title_short) =
TRIM(LOWER($1)) LIMIT 1;' LANGUAGE "sql"
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
The second one is the same name ("verse"), but this time you pass in
three variables: chapter -- which can be form of long, short or
lds_org format ('Genesis', 'Gen.', 'gen'), chapter, verse.
So, running this query:
SELECT verse('Articles of Faith', 1, 4);
would return:
WE believe in God, the Eternal Father, and in His Son, Jesus Christ,
and in the Holy Ghost.
Here's the function:
CREATE OR REPLACE FUNCTION "verse" (varchar, integer, integer) RETURNS
text AS 'SELECT verse_scripture
FROM lds_scriptures_verses
INNER JOIN lds_scriptures_books ON lds_scriptures_books.book_id =
lds_scriptures_verses.book_id
WHERE (LOWER(book_title) = TRIM(LOWER($1)) OR LOWER(book_title_short)
= TRIM(LOWER($1)) OR LOWER(lds_org) = TRIM(LOWER($1))) AND chapter =
$2 AND verse = $3 ORDER BY verse;' LANGUAGE "sql"
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
I'm still new at creating functions so if anyone sees any ways to
improve these, let me know.
Also, in other news, I'm going to release a long-needed XML bugfix
bump. Plus I'm going to start working on a port to Firebird since I
had a request for it, and I'm considering a port to SQL Server (if
anyone's genuinely interested, let me know, and I'll make it more of a
priority). Also, once I figure out how to do stored procedures on
other databases (MySQL, mostly) I'll write some for that too.
Steve
_______________________________________________
Ldsoss mailing list
[email protected]
http://lists.ldsoss.org/mailman/listinfo/ldsoss
_______________________________________________
Ldsoss mailing list
[email protected]
http://lists.ldsoss.org/mailman/listinfo/ldsoss
