The primary way of interacting with the Host Integrator Server using the JDBC connector is SQL statements. Host Integrator supports a subset of the SQL language that allows retrieving information from the server, adding new information, deleting information, and updating information. See Creating SQL-Based Queries for a full description of the allowed SQL subset and how it relates to tables constructed in the Design Tool.
To execute an SQL statement using the JDBC connector, use one of the execute methods in the java.sql.Statement class. There are three forms of execute: one for any SQL statement, one for queries, and one for updates, selects, inserts, and deletes. The most common form, executeQuery, is for retrieving information using a SELECT SQL statement, (queries). An example of using executeQuery() is:
Statement stmt = myConnection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM Accounts where AcctNum=20000");
This query asks the Host Integrator Server to fetch all of the columns from the Accounts table for the account number (20000) specified. The JDBC ResultSet object, rs, will hold the record that the Host Integrator returns. (There is a single record since AcctNum selects a unique record.)
To update, add, or delete information in a table, use the executeUpdate() JDBC method. For example, the following shows how to insert a record:
Statement stmt = myConnection.createStatement();
String acctCols = "AcctNum, LastName, FirstName, MiddleInitial, Title, Phone, Address1, Address2,";
acctCols += "NumCardsIssued, DayIssued, MonthIssued, YearIssued, Reason, CardCode, ApprovedBy";
String acctVals = "20005, 'Smith', 'Steven', 'W', 'exec','2065551234','1342 15th Street E', 'Seattle, WA',";
acctVals += "2,'06','01','99','M','C','GWB'";
int recordsUpdated = stmt.executeUpdate("INSERT INTO Accounts ("+acctCols+") VALUES ("+acctVals+")");
Finally, an SQL statement can be executed with the java.sql.statement execute() method. For a query statement, the resulting recordset object can be retrieved by calling the getResultSet() method on the statement object.
|