Re: SQL Integration

On Wed, 2 Feb 2005 22:31:30 -0800, James Kilts 
<jameskilts-Re5JQEeQqe8AvxtiuMwx3w@xxxxxxxxxxxxxxxx> wrote:
> using boosql
> db = Database("blah.mdb")
> rows = select * from db.good_people where fname == "bob" order by lname
> for row as duck in rows:
>    print "addresses for bob: " + row.address

I don't see how you would be able to insert variables or expressions
into the sql command though, like you can with strings:

searchname = "bob"
sql = "select * from db.good_people where fname == "${searchname}"
order by lname"

or an expression like "...where total='${x+y}'..."

What if we made helper functions that still used strings like this:

rows = conn.select("* from db.good_people where fname ==
"${searchname}" order by lname")
for r in rows:
    print r["address"]

I guess that's a little more like how Python does databases.  ADO.NET
is pretty much the standard for .NET though.  I have a sample below.

> Before I get too deep into designing/implementing this, is this
> something that would be useful enough to add to the core language
> distribution (like the print macro)?  Are there other (better) plans
> for SQL or database integration?  How much (or what kind of)
> support/integration should exist for ADO.NET?

Others have shown interest in C-Omega's XML integration too.  Anything
to make working with SQL databases would be nice, too, I imagine.

I'm new to ADO.NET.  I was going to put a basic sample up on the site.
 I converted the Mono Sqlite test case to boo (see below), but I found
out it doesn't support DataAdapter yet.  So I may put up a Firebird
example instead (http://www.dotnetfirebird.org/).

On the XML side for those who have not seen it, there's this sample:
http://svn.boo.codehaus.org/trunk/examples/duck-typing/XmlObject.boo?view=auto

And If you look at http://jira.codehaus.org/browse/BOO-192 I think we
can use something called JSON too, to make working with XML and other
structured data easier (it's just lists and hashes/dictionaries, see
http://www.crockford.com/JSON/example.html ).


//Sqlite example (DataAdapter part doesn't work)
//Requires Mono.Data.SqliteClient.dll which is included with Mono.

//On Windows, if you are compiling with .NET instead of Mono,
//put Mono.Data.SqliteClient in your build folder (if you are using booi)
// and first run "sn -Vr build\Mono.Data.SqliteClient.dll" to turn off
strong name validation.

import System
import System.Data from System.Data

//converted using Daniel Grunwald's C# to boo converter:

Console.WriteLine('If this test works, you should get:')
Console.WriteLine('Data 1: 5')
Console.WriteLine('Data 2: Mono')
Console.WriteLine('create SqliteConnection...')
dbcon as SqliteConnection = SqliteConnection()
connectionString as string = 'URI=file:SqliteTest.db'
Console.WriteLine('setting ConnectionString using: ' + connectionString)
dbcon.ConnectionString = connectionString
Console.WriteLine('open the connection...')
dbcon.Open()
Console.WriteLine('create SqliteCommand to CREATE TABLE MONO_TEST')
dbcmd as SqliteCommand = SqliteCommand()
dbcmd.Connection = dbcon
dbcmd.CommandText = 'CREATE TABLE MONO_TEST ( ' + 'NID INT, ' + 'NDESC TEXT )'
Console.WriteLine('execute command...')
dbcmd.ExecuteNonQuery()
Console.WriteLine('set and execute command to INSERT INTO MONO_TEST')
dbcmd.CommandText = 'INSERT INTO MONO_TEST  ' + '(NID, NDESC )' +
'VALUES(5,\'Mono\')'
dbcmd.ExecuteNonQuery()
Console.WriteLine('set command to SELECT FROM MONO_TEST')
dbcmd.CommandText = 'SELECT * FROM MONO_TEST'
reader as SqliteDataReader
Console.WriteLine('execute reader...')
reader = dbcmd.ExecuteReader()
Console.WriteLine('read and display data...')
while reader.Read():
        Console.WriteLine('Data 1: ' + reader[0].ToString())
        Console.WriteLine('Data 2: ' + reader[1].ToString())

Console.WriteLine('read and display data using DataAdapter...')
adapter as SqliteDataAdapter = SqliteDataAdapter('SELECT * FROM
MONO_TEST', connectionString)
dataset as DataSet = DataSet()
adapter.Fill(dataset)
for myTable as DataTable in dataset.Tables:
        for myRow as DataRow in myTable.Rows:
                for myColumn as DataColumn in myTable.Columns:
                        Console.WriteLine(myRow[myColumn])
                                
                        
                
                Console.WriteLine('clean up...')
                dataset.Dispose()
                adapter.Dispose()
                reader.Close()
                dbcmd.Dispose()
                dbcon.Close()
                Console.WriteLine('Done.')