An SQL Primer by Eric Pankoke.

SQLite is a nice, lightweight database available for both the PC and PocketPC, and can be accessed through PPL using the Sql.ppl header. If you are interested in adding database support to your applications, I strongly suggest giving SQLite a try, since the database format is identical between the Windows and Windows CE platforms. Here’s a quick look at how to use SQLite.

Of course, you’ll first want to open the database. You must keep in mind that SQLite’s open function will create a database if one with the specified name does not exist, so if your application should not run unless using a database supplied with the install, make sure you check for the existence of the file before calling the open function.

if not (FileExists(AppPath$ + “mydata.db”))
  ShowMessage(“Data does not exist.  Please reinstall application”);
  //Exit function or possibly application
end;

db$ = SqlOpen(AppPath$ + “mydata.db”);
if(db$ == 0)
  ShowMessage(“error opening database”);
  //Exit function or possibly application
end;

Now you have a variable called db$ that holds a reference to your database. To retrieve data from a table, simply do the following:

rs$ = SqlExec(db$, “SELECT * FROM tblName”, &data$, &rows$, &cols$);
if(rs$ <> SQLITE_OK)
  ShowMessage(“Error retrieving data”);
  //Exit function or possibly application
end;

If rows$ is greater than 0, data was found in the table. Data$ is a list containing all of the elements retrieved from the SqlExec command. The first “row” in the list contains the name of each column. To navigate through the data, you could do something like the following:

for(row_cnt$, 1, row$)
  for(col_cnt$, 1, cols$)
    goto(data$, col_cnt$ - 1);
    data_name$ = data$;
    goto(data$, (row_cnt$ * cols$) – 1 + (col_cnt$ - 1));
    data_value$ = data$;
    ShowMessage(data_name$ + “ = “ + data_value$);
  end;
end;

Finally, you need to make sure you close the database. To do this, simply call the following:

SqlClose(db$);

A demo called SqlDemo.ppl comes with PPL, demonstrating some more SQLite commands, such as creating tables and inserting data. For a comprehensive list of the commands supported by SQLite, check out this web site: http://www.sqlite.org. Remember that PPL currently supports version 2.8.x of SQLite, so information specific to version 3.x of SQLite don’t apply here.