TOPIC: Accessing othe databases from suneido
#1047
Accessing othe databases from suneido 2 Years, 9 Months ago
Accessing other databases from suneido
Category: Database

Problem

You need to access data living in othe database/server

Ingredients

ADODB From Microsoft
In case you did not allredy have it you can get it from Microsoft (I think it's free download)
Documentation about ADODB, it's objects and methods can be found in MSDN
(Microsoft Developers Network)


Recipe

Step 1: Instantiate an ADODB COMobject

With the Help of COMobject you can instantiate a COM Object from suneido
as showed in the "Using MS-Word from Suneido" Recipe
So let's do it:

try
{
oConn=COMobject("ADODB.Connection")
oConn.Open( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.Test.mdb;" )
oRS=COMobject("ADODB.Recordset")
oRS.Open('select * from Tabla1;',oConn)
}
catch(e)
{
Alert(e)
}

Here we instantiate 2 objects a Connection and a Recordset
the try statement will take care of errors (Perhaps inexistent database/ADODB not installed and so on)

Step 2: Process the Recordset

If the previous code executed OK now you have a Recordset object With
the desired data, so we can process it, adding it's values to a couple of
suneido objects:


data=Object()
cols=Object()

oRS.MoveFirst()
for(i=0;i<oRS.Fields.Count;i++)
{
cols.Add(oRS.Fields(i).Name)
}
while(not oRS.EOF)
{
row=Object()
for(i=0;i<oRS.Fields.Count;i++)
{

row[oRS.Fields(i).Name]=oRS.Fields(1).Value
}

data.Add(row)
oRS.MoveNext()
}

as you can see there are 2 objects
columns: Will hold columns names
data: Will hold the actual values

Step 3: Release resources

After being used we need to close the opened resources With

oRS.Close()
oConn.Close()

Step 4 : Closing is not releasing
After closing we need to release the used object so windows can free the
memory used by them (For further discussion see Suneido's User Manual entry for COMobject)

Step 5: Dish looks is as important as food taste

We can present the data we have in our data object using a ListControl


Window(Object('List' columns: cols,
data: data
defWidth: false)
)

Step 6: The final example function

function()
{

try
{
oConn=COMobject("ADODB.Connection")
oConn.Open( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.Test.mdb;" )
oRS=COMobject("ADODB.Recordset")
oRS.Open('select * from Tabla1;',oConn)
}
catch(e)
{
Alert(e)
}
oRS.MoveFirst()
data=Object()
cols=Object()
for(i=0;i<oRS.Fields.Count;i++)
{
cols.Add(oRS.Fields(i).Name)
}
while(not oRS.EOF)
{
row=Object()
for(i=0;i<oRS.Fields.Count;i++)
{

row[oRS.Fields(i).Name]=oRS.Fields(1).Value
}

data.Add(row)
oRS.MoveNext()
}
oRS.Close()
oConn.Close()
oRS.Release()
oConn.Release()

Window(Object('List' columns: cols,
data: data
defWidth: false)
)
}
oRS.Release()
oConn.Release()

Hope someones fid it usefull
 
 
#1048
Re:Accessing othe databases from suneido 2 Years, 9 Months ago
Thanks Sanotto, it looks useful.
 
 
andrew
 
#1671
Re:Accessing othe databases from suneido 8 Months, 3 Weeks ago
Based on this code I've got this function that can create and import a table from an odbc connection:

Code:


Import_Ado

function(odbcdbname)
{
try
{
oConn=COMobject("ADODB.Connection")
oConn.Open( odbcdbname)
oRS=COMobject("ADODB.Recordset")
}
catch(e)
{
Alert(e)
}

copyTable = function(oConn, oRS, atable)
{
oRS.Open('select * from ' $ atable $
' order by id;', oConn)

oRS.MoveFirst()
cols=Object()

for(i=0;i<oRS.Fields.Count;i++)
{
cols.Add(oRS.Fields(i).Name)
}
Database('
ensure ' $ atable $ '(' $ cols.Join(',') $ ')
key(id)
')
Print(cols.Join(','))
Transaction(update:)
{|t|
t.Query('delete ' $ atable)
while(not oRS.EOF)
{
row = oRS.GetString(2,1,'|').Trim().Split('|')
record = Object()
for(i=0;i<cols.Size()-1;i++)
{
record[cols[i]] = row[i]
}
t.QueryOutput(atable, record)
//Print(record)
}
}
oRS.Close()
}

for(atable in #('products_vat','products_groups',
'product_prices', 'invoice_kinds',
'partners', 'products_sb', 'invoices_sb',
'invoices_sb_lines'))
copyTable(oConn, oRS, atable)

oConn.Close()
oRS.Release()
oConn.Release()
}

 
 
#1673
Re:Accessing othe databases from suneido 8 Months, 3 Weeks ago
Thanks!
 
 
andrew
 
#1682
Re:Accessing othe databases from suneido 8 Months, 3 Weeks ago
Strange enough, the snippet that I published worked the first day, today I could see that some fields aren't copied correctly so I tried again bu now I'm getting "win32 exceptions all the time"

For some small tables it works, but for big ones no !

Have someone tested it ?

I tested both trunk and stable and got the same result, also I'm testing with another ADO query tool and it works fine, so probably isn't my ADO installation.

Windows Vista Home edition.
 
 
Last Edit: 2009/11/30 23:17 By mingodad.
 
#1690
Re:Accessing othe databases from suneido 8 Months, 3 Weeks ago
Possibly something not getting released? That might explain why it would work on small examples but not larger.
 
 
andrew