Like most web programming languages, the moto language was designed with database interaction
in mind. Extensions are included with moto in order to facilitate interaction with Postgres and
MySQL databases. To connect to one of these databases you must use the appropriate codex.db library
$use("codex.db.mysql")
$use("codex.db.pgsql")
| |
Prior to using a database you must connect to it, this is done in moto by constructing a connection object
$declare(MySQLConnection myconn = new MySQLConnection(server address,database name,user,password))
$declare(PGConnection pgconn = new PGConnection(server address,database name,user,password))
| |
Once you have a connection to the database you can issue SQL queries against it.
Queries results are returned in a ResultSet.
$declare(String query =
"SELECT cardsets.id,cardsets.name,cardsets.shortname,count(print.id) AS cardcount ,sum(price.buy) AS setprice,sum(price.diff) FROM cardsets,print,price WHEREprice.print=print.id AND print.edition=cardsets.id GROUP BY print.edition")
$switch(getValue("_orderby","Set"))
$case("Set")
$do(query = query + " ORDER BY cardsets.name")
$case("Cards")
$do(query = query + " ORDER BY cardcount DESC")
$case("Price*")
$do(query = query + " ORDER BY setprice DESC")
$endswitch
$declare(MySQLResultSet rset = conn.query(query))
| |
Those results are then iterated over and either displayed or used in other computations. The result set object
keeps track of what row you are currently looking at. Methods are called on the result set object to retrieve
individual columns of the current row.
$while( rset.next())
<tr>
<td align=center><img src="images/exp_sym_$(rset.getString(2)).gif"></td> <td >
<a href="cardlist.moto?sid=$(
getState().getSID()
)&searchby=set&set=$(
rset.getString(0)
)">$(rset.getString(1))</a>
</td> <td align=center >
$(rset.getInt(3))
</td> <td align=center ><nobr>
$("$ ")$(rset.getString(4))
$if(rset.getFloat(5) > 0)
<img src="images/uparrow1.gif">
$elseif(rset.getFloat(5) < 0)
<img src="images/downarrow2.gif">
$endif
</nobr></td>
</tr>
$endwhile
| |
The result set method next() must be called prior to retrieving values for fields in the result set. This method
returns true if there are more rows in the result set then scrolls to the next one.
Values for specific columns in the result set are returned either by specifying the column number
(as in the above example) or by referencing the column name. Column names are not case sensitive.
|
$(rset.getString("column name"))
| |
To find out how many rows were returned in the result set, call the method size() on the result set object
You can also execute SQL inserts, updates, or deletes against the database.
This occurs via the update method of the Connection object. This method returns the
number of rows inserted, updated, or deleted by the method.
$if(rset.next())
$do(deckid=rset.getInt(0))
$do(conn.update("DELETE FROM deckcards WHERE deck="+str(deckid)))
$else
$do(conn.update(
"INSERT INTO deck VALUES (0, '"+
conn.escape(getValue("_name"))+"','"+
conn.escape(getValue("_creator"))+"',"+
str(uid)+",0)"
))
$do(deckid = conn.insertID())
$endif
$do(tok= new Tokenizer(ids,'|'))
$while((curID=tok.next()) != null)
$declare(String curQTY =getValue("_q"+curID))
$if(curQTY != null && curQTY ne "0")
$do(conn.update(
"INSERT INTO deckcards VALUES ("+str(deckid)+","+curID+","+curQTY+",0)"))
$endif
$do(curQTY =getValue("_s"+curID))
$if(curQTY != null && curQTY ne "0")
$do(conn.update(
"INSERT INTO deckcards VALUES ("+str(deckid)+","+curID+","+curQTY+",1)"))
$endif
$endwhile
| |
When inserting String values into the database you should first escape the string value being inserted.
This is accomplished by calling the connection method escape() on the value to be inserted. Binary
data may also be escaped in this way for insertion into BLOB fields.
The escape function effectively backslashes single quotes and other restricted characters.
conn.escape(String value to be escaped)
conn.escape(byte[] to be escaped)
| |
Once connected to a database you can retrieve a list of tables in the database as a result set by
calling the listTables() method on your connection.
$do(rset=conn.listTables())
$while(rset.next())
$(rset.getString(0))
$endwhile
| |
Once you have a result set in hand you can retrieve the names and types of the columns returned
$declare(int cols=rset.getColumns())
$declare(int i)
$for(i=0;i<cols;i++)
$(rset.getColumnName(i))
$(rset.getColumnType(i))
$endfor
| |
ResultSets cannot be stored in the session or the context since the results
are not stored in shared memory. Yet caching queries often provides a very good performance
benefit. To do this, TabularData objects have been provided to store result sets in shared memory.
The TabularData object is provided in the codex.util extension.
$use("codex.util")
$declare(TabularData ocs)
$if((ocs=<TabularData>getContext().get(query)) == null)
$do(ocs = conn.query(query).store())
$do(getContext().promote(ocs))
$do(getContext().put(query,ocs))
$endif
| |
TabularData objects are similar to result sets except the entire result is stored in
memory. Thus rows my be accessed in an arbitrary order.
$for(i=0 ;i < ocs.getRows() ;i++)
$do(curSet = ocs.getInt(i,0))
$endfor
| |
The standard field retrieval functions getInt,getString,getFloat etc... take both a row and a column (
or a row and a column name)
TabularData objects are also modifyable and may be used as a convenient data structure with which to
store arbitrary table data
$declare(TabularData cardData)
$if((cardData =<TabularData>getContext().get("CardList")) == null)
$declare(MySQLResultSet cacherset = conn.query("SELECT max(id) from print"))
$do(cacherset.next())
$do(cardData = new TabularData(
cacherset.getInt(0)+1,
10,
"shortname,name,card,print,color,type,cost,rarity,price,diff",
"String,String,int,int,char,String,String,char,float,float")
)
$do(cacherset = conn.query(
"SELECT cardsets.shortname,card.name,card.id,print.id" +
",card.color,card.type,card.cost,card.rarity,price.buy,price.diff "+
"FROM cardsets,card,print LEFT OUTER JOIN price "+
"ON price.print=print.id "+
"WHERE card.id = print.card "+
"AND print.edition = cardsets.id"
))
$while(cacherset.next())
$do(cardData.setString(cacherset.getInt(3),0,cacherset.getString(0)))
$do(cardData.setString(cacherset.getInt(3),1,cacherset.getString(1)))
$do(cardData.setInt(cacherset.getInt(3),2,cacherset.getInt(2)))
$do(cardData.setInt(cacherset.getInt(3),3,cacherset.getInt(3)))
$do(cardData.setChar(cacherset.getInt(3),4,cacherset.getChar(4)))
$do(cardData.setString(cacherset.getInt(3),5,cacherset.getString(5)))
$do(cardData.setString(cacherset.getInt(3),6,cacherset.getString(6)))
$do(cardData.setChar(cacherset.getInt(3),7,cacherset.getChar(7)))
$do(cardData.setFloat(cacherset.getInt(3),8,cacherset.getFloat(8)))
$do(cardData.setFloat(cacherset.getInt(3),9,cacherset.getFloat(9)))
$endwhile
$do(getContext().promote(cardData))
$do(getContext().put("CardList",cardData))
$endif
| |
Copyright © 2000 - 2003 David Hakim
