Lua and databases

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Lua and databases

Steve Dekorte-4
Is anyone using Lua with SQL databases? 
Is there any public code for doing this?

Steve

Reply | Threaded
Open this post in threaded view
|

Re: Lua and databases

Erik Hougaard
Steve Dekorte wrote:
> 
> Is anyone using Lua with SQL databases?
> Is there any public code for doing this?
> 
> Steve

Dunno, but I'm planning to do a library for Microsoft SQL 7.0 - Their
C-Library is quite simple to work with. This is my plan:

(0) Connect to server
1. You build a SQL statement in a string
2. You submit the statement
3. You retreive rows and columns from the result
(4) Disconnect from server

Take a look at the C-Library for SQL7 and you will be surprised how
simple it is, considering that it is a Microsoft API !

Erik

Reply | Threaded
Open this post in threaded view
|

Re: Lua and databases

Bennett Todd-2
1998-12-15-06:24:41 Erik Hougaard:
> Dunno, but I'm planning to do a library for Microsoft SQL 7.0 - Their
> C-Library is quite simple to work with. This is my plan:
> 
> (0) Connect to server
> 1. You build a SQL statement in a string
> 2. You submit the statement
> 3. You retreive rows and columns from the result
> (4) Disconnect from server
> 
> Take a look at the C-Library for SQL7 and you will be surprised how
> simple it is, considering that it is a Microsoft API !

Some additions that might be useful, if they aren't already there:

(1) Allow multiple iterations of steps 1-3 on a single open connection.
(2) Use a "connection handle" of some sort, allowing multiple simultaneous
    connections to the same DB.
(3) Provide a thin ``database-independant'' shim layer on top, that talks to
    database-specific layers below.

That third point, the idea behind Perl's DBI/DBD system, makes it much easier
to write code against one database management system, then port it to another;
it should also support running database queries against multiple database
systems simultaneously. It'd be good to look over the docs to perl's DBI, as
that went through some years of evolution to reach its current state.

-Bennett

Reply | Threaded
Open this post in threaded view
|

Re: Lua and databases

Roberto Ierusalimschy
> (0) Connect to server
> 1. You build a SQL statement in a string
> 2. You submit the statement
> 3. You retreive rows and columns from the result
> (4) Disconnect from server


The implementation of DBLua (the one made by Mauricio Mediano) is almost
that. It has mainly 4 functions:

- DBOpen: connect to server
- DBExec: executes an SQL statement given as a string
- DBRow: retreive "next" row from the result. Each row is retrieved at once
with all columns stored in a Lua table, indexed by the field name. Returns
nil after last row.
- DBClose: disconnect from server.


-- Roberto

Reply | Threaded
Open this post in threaded view
|

Re: Lua and databases

Erik Hougaard
In reply to this post by Bennett Todd-2

Bennett Todd wrote:
> (1) Allow multiple iterations of steps 1-3 on a single open connection.
> (2) Use a "connection handle" of some sort, allowing multiple simultaneous
>     connections to the same DB.
> (3) Provide a thin ``database-independant'' shim layer on top, that talks to
>     database-specific layers below.

Re 1 & 2:

I will actually create a userdata tag for handling the connection. So
what you do, is supplying this userdata (That holds your SQL pointer for
the connection) as a parameter to all your "sql" call in the library, in
this way you can have as many connections as your SQL API will supply
you.

Re 3:

Just create several different tags, and do a simple switch(tag) to add
support for different database types.

Another thing, is to create a Lua table wrapper around this, so you can
get some sort of OO functionality if you want to address several
databases at the same time!. 

Erik

Reply | Threaded
Open this post in threaded view
|

Re: Lua and databases

Erik Hougaard
In reply to this post by Roberto Ierusalimschy

