Programming i!-Database Plus
11
i!-Database Plus and DBWizard
Once the SQL has been generated, the request is entered into a queue to be sent to the web server.
The parameters for
DB_ADD_TO_QUE
are the queue structure for the connection, a tag, the SQL
statement and a list box structure. The tag will be returned by the web server to identify the results.
Tags should be unique for every query or sets of queries generated. (In this case, the tags are shared
for all functions that operate on the same table.) The list box structure tells the server how many
records we want to read starting from what position. Normally, you only need to read one page at a
time and the list box routines manage that. The list box value has no effect if you are not reading
from the database.
You may notice the calls to
DB_STRING_REPLACE
. These calls are used to replace any single
quotes with two single quotes. SQL represents a single quote as two single quotes, like Axcess and
NetLinx do.
Sometimes, the column names contains other characters that SQL considers invalid, like spaces and
dashes. If you are using column names with these characters or continually get SQL syntax errors,
try wrapping the column names in a pair of brackets
[ ]
. This is SQL's way of handling invalid
characters.
(*********************************************)
(* NAME: DB_DELETE_TITLES *)
(***************************************************************************)
(* Format query to delete an entry of TITLES data *)
(***************************************************************************)
DEFINE_FUNCTION DB_DELETE_TITLES(SLONG slTitleID)
STACK_VAR
CHAR strSQL[1000]
_sDB_LISTBOX sTempListBox
{
(* BUILD A QUERY STRING *)
strSQL = "'DELETE FROM Titles WHERE TitleID = ',ITOA(slTitleID)"
(* SEND THE QUERY *)
DB_ADD_TO_QUE(sDB_QUE,'TITLES',strSQL,sTempListBox)
}
(*********************************************)
(* NAME: DB_ADD_TITLES *)
(***************************************************************************)
(* Format query to add an entry to TITLES data *)
(***************************************************************************)
DEFINE_FUNCTION DB_ADD_TITLES(CHAR strArtist[128],
CHAR strCopyright[128],
CHAR strLabel[128],
CHAR strReleaseDate[128],
CHAR strTitle[128],
SLONG slTitleID)
STACK_VAR
CHAR strSQL[1000]
_sDB_LISTBOX sTempListBox
{
(* ESCAPE ANY SINGLE QUOTES *)
strArtist = DB_STRING_REPLACE(strArtist,"39","39,39") (* ' FOR '' *)
strCopyright = DB_STRING_REPLACE(strCopyright,"39","39,39") (* ' FOR '' *)
strLabel = DB_STRING_REPLACE(strLabel,"39","39,39") (* ' FOR '' *)
strReleaseDate = DB_STRING_REPLACE(strReleaseDate,"39","39,39") (* ' FOR ''
*)
strTitle = DB_STRING_REPLACE(strTitle,"39","39,39") (* ' FOR '' *)
(* BUILD A QUERY STRING *)
strSQL = "'INSERT INTO Titles (Artist, Copyright, Label, ReleaseDate, Title,
TitleID) ',
Continued
