The javaxt.sql.Connection class is used primarily to open and close connections to a database. Once a database connection has been established, you can execute queries and browse database metadata (e.g. table info, column info, etc).
Basic UsageThe basic flow is quite simple:
//Instantiate the Connection Class javaxt.sql.Connection conn = new javaxt.sql.Connection(); //Open a connection to the database conn.open(db); //Do something with the connection. In this case, we will open a Recordset and execute a query javaxt.sql.Recordset rs = new javaxt.sql.Recordset(); rs.open("SELECT * FROM EMPLOYEE", conn); rs.close(); //Close connection conn.close();
How to Open a Database Connection
There are currently 3 different ways to open a database connection:
It is imparative that you close the database connection after you have executed your queries. This is especially true when using the Connection in a JDBC Connection Pool.
Executing Raw SQL Commands
The javaxt.sql.Connection object is typically used with the javaxt.sql.Recordset class to execute queries. However, you can also use the javaxt.sql.Connection object to execute raw SQL statements without invoking the Recordset object. Typically, these SQL commands don't yield a resultset (e.g. Create/Drop Table, Create/Drop Index, Grant Permissions, Backup/Restore, etc).
Here's a really simple example of how to create a table in SQLServer using a live javaxt.sql.Connection.
//Instantiate the Connection Class javaxt.sql.Connection conn = new javaxt.sql.Connection(); //Open a connection to the database conn.open(db); //Execute SQL Statement conn.execute( "CREATE TABLE EMPLOYEE(" + "ID INTEGER IDENTITY(1,1) NOT NULL UNIQUE," + "FIRST_NAME nvarchar(50)," + "LAST_NAME nvarchar(50)," + "CONSTRAINT pk_employee PRIMARY KEY (ID)" + ");" ); //Close the connection conn.close();
You can delete the newly created table with a simple "DROP" command. Example:
conn.execute("DROP TABLE EMPLOYEE");