Using DAL functions in projects with multiple database connections

PHPRunner 8, ASPRunnerPro 9 and ASPRunner.NET 8 added an option to use multiple database connections in single project. This article explains how you can access data from multiple databases in your events.

Method 1: using DAL functions

PHPRunner

We now have three options to refer to a table

1. $dal->Table(“table”)

finds first matching table by it’s name in all connections, starting with the primary connection.

2. $dal->Table(“table”,”schema”)

schema name helps to identify tables with identical names located in different schemas in databases like Oracle, Postgre and SQL Server.

3. $dal->Table(“table”,”schema”,”connection”)

Schema name can be left empty. Last parameter is connection name as it appears on ‘Datasource tables’ screen.

A complete code example:

$table = $dal->Table("cars","","cars at localhost");
$rs = $table->QueryAll();
while ($data = db_fetch_array($rs))
{
echo $data["Make"].", ".$data["Model"]."
";
}

ASPRunnerPro

We have 4 options to access a table object:

1. dal.Table(“table”)

finds first matching table by it’s name in all connections, starting with the primary connection.

2. dal.TableSchema(“table”, “schema”)

finds table by table name and schema name

3. dal.TableSchemaConn(“table”, “schema”, “connection”)

finds table by table name, schema name and connection name

4. dal.TableConn(“table”, “connection”)

finds table by table name and connection name

A complete code example:

set data = dal.TableConn("carscars", "cars at localhost").QueryAll()
while not data.eof 
	Response.Write data("Make") & ", " & data("Model") & "
"
	data.MoveNext
wend
data.close : set data=nothing

ASPRunner.NET (C#)

We have three options to refer to a table

1. GlobalVars.dal.Table(“table”)

finds first matching table by it’s name in all connections, starting with the primary connection.

2. GlobalVars.dal.Table(“table”,”schema”)

schema name helps to identify tables with identical names located in different schemas in databases like Oracle, Postgre and SQL Server.

3. GlobalVars.dal.Table(“table”,”schema”,”connection”)

Schema name can be left empty. Last parameter is connection name as it appears on ‘Datasource tables’ screen.

A complete code example:

var rs = GlobalVars.dal.Table("carscars","","cars at localhost").QueryAll();
XVar data;
while (data = CommonFunctions.db_fetch_array(rs))
{
	MVCFunctions.Echo(String.Format("{0}, {1} 
", data["Make"], data["Model"]));
}

Method 2: using free form SQL Queries

Update all cars where make is ‘Audi’ making YearOfMake equals 2002

PHPRunner

$cman->byName("cars at localhost")->query("Update carscars set yearofmake=2002 where make='Audi'");

ASPRunnerPro

cman.byName_p1("cars at localhost").query_p1("Update carscars set yearofmake=2002 where make='Audi'")