13.2. Quick start

13.2.1. Building an application

To build an application working with desired SQL server you have to install appropriate clip-<rdbms> package. The following packages are available currently:

Once installed desired package you can build an application like this:

bash$ clip -e test.prg -lclip-mysql

13.2.2. Step by step

Before you will be able to make something you should create a connect to the server. The function ConnectNew() is intended for that purpose. ConnectNew() - is a constructor of the class TConnect, i.e. it returns a TConnect object on success. Once obtained that object can be used to execute SQL statements, to select a set of desired rows, to start and end transactions. For example:

conn := ConnectNew(...)  // obtain a connection
conn:Start()             // start a transaction

conn:Command("UPDATE emp SET name='Total' WHERE name='Rust'")
// next time, in pay office i'll say: "My name is Total"

conn:Rollback()          // just kidding :) cancel the change

Several connections can be made simultaneously. Moreover, it is possible to connect to several various servers simultaneously.

SQL statements and queries can have parameters. Parameter names must be preceded with ':'. Parameter values are passed in the bidimensional array - one row per parameter. First column contains parameter name, second - the value. For example:

conn:Command("UPDATE emp SET fname=:fname,lname=:lname",;
	{{"fname","John"},{"lname","Smith"}})

TConnect member function CreateRowset() is used to obtain a set of rows - result of SELECT statement. It returns an object of TRowset class. For example:

rs := conn:CreateRowset("SELECT * FROM emp WHERE fname=:fname",{{"fname","John"}})
rs:Browse()    // simple BROWSE for TRowset

TRowset member functions allow you to navigate through the set of result rows. Here they are: Bof(), Eof(), Skip(), Goto(), GoTop(), GoBottom(), Lastrec(), Recno().

Two functions are intended to read/write the current row: Read() and Write(). Read() returns an object whose structure is the same as the structure of the row. For example:

rs := conn:CreateRowset("SELECT fname,lname FROM emp")
? rs:Recno(), rs:Read() // 1 {FNAME: John, LNAME: Smith}

Write() function receives an object and sets the values of fields whose names are in compliance with the attribute names of that object. For example:

? rs:Read()            // {FNAME: John, LNAME: Smith}
obj := map()
obj:fname := "Robert"
obj:salary := 10000
rs:Write(obj)
? rs:Read()            // {FNAME: Robert, LNAME: Smith}

You can add row to the set (Append()) and delete (Delete()). Append() receives parameter - obj. For example:

rs := conn:CreateRowset("SELECT fname,lname FROM emp")
? rs:Lastrec() // 100
obj := map()
obj:fname := "Homer"
obj:lname := "Simpson"
rs:Append(obj)
? rs:Lastrec() // 101
? rs:Read()    // {FNAME: Homer, LNAME: Simpson}
rs:Delete()
? rs:Lastrec() // 100

All changes performed by Write(), Append(), Delete() are applied to the set only. However, three additional parameters (<cInsertSQL>, <cDeleteSQL>, <cUpdateSQL>) can be passed to CreateRowset(). If passed, <cInsertSQL> will be executed implicitly by invoking Append() method. Likewise, <cDeleteSQL> and <cUpdateSQL> will be used when Delete() and Write() have been invoked. An unique ID of the row should be SELECTed in the case of Write() and Delete(). (Read about this issue documentation on the driver of desired RDBMS.) For example:

rs := conn:CreateRowset("SELECT rowid,fname,lname FROM emp",,;
	"INSERT INTO emp values (:fname,:lname)",;
	"DELETE FROM emp WHERE rowid=:rowid",;
	"UPDATE emp SET fname=:fname,lname=:lname WHERE rowid=:rowid")

If amount of matched rows can't be estimated two parameters <bEval> and <nEvery> will be helpful. Code block <bEval> will be executed during fetching process after each fetched <nEvery> rows. If it returns .F. the process breaks. Thus you could make a progressbar for huge result sets and cancel fetching. This example prints '.' per 100 rows and can be cancelled by pressing ESC.

rs := conn:CreateRowset("SELECT * FROM hugetable",,,,,,,,,,;
	{|| qqout("."), inkey() != K_ESC},100)

For the same reason (when amount of matched rows can't be estimated) you may direct TRowset to not fetch all found rows immediately, but fetch them on demand. There is another one parameter <lNoFetch> for this purpose. If passed .T., CreateRowset() terminates at once. But number of matched rows can't be obtained while not fetched rows are remained. To fetch the rest of them TRowset:FetchAll() function could be used. TRowset:Fetched() returns number of rows fetched at the moment. For example:

rs := conn:CreateRowset("SELECT * FROM hugetable",,,,,,,,,.T.)
rs:Gotop()
? rs:Fetched() // 1
? rs:Lastrec() // 0
for i:=1 to 100
	rs:Skip()
	? rs:Fetched() // 2,3,...,101
next
rs:FetchAll()
? rs:Lastrec() == rs:Fetched() // .T.

TRowset supports so-called 'local orders'. 'Local order' is an index created on the client side and it allows to change an order of rows in the set. It is mostly the same as standard RDD indices, but it's time life is limited by row set's life time, i.e. it is located in the memory and does not occupy any files. TRowset:CreateOrder() creates an order with given name, TRowset:SetOrder() activates an order. For example:

rs := conn:CreateRowset("SELECT fname,lname FROM emp")

// create an order 'Firstname' on the 'fname' field. Key length is 20 chars.
rs:CreateOrder("Firstname","fname",20)

// create an order 'Lastname' on the 'lname' field. Key length is 20 chars.
rs:CreateOrder("Lastname","lname",20)

// create an order 'Fullname' on the both 'fname' and 'lname' fields.
// Key length is 40 chars.
rs:CreateOrder("Fullname",{|rs| rs:GetValue("fname")+rs:GetValue("lname")},20)

rs:SetOrder("Firstname")
rs:Browse() // show rows sorted by first name

rs:SetOrder("Lastname")
rs:Browse() // show rows sorted by last name

rs:SetOrder("Fullname")
rs:Browse() // show rows sorted by fname and lname

Now we shall proceed to the description of SQL related classes and functons.