Servlet NewJavaServlet Test

CHAPTER 12 
■ 
DATABASES AND JYTHON: OBJECT RELATIONAL MAPPING AND USING JDBC 
 
245 
 
# Obtain information about the cursor and the query 
>>> cursor = conn.cursor() 
# List all tables 
>>> cursor.tables(None, None, '%', ('TABLE',)) 
>>> cursor.fetchall() 
[(None, u'public', u'city', u'TABLE', None), (None, u'public', u'country', u'TABLE', None), 
(None, u'public', u'countrylanguage', u'TABLE', None), (None, u'public', u'test', u'TABLE', 
None)] 
Data Manipulation Language and Data Definition Language 
Any application that will manipulate data contained in a RDBMS must be able to issue Data 
Manipulation Language (DML). Of course, DML consists of issuing statements such as INSERT, 
UPDATE, and DELETE. . .the basics of CRUD programming. zxJDBC makes it rather easy to use DML in a 
standard cursor object. When doing so, the cursor will return a value to provide information about the 
result. A standard DML transaction in JDBC uses a prepared statement with the cursor object, and 
assigns the result to a variable that can be read afterwards to determine whether the statement 
succeeded. 
ZxJDBC also uses cursors to define new constructs in the database using Data Definition Language 
(DDL). Examples of doing such are creating tables, altering tables, creating indexes, and the like. 
Similarly to performing DML with zxJDBC, a resulting DDL statement returns a value to assist in 
determining whether the statement succeeded or not. 
In the next couple of examples, we’ll create a table, insert some values, delete values, and finally 
delete the table. 
Listing 12-19. Using DML 
# Create a table named PYTHON_IMPLEMENTATIONS 
>>> stmt = "create table python_implementations (id integer, python_implementation varchar, 
current_version varchar)" 
>>> result = cursor.execute(stmt) 
>>> print result 
None 
>>> cursor.tables(None, None, '%', ('TABLE',)) 
# Ensure table was created 
>>> cursor.fetchall() 
[(None, u'public', u'city', u'TABLE', None), (None, u'public', u'country', u'TABLE', None), 
(None, u'public', u'countrylanguage', u'TABLE', None), (None, u'public', 
u'python_implementations', u'TABLE',   None), (None, u'public', u'test', u'TABLE', None)] 
 
# Insert some values into the table 
>>> stmt = "insert into PYTHON_IMPLEMENTATIONS values (?, ?, ?)" 
>>> result = cursor.executemany(stmt, [1,'Jython','2.5.1']) 
>>> result = cursor.executemany(stmt, [2,'CPython','3.1.1']) 
>>> result = cursor.executemany(stmt, [3,'IronPython','2.0.2']) 
>>> result = cursor.executemany(stmt, [4,'PyPy','1.1']) 
>>> conn.commit() 
 
# Query the database 
>>> cursor.execute("select python_implementation, current_version from 
python_implementations") 
>>> cursor.rowcount 
www.it-ebooks.info

---

CHAPTER 12 
■
 DATABASES AND JYTHON: OBJECT RELATIONAL MAPPING AND USING JDBC 
 
246 
 
4 
>>> cursor.fetchall() 
[(u'Jython', u'2.5.1'), (u'CPython', u'3.1.1'), (u'IronPython', u'2.0.2'), (u'PyPy', 
u'1.1')] 
 
# Update values and re-query 
>>> stmt = "update python_implementations set python_implementation = 'CPython -Standard 
Implementation' where id = 2" 
>>> result = cursor.execute(stmt) 
>>> print result 
None 
>>> conn.commit() 
>>> cursor.execute("select python_implementation, current_version from 
python_implementations") 
>>> cursor.fetchall() 
[(u'Jython', u'2.5.1'), (u'IronPython', u'2.0.2'), (u'PyPy', u'1.1'), (u'CPython -Standard 
Implementation', u'3.1.1')] 
It is a good practice to make use of bulk inserts and updates. Each time a commit is issued it incurs a 
performance penalty. If DML statements are grouped together and then followed by a commit, the 
resulting transaction will perform much better. Another good reason to use bulk DML statements is to 
ensure transactional safety. It is likely that if one statement in a transaction fails, all others should be 
rolled back. As mentioned previously in the chapter, using a try/except clause will maintain 
transactional dependencies. If one statement fails then all others will be rolled back. Likewise, if they all 
succeed then they will be committed to the database with one final commit. 
Calling Procedures 
Database applications often make use of procedures and functions that live inside the database. Most 
often these procedures are written in a SQL procedural language such as Oracle’s PL/SQL or 
PostgreSQL’s PL/pgSQL. Writing database procedures and using them with external applications such 
written in Python, Java, or the like makes lots of sense, because procedures are often the easiest way to 
work with data. Not only are they running close to the metal since they are in the database, but they also 
perform much faster than say a Jython application that needs to connect and close connections on the 
database. Since a procedure lives within the database, there is no performance penalty due to 
connections being made. 
ZxJDBC can easily invoke a database procedure just as JDBC can do. This helps developers to create 
applications that have some of the more database-centric code residing within the database as 
procedures, and other application-specific code running on the application server and interacting 
seamlessly with the database. In order to make a call to a database procedure, zxJDBC offers the 
callproc() method which takes the name of the procedure to be invoked. In Listing 12-20, we create a 
relatively useless procedure and then call it using Jython (Listing 12-21). 
Listing 12-20. PostgreSQL Procedure 
CREATE OR REPLACE FUNCTION proc_test( 
  OUT out_parameter CHAR VARYING(25) ) 
AS $$ 
DECLARE 
BEGIN 
  SELECT python_implementation 
    INTO out_parameter 
www.it-ebooks.info

---