Database Access

From GAB Help
Jump to: navigation, search

A Connection is what links the program to data, database example are the Pervasive Database, Excel Spreadsheets, or an Access Database.

Always close the connection at the end of the program!

Opening a Connection to the Pervasive Database

When opening a connection to Pervasive, you may use Variable.Ambient.pDSN, Variable.Ambient.pUser, and Variable.Ambient.pPass to retrieve the valid DSN, ”Master” username, and “Master” password. This functionality is only available in Global Shop-authored GTL programs.

To open a connection to the GLOBALCOMMON database, you may use Variable.Ambient.Ccon, Variable.Ambient.Cuser, Variable.Ambient.Cpass.

Syntax

Function.ODBC.Connection!con.openConnection(DSN as String, Username as string,Password as string) 

Example

Function.ODBC.Connection!con.OpenConnection("Global_TST","Master","master") 

Recordsets

  • A recordset is a set of data that is returned from the database.
  • Recordsets have global scope, meaning that the same one can be declared in one subroutine and used in another subroutine. On that note make sure that you have unique recordset names, so that there is no overlapping when trying to get information from the database.
  • Always close a recordset at the end of the subroutine
  • When naming a recordset choose a name that represents what you are doing, and try to name each recordset something different, as these are Global and can and will be reused throughout the program.

Table = Specifies a certain table to open

SQL = Is a Query Language that tells the database exactly what information from a specified table that you need returned.

Creating a Recordset

Opening for just reading data:

Function.ODBC.Connection!con.openRecordSetRO(rst,Table or SQL)

F.ODBC.Connection!con.openRecordsetRO(“rst”, “Select * from Inventory_MSTR”) 

Opening to write out to the database or opening a table that has a memo type field:

Function.ODBC.Connection!con.openRecordSetRW(rst,Table or SQL)

F.ODBC.Connection!con.openRecordsetRW(“rst”, “Inventory_MSTR”) 

Updating Information to the Database

Adding a new record to the database:

Function.ODBC.Con!rst.addnew

Sets a field to a new value:

Function.ODBC.Con!rst.set!fieldName(“Value”)

Updates information to the database:

Function.ODBC.Con!rst.update

When looping through data this function will move to the next record in the recordset

Function.ODBC.Con!rst.movenext

Closing the Recordset

Function.ODBC.Con!rst.close 

ALWAYS CLOSE YOUR RECORDSETS!!!

Reading Information from the Database

Find out if the Database is connected for use by the Program:

Variable.ODBC.ConnectionName.State

Verify that you have records:

Variable.ODBC.ConnectionName!RecordsetName.EOF

Gives the number of columns in the table or returned in the SQL statement:

Variable.ODBC.ConnectionName!RecordsetName.FieldCount

Gives the Type of data returned for a specific field in the recordset:

Variable.ODBC.ConnectionName!RecordsetName.FieldType!FieldName

Gives the data in a specified field in the recordset:

Variable.ODBC.ConnectionName!RecordsetName.FieldVal!FieldName  

Example

Program.Sub.Main.Start
Variable.local.sRtr.declare(String)
Variable.local.sSQL.declare(String)
Variable.local.sDraw.declare(String)

Need to get screen id for Job and Suffix
Function.Odbc.Connection!con.Openconnection(V.Am.pDSN,V.Am.pUser,V.Am.pPass)
F.In.String.Concat(“Select * from Job_Header where Job=’”+,variable.passed.000000, +“’ and Suffix= ‘”+, variable.passed.00000, variable.local.sSQL)
function.odbc.connection!con.openrecordsetro("rstWO",variable.local.sSQL)

function.intrinsic.Control.If(V.OD.Con!rstWO.EOF,=,True)
function.Intrinsic.UI.MsgBox(“The Work Order was not found!”)
function.Intrinsic.Control.ElseIf(V.OD.Con!rstWO.EOF,=,False)
variable.local.sRtr.set(variable.ODBC.Con!rst.FieldVal!Router)
F.In.String.Concat(“Select * from Router_Header where Router=’”+,variable.local.sRtr, +“’, variable.local.sSQL)
function.odbc.connection!con.openrecordsetro("rstRtr",variable.local.sSQL)
function.Intrinsic.Control.if(Variable.ODBC.Con!rstRtr.eof,=,False)
    Variable.local.sDraw.set(variable.ODBC.con!rstRtr.FieldVal!Drawing_Customer)
Function.intrinsic.Control.Endif
Variable.ODBC.Con!rstRtr.Close
Function.Intrinsic.Control.Endif
function.odbc.con!rstWO.close
function.odbc.connection!con.close
Program.Sub.Main.End