|
|
 |
|
|
| NOTE: This Design document is subject to change at any time. |
Syntax: <SQLSTORE property-list/>
The zSQLStore object implements storage in an SQL database, either local or on a remote database server.
The Connection property should be set to either the local database file name, the name of a zConnection object, or to a full ConnectionString property (for ADO databases).
The KeyField property indicates the field(s) to be used to locate a record in the database. If more than one field name, separated by semicolons, is used, then the KeyValue argument in the Get and Put methods should be an array of values to use to lookup the record to be accessed.
The Filter property is set to the name of a table within the database.
Once the Connection and Filter (and optionally the KeyField) have been set, you can then retrieve data using the Get method, or change data using the Set method (if the database and query supports updates).
When using the Get and Set methods, the KeyValue gives the value of the KeyField to be located in the database. If the KeyValue is left blank, the current record of the dataset is used. The FieldName argument specifies the field of the record to be set or returned.
Note that unlike the zQuery object, this SQLStore never queries a full dataset. Each time the Get method is called, an SQL SELECT statement is sent to the database to retrieve the specific field being requested. Each time the Put method is called, an SQL UPDATE statement is sent to the database to set a specific field. If the field does not exist, the field is added to the database table.
For more complex database access, you should use the zQuery component directly.
Examples:
| Code: |
Set SQL = core.CreateObject("SQLStore")
SQL.Connection = "MyData.db"
SQL.Keyfield = "CustNo"
SQL.Filter = "Customers"
LastName = SQL.Get( 123, "LastName")
SQL.Put( 123, "Comment", "This is a comment") |
This example opens a local SQLite database called MYDATA.DB and searches for a record where CustNo=123. The LastName of this record is returned, and the Comment field is set.
The following SQL statements were executed in the background from the previous example:
| Code: |
SELECT LastName FROM Customers WHERE CustNo=123
UPDATE Customers SET Comment="This is a comment" WHERE CustNo=123 |
Usage Tips
- When using a local database file, the file extension is used to determine which database drivers to load. For example, opening a *.MDB file will load the Jet 4 drivers for MS Access databases. Opening a *.DB file will load the SQLite drivers.
- When working with local database files, if the specified database does not exist, then it is created automatically.
- If you set Filter to a non-existent table and then use the Put method to store a value, the needed table will be automatically created in the database.
- If the Put method is used to store a value in a non-existent record, the needed record will be automatically created.
|
|
POP MessageStore