 |
| BeginTrans |
Description: Begin a Transaction
Syntax: 'obj' Świ 'BeginTrans'
Note: use the CommitTrans method to commit the Transaction which has been initiated with BeginTrans
Use the RollBack method to roll back the Transaction
Example:
'ado' Świ 'BeginTrans' |
| Close |
Description: Closes an ADODB connection
Syntax: 'obj' Świ 'Close'
Example:
'ado' Świ 'Close' |
| ColNames |
Description: Return the database colum names for a given table
Syntax: 'obj' Świ 'ColNames'table
table: a table name
Example:
'ado' Świ 'ColNames' 'shippers' |
| Cols |
Description: Returns the table names & columns names for one, several or all tables from the database
Syntax: tblcols„'obj' Świ 'Cols' {tablenames}
tablenames: one or more table names
tblcols: a 2-column nested matrix sorted by table names and column names
[;1] „… table names
[;2] „… column number
[;3] „… column names
Note: this method works with MS SQL Server, ???
.…---------------------------------------------------------------------------------------------------------------.
‡Sysobject Table Documentation:
-----------------------------
Column Datatype Description
name varchar(30) Object name
id int Object ID
uid smallint User ID of owner object
type char(2) One of the following object types:
C CHECK constraint
D Default or DEFAULT constraint
F FOREIGN KEY constraint
K PRIMARY KEY or UNIQUE constraint
L Log
P Stored procedure
R Rule
RF Stored procedure for replication
S System table
TR Trigger
U User table
V View
X Extended stored procedure
userstat smallint Application-dependent type information
sysstat smallint Internal-status information
indexdel smallint Index delete count (incremented if an index is deleted)
schema smallint Count of changes in schema of a given object (incremented if a rule or default is added)
refdate datetime Reserved for future use
crdate datetime Indicates the date that the object was created
version datetime Reserved for future use
deltrig int Stored-procedure ID of a delete trigger
instrig int Stored-procedure ID of an insert trigger
updtrig int Stored-procedure ID of an update trigger
seltrig int Reserved
category int Used for publication, constraints, and identity
cache smallint Reserved
'----------------------------------------------------------------------------------------------------------------'
.…----------------------------------------------------------------------------------------------------------------------------------------.
‡Syscolumns Table Documentation:
-------------------------------
Column Datatype Description
id int ID of the table to which this column belongs or of the stored procedure with which this parameter is associated
number smallint Subprocedure number when the procedure is grouped (0 for nonprocedure entries)
colid tinyint Column ID
status tinyint Bitmap used to describe a property of the column or the parameter: 0x08
The column allows null values.0x40 The parameter is an OUTPUT parameter.0x80 The column is an identity column
type tinyint Physical storage type; copied from systypes
length tinyint Physical length of data; copied from systypes or supplied by the user
offset smallint Offset into the row where this column appears; if negative, variable-length column
usertype smallint User type ID; copied from systypes
cdefault int ID of the stored procedure that generates the default value for this column
domain int ID of the stored procedure that contains the rule for this column
name varchar(30) Column name
printfmt varchar(255) Reserved
prec tinyint Level of precision for this column
scale tinyint Scale for this column.
'-----------------------------------------------------------------------------------------------------------------------------------------'
Example:
'ado' Świ 'Cols'
'ado' Świ 'Cols' 'DCC_DONNEESCCI'
'ado' Świ 'Cols' 'DCC_DONNEESCCI' 'VER_VERSION' |
| Columns |
Description: Return the complete list of columns from opened database or from specified table
Syntax: columns„'obj' Świ 'Columns' {tablename}
tablename: a table name (case insensitive)
columns: a nested array describing columns in the table
[;3]„… field type (for Access Databases: 3=Number 11=YesNo 131=Currency 135=DateTime 202=Text 203=Memo 205=OLE Object)
Example:
'ado' Świ 'Columns'
'ado' Świ 'Columns' 'Shippers' |
|
| CommitTrans |
Description: Commits a Transaction which has been initiated with the BeginTrans method
Syntax: 'obj' Świ 'CommitTrans'
Note:
Use the RollBack method to roll back the Transaction
Example:
'ado' Świ 'CommitTrans' |
| ConnectionString |
Description: Return a valid Template Connection String for the specified parameters
Syntax: doc„'obj' Świ 'ConnectionString' mode product
mode: one of 'ODBC' 'OLEDB' 'DSN'
product: one of 'dBASE' 'Excel' 'Text' 'FoxPro' 'Access' 'SQL Server' 'Oracle' 'MySQL'
'AS400' 'Sybase' 'Sybase Sql Anywhere' 'Active Directory' 'DB2' 'Jet'
'Exchange Mail' 'OLAP' 'Index Server'
doc: documentation with Template Connection Strings
Example:
'ado' Świ 'ConnectionString' 'OLEDB' 'SQL Server' |
| Database |
Description: Returns the name of the currently opened database
Syntax: database„'obj' Świ 'Database'
database: the currently opened database (or connection string used to open it)
Example:
'ado' Świ 'Database' |
| Error |
Description: Enumerate Errors collection and display properties of each Error object
Syntax: errors„'obj' Świ 'Error'
errors: a matrix of errors
Example:
'ado' Świ 'Error'
Notes: Here are the Error Codes:
adErrBoundToCommand 3707 -2146824581 0x800A0E7B Cannot change the ActiveConnection property of a Recordset object which has a Command object as its source.
adErrCannotComplete 3732 -2146824556 0x800A0E94 Server cannot complete the operation.
adErrCantChangeConnection 3748 -2146824540 0x800A0EA4 Connection was denied. New connection you requested has different characteristics than the one already in use.
adErrCantChangeProvider 3220 -2146825068 0X800A0C94 Supplied provider is different from the one already in use.
adErrCantConvertvalue 3724 -2146824564 0x800A0E8C Data value cannot be converted for reasons other than sign mismatch or data overflow. For example, conversion would have truncated data.
adErrCantCreate 3725 -2146824563 0x800A0E8D Data value cannot be set or retrieved because the field data type was unknown, or the provider had insufficient resources to perform the operation.
adErrCatalogNotSet 3747 -2146824541 0x800A0EA3 Operation requires a valid ParentCatalog.
adErrColumnNotOnThisRow 3726 -2146824562 0x800A0E8E Record does not contain this field.
adErrDataConversion 3421 -2146824867 0x800A0D5D Application uses a value of the wrong type for the current operation.
adErrDataOverflow 3721 -2146824567 0x800A0E89 Data value is too large to be represented by the field data type.
adErrDelResOutOfScope 3738 -2146824550 0x800A0E9A URL of the object to be deleted is outside the scope of the current record.
adErrDenyNotSupported 3750 -2146824538 0x800A0EA6 Provider does not support sharing restrictions.
adErrDenyTypeNotSupported 3751 -2146824537 0x800A0EA7 Provider does not support the requested kind of sharing restriction.
adErrFeatureNotAvailable 3251 -2146825037 0x800A0CB3 Object or provider is not capable of performing requested operation.
adErrFieldsUpdateFailed 3749 -2146824539 0x800A0EA5 Fields update failed. For further information, examine the Status property of individual field objects.
adErrIllegalOperation 3219 -2146825069 0x800A0C93 Operation is not allowed in this context.
adErrIntegrityViolation 3719 -2146824569 0x800A0E87 Data value conflicts with the integrity constraints of the field.
adErrInTransaction 3246 -2146825042 0x800A0CAE Connection object cannot be explicitly closed while in a transaction.
adErrInvalidArgument 3001 -2146825287 0x800A0BB9 Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
adErrInvalidConnection 3709 -2146824579 0x800A0E7D The connection cannot be used to perform this operation. It is either closed or invalid in this context.
adErrInvalidParamInfo 3708 -2146824580 0x800A0E7C Parameter object is improperly defined. Inconsistent or incomplete information was provided.
adErrInvalidTransaction 3714 -2146824574 0x800A0E82 Coordinating transaction is invalid or has not started.
adErrInvalidURL 3729 -2146824559 0x800A0E91 URL contains invalid characters. Make sure the URL is typed correctly.
adErrItemNotFound 3265 -2146825023 0x800A0CC1 Item cannot be found in the collection corresponding to the requested name or ordinal.
adErrNoCurrentRecord 3021 -2146825267 0x800A0BCD Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
adErrNotExecuting 3715 -2146824573 0x800A0E83 Operation cannot be performed while not executing.
adErrNotReentrant 3710 -2146824578 0x800A0E7E Operation cannot be performed while processing event.
adErrObjectClosed 3704 -2146824584 0x800A0E78 Operation is not allowed when the object is closed.
adErrObjectInCollection 3367 -2146824921 0x800A0D27 Object is already in collection. Cannot append.
adErrObjectNotSet 3420 -2146824868 0x800A0D5C Object is no longer valid.
adErrObjectOpen 3705 -2146824583 0x800A0E79 Operation is not allowed when the object is open.
adErrOpeningFile 3002 -2146825286 0x800A0BBA File could not be opened.
adErrOperationCancelled 3712 -2146824576 0x800A0E80 Operation has been cancelled by the user.
adErrOutOfSpace 3734 -2146824554 0x800A0E96 Operation cannot be performed. Provider cannot obtain enough storage space.
adErrPermissionDenied 3720 -2146824568 0x800A0E88 Insufficent permission prevents writing to the field.
adErrProviderFailed 3000 -2146825288 0x800A0BB8 Provider failed to perform the requested operation.
adErrProviderNotFound 3706 -2146824582 0x800A0E7A Provider cannot be found. It may not be properly installed.
adErrReadFile 3003 -2146825285 0x800A0BBB File could not be read.
adErrResourceExists 3731 -2146824557 0x800A0E93 Copy operation cannot be performed. Object named by destination URL already exists. Specify adCopyOverwrite to replace the object.
adErrResourceLocked 3730 -2146824558 0x800A0E92 Object represented by the specified URL is locked by one or more other processes. Wait until the process has finished and attempt the operation again.
adErrResourceOutOfScope 3735 -2146824553 0x800A0E97 Source or destination URL is outside the scope of the current record.
adErrSchemaViolation 3722 -2146824566 0x800A0E8A Data value conflicts with the data type or constraints of the field.
adErrSignMismatch 3723 -2146824565 0x800A0E8B Conversion failed because the data value was signed and the field data type used by the provider was unsigned.
adErrStillConnecting 3713 -2146824575 0x800A0E81 Operation cannot be performed while connecting aynchronously.
adErrStillExecuting 3711 -2146824577 0x800A0E7F Operation cannot be performed while executing asynchronously.
adErrTreePermissionDenied 3728 -2146824560 0x800A0E90 Permissions are insufficient to access tree or subtree.
adErrUnavailable 3736 -2146824552 0x800A0E98 Operation failed to complete and the status is unavailable. The field may be unavailable or the operation was not attempted.
adErrUnsafeOperation 3716 -2146824572 0x800A0E84 Safety settings on this computer prohibit accessing a data source on another domain.
adErrURLDoesNotExist 3727 -2146824561 0x800A0E8F Either the source URL or the parent of the destination URL does not exist.
adErrURLNamedRowDoesNotExist 3737 -2146824551 0x800A0E99 Record named by this URL does not exist.
adErrVolumeNotFound 3733 -2146824555 0x800A0E95 Provider cannot locate the storage device indicated by the URL. Make sure the URL is typed correctly.
adErrWriteFile 3004 -2146825284 0x800A0BBC Write to file failed.
adWrnSecurityDialog 3717 -2146824571 0x800A0E85 For internal use only. Don't use.
adWrnSecurityDialogHeader 3718 -2146824570 0x800A0E86 For internal use only. Don't use. |
| Exec |
Description: Executes any SQL command
Syntax: 'obj' Świ 'Exec' sqlcommand
sqlcommand: any SQL command (see 'obj' Świ '?help' for examples)
Example:
'ado' Świ 'Exec' "select * from shippers where phone like'%99%'"
'ado' Świ 'Exec' 'insert into shippers(ShipperID,CompanyName,Phone) values(99,''UPS'','' (503) 555-5555'') '
'ado' Świ 'Exec' 'update shippers set CompanyName=''United Parcels Service'' where ShipperID = 99'
'ado' Świ 'Exec' 'delete from shippers where ShipperID = 99' |
|
| Insert |
Description: Inserts a set of records in a database table
Syntax: 'obj' Świ 'Insert'table columns data
table: an existing table name
columns: a nested vector of column names pertaining to the specified table or '' to mean all columns
data: a nested matrix, each line representing a record (columns must correspond to the <columns> parameter)
Example:
'ado' Świ 'Insert' 'CD' ('CD Id' 'CD Title' 'Price') (3 3˝2'Abbey Road'20.5 3'Help'18.2 4'Magical Mystery Tour'19)
'ado' Świ 'Insert' 'CD' '' (3 3˝2'Abbey Road'20.5 3'Help'18.2 4'Magical Mystery Tour'19) |
| Keys |
Description: Return the complete list of keys from opened database or from specified table
Syntax: keys„'obj' Świ 'Keys' {tablename}
tablename: a table name (case insensitive)
keys: a nested array describing keys in the table or database
[;1] „… table name
[;2] „… Column name
[;3] „… field type (1=Primary Key; 2=Foreign Key)
Example:
'ado' Świ 'Keys'
'ado' Świ 'Keys' 'Shippers' |
| New |
Description: Create a new instance of TADO5
Example:
'obj' Świ '*Create' 'TADO5'
|
| Open |
Description: Opens a new ADODB connection
Syntax: 'obj' Świ 'Open'datasource
datasource: a data source (file) name declared in Control Panel ODBC data sources
or: a ConnectionString such as 'driver={SQL Server};server=DELL8200;database=SIMV1_DEV01'
Example:
'ado' Świ 'Open' 'Northwind Access Database'
'ado' Świ 'Open' 'driver={SQL Server};server=DELL8200;database=SIMV1_DEV01' |
|
| PrimaryKeys |
Description: Return the Primary Key(s) for a given table or for all tables
Syntax: pkeys„'obj' Świ 'PrimaryKeys' {table}
table: an existing table name
pkeys: 2-columns matrix [;1]=table names [;2]=primary key column names
Example:
'ado' Świ 'PrimaryKeys'
'ado' Świ 'PrimaryKeys' 'CD' |
| RecordSet |
Description: Executes a command and return its result
Syntax: recordset„'obj' Świ 'RecordSet'sqlcommand
sqlcommand: any SQL command
Example:
'ado' Świ 'Result' 'Select count(*) From CD' |
|
| RollbackTrans |
Description: Rolls back a Transaction
Syntax: 'obj' Świ 'RollbackTrans'
Example:
'ado' Świ 'RollbackTrans' |
| Select |
Description: Executes an SQL Select command and returns recordset with field names
Syntax: 'obj' Świ 'Select'sqlcommand
sqlcommand: any Select SQL command
Example:
'ado' Świ 'Select' 'select * from shippers' |
| StProc |
Description: Returns the SQL code of a Stored Procedure given by its name
Syntax: stproccode„'obj' Świ 'StProc'stprocname
stprocname: Stored Procedure Name
stproccode: Stored Procedure Code
Note: assumes that each executable line in the stored procedure
ends with -- (SQL Server comment) and that all
comments start with /* and end with */
Here is a sample SQL Server Stored Procedure as it should be written:
.…------------------------------------------------------------------------------------.
‡ /*
Procedure Name: sp_typ_s_01
Description: Select a Simulation Type
Author: T.TREPIED
Created: 15apr2002
*/
/* Remove Stored Procedure if already exists */
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'sp_typ_s_01' AND type = 'P') --
DROP PROCEDURE sp_typ_s_01 --
GO
/* Create Stored Procedure */
CREATE PROCEDURE sp_typ_s_01 --
( --
@TYP_ID VarChar(7) = NULL /* Simulation Type ID */ --
) --
AS --
/* Stored Procedure Body */
SELECT TYP_LIBCOURT, --
TYP_LIBLONG --
FROM TYP_TYPESIMULATION --
WHERE TYP_ID = @TYP_ID --
AND TYP_TOPSUPLOG = 0 --
/* End of Stored Procedure */
GO
/*
Sample Call:
EXECUTE sp_typ_s_01
@TYP_ID = ''
*/
'-------------------------------------------------------------------------------------'
Example: Calling StProc on the above Stored Procedure return its executable code:
'ado' Świ 'StProc' 'sp_typ_s_01'
.…----------------------------.
‡CREATE PROCEDURE sp_typ_s_01 |
|( |
|@TYP_ID VarChar(7) = NULL |
|) |
|AS |
|SELECT TYP_LIBCOURT, |
|TYP_LIBLONG |
|FROM TYP_TYPESIMULATION |
|WHERE TYP_ID = @TYP_ID |
|AND TYP_TOPSUPLOG = 0 |
'-----------------------------' |
| StProcArg |
Description: Builds a valid template text vector argument for a Stored Procedure
Syntax: 'ado' Świ 'StProcArg'fieldvarnames
fieldvarnames: a nested vector of APL variables containing values to pass to the Stored Procedure
Example:
'ado' Świ 'StProcArg' 'ted_id' 'tes_libcourt'
@tes_id=2,@tes_libcourt="TRIAL" |
| StProcs |
Description: Returns the names of all existing Stored Procedures in opened SQL Server database
Syntax: 'ado' Świ 'StProcs' {string}{'args'}
strings: (optional) part of Stored Procedure name
'args': return argument details for each Stored Proc
Returns all procedure names containing <string>
Example:
'ado' Świ 'StProcs'
...
'ado' Świ 'StProcs' 'i_s_01'
sp_cci_s_01
sp_cli_s_01
sp_dsi_s_01
sp_pri_s_01
sp_sui_s_01
'ado' Świ 'StProcs' 'i_s_01' 'args'
sp_cci_s_01
@TES_ID Char(16) = NULL,
@PER_CODE Char(16) = NULL,
@SPP_ID Char(16) = NULL
sp_cli_s_01
sp_dsi_s_01
@TES_ID Char(16) = NULL,
@PER_CODE Char(16) = NULL,
@SPP_ID Char(16) = NULL,
@SIM_ID Numeric = NULL,
@TYC_ID VarChar(7) = NULL |
| StoredProc |
Description: Executes a Stored Procedure with or without arguments (note: assumes MS SQL Server)
Syntax: 'obj' Świ 'StoredProc'spname {arguments}
spname: the Stored Procedure name
arguments: an optional nested vector of values
Example:
'ado' Świ 'StoredProc' 'Region5'5'France'
where the corresponding SQL Server 2000 Stored Procedure is:
CREATE PROCEDURE Region5 (@id INT, @desc VARCHAR(50)) AS
INSERT INTO Region (RegionID, RegionDescription)
VALUES(@id, @desc)
Notes:
1. to pass NULL numeric argument values, pass 'NULL' as in this example:
'ado' Świ 'StoredProc' 'Region5' 'NULL' 'France'
2. it is also possible to pass named column arguments with the Stored Procedure name as in:
'ado' Świ 'StoredProc' 'sp_tes_i_01 @tes_id="2",@tes_libcourt="ESSAI"' |
| StoredProcs |
Description: Returns all Stored Procedures available in SQL Server database (note: assumes MS SQL Server)
Syntax: {res}„'obj' Świ 'StoredProcs' {stprocname}
res: 2-column matrix [;1]„…Stored Proc names [;2]„…Stored Procs Code
stprocname: name of the Stored Procedure
Example:
'ado' Świ 'StoredProcs'
sp_cci_d_05 /* Create Stored Procedure */ CREATE PROCEDURE sp_cci_d_05 -- ( -- @SPP_ID Char(16) = NULL, /...
sp_cci_d_06 /* Create Stored Procedure */ CREATE PROCEDURE sp_cci_d_06 -- ( -- @TES_ID Char(16) = NULL, /...
sp_dsi_d_02 /* Create Stored Procedure */ CREATE PROCEDURE sp_dsi_d_02 -- ( -- @SIM_ID Numeric = NULL /* ...
sp_dsi_d_03 /* Create Stored Procedure */ CREATE PROCEDURE sp_dsi_d_03 -- ( -- @PDS_ID Numeric = NULL /* ...
sp_fcd_d_01 /* Create Stored Procedure */ CREATE PROCEDURE sp_fcd_d_01 -- ( -- @FCD_ID VarChar(20) = NULL...
sp_fcd_i_01 /* Create Stored Procedure */ CREATE PROCEDURE sp_fcd_i_01 -- ( -- @FCD_ID VarChar(20) = NULL...
sp_fcd_s_01 /* Create Stored Procedure */ CREATE PROCEDURE sp_fcd_s_01 -- ( -- @FCD_ID VarChar(20) = NULL...
... |
| Tables |
Description: Return the complete list of tables from opened database
Syntax: tables„'obj' Świ 'Tables' {type}{table}
or: tables„'obj' Świ 'Tables' {table}
or: tables„'obj' Świ 'Tables'
types: one of 'SYSTEM TABLE' 'TABLE' or 'VIEW'
table: a character string to be contained in the table name
Example:
'ado' Świ 'Tables'
'ado' Świ 'Tables' 'TABLE'
'ado' Świ 'Tables' 'VIEW'
'ado' Świ 'Tables' 'TABLE' 'CON_CONNECTIONS' |
| Tbls |
Description: Return the complete list of tables from opened database
Syntax: tables„'obj' Świ 'Tbls' {table}
table: a character string to be contained in the table name
tables: a nested vector of table names (alphabetically sorted)
Notes:
1. Works with MS SQL Server, ???
2. See method <Cols> above for a description of the <sysobjects> table
Example:
'ado' Świ 'Tbls'
'ado' Świ 'Tbls' 'TABLE' 'CON' |
|
| Update |
Description: Updates a set of records in a database table
Syntax: 'obj' Świ 'Update'table columns data
table: an existing table name
columns: a nested vector of column names pertaining to the specified table or '' to mean all columns
data: a nested matrix, each line representing a record (columns must correspond to the <columns> parameter)
Example:
'ado' Świ 'Update' 'CD' ('CD Id' 'CD Title' 'Price') (3 3˝2'Abbey Road'20.5 3'Help'18.2 4'Magical Mystery Tour'19)
'ado' Świ 'Update' 'CD' '' (3 3˝2'Abbey Road'20.5 3'Help'18.2 4'Magical Mystery Tour'19) |
TADO5 