|
Using database's in Furbot is not mysterious, just takes time to plan out how you want to use your database and to lay it out correctly. There is a whole bunch of database commands which can be used in the bot, some of them don't work, but they will one day, but of those which work, there's not much missing from the available commands.
| DBADDNEW DatabaseID |  | | | Uses the AddNew method to create and add a new record. This method sets the fields to default values, and if no default values are specified, it sets the fields to Null (the default values specified for a table-type Recordset).
After you modify the new record, use the DBRSETUPDATE method to save the changes and add the record to the Recordset. No changes occur in the database until you use the DBRSETUPDATE . If you change the record pointer without updating the record, changes are lost but the record remains . | | | | DBDATE(DateString) | | | | Converts DateString to CDate DataType . This formats the date and time to system locale. Supposedly, passing it a "bad date" will result in an error. | | | | DBDELETE DatabaseID | | | | Deletes the current record in an updatable Recordset object. One shot, when executed, it's done. | | | | DBEDIT DatabaseID | | | | Copies the current record to the copy buffer for subsequent editing.
Once you use the DBEDIT , changes made to the current record's fields are copied to the copy buffer. After you make the desired changes to the record, use the DBRSETUPDATE to save your changes.
The current record remains current after you use DBEDIT .
Caution: If you edit a record and then perform any operation that moves to another record, but without first using DBRSETUPDATE , your changes are lost without warning. | | | | DBEOF(ID) | | | | Checks if the database record pointer is "beyond" the last record, will return "1" if it is, otherwise will return "0". | | | | DBEXECUTE ID SqlExecuteCommand | | | 0 | DatabaseID | | 1 | SqlExecuteCommand | | The most dangerous command, unlike the normal means of accessing and using a DataBase, this is the only qualified means to conduct SQL operations on a database, like dropping tables, and creating non furbot standard tables:
&dbexecute 1 {DROP TABLE dreams}
Will drop the table named dreams from the open database.
&dbexecute 1 {CREATE TABLE positions (ID COUNTER, pos TEXT, total TEXT, shape TEXT)}
And this will create a table in which ID is a number, and an autoincrement number at that, so you can then use a command like:
&dbexecute 3 &sprintf({INSERT INTO dreams _
(map, pos, object, dreamname, dreamtitle) _
VALUES('%1', '%2', '%3', '%4', '%5')} _
#mainname &dbf(1 pos) #dreamobject &dbf(1 dreamname) &dbf(1 dreamtitle))
To insert a new record into the database and have it be unique. Note that this type of usage can break regular BotScript commands because they don't understand a table with autonumbers. | | | | DBF(DatabaseID FieldName) | | | 0 | ID Of Open DB | | 1 | Field Value To Get | | Gets the contents of FieldName from DatabaseID and returns it. Must have prepped the database beforehand by using &dbopen with a valid SQL string. If &dbopen causes more than one Record to become accessable in the database, the result will be whatever is the currently "selected" RecordSet from the query. Usually this will be the first record in the selection, but don't count on this behavior. If you're selecting multiple records with a query, you should take steps to ensure that you're starting where you want. &dbopen 1 {select * from members}
&set #count &dbrecordcount(1)
&ifn #count >&dbmovelast 1
&dbmovefirst 1
&set #count &dbrecordcount(1)
&display #count
&for #index=1 to #count
&set #name &dbf(1 ID)
&join #result #index
&join #result {-}
&join #result &dbtostr(#name)
&join #result {, }
&dbmovenext 1
&endfor
&display #result
&endif | | | | DBFREETABLEID(DatabaseID TableName) | | | 0 | ID Of Open DB | | 1 | Name Of Table | | This is really for tables in which you"re using numbers for the ID. The term here is (AutoNumber), which is not actually supported directly, but it's a database table setup where each record in the table is identified by a numeric entry for the ID field. For example, you have a table with 20 records, but it used to have 21 and you deleted, say, record 5. DBFREETABLEID will return 22 because the last record in the database is still 21. This does not create a new record, just returns the last record's ID + 1. If you use this function on a table where the ID's are text, it will return 0. Will also fail and return 0 if the table does not have a field called "ID". | | | | DBMOVEFIRST DatabaseID / DBMOVELAST DatabaseID / DBMOVENEXT DatabaseID / DBMOVEPREV DatabaseID | | | | Move to the first, last, next, or previous record in a specified Recordset object and make that record the current record.
Use the DBMOVE* to move from record to record without applying a condition.
Caution: If you edit the current record, be sure you use the DBRSETUPDATE to save the changes before you move to another record. If you move to another record without updating, your changes are lost without warning. | | | | DBMOVETO DatabaseID MoveIndex | | | 0 | ID Of Open DB | | 1 | Offset To New Index | | Moves the position of the current record in a Recordset object.
The DBMOVETO syntax has these parts:
- DatabaseID : A value that represents the open database whose current record position is being moved.
- MoveIndex : A signed Long value specifying the number of Records to Move by.
If MoveIndex is greater than 0, the position is moved forward (toward the end of the table). If MoveIndex is less than 0, the position is moved backward (toward the beginning of the table). If MoveIndex is 0, the pointer goes nowhere. | | | | DBOPEN DatabaseID SQLStatement | | | 0 | ID Of Open DB | | 1 | SQL Statement | | Oh yeah, this is a SQL statement which selects what you want to do with the DB. I do believe that you will want to use the syntax "SELECT * FROM " to begin access of the DataBase. This also does the QUERY and sets the internal recordset object to the results of the query. | | | | DBRECORDCOUNT(DatabaseID) | | | | Will return the count of records returned by the last &dbopen query you executed. If you use this without first doing ANY queries, you get back "Object variable or With block variable not set" in the connection log. If you've done anything with the database which has executed a query, (and most of the DB commands will), you get back the count of records from the query. Notation: Unless you move the record pointer to the end and back to the beginning, you always get >0 records as the result, once you do move the record pointer (back and forth as I call it), you will get the exact number of records the query returned. Very important to move the record point back to the beginning if you do this, otherwise nastiness ensures. | | | | DBRSETUPDATE DatabaseID | | | | Updates the database whose open file is references by DatabaseID . | | | | DBSET DatabaseID FieldName Value | | | 0 | ID Of Open DB | | 1 | Field To Change | | 2 | New Value | | Use DBEDIT to select the record for editing. Sets the currently selected Recordset, in file DatabaseID, in FieldName, to be Value. Remember to use DBRSETUPDATE when you are done making changes to the Record. | | | |
Commands 