Roberto Ierusalimschy wrote:
> The implementation of DBLua (the one made by Mauricio Mediano) is almost
> that. It has mainly 4 functions:
> 
> - DBOpen: connect to server
> - DBExec: executes an SQL statement given as a string
> - DBRow: retreive "next" row from the result. Each row is retrieved at once
> with all columns stored in a Lua table, indexed by the field name. Returns
> nil after last row.
> - DBClose: disconnect from server.
> 
> -- Roberto

Amen!

SQL Server API's are usually quite simple because they parse the SQL
statement, the only problem is the whole cursor issuse. If you want to
create a nice "PC-like" behaving application where you can do
pageup/pagedown/up/down navigation you can end up doing a lot of server
queries.. Ohh the day when SQL will get "real" NextRecord/PreviousRecord
funtions.

Erik

Reply | Threaded
Open this post in threaded view
|

Re: Lua and databases

Steve Dekorte-4
In reply to this post by Bennett Todd-2
Bennett Todd <[hidden email]> wrote:
> (3) Provide a thin ``database-independant'' shim layer on top, that talks to
>     database-specific layers below.
> 
> That third point, the idea behind Perl's DBI/DBD system, makes it much easier
> to write code against one database management system, then port it to another;
> it should also support running database queries against multiple database
> systems simultaneously. It'd be good to look over the docs to perl's DBI, as
> that went through some years of evolution to reach its current state.

All these different "scripting" languages need to talk to databases.
Hasn't a common C API for doing this come about so they can all just
link to the same C libs?

Steve

Reply | Threaded
Open this post in threaded view
|

Re: Lua and databases

David Jeske-2
On Tue, Dec 15, 1998 at 04:51:37PM -0200, Steve Dekorte wrote:
> All these different "scripting" languages need to talk to databases.
> Hasn't a common C API for doing this come about so they can all just
> link to the same C libs?

ODBC is the most standardized C level database API. However, every
database has some proprotary operations that you may need to code for
also. ODBC standardizes the 'connection' to a database by making you
setup the database specific connection information and give it a
logical name. However, you will still write DB specific code to do DB
specific things.

-- 
David Jeske (N9LCA) + http://www.chat.net/~jeske/ + [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Lua and databases

Jim Mathies-2
In reply to this post by Steve Dekorte-4
This model needs persistence for the database connections, and some more
advanced
tansactional stuff.  Plus some caching of database content would be nice.

If no one is working on such a thing - I have an application which requires
a generalized
database api and connection (with the actual datasource adapter interface
code specifics hidden) in combination with
a new apache mod which allows you to use Lua and the vm as a scripting
engine for HTML.
(Similar to PHP/FI or ColdFusion)

I might consider puting some basics together unelss someone else has a great
deal of work done here.

The database API would be standardized and not be SQL engine specific.  It
would have the ability
to connect to any datasource as long as somebody wrote the underlying
adapter code.

Then all you need is a way to get the database info into the web page -
which the apache mod
could handle.

Jim

-----Original Message-----
From: Erik Hougaard <[hidden email]>
To: Multiple recipients of list <[hidden email]>
Date: Tuesday, December 15, 1998 10:20 AM
Subject: Re: Lua and databases


>
>
>Roberto Ierusalimschy wrote:
>> The implementation of DBLua (the one made by Mauricio Mediano) is almost
>> that. It has mainly 4 functions:
>>
>> - DBOpen: connect to server
>> - DBExec: executes an SQL statement given as a string
>> - DBRow: retreive "next" row from the result. Each row is retrieved at
once
>> with all columns stored in a Lua table, indexed by the field name.
Returns
>> nil after last row.
>> - DBClose: disconnect from server.
>>
>> -- Roberto
>
>Amen!
>
>SQL Server API's are usually quite simple because they parse the SQL
>statement, the only problem is the whole cursor issuse. If you want to
>create a nice "PC-like" behaving application where you can do
>pageup/pagedown/up/down navigation you can end up doing a lot of server
>queries.. Ohh the day when SQL will get "real" NextRecord/PreviousRecord
>funtions.
>
>Erik