|
Column attributes
ColNum
Retrieves the column's ordinal number in the table.
This attribute is read only at runtime.
Syntax
tablename.column(columnid).ColNum
Data
Retrieves or assigns the value of binary data columns. The data attribute contains binary data which, in most instances, may be manipulated as a string.
Syntax
tablename.column(columnid).Data
Datatype
Retrieves the column's data type. It contains a constant integer value indicating the data type of the column.
This attribute is read only at runtime.
Syntax
tablename.column(columnid).Datatype [constant]
These are the possible values for constant:
• fcString - character or text data
• fcInteger - long integer numeric data
• fcLong - long Integer numeric data
• fcSingle - double floating point numeric data
• fcDouble - double floating point numeric data
• fcCurrency - currency data
• fcDate - date and time data
• fcBinary - binary data.
Name
Retrieves the column's database identifier.
This attribute is read only at runtime.
Syntax
tablename.column(columnid).Name
Nullable
Returns whether or not the column can be null.
This attribute is read only at runtime.
Syntax
tablename.column(columnid).Nullable
Precision
Retrieves the column's (or parameter's) maximum number of digits used by the data type of the column or parameter. This attribute is read only at runtime.
 Note For character types, this is the length in characters of the data; for binary data types, column size is defined as the length in bytes of the data. For the time, timestamp, and all interval data types, this is the number of characters in the character representation of this data.
