{"id":1110,"date":"2015-05-12T17:57:30","date_gmt":"2015-05-12T22:57:30","guid":{"rendered":"http:\/\/xlinesoft.com\/blog\/?p=1110"},"modified":"2015-05-12T21:59:10","modified_gmt":"2015-05-13T02:59:10","slug":"using-dal-functions-in-projects-with-multiple-database-connections","status":"publish","type":"post","link":"https:\/\/xlinesoft.com\/blog\/2015\/05\/12\/using-dal-functions-in-projects-with-multiple-database-connections\/","title":{"rendered":"Using DAL functions in projects with multiple database connections"},"content":{"rendered":"

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.<\/p>\n

\"\"<\/p>\n

Method 1: using DAL functions<\/p>\n

\n<\/p>\n

PHPRunner<\/h3>\n

We now have three options to refer to a table<\/p>\n

1. $dal->Table(“table”)<\/h4>\n

finds first matching table by it’s name in all connections, starting with the primary connection. <\/p>\n

2. $dal->Table(“table”,”schema”)<\/h4>\n

schema name helps to identify tables with identical names located in different schemas in databases like Oracle, Postgre and SQL Server.<\/p>\n

3. $dal->Table(“table”,”schema”,”connection”)<\/h4>\n

Schema name can be left empty. Last parameter is connection name as it appears on ‘Datasource tables’ screen.<\/p>\n

A complete code example:<\/p>\n

\r\n$table = $dal->Table(\"cars\",\"\",\"cars at localhost\");\r\n$rs = $table->QueryAll();\r\nwhile ($data = db_fetch_array($rs))\r\n{\r\necho $data[\"Make\"].\", \".$data[\"Model\"].\"
\";\r\n}\r\n<\/pre>\n
ASPRunnerPro<\/h3>\n
We have 4 options to access a table object:<\/p>\n
1. dal.Table(“table”)<\/h4>\n
finds first matching table by it’s name in all connections, starting with the primary connection. <\/p>\n
2. dal.TableSchema(“table”, “schema”) <\/h4>\n
finds table by table name and schema name<\/p>\n
3. dal.TableSchemaConn(“table”, “schema”, “connection”)<\/h4>\n
finds table by table name, schema name and connection name<\/p>\n
4. dal.TableConn(“table”, “connection”)<\/h4>\n
finds table by table name and connection name<\/p>\n
A complete code example:<\/p>\n
\r\nset data = dal.TableConn(\"carscars\", \"cars at localhost\").QueryAll()\r\nwhile not data.eof \r\n\tResponse.Write data(\"Make\") & \", \" & data(\"Model\") & \"
\"\r\n\tdata.MoveNext\r\nwend\r\ndata.close : set data=nothing\r\n<\/pre>\n
ASPRunner.NET (C#)<\/h3>\n
We have three options to refer to a table<\/p>\n
1. GlobalVars.dal.Table(“table”)<\/h4>\n
finds first matching table by it’s name in all connections, starting with the primary connection. <\/p>\n
2. GlobalVars.dal.Table(“table”,”schema”)<\/h4>\n
schema name helps to identify tables with identical names located in different schemas in databases like Oracle, Postgre and SQL Server.<\/p>\n
3. GlobalVars.dal.Table(“table”,”schema”,”connection”)<\/h4>\n
Schema name can be left empty. Last parameter is connection name as it appears on ‘Datasource tables’ screen.<\/p>\n
A complete code example:<\/p>\n
\r\nvar rs = GlobalVars.dal.Table(\"carscars\",\"\",\"cars at localhost\").QueryAll();\r\nXVar data;\r\nwhile (data = CommonFunctions.db_fetch_array(rs))\r\n{\r\n\tMVCFunctions.Echo(String.Format(\"{0}, {1} 
\", data[\"Make\"], data[\"Model\"]));\r\n}\r\n<\/pre>\n
Method 2: using free form SQL Queries<\/h2>\n
Update all cars where make is ‘Audi’ making YearOfMake equals 2002<\/p>\n
PHPRunner<\/h4>\n
\r\n$cman->byName(\"cars at localhost\")->query(\"Update carscars set yearofmake=2002 where make='Audi'\");\r\n<\/pre>\n
ASPRunnerPro<\/h4>\n
\r\ncman.byName_p1(\"cars at localhost\").query_p1(\"Update carscars set yearofmake=2002 where make='Audi'\")\r\n<\/pre>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
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<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16,10,1,4,3,8],"tags":[],"class_list":["post-1110","post","type-post","status-publish","format-standard","hentry","category-asp-net","category-news","category-php-category","category-php-code-generator","category-php-form-generator","category-tutorials"],"_links":{"self":[{"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/posts\/1110","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/comments?post=1110"}],"version-history":[{"count":25,"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/posts\/1110\/revisions"}],"predecessor-version":[{"id":1140,"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/posts\/1110\/revisions\/1140"}],"wp:attachment":[{"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/media?parent=1110"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/categories?post=1110"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/tags?post=1110"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}