Function.ODBC.General
Available commands
Contents
- 1 Function.ODBC.Connection!conname.BeginTransaction
- 2 Function.ODBC.Connection!conname.CommitTransaction
- 3 Function.ODBC.Connection!conname.ExecuteAndReturn
- 4 Function.ODBC.Connection!conname.GetID
- 5 Function.ODBC.Connection!conname.GetSchemaFieldList
- 6 Function.ODBC.Connection!Conname.OpenLocalRecordsetRW
- 7 Function.ODBC.Connection!conname.RollbackTransaction
- 8 Function.ODBC.Connection!conname.RunStoredProc
- 9 Function.ODBC.Connection!conname.RunStoredProcNR
- 10 Function.ODBC.Connection!conname.RunStoredProcRst
- 11 Function.ODBC.Connection!conname.RunStoredProcRstLocal
- 12 Function.ODBC.Connection!conname.StoredProcExistsPervasive
- 13 Function.ODBC.Connection!ConnectionName.Close
- 14 Function.ODBC.Connection!ConnectionName.CloseRecordsets
- 15 Function.ODBC.Connection!ConnectionName.Execute
- 16 Function.ODBC.Connection!ConnectionName.ExecuteAndReturn
- 17 Function.ODBC.Connection!ConnectionName.FieldExists
- 18 Function.ODBC.Connection!ConnectionName.GetID
- 19 Function.ODBC.Connection!ConnectionName.GetSchemaFieldList
- 20 Function.ODBC.Connection!ConnectionName.GetSchemaList
- 21 Function.ODBC.Connection!ConnectionName.OpenConnection
- 22 Function.ODBC.Connection!ConnectionName.OpenLocalRecordset
- 23 Function.ODBC.Connection!ConnectionName.OpenmdbConnection
- 24 Function.ODBC.Connection!ConnectionName.OpenRecordsetRO
- 25 Function.ODBC.Connection!ConnectionName.OpenRecordsetRW
- 26 Function.ODBC.Connection!ConnectionName.TableExists
- 27 Function.ODBC.Connection.BeginTransaction
- 28 Function.ODBC.Connection.Close
- 29 Function.ODBC.Connection.CloseRecordsets
- 30 Function.ODBC.Connection.CommitTransaction
- 31 Function.ODBC.Connection.Execute
- 32 Function.ODBC.Connection.ExecuteANDReturn
- 33 Function.ODBC.Connection.FieldExists
- 34 Function.ODBC.Connection.GetID
- 35 Function.ODBC.Connection.GetSchemaFieldList
- 36 Function.ODBC.Connection.GetSchemaFieldListExt
- 37 Function.ODBC.Connection.GetSchemaList
- 38 Function.ODBC.Connection.OpenCommonConnection
- 39 Function.ODBC.Connection.OpenCompanyConnection
- 40 Function.ODBC.Connection.OpenConnection
- 41 Function.ODBC.Connection.OpenConnectionPSQL
- 42 Function.ODBC.Connection.OpenLocalRecordsetRO
- 43 Function.ODBC.Connection.OpenMDBConnection
- 44 Function.ODBC.Connection.OpenRecordsetRO
- 45 Function.ODBC.Connection.OpenRecordsetRW
- 46 Function.ODBC.Connection.RollBackTransaction
- 47 Function.ODBC.Connection.RunStoredProc
- 48 Function.ODBC.Connection.RunStoredProcNR
- 49 Function.ODBC.Connection.RunStoredProcRST
- 50 Function.ODBC.Connection.RunStoredProcRSTLOCAL
- 51 Function.ODBC.Connection.SaveStringToLongVarChar
- 52 Function.ODBC.Connection.StoredProcExistsPervasive
- 53 Function.ODBC.Connection.TableExists
- 54 Function.ODBC.ConnectionName!RecordsetName.AddNew
- 55 Function.ODBC.ConnectionName!RecordsetName.Close
- 56 Function.ODBC.ConnectionName!RecordsetName.Delete
- 57 Function.ODBC.ConnectionName!RecordsetName.FieldExists
- 58 Function.ODBC.ConnectionName!RecordsetName.InvokeSpy
- 59 Function.ODBC.ConnectionName!RecordsetName.MoveFirst
- 60 Function.ODBC.ConnectionName!RecordsetName.MoveLast
- 61 Function.ODBC.ConnectionName!RecordsetName.MoveNext
- 62 Function.ODBC.ConnectionName!RecordsetName.MovePrevious
- 63 Function.ODBC.ConnectionName!RecordsetName.RecordToString
- 64 Function.ODBC.ConnectionName!RecordsetName.RecordType2String
- 65 Function.ODBC.ConnectionName!RecordsetName.RecordTypeToString
- 66 Function.ODBC.ConnectionName!RecordsetName.ReQuery
- 67 Function.ODBC.ConnectionName!RecordsetName.SaveBLOBToFile
- 68 Function.ODBC.ConnectionName!RecordsetName.SaveFileToBlob
- 69 Function.ODBC.ConnectionName!RecordsetName.SaveFiletoBLOB
- 70 Function.ODBC.ConnectionName!RecordsetName.Set
- 71 Function.ODBC.ConnectionName!RecordsetName.Set!Fieldname
- 72 Function.ODBC.ConnectionName!RecordsetName.Set!Record2String
- 73 Function.ODBC.ConnectionName!RecordsetName.Update
- 74 Function.ODBC.Misc.CreateDSN
- 75 Function.ODBC.Misc.GetDriverList
- 76 Function.ODBC.Misc.GetDSNList
- 77 Function.ODBC.Misc.GetGlobalDSNList
- 78 Function.ODBC.Misc.IsDSN
- 79 Function.ODBC.Misc.SwitchIhopCreds
Function.ODBC.Connection!conname.BeginTransaction
Syntax
Function.ODBC.Connection.BeginTransaction()
Arguments
Examples
Remarks
Function.ODBC.Connection!conname.CommitTransaction
Syntax
Function.ODBC.Connection.CommitTransaction()
Arguments
Examples
Remarks
Function.ODBC.Connection!conname.ExecuteAndReturn
This command executes the specified query against the specified database connection, and returns the results in the return variable. If the query returns a single value (one field and one row), the return variable should be declared as the appropriate type (string, float, long, date, etc.). If the query returns more than a single field or row, the return variable must be a string. The returned string will be double-delimited, with *!* delimiting the fields, and #$# delimiting the rows. Optionally transformations may be specified on the return, if the return holds multiple fields and/or fields. The transforms may be specified as follows: 1 - Left trim 2 - Right trim 4 - Trim 8 - Lowercase 16 - Uppercase Multiple transform values can be added together to create a composite transformation, but the trim values should be treated as mutually exclusive, as should be the casing values. A value of 18 will result in return string, with each field right trimmed and uppercase. Examples: Function.ODBC.Connection!conP.ExecuteAndReturn("Select JOB, SUFFIX, PART, QTY_ORDER From Job_Header Where CUSTOMER = 'BMF'",8,Variable.Local.sRet)This returns a double-delimited, uppercase string Function.ODBC.Connection!conP.ExecuteAndReturn("Select Count(*) From Job_Header",Variable.Local.iRet)This returns the job count in a long variable Note: Specifying ALL or * in the select query (for returned fields) is not recommended, since addressing the data in the resulting string needs to be done by array index position.
Syntax
Function.ODBC.Connection.ExecuteAndReturn(SQL query, return)
Function.ODBC.Connection.ExecuteAndReturn(SQL query, transform, return)
Arguments
SQL query
transform
return
Examples
Remarks
Function.ODBC.Connection!conname.GetID
Given a table and field, this function returns a long integer. If the Increment boolean is set to True, the long return will be equal to the greatest field value, plus one. If the boolean is False, the highest value is returned. Note: This command should only be run against a numeric field.
Syntax
Function.ODBC.Connection.GetID(Table name, field name, increment boolean, long return)
Function.ODBC.Connection.GetID(Table name, field name, increment boolean, numeric source boolean, long return)
Arguments
Table name
field name
increment boolean
numeric source boolean
long return
Examples
Remarks
Function.ODBC.Connection!conname.GetSchemaFieldList
Syntax
Function.ODBC.Connection.GetSchemaFieldList(table name [string], string return [string])
Arguments
table name
string
string return
string
Examples
Remarks
Function.ODBC.Connection!Conname.OpenLocalRecordsetRW
These commands are similar in function to OpenRecordsetRO/RW, but they create local scope recordsets instead of global scope recordsets.
Syntax
Function.ODBC.Connection!Conname.OpenLocalRecordsetRO and Function.ODBC.Connection!Conname.OpenLocalRecordsetRW
Arguments
Examples
Remarks
Function.ODBC.Connection!conname.RollbackTransaction
Syntax
Function.ODBC.Connection.RollbackTransaction()
Arguments
Examples
Remarks
Function.ODBC.Connection!conname.RunStoredProc
This command executes the specified stored procedure against the specified database connection, providing the inbound parameters in a double-delimited string. The outer delimiter is @!@ and the inner delimiter is *!*. The command Function.Intrinsic.String.DoubleDelimitedSeek can be used to easily retreive the desired inbound parameter values, by name.
Syntax
Function.ODBC.Connection.RunStoredProc(stored proc name, param names, param types, param dirs, param sizes, param vals, string return)
Arguments
stored proc name
param names
param types
param dirs
param sizes
param vals
string return
Examples
Remarks
Function.ODBC.Connection!conname.RunStoredProcNR
This executes the specified stored procedure against the specified database connection.
Syntax
Function.ODBC.Connection.RunStoredProcNR(stored proc name)
Function.ODBC.Connection.RunStoredProcNR(stored proc name, param names, param types, param sizes, param vals)
Arguments
stored proc name
param names
param types
param sizes
param vals
Examples
Remarks
Function.ODBC.Connection!conname.RunStoredProcRst
This executes the specified stored procedure, against the specified database connection, and returns the results in the specified recordset.
Syntax
Function.ODBC.Connection!Conname.RunStoredProcRst(stored proc name [String], recordset name [String])
Function.ODBC.Connection!Conname.RunStoredProcRst(stored proc name [String], recordset name [String], param names [String], param types [Long], param sizes [Long], param vals [String])
Arguments
stored proc name
[String]
recordset name
[String]
param names
[String]
List of Input and Output parameters that needs to be passed to the stored procedure. It is a good practice to match these names with the parameter names in the stored procedure definition.
param types
[Long]
Datatype of the parameters.
0 - Boolean
1 - Date-Time
2 - Float
3 - Integer
4 - String
param sizes
[Long]
Size of the I/O parameters. By convention, this size should match the parameter size in the procedure definition, and 0 is used for integers.
param vals
[String]
Values for the input parameters. Blank string should be passed for output parameter.
Examples
Function.ODBC.Connection!Conn.RunStoredProcRst("GetVendorInfo","rstVendorInfo","Vendor",4,6,"100845")
Remarks
Function.ODBC.Connection!conname.RunStoredProcRstLocal
This executes the specified stored procedure, against the specified database connection, and returns the results in the specified recordset.
Syntax
Function.ODBC.Connection!conname.RunStoredProcRstLocal(stored proc name [String], recordset name [String])
Function.ODBC.Connection!conname.RunStoredProcRstLocal(stored proc name [String], recordset name [String], param names [String], param types [Long], param sizes [Long], param vals [String])
Arguments
stored proc name
[String]
recordset name
[String]
param names
[String]
List of Input and Output parameters that needs to be passed to the stored procedure. It is a good practice to match these names with the parameter names in the stored procedure definition.
param types
[Long]
Datatype of the parameters.
0 - Boolean
1 - Date-Time
2 - Float
3 - Integer
4 - String
param sizes
[Long]
Size of the I/O parameters. By convention, this size should match the parameter size in the procedure definition, and 0 is used for integers.
param vals
[String]
Values for the input parameters. Blank string should be passed for output parameter.
Examples
Function.ODBC.Connection!Conn.RunStoredProcRstLocal("GetVendorInfo","rstVendorInfoLocal","Vendor",4,6,"100845")
Remarks
Function.ODBC.Connection!conname.StoredProcExistsPervasive
This function determines if the specified stored procedure is present on the specified Pervasive SQL database connection object.
Syntax
Function.ODBC.Connection.StoredProcExistsPervasive(stored proc name [String], Return [Boolean])
Arguments
Stored Proc Name
[String]
Return
[Boolean]
Examples
Function.ODBC.Connection!Conn.storedProcExistsPervasive("GetVendorCount",Variable.Local.bRet)
Remarks
Function.ODBC.Connection!ConnectionName.Close
Closes a connection to a database that is open.
Syntax
Function.ODBC.Connection!ConnectionName.Close
Arguments
Examples
Remarks
Function.ODBC.Connection!ConnectionName.CloseRecordsets
This function closes and destroys all recordsets for the specified database connection.
Syntax
Function.ODBC.Connection!conname.CloseRecordsets
Arguments
Example
Remarks
Function.ODBC.Connection!ConnectionName.Execute
Executes a SQL statement for database access. The example will update the CompDt, Seq,and Grp where the row where the parent is equal to the variable.
Syntax
Function.ODBC.Connection!ConnectionName.Execute (SQL Statements)
Arguments
SQL Statement
Statement that tells what information to get from a database
Example
Variable.local.sSQL.declare(string) Variable.local.sDate.declare(string) Variable.local.iGrp.declare(long) F.Intrinsic.String.Concat("Update ATG_BOM_Comp set CompDt='2007-11-15',Seq=2,Grp=",V.Local.iGrp," where Parent='",V.Global.sBoM2.Trim,"'",V.Local.sSQL) F.ODBC.Connection!conP.Execute(V.Local.sSQL)
Remarks
- For those VB Programmers out there this is like a con.execute statement.
Function.ODBC.Connection!ConnectionName.ExecuteAndReturn
This command executes the specified query against the specified database connection, and returns the results in the return variable. If the query returns a single value (one field and one row), the return variable should be declared as the appropriate type (string, float, long, date, etc.). If the query returns more than a single field or row, the return variable must be a string. The returned string will be double-delimited, with *!* delimiting the fields, and #$# delimiting the rows.
Syntax
Function.ODBC.Connection!conname.ExecuteAndReturn(SQL query, return) or
Function.ODBC.Connection!conname.ExecuteAndReturn(SQL query, transform, return)
Arguments
Example
Remarks
Function.ODBC.Connection!ConnectionName.FieldExists
This function returns a boolean to indicate if the specified field (or fields) is in the specified table. To specify multiple fields use *!* as the delimiter. The boolean return will indicate True if the field was found. If multiple fields are specified, a True will be returned if ALL of the specified fields were found.
Syntax
Function.ODBC.Connection!conname.FieldExists(Table name, field name(s), boolean return)
Arguments
Example
Remarks
- This command takes about 15 seconds to run against a normal Global Shop database.
Function.ODBC.Connection!ConnectionName.GetID
Given a table and field, this function returns a long integer. If the Increment boolean is set to True, the long return will be equal to the greatest field value, plus one. If the boolean is False, the highest value is returned.
Syntax
Function.ODBC.Connection!conname.GetID(Table name, field name, increment boolean, long return)
Arguments
Example
Function.ODBC.Connection!con.GetID("ATG_B_SETUP_SHEET","COL_NO",True,V.Local.iRet)
Remarks
This command should only be run against a numeric field.
Function.ODBC.Connection!ConnectionName.GetSchemaFieldList
This function returns a double delimited string of the tables and fields in the database on the specified connection. The inner delimiter is :: and the outer delimiter is *!*.
Syntax
Function.ODBC.Connection!conname.GetSchemaFieldList(string return) or
Function.ODBC.Connection!conname.GetSchemaFieldList(table name, string return)
Arguments
Example
Remarks
- The alternate syntax version still returns the table name in the double delimited string.
Function.ODBC.Connection!ConnectionName.GetSchemaList
Syntax
Function.ODBC.Connection!conname.GetSchemaList(Tables Boolean, Views Boolean, string Return)
Arguments
Tables
True to Show Tables, False to Exclude Tables
Views
True to show views, False to exclude views
String Return
String that delimits tables and views with *!*
Example
Function.ODBC.Connection!con.GetSchemaList(True, False,variable.local.sReturn)
Remarks
When using PERVASIVE views must always be set to False (Pervasive does not support this feature). Can you F.I.S.Split to separate the string Return.
Function.ODBC.Connection!ConnectionName.OpenConnection
Opens a connection to a database
Syntax
Function.ODBC.Connection!ConnectionName.OpenConnection (DSN Name, UserName, Password) or
Function.ODBC.Connection!connection_name.OpenConnection(DSN, username, password, timeout) or
Function.ODBC.Connection!connection_name.OpenConnection(Connection string) or
Function.ODBC.Connection!connection_name.OpenConnection(DSN)
Arguments
Connection Name
The name of the connection
DSNName
The name of the DSN (Database) on the computer
UserName
Default use “Admin”
Password
Default use “”
Example
Function.ODBC.Connection!testConnect.OpenConnection (“GLOBAL_GSS”, “Master”, master“”)
Remarks
- The timeout value is in seconds
- This only affects the timeout of commands that execute against the database directly (UPDATE, DELETE, INSERT INTO, etc.)
- This isn't a bug fix, per se, but some ODBC commands executing against the Pervasive database engine will exceed the command timeout. When this happens, a S100 Timeout Expired fault will be thrown.
- This command can be used if you have a properly formatted connection string, or if you want to use NTLM security with your DSN.
- If you are using a DSN with this command, you will need to manually check for its presence.
Function.ODBC.Connection!ConnectionName.OpenLocalRecordset
These commands are similar in function to OpenRecordsetRO/RW, but they create local scope recordsets instead of global scope recordsets.
Syntax
Function.ODBC.Connection!Conname.OpenLocalRecordsetRO
Function.ODBC.Connection!Conname.OpenLocalRecordsetRW
Arguments
Example
Remarks
- When working with a table that has a memo field, the recordset must be opened using OpenRecordsetRW not OpenRecordsetRO. Once you have gotten into the recordset records the memo field will need to be placed in a stored variable, other wise the memo field will disappear from you.
Function.ODBC.Connection!ConnectionName.OpenmdbConnection
Opens a connection to a Microsoft Access database
Syntax
Function.ODBC.Connection!ConnectionName.OpenMDBConnection (Fully Qualified Name-mdb)
Arguments
Connection Name
The name of the connection
MDB Name
The fully qualified name to the .mdb (Microsoft Access Database) on the computer
Example
Function.ODBC.Connection!testConnect.OpenMDBConnection (“C:\test.mdb”)
Remarks
Function.ODBC.Connection!ConnectionName.OpenRecordsetRO
Opens a record set for read-only
Syntax
Function.ODBC.Connection!connectionName.OpenRecordsetRO(RecordsetName, Table or SQL Statement)
Arguments
Table or SQL Statement
Example
Function.ODBC.Connection!testCon.OpenRecordsetRO(“rst”, “Select * from CUST_master where CustName = ‘“+”Global Shop Solutions”+”’”)
Remarks
- When working with a table that has a memo field, the recordset must be opened using OpenRecordsetRW not OpenRecordsetRO. Once you have gotten into the recordset records the memo field will need to be placed in a stored variable, other wise the memo field will disappear from you.
- Some data types, specifically LONGVARCHAR will only work if the recordset is opened as RW instead of RO.
Function.ODBC.Connection!ConnectionName.OpenRecordsetRW
Opens records for read and write
Syntax
Function.ODBC.Connection.OpenRecordsetRW(RecordsetName, Table or SQL Statement)
Arguments
Table or SQL Statement
Example
Function.ODBC.Connection!testCon.OpenRecordsetRW(“rst”, “Select * from CUST_master where CustName = ‘“+”Global Shop Solutions”+”’”)
Remarks
- When working with a table that has a memo field, the recordset must be opened using OpenRecordsetRW not OpenRecordsetRO. Once you have gotten into the recordset records the memo field will need to be placed in a stored variable, other wise the memo field will disappear from you.
Function.ODBC.Connection!ConnectionName.TableExists
Syntax
Function.ODBC.Connection!conname.TableExists(Table name(s), Boolean return)
Arguments
Table Name(s)
Can list single table name or multiple ones delimited with a *!*
Boolean Return
True if table(s) exist or false if any do not exists
Example
Function.ODBC.Connection!con.TableExists(“ATG_Ext_BuildUp”,variable.local.bTable)
Remarks
Function.ODBC.Connection.BeginTransaction
Function.ODBC.Connection.BeginTransaction
Syntax
Arguments
Examples
Remarks
Function.ODBC.Connection.Close
Function.ODBC.Connection.Close
Syntax
Arguments
Examples
Remarks
Function.ODBC.Connection.CloseRecordsets
Function.ODBC.Connection.CloseRecordsets
Syntax
Arguments
Examples
Remarks
Function.ODBC.Connection.CommitTransaction
Function.ODBC.Connection.CommitTransaction
Syntax
Arguments
Examples
Remarks
Function.ODBC.Connection.Execute
Function.ODBC.Connection.Execute
Syntax
Arguments
Examples
Remarks
Function.ODBC.Connection.ExecuteANDReturn
Function.ODBC.Connection.ExecuteANDReturn
Syntax
Arguments
Examples
Remarks
Function.ODBC.Connection.FieldExists
Function.ODBC.Connection.FieldExists
Syntax
Arguments
Examples
Remarks
Function.ODBC.Connection.GetID
Function.ODBC.Connection.GetID
Syntax
Arguments
Examples
Remarks
Function.ODBC.Connection.GetSchemaFieldList
Function.ODBC.Connection.GetSchemaFieldList
Syntax
Arguments
Examples
Remarks
Function.ODBC.Connection.GetSchemaFieldListExt
Function.ODBC.Connection.GetSchemaFieldListExt
Syntax
Arguments
Examples
Remarks
Function.ODBC.Connection.GetSchemaList
Function.ODBC.Connection.GetSchemaList
Syntax
Arguments
Examples
Remarks
Function.ODBC.Connection.OpenCommonConnection
Function.ODBC.Connection.OpenCommonConnection
Syntax
Arguments
Examples
Remarks
Function.ODBC.Connection.OpenCompanyConnection
Function.ODBC.Connection.OpenCompanyConnection
Syntax
Arguments
Examples
Remarks
Function.ODBC.Connection.OpenConnection
Function.ODBC.Connection.OpenConnection
Syntax
Arguments
Examples
Remarks
Function.ODBC.Connection.OpenConnectionPSQL
Function.ODBC.Connection.OpenConnectionPSQL
Syntax
Arguments
Examples
Remarks
Function.ODBC.Connection.OpenLocalRecordsetRO
Function.ODBC.Connection.OpenLocalRecordsetRO
Syntax
Arguments
Examples
Remarks
Function.ODBC.Connection.OpenMDBConnection
Function.ODBC.Connection.OpenMDBConnection
Syntax
Arguments
Examples
Remarks
Function.ODBC.Connection.OpenRecordsetRO
Function.ODBC.Connection.OpenRecordsetRO
Syntax
Arguments
Examples
Remarks
Function.ODBC.Connection.OpenRecordsetRW
Function.ODBC.Connection.OpenRecordsetRW
Syntax
Arguments
Examples
Remarks
Function.ODBC.Connection.RollBackTransaction
Function.ODBC.Connection.RollBackTransaction
Syntax
Arguments
Examples
Remarks
Function.ODBC.Connection.RunStoredProc
Function.ODBC.Connection.RunStoredProc
Syntax
Arguments
Examples
Remarks
Function.ODBC.Connection.RunStoredProcNR
Function.ODBC.Connection.RunStoredProcNR
Syntax
Arguments
Examples
Remarks
Function.ODBC.Connection.RunStoredProcRST
Function.ODBC.Connection.RunStoredProcRST
Syntax
Arguments
Examples
Remarks
Function.ODBC.Connection.RunStoredProcRSTLOCAL
Function.ODBC.Connection.RunStoredProcRSTLOCAL
Syntax
Arguments
Examples
Remarks
Function.ODBC.Connection.SaveStringToLongVarChar
Function.ODBC.Connection.SaveStringToLongVarChar
Syntax
Arguments
Examples
Remarks
Function.ODBC.Connection.StoredProcExistsPervasive
Function.ODBC.Connection.StoredProcExistsPervasive
Syntax
Arguments
Examples
Remarks
Function.ODBC.Connection.TableExists
Function.ODBC.Connection.TableExists
Syntax
Arguments
Examples
Remarks
Function.ODBC.ConnectionName!RecordsetName.AddNew
Adds new row to a table
Syntax
Function.ODBC.ConnectionName!RecordsetName.AddNew
Arguments
None
Example
Function.ODBC.testCon!testrst.addnew
Remarks
- Addnew opens the recordset up for a new row in the table.
- Must be used with OpenRecordSetRW.
Function.ODBC.ConnectionName!RecordsetName.Close
Closes the record set
Syntax
Function.ODBC.ConnectionName!RecordsetName.Close
Arguments
None
Example
Function.ODBC.testCon!testrst.close
Remarks
- It is very important to always close your recordset!
Function.ODBC.ConnectionName!RecordsetName.Delete
Deletes a specified record set
Syntax
Function.ODBC.ConnectionName!RecordsetName.delete
Arguments
None
Example
Function.ODBC.testCon!testrst.delete
Remarks
Function.ODBC.ConnectionName!RecordsetName.FieldExists
This function returns a boolean value indicating if the specified field exists within the specified recordset. This function differs from Function.ODBC.Connection!conname.FieldExists in that it is much faster, but requires a recordset.
Syntax
Function.ODBC.ConnectionName!RecordsetName.FieldExists(field name, boolean return)
Arguments
Field Name
[String]
Return
[Boolean]
Example
Function.ODBC.conx!rst.FieldExists(PART,v.local.bRet)
Remarks
Function.ODBC.ConnectionName!RecordsetName.InvokeSpy
Description
Syntax
Arguments
Example
Remarks
Function.ODBC.ConnectionName!RecordsetName.MoveFirst
Description
Syntax
Arguments
Example
Remarks
Function.ODBC.ConnectionName!RecordsetName.MoveLast
Description
Syntax
Arguments
Example
Remarks
Function.ODBC.ConnectionName!RecordsetName.MoveNext
Moves to the next record in the record set
Syntax
Function.ODBC.ConnectionName!RecordsetName.MoveNext
Arguments
None
Example
Function.ODBC.testCon!testrst.Movenext
Remarks
- This command is helpful when looping through a recordset.
Function.ODBC.ConnectionName!RecordsetName.MovePrevious
Description
Syntax
Arguments
Example
Remarks
Function.ODBC.ConnectionName!RecordsetName.RecordToString
Syntax
Function.ODBC.ConnectionName!RecordsetName.RecordToString(transform flag)
Arguments
Example
Remarks
- This overload accepts a transform flag parameter, which accepts the following values:
- 1 - force uppercase
- 2 - force lowercase
- 8 - trim leading and trailing spaces
- 16 - trim leading spaces
- 32 - trim trailing spaces
- The flag values are additive, so a value of 33 will result in an uppercase return with trailing spaces removed.
Function.ODBC.ConnectionName!RecordsetName.RecordType2String
Syntax
Function.ODBC.ConnectionName!rstName.RecordType2String(String Return)
Arguments
Example
Variable.Local.sRet.Declare(String) Function.ODBC.Con!rstLU.RecordType2String(variable.local.sRet)
Remarks
Function.ODBC.ConnectionName!RecordsetName.RecordTypeToString
Syntax
Function.ODBC.ConnectionName!rstName.RecordTypeToString(String Return)
Arguments
None
Example
Variable.Local.sRet.Declare(String) Function.ODBC.Con!rstLU.RecordTypeToString(variable.local.sRet)
Remarks
None
Function.ODBC.ConnectionName!RecordsetName.ReQuery
Syntax
Function.ODBC.ConnectionName!rstName.Requery
Arguments
None
Example
Function.ODBC.Con!rstLU.ReQuery
Remarks
None
Function.ODBC.ConnectionName!RecordsetName.SaveBLOBToFile
This function saves the value of a Binary Long OBject (BLOB) database field into the specified fully-qualified filename. This command can be used with any database supporting this data type (MySQL, SQL Server, Pervasive SQL, Oracle, DB2, etc.). In Pervasive, the data type is LongVarBinary.
Syntax
Function.ODBC.ConnectionName!RecordsetName.SaveBLOBtoFile(field name, FQ filename)
Arguments
Field Name
Field name as string
File Name
Fully qualified file name as string
Example
Function.ODBC.ConnectionName!RecordsetName.SaveBLOBtoFile("PART","c:/")
Remarks
- This function must be used with recordsets opened with either Function.ODBC.Connection!ConnectionName.OpenLocalRecordsetRW, or Function.ODBC.Connection!ConnectionName.OpenRecordsetRW
- When using Pervasive SQL, this function is supported on 9.x versions, but is recommended only for 10.x and higher.
Function.ODBC.ConnectionName!RecordsetName.SaveFileToBlob
This function saves the binary contents of the specified fully-qualified filename into a Binary Long OBject (BLOB) database field. This command can be used with any database supporting this data type (MySQL, SQL Server, Pervasive SQL, Oracle, DB2, etc.). In Pervasive, the data type is LongVarBinary.
Syntax
Function.ODBC.ConnectionName!RecordsetName.SaveFiletoBLOB(field name, FQ filename)
Arguments
Field Name
Field name as string
File Name
Fully qualified file name as string
Example
Function.ODBC.ConnectionName!RecordsetName.SaveFiletoBLOB("PART","c:/")
Remarks
- This function must be used with recordsets opened with either Function.ODBC.Connection!Conname.OpenLocalRecordsetRW, or Function.ODBC.Connection!Conname.OpenRecordsetRW
- When using Pervasive SQL, this function is supported on 9.x versions, but is recommended only for 10.x and higher.
Function.ODBC.ConnectionName!RecordsetName.SaveFiletoBLOB
This function saves the binary contents of the specified fully-qualified filename into a Binary Long OBject (BLOB) database field. This command can be used with any database supporting this data type (MySQL, SQL Server, Pervasive SQL, Oracle, DB2, etc.). In Pervasive, the data type is LongVarBinary. Note: This function must be used with recordsets opened with either Function.ODBC.Connection!Conname.OpenLocalRecordsetRW, or Function.ODBC.Connection!Conname.OpenRecordsetRW Note: When using Pervasive SQL, this function is supported on 9.x versions, but is recommended only for 10.x and higher.
Syntax
Function.ODBC.ConnectionName!RecordsetName.SaveFiletoBLOB(field name, FQ filename)
Arguments
field name
FQ filename
Examples
Remarks
Function.ODBC.ConnectionName!RecordsetName.Set
Set is used with the Update functionality to change the database information either by updating an existing row or adding a new row with the AddNew function. The alternate syntax sets the specified recordset field to the truncated passed value. You may have unpredictable results when using this with non-text fields.
Syntax
Function.ODBC.ConnectionName!RecordsetName.Set!FieldName(“Value to set”) or
Function.ODBC.ConnectionName!RecordsetName.Set!FieldName(value, truncate length)
Arguments
Value to set
This is a value you want the specified field to be
Example
Function.ODBC.testCon!testrst.Set!FirstName(“George”)
Remarks
Function.ODBC.ConnectionName!RecordsetName.Set!Fieldname
Syntax
Function.ODBC.ConnectionName!RecordsetName.Set!fieldname(value, truncate length)
Arguments
value
truncate length
Examples
Remarks
Function.ODBC.ConnectionName!RecordsetName.Set!Record2String
Used to convert a row into a string passed with *!* delimeters
Syntax
Function.ODBC.ConnectionName!RecordsetName.Set!Record2String(Return Variable)
Arguments
Return Variable
String returned with the data from the recordset row
Example
Function.ODBC.ConnectionName!RecordsetName.Set!Record2String(variable.local.sRecord)
Remarks
- Use F.I.String.Split(variable.local.sRecord,”*!*”, variable.local.sRetArray) to get the records out of the string and into an array to use for manipulation.
Function.ODBC.ConnectionName!RecordsetName.Update
Updates the record set that is specified in the code with the data in the Set statements.
Syntax
Function.ODBC.ConnectionName!RecordsetName.update
Arguments
Example
Function.ODBC.testCon!testrst.update
Remarks
Function.ODBC.Misc.CreateDSN
Syntax
Arguments
Examples
Remarks
Function.ODBC.Misc.GetDriverList
Returns a list of the drivers for the pc the user is on
Syntax
Function.ODBC.Misc.GETDRIVERLIST(Ret String Array)
Arguments
Return String Array
Converts string variables to arrays, auto declares
Example
Function.ODBC.MISC.GetDriverList(variable.local.strArray())
Remarks
Function.ODBC.Misc.GetDSNList
Retrieves list of DSN’s on the computer being used.
Syntax
Function.ODBC.Misc.GETDSNLIST(Ret String Array)
Arguments
Return String Array
Converts string variables to arrays
Example
Function.ODBC.MISC.GetDSNList(variable.local.strArray)
Remarks
Function.ODBC.Misc.GetGlobalDSNList
Syntax
Function.ODBC.Misc.GetGlobalDSNList(string array return)
Arguments
string array return
Examples
Function.ODBC.MISC.GetGlobalDSNList(variable.local.sRet)
Remarks
Function.ODBC.Misc.IsDSN
Pass a DSN to be verified
Syntax
Function.ODBC.Misc.ISDSN(DSN Name, Ret boolean)
Arguments
DSN Name
Pass the System DSN Name
Return Boolean
True=Present on System, and False if not present on System
Example
Function.ODBC.MISC.IsDSN(“Global_GSS”, variable.local.bDSN) Returns true if the DSN “Global_GSS” exists on the machine.
Remarks
Function.ODBC.Misc.SwitchIhopCreds
Function.ODBC.Misc.SwitchIhopCreds