Syntax
tablename.column(columnid).Precision
Scale
Retrieves the maximum number of digits to the right of the decimal point for the column.
This attribute is read only at runtime.
Note For approximate floating point number columns or parameters, the scale is undefined, since the number of digits to the right of the decimal point is not fixed. For datetime or interval data that contains a seconds component, the decimal digits is defined as the number of digits to the right of the decimal point in the seconds component of the data.
For the SQL_DECIMAL and SQL_NUMERIC data types, the maximum scale is generally the same as the maximum precision. However, some data sources impose a separate limit on the maximum scale.
Syntax
tablename.column(columnid).Scale
Text
Retrieves or assigns the text string of the column on the current row. Text may also contain date information.
Text and Value are the default attributes for columns.
Syntax
tablename.column(columnid).Text
Value
Retrieves or assigns the numeric value of the column on the current row. Value may also contain date information.
Text and Value are the default attributes for columns.
Syntax
tablename.column(columnid).Value
Connection attributes
AutoCommit
Specifies whether to use auto-commit or manual-commit mode.
Syntax
connectionname.AutoCommit [= constant]
These are the possible values for constant:
• SQL_AUTOCOMMIT_OFF
The connection uses manual-commit mode. The application must explicitly commit or roll back a transaction with the Commit or Rollback methods.
• SQL_AUTOCOMMIT_ON
The driver uses auto-commit mode. Each command is committed immediately after it is executed.
• SQL_AUTOCOMMIT_DEFAULT.
Autocommit On.
If no value for constant is assigned, the default depends on the settings in your ODBC setup. Changing from manual-commit mode to auto-commit mode commits any open transactions on the connection.
AccessMode
Retrieves or assigns an integer value indicating the ODBC access mode of a connection.
Syntax
connectionname.AccessMode [= constant]
These are the possible values for constant:
• SQL_MODE_READ_WRITE
Connections may process any read or write requests submitted.
• SQL_MODE_READ_ONLY
Connections will be instructed not to process requests which are not read-only; the behavior of the data source and ODBC driver which receive such requests is defined by the implementation.
• SQL_MODE_DEFAULT.
Read-write access mode.
The default value for this attribute is SQL_MODE_READ_WRITE.
CurrentQualifier
Specifies the concurrency attribute, which is a string containing the SQL command qualifier to be used by the data source.
Syntax
connectionname.CurrentQualifier [= qualifier]
For some databases the qualifier string might indicate a database to access, or in the case of some file-based data sources, a directory where table files are stored. Either can be specified.
ODBCCursors
Specifies how the connection will use the ODBC cursor library.
Syntax
connectionname.ODBCCursors [= constant]
These are the possible values for constant:
• SQL_CUR_USE_IF_NEEDED
The connection uses the ODBC cursor library only if it is needed. If Move and Find methods are not supported by the ODBC data source, the ODBC driver manager will attempt to emulate them.
• SQL_CUR_USE_ODBC
The connection uses the ODBC cursor library.
• SQL_CUR_USE_DRIVER
The connection uses the cursors and scrolling capabilities of the driver. Move and Find methods are driven using the data source's cursor model.
• SQL_CUR_USE_DEFAULT.
Use driver-driven cursors.
The default value for this attribute is SQL_CUR_USE_DRIVER.
LoginTimeout
Specifies an integer value indicating the number of seconds to wait for a login request to complete. The default is data source-driver dependent and is always a non-zero integer. If LoginTimeout is assigned 0, the timeout is disabled and a connection will wait indefinitely while attempting to log in.
Syntax
connectionname.LoginTimeout [= logintimeout]
The default login timeout for the connection is SQL_LOGIN_TIMEOUT_DEFAULT.
OptTraceFile
Specifies the name of the ODBC trace file.
Syntax
connectionname.OptTraceFile [ = filename]
filename can be any valid string expression that is a valid file system path.
OptTrace
Specifies an integer value indicating whether the ODBC Driver Manager performs tracing.
Syntax
connectionname.OptTrace [= constant]
These are the possible values for constant:
• SQL_OPT_TRACE_OFF - tracing off
• SQL_OPT_TRACE_ON - tracing on
• SQL_OPT_TRACE_DEFAULT - tracing off.
An application specifies a trace file with the OptTraceFile attribute. If the file already exists, the ODBC Driver Manager appends to the file. If the trace file does not exist, the ODBC Driver Manager creates the file. If no tracing file has been specified, the ODBC Driver Manager writes to the file \SQL.LOG.
The default value for this attribute is SQL_OPT_TRACE_OFF, which indicates that no ODBC tracing will occur.
PacketSize
An integer value that specifies the network packet size in bytes.
Note Many data sources do not support this attribute.
Syntax
connectionname.PacketSize [= size]
QuoteChar
Retrieves the data source-specific quote character.
This attribute is read only at runtime.
Syntax
connectionname.QuoteChar
TranslateDLL
Specifies the name of the DLL containing ODBC translation functions.
Syntax
connectionname.TranslateDLL [= DLLname]
DLLname can be any valid string expression that is a valid file system path to the translation DLL file. The translation DLL provides a facility for implementing additional translation functionality, including character set translation.
TxnIsolation
Retrieves or assigns an integer value indicating the ODBC transaction isolation level.
The following definitions and behaviors are defined in ODBC:
• Dirty Read
Transaction1 modifies a row and Transaction2 reads the modified row before Transaction1 commits the change. If Transaction1 rolls back the change, Transaction2 will have read a row that should have never existed.
• Nonrepeatable Read
Transaction1 reads a row and Transaction2 updates or deletes that row and commits this change. If Transaction1 rereads the row, it will receive different values or discover that the row no longer exists.
• Phantom
Transaction1 reads a set of rows that satisfy some search criteria. Transaction2 inserts a row that matches the search criteria. If Transaction1 re-executes the query that reads the rows, it receives a different set of rows.
Syntax
connectionname.Txnlsolation [= constant]
These are the possible values for constant:
• SQL_TXN_READ_UNCOMMITTED
Dirty reads, nonrepeatable reads, and phantoms are possible.
• SQL_TXN_READ_COMMITTED
Dirty reads are not possible. Nonrepeatable reads and phantoms are possible.
• SQL_TXN_REPEATABLE_READ
Dirty reads and nonrepeatable reads are not possible. Phantoms are possible.
• SQL_TXN_SERIALIZABLE
Transactions are serializable. Dirty reads, nonrepeatable reads, and phantoms are not possible.
• SQL_TXN_VERSIONING.
Transactions are serializable, but higher concurrency is possible than with SQL_TXN_SERIALIZABLE. Dirty reads are not possible.
Connection methods
BrowseConnection
Calls against an ODBC driver or data source to determine which arguments are required to connect.
Information is returned in the connection's ConnectionString property, and is useful when connecting to an ODBC database for the first time. This same information states what data is required to establish a connection. The normal connection method used in FirstClass RAD is OpenConnection, which takes the three arguments for normal data sources.
Syntax
connectionname.BrowseConnection(string)
Most ODBC data sources have a maximum of three arguments for string (dsn, userid, and password). Oracle and other enterprise databases may require a minimum of four arguments for string (dsn, userid, password, and connection string).
Example
Dim cnct as dbConnection
cnct.BrowseConnection ("DSN=ORACLE") 'Oracle data source
Print cnct.ConnectionString
UID:Login=?,PWD:Password=?;ConnectString: Connection String=?;
'string returned from the driver indicating which parameters are required
REM
'the format of the string returned from the driver indicating which parameters are required is (KEYWORD:DESCRIPTION=VALUE)
CloseConnection
Closes a connection on an ODBC data source. All open statements on the connection will automatically be closed.
Syntax
connectionname.CloseConnection
Example
See the code example for the OpenConnection connection method.
Commit
Commits a transaction on a data source.
Note This method is only available to data sources that support transactions. Commit is not a valid operation for connections that have the AutoCommit attribute turned on.
Syntax
connectionname.Commit
Example
Sub Main()
Dim cnct as DBConnection
cnct.OpenConnection("MyDSN") 'Open the data source "MyDSN"
cnct.AutoCommit = SQL_AUTOCOMMIT_OFF
'Begin the transaction
' Create statements and execute SQL...
' ...
If (UserClickedCancel = TRUE)
cnct.Rollback 'Rollback changes
Else
cnct.Commit 'Commit changes
End if
cnct.CloseConnection 'Close the connection
End Sub
DriverConnection
Establishes a data source with the raw ODBC connection information. This is the information returned by BrowseConnection.
Note If you have built a bound columns application (using table and connection FirstClass RAD objects instead of code) with a different database, and you would like to migrate to Oracle, you must do the following:
1 set the establish connections selection list to manual startup
2 run something similar to the DriverConnection code example (below) to connect to the database in your Sub Main()
3 match the table and column names in the Oracle database to the names in your previous project (or the migration will not work).
Syntax
connectionname.DriverConnection(connection)
where connection specifies a connection string for the ODBC driver.
Example
The following code can be used in a program to establish a live connection to the ORACLE data source, and allocate statements and run queries against it.
Dim cnct As dbConnection
cnct.DriverConnection("DSN=ORACLE;UID=admin;PWD=frisky;ConnectString=MyOracleServerString")
OpenConnection
Opens an ODBC data source connection.
Note This method must be run on any DBConnection object prior to opening statements and executing database operations.
Syntax
connectionname.OpenConnection(DSN[,login] [,password])
The DSN argument accepts any string expression that specifies a valid data source name as configured in the ODBC Data Source Administrator. The optional login and password arguments accept any string expressions that specify the data source login and password respectively.
Example
Sub Main()
Dim cnct As DBConnection
cnct.OpenConnection("MyDSN")
'Open the data source "MyDSN"
' Create statements and execute SQL...
' ...
cnct.CloseConnection
'Close the connection
End Sub
Rollback
Rolls back a transaction on a data source.
Note This method is only available to data sources that support transactions. Rollback is not a valid operation for connections that have the AutoCommit attribute turned on.
Syntax
connectionname.Rollback
Example
See the code example for the Commit connection method.
Email attributes
Body
Sets the body text for an email message.
This attribute is optional as body text is not required for an email message to be sent.
Syntax
emailvariable.Body [= string expression]
string expression can have any text value and is not limited to valid users on the system.
Example
Sub Main()
Dim MyMessage As Email
...
MyMessage.Body = "FCRAD demo, you are using too much disk space."
MyMessage.Send
End Sub
From
Sets the sender's identity for an email message.
This attribute must be set or the email message will not be delivered.
Syntax
emailvariable.From [= string expression]
string expression can have any text value and is not limited to valid users on the system.
Example
Sub Main()
Dim MyMessage As Email
...
MyMessage.From = "Administrator"
MyMessage.Send
End Sub
Subject
Sets the subject text for an email message.
This attribute is optional as subject text is not required for an email message to be sent.
Syntax
emailvariable.Subject [= string expression]
string expression can have any text value.
Example
Sub Main()
Dim MyMessage As Email
...
MyMessage.Subject = "Too many files..."
MyMessage.Send
End Sub
To
Sets the recipient's user name for an email message.
Note The To attribute supports FirstClass mail lists, but does not currently support the use of gateways. If the user name is invalid, the email message will fail. This attribute must be set or the email message will not be delivered.
Syntax
emailvariable.To [= username]
username can be any user or conference name as long as the user has send privileges to the recipient.
Example
Sub Main()
Dim MyMessage As Email
...
MyMessage.To = "FCRAD Demo"
MyMessage.Send
End Sub
Email methods
Send
Sends an email message.
If the To and From email attributes have not been set, an error occurs.
Syntax
emailvariable.Send
Example
Sub Main()
Dim MyMessage As Email
...
MyMessage.From = "Administrator"
MyMessage.Subject = "Welcome!"
For i = 0 to 3
MyMessage.To = Users(i)
MyMessage.Body = "Welcome " & Users(i) & "your account has " _ & "been created."
MyMessage.Send
Next i
End Sub
|
