Packages--JLink class is loaded but cannot be found?
- To: mathgroup at smc.vnet.net
- Subject: [mg66548] Packages--JLink class is loaded but cannot be found?
- From: wbogorad at yahoo.com
- Date: Fri, 19 May 2006 03:40:40 -0400 (EDT)
- Sender: owner-wri-mathgroup at wolfram.com
Hi, I'm very new to Mathematica and JLink but I know a thing or two about Java. I'm using Mathematica 5.0 and I'm trying to follow Brian Higgins' example (http://www.higgins.ucdavis.edu/JLink/JavaDatabaseConnect.nb) to connect to Oracle (not MySQL database and I'm having a strange problem that the system seems is loading oracle jdbc driver but than cannot see it. Here how I do it Needs["JLink`"] InstallJava[]; \ AddToClassPath["c:\oracle\product\9.2\db_1\jdbc\lib\ojdbc14.jar"] --- at this point the system seems to be happy with the classpath: {C:\Program Files\WolframResearch\Mathematica\5.0\SystemFiles\Java\, \ C:\Program Files\WolframResearch\Mathematica\5.0\.\, \ c:\oracle\product\9.2\db_1\jdbc\lib\ojdbc14.jar, C:\Program \ Files\WolframResearch\Mathematica\5.0\SystemFiles\Java\Windows\lib\tools.jar} Off[General::"spell1"] myDriverManager = LoadJavaClass["java.sql.DriverManager"] Methods[myDriverManager] myClass = LoadJavaClass["java.lang.Class"] LoadJavaClass["com.wolfram.jlink.JLinkClassLoader"]; cls = JLinkClassLoader`classFromName["oracle.jdbc.driver.OracleDriver"] cls[newInstance[]] -- the output is: «JavaObject[oracle.jdbc.driver.OracleDriver]» so it seems that the driver instance is created. myDriverList = java`sql`DriverManager`getDrivers[] s = {}; While[myDriverList[hasMoreElements[]], AppendTo[s, \ myDriverList[nextElement[]]]] s -- but when I try to get a list of loaded drivers the list is empty; the output is: {} My question is what have I done wrong? Thank you very much for your help. Here is a complete nb file: (************** Content-type: application/mathematica ************** CreatedBy='Mathematica 5.0' Mathematica-Compatible Notebook This notebook can be used with any Mathematica-compatible application, such as Mathematica, MathReader or Publicon. The data for the notebook starts with the line containing stars above. To get the notebook into a Mathematica-compatible application, do one of the following: * Save the data starting with the line of stars above into a file with a name ending in .nb, then open the file inside the application; * Copy the data starting with the line of stars above to the clipboard, then use the Paste menu command inside the application. Data for notebooks contains only printable 7-bit ASCII and can be sent directly in email or through ftp in text mode. Newlines can be CR, LF or CRLF (Unix, Macintosh or MS-DOS style). NOTE: If you modify the data for this notebook not in a Mathematica- compatible application, you must delete the line below containing the word CacheID, otherwise Mathematica-compatible applications may try to use invalid cache data. For more information on notebooks and Mathematica-compatible applications, contact Wolfram Research: web: http://www.wolfram.com email: info at wolfram.com phone: +1-217-398-0700 (U.S.) Notebook reader applications are available free of charge from Wolfram Research. *******************************************************************) (*CacheID: 232*) (*NotebookFileLineBreakTest NotebookFileLineBreakTest*) (*NotebookOptionsPosition[ 62602, 1579]*) (*NotebookOutlinePosition[ 94837, 2644]*) (* CellTagsIndexPosition[ 94793, 2640]*) (*WindowFrame->Normal*) Notebook[{ Cell[CellGroupData[{ Cell["Using JDBC and J/Link to Connect with an Oracle Database", "Title", TextAlignment->Center, TextJustification->0], Cell[CellGroupData[{ Cell["", "Subsubsection"], Cell["\<\ All rights reserved. You may copy and modify these notebooks and \ its content only for internal use in your organization, provided that credit \ is given to Brian G. Higgins as the original author. All other uses require \ the written permission of the author. In particular, these notebooks and \ their content cannot be bought or sold or exchanged for profit, or \ incorporated into material that is bought or sold or exchanged for profit. \ The notebooks are provided \"as is\" without express or implied warranty. \ \ \>", "Text"], Cell["Last Revision October 2004", "Text"] }, Closed]], Cell[CellGroupData[{ Cell["Overview", "Section"], Cell[TextData[{ "In this tutorial we show how to use the JDBC driver MySQL Connector/J to \ connect to a MySQL relational database via a TCIP connection. The MySQL \ Connector/J is an implementation of Sun's JDBC 3.0 API for the MySQL \ relational database server ( ", ButtonBox["http://www.mysql.com";, ButtonData:>{ URL[ "http://www.mysql.com";], None}, ButtonStyle->"Hyperlink"], " ). It strives to conform as much as possible to the JDBC API as \ specified by JavaSoft. " }], "Text"], Cell["\<\ This notebook we developed on a Macintosh computer running Mac OS X \ 10.3.5 with Java 1.4.2.\ \>", "Text"], Cell["\<\ To establish a connection with a MySQL database we need to load the JDBC \ driver, register the driver with the Driver Manager, and then make a \ connection. Once we have made a connection and have obtained a connection \ object, we can execute a query to the database on our connection object. \ \>", "Text"], Cell["We need to load the J/Link package", "Text"], Cell[BoxData[ \(Needs["\<JLink`\>"]\)], "Input"], Cell["and then launch the Java runtime", "Text"], Cell[CellGroupData[{ Cell[BoxData[ RowBox[{\(InstallJava[]\), ";", FormBox[\(AddToClassPath["\<c:\oracle\product\9.2\db_1\jdbc\lib\ojdbc14.\ jar\>"]\), "TraditionalForm"]}]], "Input"], Cell[BoxData[ \({"C:\\Program \ Files\\WolframResearch\\Mathematica\\5.0\\SystemFiles\\Java\\", "C:\\Program Files\\WolframResearch\\Mathematica\\5.0\\.\\", "c:\\oracle\\product\\9.2\\db_1\\jdbc\\lib\\ojdbc14.jar", "C:\\Program \ Files\\WolframResearch\\Mathematica\\5.0\\SystemFiles\\Java\\Windows\\lib\\\ tools.jar"}\)], "Output"] }, Open ]], Cell[TextData[{ "In this tutorial we will use a color code to make clear which variables in \ ", StyleBox["Mathematica", FontSlant->"Italic"], " code are Java related. Java class variables will be displayed in \"", StyleBox["magenta", FontColor->RGBColor[1, 0, 1]], "\" , Java objects will be displayed in \"", StyleBox["blue", FontColor->RGBColor[0, 0, 1]], "\" and Java methods/fields in \"", StyleBox["red", FontColor->RGBColor[1, 0, 0]], "\". ", StyleBox["Mathematica", FontSlant->"Italic"], " uses the prefix operator \"@\" instead of Java's \".\"(dot) as the member \ access operator. Thus suppose we have an Java object ", StyleBox["foo", FontColor->RGBColor[0, 0, 1]], " and want to access its method ", StyleBox["set", FontColor->RGBColor[1, 0, 0]], "() . In ", StyleBox["Mathematica", FontSlant->"Italic"], " we would write ", StyleBox["foo", FontColor->RGBColor[0, 0, 1]], "@", StyleBox["set", FontColor->RGBColor[1, 0, 0]], "[], while in Java we would write ", StyleBox["foo", FontColor->RGBColor[0, 0, 1]], ".", StyleBox["set", FontColor->RGBColor[1, 0, 0]], "( ). Now since ", StyleBox["foo", FontColor->RGBColor[0, 0, 1]], "@", StyleBox["set", FontColor->RGBColor[1, 0, 0]], " is the prefix form for ", StyleBox["foo", FontColor->RGBColor[0, 0, 1]], "[", StyleBox["set", FontColor->RGBColor[1, 0, 0]], "], we can also represent ", StyleBox["foo", FontColor->RGBColor[0, 0, 1]], "@", StyleBox["set", FontColor->RGBColor[1, 0, 0]], "[] in standard form as ", StyleBox["foo", FontColor->RGBColor[0, 0, 1]], "[", StyleBox["set", FontColor->RGBColor[1, 0, 0]], "[]]. In these notes we will use the standard form exclusively." }], "Text"], Cell["\<\ This notebook we developed on a Macintosh computer running Mac OS X 10.3.5 \ with Java 1.4.2.\ \>", "Text"], Cell["For convenience we will suppress all messages", "Text"], Cell[BoxData[ \(Off[General::"\<spell1\>"]\)], "Input"] }, Open ]], Cell[CellGroupData[{ Cell["Installing the MySQL JDBC Driver", "Section"], Cell[TextData[{ "We will need to install an appropriate JDBC (Java Database Connectivity) \ driver in order to access our data from a MySQL database The appropriate \ driver can be downloaded from ", StyleBox[" ", FontFamily->"Helvetica"], ButtonBox["http://dev.mysql.com/doc/connector/j/en/#id2486573";, ButtonData:>{ URL[ "http://dev.mysql.com/doc/connector/j/en/#id2486573";], None}, ButtonStyle->"Hyperlink"] }], "Text"], Cell["\<\ The name of the JDBC driver for MySQL is called \ \"com.mysql.jdbc.Driver\"\ \>", "Text"], Cell["\<\ It is important that the MySQL driver be in the CLASSPATH of your \ computer. This notebook was created on Mac OSX 10.3.5 (Panther). Thus the \ mysql-connector-java-3.0.11-stable-bin.jar was placed in \ Library/Java/Extensions and the following line was added to my .bash_profile \ file\ \>", "Text"], Cell["\<\ export \ CLASSPATH=/Library/Java/Extensions/mysql-connector-java-3.0.11-stable-bin.jar:\ $CLASSPATH\ \>", "Program"] }, Open ]], Cell[CellGroupData[{ Cell["Registering with Driver Manager", "Section"], Cell["\<\ The DriverManager class establishes the connection to the data \ base, via the JDBC driver. Thus it is necessary to load the DriverManger at \ the outset\ \>", "Text"], Cell[CellGroupData[{ Cell[BoxData[ \(myDriverManager = LoadJavaClass["\<java.sql.DriverManager\>"]\)], "Input"], Cell[BoxData[ \(JavaClass["java.sql.DriverManager", 0]\)], "Output"] }, Open ]], Cell["\<\ There are several static methods available in the DriverManager \ class. Here is a list\ \>", "Text"], Cell[CellGroupData[{ Cell[BoxData[ RowBox[{"Methods", "[", StyleBox["myDriverManager", FontColor->RGBColor[1, 0, 1]], "]"}]], "Input"], Cell[BoxData[ InterpretationBox[GridBox[{ {"\<\"static void deregisterDriver(java.sql.Driver) throws \ java.sql.SQLException\"\>"}, {"\<\"boolean equals(Object)\"\>"}, {"\<\"Class getClass()\"\>"}, {"\<\"static java.sql.Connection getConnection(String, \ java.util.Properties) throws java.sql.SQLException\"\>"}, {"\<\"static java.sql.Connection getConnection(String, String, \ String) throws java.sql.SQLException\"\>"}, {"\<\"static java.sql.Connection getConnection(String) throws \ java.sql.SQLException\"\>"}, {"\<\"static java.util.Enumeration getDrivers()\"\>"}, {"\<\"static java.sql.Driver getDriver(String) throws \ java.sql.SQLException\"\>"}, {"\<\"static int getLoginTimeout()\"\>"}, {"\<\"static java.io.PrintStream getLogStream()\"\>"}, {"\<\"static java.io.PrintWriter getLogWriter()\"\>"}, {"\<\"int hashCode()\"\>"}, {"\<\"void notify()\"\>"}, {"\<\"void notifyAll()\"\>"}, {"\<\"static void println(String)\"\>"}, {"\<\"static void registerDriver(java.sql.Driver) throws \ java.sql.SQLException\"\>"}, {"\<\"static void setLoginTimeout(int)\"\>"}, {"\<\"static void setLogStream(java.io.PrintStream)\"\>"}, {"\<\"static void setLogWriter(java.io.PrintWriter)\"\>"}, {"\<\"String toString()\"\>"}, {"\<\"void wait(long, int) throws InterruptedException\"\>"}, {"\<\"void wait(long) throws InterruptedException\"\>"}, {"\<\"void wait() throws InterruptedException\"\>"} }, RowSpacings->1, ColumnSpacings->3, RowAlignments->Baseline, ColumnAlignments->{Left}], TableForm[ { "static void deregisterDriver(java.sql.Driver) throws \ java.sql.SQLException", "boolean equals(Object)", "Class getClass()", "static java.sql.Connection getConnection(String, \ java.util.Properties) throws java.sql.SQLException", "static java.sql.Connection getConnection(String, String, String) \ throws java.sql.SQLException", "static java.sql.Connection getConnection(String) throws \ java.sql.SQLException", "static java.util.Enumeration getDrivers()", "static java.sql.Driver getDriver(String) throws \ java.sql.SQLException", "static int getLoginTimeout()", "static java.io.PrintStream getLogStream()", "static java.io.PrintWriter getLogWriter()", "int hashCode()", "void notify()", "void notifyAll()", "static void println(String)", "static void registerDriver(java.sql.Driver) throws \ java.sql.SQLException", "static void setLoginTimeout(int)", "static void setLogStream(java.io.PrintStream)", "static void setLogWriter(java.io.PrintWriter)", "String toString()", "void wait(long, int) throws InterruptedException", "void wait(long) throws InterruptedException", "void wait() throws InterruptedException"}]]], "Output"] }, Open ]], Cell[TextData[{ "We register our JDBC driver using the static ", StyleBox["forName", FontSlant->"Italic", FontColor->RGBColor[1, 0, 0]], StyleBox["(String)", FontSlant->"Italic"], " method in the java.lang,Class to load the driver. But first, we need to \ load the \"java.lang.Class\" package" }], "Text"], Cell[CellGroupData[{ Cell[BoxData[ RowBox[{ StyleBox["myClass", FontColor->RGBColor[1, 0, 1]], "=", \(LoadJavaClass["\<java.lang.Class\>"]\)}]], "Input"], Cell[BoxData[ \(JavaClass["java.lang.Class", 2]\)], "Output"] }, Open ]], Cell[TextData[{ "\nWe use the static ", StyleBox["forName", FontSlant->"Italic", FontColor->RGBColor[1, 0, 0]], StyleBox["(string) ", FontSlant->"Italic"], "method to dynamically load the JDBC driver, This returns a Class object" }], "Text"], Cell[CellGroupData[{ Cell[BoxData[{ \(\(LoadJavaClass["\<com.wolfram.jlink.JLinkClassLoader\>"];\)\), "\n", \(cls\ = \ JLinkClassLoader`classFromName["\<oracle.jdbc.driver.OracleDriver\>"]\)}\ ], "Input"], Cell[BoxData[ InterpretationBox[\(\[LeftGuillemet]JavaObject[ "java.lang.Class"] \[RightGuillemet]\), JLink`Objects`JavaObject29010071388161]], "Output"] }, Open ]], Cell[TextData[{ "and we use the ", StyleBox["newInstance", FontSlant->"Italic", FontColor->RGBColor[1, 0, 0]], StyleBox["()", FontSlant->"Italic"], " method in the java.lang,Class to obtain a JDBC Driver object:" }], "Text"], Cell[CellGroupData[{ Cell[BoxData[ RowBox[{ StyleBox["cls", FontColor->RGBColor[0, 0, 1]], "[", RowBox[{ StyleBox["newInstance", FontColor->RGBColor[1, 0, 0]], "[", "]"}], "]"}]], "Input"], Cell[BoxData[ InterpretationBox[\(\[LeftGuillemet]JavaObject[ "oracle.jdbc.driver.OracleDriver"] \[RightGuillemet]\), JLink`Objects`JavaObject305969695293441]], "Output"] }, Open ]], Cell[TextData[{ "Let us see if the driver is listed by the DriverManager, We use the static \ ", StyleBox["getDrivers", FontSlant->"Italic", FontColor->RGBColor[1, 0, 0]], StyleBox["()", FontSlant->"Italic"], " method that returns a vector object" }], "Text"], Cell[CellGroupData[{ Cell[BoxData[ RowBox[{ StyleBox["myDriverList", FontColor->RGBColor[0, 0, 1]], "=", RowBox[{ StyleBox["java`sql`DriverManager`getDrivers", FontColor->RGBColor[1, 0, 0]], "[", "]"}]}]], "Input"], Cell[BoxData[ InterpretationBox[\(\[LeftGuillemet]JavaObject[ "java.util.Vector$1"] \[RightGuillemet]\), JLink`Objects`JavaObject252360903360513]], "Output"] }, Open ]], Cell[TextData[{ "We can use the ", StyleBox["hasMoreElements", FontSlant->"Italic", FontColor->RGBColor[1, 0, 0]], StyleBox["()", FontSlant->"Italic"], " and", StyleBox[" ", FontSlant->"Italic"], StyleBox["nextElement", FontSlant->"Italic", FontColor->RGBColor[1, 0, 0]], StyleBox["()", FontSlant->"Italic"], " methods to interrogate the contents of our vector object" }], "Text"], Cell[CellGroupData[{ Cell[BoxData[{\(s = {};\), "\[IndentingNewLine]", RowBox[{"While", "[", RowBox[{ RowBox[{ StyleBox["myDriverList", FontColor->RGBColor[0, 0, 1]], "[", RowBox[{ StyleBox["hasMoreElements", FontColor->RGBColor[1, 0, 0]], "[", "]"}], "]"}], ",", RowBox[{"AppendTo", "[", RowBox[{"s", ",", RowBox[{ StyleBox["myDriverList", FontColor->RGBColor[0, 0, 1]], "[", RowBox[{ StyleBox["nextElement", FontColor->RGBColor[1, 0, 0]], "[", "]"}], "]"}]}], "]"}]}], "]"}], "\[IndentingNewLine]", "s"}], "Input"], Cell[BoxData[ \({}\)], "Output"] }, Open ]], Cell["\<\ This shows that our JDBC driver is listed with the DriverManager.\ \ \>", "Text"] }, Open ]], Cell[CellGroupData[{ Cell["Making a Connection", "Section"], Cell["\<\ In this section we create a connection to our database . First we \ load the java.sql.Connection package to get a connection class\ \>", "Text"], Cell[CellGroupData[{ Cell[BoxData[ RowBox[{ StyleBox["myConnectionClass", FontColor->RGBColor[1, 0, 1]], "=", \(LoadJavaClass["\<java.sql.Connection\>"]\)}]], "Input"], Cell[BoxData[ \(JavaClass["java.sql.Connection", 6]\)], "Output"] }, Open ]], Cell[TextData[{ "We will connect to a test database called JLinkTestDB, using UserID: \ \"JLinkUser\" , and password: \"Brian\". The only privilege this user has is \ \"Select\". The URL for the MySQL database is www.higgins.ucdavis.edu. We use \ the static ", StyleBox["getConnection", FontSlant->"Italic", FontColor->RGBColor[1, 0, 0]], StyleBox["(string,string,string)", FontSlant->"Italic"], " method which returns a connection object" }], "Text"], Cell[CellGroupData[{ Cell[BoxData[ RowBox[{ StyleBox["conn", FontColor->RGBColor[0, 0, 1]], "=", RowBox[{ StyleBox["DriverManager`getConnection", FontColor->RGBColor[1, 0, 0]], "[", \("\<jdbc:oracle:thin:@localhost:1521:ARIK\>", "\<pbt\>", "\<pbt\ \>"\), "]"}]}]], "Input"], Cell[BoxData[ \(Java::"excptn" \(\(:\)\(\ \)\) "A Java exception occurred: java.sql.SQLException: No suitable driver\n\ \tat java.sql.DriverManager.getConnection(DriverManager.java:532)\n\tat \ java.sql.DriverManager.getConnection(DriverManager.java:171)\n\tat \ sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\n\tat \ sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)\ \n\tat sun.reflect.DelegatingMethodAccessorImpl.invoke(\ DelegatingMethodAccessorImpl.java:25)."\)], "Message"], Cell[BoxData[ \($Failed\)], "Output"] }, Open ]], Cell["We are now ready to execute a query using our connection", "Text"], Cell["\<\ This database contains a single table called antoineeqn2 that has \ 7 fields:\ \>", "Text"], Cell["\<\ Species_ID(int),Species_name(char(20)),PUnits(char(10)),TUnits(char(\ 10)),A(Float),B(Float),C(Float)\ \>", "Program"], Cell[TextData[{ "We will use this table to illustrate various Select operations. A version \ of this table is used on my ", ButtonBox["webMathematica", ButtonData:>{ URL[ "http://www.higgins.ucdavis.edu/webMathematica/MSP/Examples/\ vaporpressures"], None}, ButtonStyle->"Hyperlink"], " site ." }], "Text"] }, Open ]], Cell[CellGroupData[{ Cell["Executing a Query", "Section"], Cell["\<\ This database that we have connected to contains a single table \ called antoineeqn2; it has 7 fields as shown below\ \>", "Text"], Cell["\<\ Species_ID(int),Species_name(char(20)),PUnits(char(10)),TUnits(char(\ 10)),A(Float),B(Float),C(Float)\ \>", "Program"], Cell["\<\ This table give the Antoine parameters for computing the vapor \ pressure of a pure component based on the formula:\ \>", "Text"], Cell[BoxData[ \(\(\(\t\t\t\)\(P = Exp[A - B\/\(T + C\)]\)\)\)], "NumberedEquation"], Cell["where P is in kPa and T is in K", "Text"], Cell["\<\ We will use this table to illustrate how to implement various Select queries. \ Consider the methods associated with the connection object\ \>", "Text"], Cell[CellGroupData[{ Cell[BoxData[ RowBox[{"Methods", "[", StyleBox["conn", FontColor->RGBColor[0, 0, 1]], "]"}]], "Input", CellLabel->"In[13]:="], Cell[BoxData[ InterpretationBox[GridBox[{ {"\<\"boolean allowLoadLocalInfile()\"\>"}, {"\<\"boolean capitalizeDBMDTypes()\"\>"}, {"\<\"void clearWarnings() throws java.sql.SQLException\"\>"}, {"\<\"void close() throws java.sql.SQLException\"\>"}, {"\<\"void commit() throws java.sql.SQLException\"\>"}, {"\<\"java.sql.Statement createStatement(int, int, int) throws \ java.sql.SQLException\"\>"}, {"\<\"java.sql.Statement createStatement(int, int) throws \ java.sql.SQLException\"\>"}, {"\<\"java.sql.Statement createStatement() throws \ java.sql.SQLException\"\>"}, {"\<\"boolean equals(Object)\"\>"}, {"\<\"void finalize() throws Throwable\"\>"}, {"\<\"boolean getAutoCommit() throws java.sql.SQLException\"\>"}, {"\<\"String getCatalog() throws java.sql.SQLException\"\>"}, {"\<\"Class getClass()\"\>"}, {"\<\"boolean getClobberStreamingResults()\"\>"}, {"\<\"String getEncoding()\"\>"}, {"\<\"int getHoldability() throws java.sql.SQLException\"\>"}, {"\<\"long getIdleFor()\"\>"}, {"\<\"java.sql.DatabaseMetaData getMetaData() throws \ java.sql.SQLException\"\>"}, {"\<\"String getNegativeInfinityRep()\"\>"}, {"\<\"String getNotANumberRep()\"\>"}, {"\<\"String getPositiveInfinityRep()\"\>"}, {"\<\"java.util.TimeZone getServerTimezone()\"\>"}, {"\<\"int getTransactionIsolation() throws \ java.sql.SQLException\"\>"}, {"\<\"java.util.Map getTypeMap() throws \ java.sql.SQLException\"\>"}, {"\<\"java.sql.SQLWarning getWarnings() throws \ java.sql.SQLException\"\>"}, {"\<\"int hashCode()\"\>"}, {"\<\"boolean isClosed()\"\>"}, {"\<\"boolean isInteractiveClient()\"\>"}, {"\<\"boolean isNegativeInfinityRepIsClipped()\"\>"}, {"\<\"boolean isNotANumberRepIsClipped()\"\>"}, {"\<\"boolean isPositiveInfinityRepIsClipped()\"\>"}, {"\<\"boolean isReadOnly() throws java.sql.SQLException\"\>"}, {"\<\"boolean lowerCaseTableNames()\"\>"}, {"\<\"String nativeSQL(String) throws java.sql.SQLException\"\>"}, {"\<\"void notify()\"\>"}, {"\<\"void notifyAll()\"\>"}, {"\<\"java.sql.CallableStatement prepareCall(String, int, int, int) \ throws java.sql.SQLException\"\>"}, {"\<\"java.sql.CallableStatement prepareCall(String, int, int) \ throws java.sql.SQLException\"\>"}, {"\<\"java.sql.CallableStatement prepareCall(String) throws \ java.sql.SQLException\"\>"}, {"\<\"java.sql.PreparedStatement prepareStatement(String, int, int, \ int) throws java.sql.SQLException\"\>"}, {"\<\"java.sql.PreparedStatement prepareStatement(String, int, int) \ throws java.sql.SQLException\"\>"}, {"\<\"java.sql.PreparedStatement prepareStatement(String, int[]) \ throws java.sql.SQLException\"\>"}, {"\<\"java.sql.PreparedStatement prepareStatement(String, int) \ throws java.sql.SQLException\"\>"}, {"\<\"java.sql.PreparedStatement prepareStatement(String, String[]) \ throws java.sql.SQLException\"\>"}, {"\<\"java.sql.PreparedStatement prepareStatement(String) throws \ java.sql.SQLException\"\>"}, {"\<\"void releaseSavepoint(java.sql.Savepoint) throws \ java.sql.SQLException\"\>"}, {"\<\"void rollback(java.sql.Savepoint) throws \ java.sql.SQLException\"\>"}, {"\<\"void rollback() throws java.sql.SQLException\"\>"}, {"\<\"void setAutoCommit(boolean) throws \ java.sql.SQLException\"\>"}, {"\<\"void setCatalog(String) throws java.sql.SQLException\"\>"}, {"\<\"void setHoldability(int) throws java.sql.SQLException\"\>"}, {"\<\"void setProfileSql(boolean) throws \ java.sql.SQLException\"\>"}, {"\<\"void setReadOnly(boolean) throws java.sql.SQLException\"\>"}, {"\<\"java.sql.Savepoint setSavepoint(String) throws \ java.sql.SQLException\"\>"}, {"\<\"java.sql.Savepoint setSavepoint() throws \ java.sql.SQLException\"\>"}, {"\<\"void setTransactionIsolation(int) throws \ java.sql.SQLException\"\>"}, {"\<\"void setTypeMap(java.util.Map) throws java.sql.SQLException\"\ \>"}, {"\<\"void shutdownServer() throws java.sql.SQLException\"\>"}, {"\<\"boolean supportsIsolationLevel()\"\>"}, {"\<\"boolean supportsQuotedIdentifiers()\"\>"}, {"\<\"boolean supportsTransactions()\"\>"}, {"\<\"String toString()\"\>"}, {"\<\"boolean useCompression()\"\>"}, {"\<\"boolean useParanoidErrorMessages()\"\>"}, {"\<\"boolean useSSL()\"\>"}, {"\<\"boolean useStrictFloatingPoint()\"\>"}, {"\<\"boolean useStrictUpdates()\"\>"}, {"\<\"boolean useTimezone()\"\>"}, {"\<\"boolean useUnicode()\"\>"}, {"\<\"void wait(long, int) throws InterruptedException\"\>"}, {"\<\"void wait(long) throws InterruptedException\"\>"}, {"\<\"void wait() throws InterruptedException\"\>"} }, RowSpacings->1, ColumnSpacings->3, RowAlignments->Baseline, ColumnAlignments->{Left}], TableForm[ {"boolean allowLoadLocalInfile()", "boolean capitalizeDBMDTypes()", "void clearWarnings() throws java.sql.SQLException", "void close() throws java.sql.SQLException", "void commit() throws java.sql.SQLException", "java.sql.Statement createStatement(int, int, int) throws \ java.sql.SQLException", "java.sql.Statement createStatement(int, int) throws \ java.sql.SQLException", "java.sql.Statement createStatement() throws java.sql.SQLException", "boolean equals(Object)", "void finalize() throws Throwable", "boolean getAutoCommit() throws java.sql.SQLException", "String getCatalog() throws java.sql.SQLException", "Class getClass()", "boolean getClobberStreamingResults()", "String getEncoding()", "int getHoldability() throws java.sql.SQLException", "long getIdleFor()", "java.sql.DatabaseMetaData getMetaData() throws \ java.sql.SQLException", "String getNegativeInfinityRep()", "String getNotANumberRep()", "String getPositiveInfinityRep()", "java.util.TimeZone getServerTimezone()", "int getTransactionIsolation() throws java.sql.SQLException", "java.util.Map getTypeMap() throws java.sql.SQLException", "java.sql.SQLWarning getWarnings() throws java.sql.SQLException", "int hashCode()", "boolean isClosed()", "boolean isInteractiveClient()", "boolean isNegativeInfinityRepIsClipped()", "boolean isNotANumberRepIsClipped()", "boolean isPositiveInfinityRepIsClipped()", "boolean isReadOnly() throws java.sql.SQLException", "boolean lowerCaseTableNames()", "String nativeSQL(String) throws java.sql.SQLException", "void notify()", "void notifyAll()", "java.sql.CallableStatement prepareCall(String, int, int, int) throws \ java.sql.SQLException", "java.sql.CallableStatement prepareCall(String, int, int) throws \ java.sql.SQLException", "java.sql.CallableStatement prepareCall(String) throws \ java.sql.SQLException", "java.sql.PreparedStatement prepareStatement(String, int, int, int) \ throws java.sql.SQLException", "java.sql.PreparedStatement prepareStatement(String, int, int) throws \ java.sql.SQLException", "java.sql.PreparedStatement prepareStatement(String, int[]) throws \ java.sql.SQLException", "java.sql.PreparedStatement prepareStatement(String, int) throws \ java.sql.SQLException", "java.sql.PreparedStatement prepareStatement(String, String[]) throws \ java.sql.SQLException", "java.sql.PreparedStatement prepareStatement(String) throws \ java.sql.SQLException", "void releaseSavepoint(java.sql.Savepoint) throws \ java.sql.SQLException", "void rollback(java.sql.Savepoint) throws java.sql.SQLException", "void rollback() throws java.sql.SQLException", "void setAutoCommit(boolean) throws java.sql.SQLException", "void setCatalog(String) throws java.sql.SQLException", "void setHoldability(int) throws java.sql.SQLException", "void setProfileSql(boolean) throws java.sql.SQLException", "void setReadOnly(boolean) throws java.sql.SQLException", "java.sql.Savepoint setSavepoint(String) throws \ java.sql.SQLException", "java.sql.Savepoint setSavepoint() throws java.sql.SQLException", "void setTransactionIsolation(int) throws java.sql.SQLException", "void setTypeMap(java.util.Map) throws java.sql.SQLException", "void shutdownServer() throws java.sql.SQLException", "boolean supportsIsolationLevel()", "boolean supportsQuotedIdentifiers()", "boolean supportsTransactions()", "String toString()", "boolean useCompression()", "boolean useParanoidErrorMessages()", "boolean useSSL()", "boolean useStrictFloatingPoint()", "boolean useStrictUpdates()", "boolean useTimezone()", "boolean useUnicode()", "void wait(long, int) throws InterruptedException", "void wait(long) throws InterruptedException", "void wait() throws InterruptedException"}]]], "Output", CellLabel->"Out[13]//TableForm="] }, Closed]], Cell[TextData[{ "The method we will use in this notebook is ", StyleBox["prepareStatement", FontSlant->"Italic", FontColor->RGBColor[1, 0, 0]], StyleBox["()", FontSlant->"Italic"], " which allows one to send precompiled statements to the database, Note: \ older versions of MySQL do not support prepared statements. In that case the \ prepared statement must be precompiled before submitting it to the database. \ In the following example our query will extract from the aintoineeqn2 table \ species names which a have a species ID less than 30:" }], "Text"], Cell[CellGroupData[{ Cell[BoxData[ RowBox[{ StyleBox["statement1", FontColor->RGBColor[0, 0, 1]], "=", RowBox[{ StyleBox["conn", FontColor->RGBColor[0, 0, 1]], "[", RowBox[{ StyleBox["prepareStatement", FontColor->RGBColor[1, 0, 0]], "[", "\"\<select Species_name from antoineeqn2 where species_id<30\>\"", "]"}], "]"}]}]], "Input", CellLabel->"In[30]:="], Cell[BoxData[ InterpretationBox[\(\[LeftGuillemet]JavaObject[ "com.mysql.jdbc.PreparedStatement"] \[RightGuillemet]\), JLink`Objects`JavaObject178979340289]], "Output", CellLabel->"Out[30]="] }, Open ]], Cell[TextData[{ "Once we have a preparedStatement object we can execute a query on the \ database using ", StyleBox["executeQuery", FontSlant->"Italic", FontColor->RGBColor[1, 0, 0]], StyleBox["(", FontSlant->"Italic"], StyleBox[")", FontSlant->"Italic"], " method" }], "Text"], Cell[CellGroupData[{ Cell[BoxData[ RowBox[{ StyleBox["myResultSet", FontColor->RGBColor[0, 0, 1]], "=", RowBox[{ StyleBox["statement1", FontColor->RGBColor[0, 0, 1]], "[", RowBox[{ StyleBox["executeQuery", FontColor->RGBColor[1, 0, 0]], "[", "]"}], "]"}]}]], "Input", CellLabel->"In[31]:="], Cell[BoxData[ InterpretationBox[\(\[LeftGuillemet]JavaObject[ "com.mysql.jdbc.ResultSet"] \[RightGuillemet]\), JLink`Objects`JavaObject193554261475329]], "Output", CellLabel->"Out[31]="] }, Open ]], Cell[TextData[{ "The result we get from the ", StyleBox["executeQuery", FontSlant->"Italic", FontColor->RGBColor[1, 0, 0]], StyleBox["()", FontSlant->"Italic"], " method is a resultSet object. Let us examine the methods associated with \ this object" }], "Text"], Cell[CellGroupData[{ Cell[BoxData[ RowBox[{"Methods", "[", StyleBox["myResultSet", FontColor->RGBColor[0, 0, 1]], "]"}]], "Input", CellLabel->"In[16]:="], Cell[BoxData[ InterpretationBox[GridBox[{ {"\<\"boolean absolute(int) throws java.sql.SQLException\"\>"}, {"\<\"void afterLast() throws java.sql.SQLException\"\>"}, {"\<\"void beforeFirst() throws java.sql.SQLException\"\>"}, {"\<\"void cancelRowUpdates() throws java.sql.SQLException\"\>"}, {"\<\"void clearWarnings() throws java.sql.SQLException\"\>"}, {"\<\"void close() throws java.sql.SQLException\"\>"}, {"\<\"void deleteRow() throws java.sql.SQLException\"\>"}, {"\<\"boolean equals(Object)\"\>"}, {"\<\"int findColumn(String) throws java.sql.SQLException\"\>"}, {"\<\"boolean first() throws java.sql.SQLException\"\>"}, {"\<\"java.sql.Array getArray(int) throws \ java.sql.SQLException\"\>"}, {"\<\"java.sql.Array getArray(String) throws \ java.sql.SQLException\"\>"}, {"\<\"java.io.InputStream getAsciiStream(int) throws \ java.sql.SQLException\"\>"}, {"\<\"java.io.InputStream getAsciiStream(String) throws \ java.sql.SQLException\"\>"}, {"\<\"java.math.BigDecimal getBigDecimal(int, int) throws \ java.sql.SQLException\"\>"}, {"\<\"java.math.BigDecimal getBigDecimal(int) throws \ java.sql.SQLException\"\>"}, {"\<\"java.math.BigDecimal getBigDecimal(String, int) throws \ java.sql.SQLException\"\>"}, {"\<\"java.math.BigDecimal getBigDecimal(String) throws \ java.sql.SQLException\"\>"}, {"\<\"java.io.InputStream getBinaryStream(int) throws \ java.sql.SQLException\"\>"}, {"\<\"java.io.InputStream getBinaryStream(String) throws \ java.sql.SQLException\"\>"}, {"\<\"java.sql.Blob getBlob(int) throws \ java.sql.SQLException\"\>"}, {"\<\"java.sql.Blob getBlob(String) throws \ java.sql.SQLException\"\>"}, {"\<\"boolean getBoolean(int) throws java.sql.SQLException\"\>"}, {"\<\"boolean getBoolean(String) throws \ java.sql.SQLException\"\>"}, {"\<\"byte getByte(int) throws java.sql.SQLException\"\>"}, {"\<\"byte[] getBytes(int) throws java.sql.SQLException\"\>"}, {"\<\"byte[] getBytes(String) throws java.sql.SQLException\"\>"}, {"\<\"byte getByte(String) throws java.sql.SQLException\"\>"}, {"\<\"java.io.Reader getCharacterStream(int) throws \ java.sql.SQLException\"\>"}, {"\<\"java.io.Reader getCharacterStream(String) throws \ java.sql.SQLException\"\>"}, {"\<\"Class getClass()\"\>"}, {"\<\"java.sql.Clob getClob(int) throws \ java.sql.SQLException\"\>"}, {"\<\"java.sql.Clob getClob(String) throws \ java.sql.SQLException\"\>"}, {"\<\"int getConcurrency() throws java.sql.SQLException\"\>"}, {"\<\"String getCursorName() throws java.sql.SQLException\"\>"}, {"\<\"java.sql.Date getDate(int, java.util.Calendar) throws \ java.sql.SQLException\"\>"}, {"\<\"java.sql.Date getDate(int) throws \ java.sql.SQLException\"\>"}, {"\<\"java.sql.Date getDate(String, java.util.Calendar) throws \ java.sql.SQLException\"\>"}, {"\<\"java.sql.Date getDate(String) throws \ java.sql.SQLException\"\>"}, {"\<\"double getDouble(int) throws java.sql.SQLException\"\>"}, {"\<\"double getDouble(String) throws java.sql.SQLException\"\>"}, {"\<\"int getFetchDirection() throws java.sql.SQLException\"\>"}, {"\<\"int getFetchSize() throws java.sql.SQLException\"\>"}, {"\<\"float getFloat(int) throws java.sql.SQLException\"\>"}, {"\<\"float getFloat(String) throws java.sql.SQLException\"\>"}, {"\<\"int getInt(int) throws java.sql.SQLException\"\>"}, {"\<\"int getInt(String) throws java.sql.SQLException\"\>"}, {"\<\"long getLong(int) throws java.sql.SQLException\"\>"}, {"\<\"long getLong(String) throws java.sql.SQLException\"\>"}, {"\<\"java.sql.ResultSetMetaData getMetaData() throws \ java.sql.SQLException\"\>"}, {"\<\"Object getObject(int, java.util.Map) throws \ java.sql.SQLException\"\>"}, {"\<\"Object getObject(int) throws java.sql.SQLException\"\>"}, {"\<\"Object getObject(String, java.util.Map) throws \ java.sql.SQLException\"\>"}, {"\<\"Object getObject(String) throws java.sql.SQLException\"\>"}, {"\<\"java.sql.Ref getRef(int) throws java.sql.SQLException\"\>"}, {"\<\"java.sql.Ref getRef(String) throws \ java.sql.SQLException\"\>"}, {"\<\"int getRow() throws java.sql.SQLException\"\>"}, {"\<\"short getShort(int) throws java.sql.SQLException\"\>"}, {"\<\"short getShort(String) throws java.sql.SQLException\"\>"}, {"\<\"java.sql.Statement getStatement() throws \ java.sql.SQLException\"\>"}, {"\<\"String getString(int) throws java.sql.SQLException\"\>"}, {"\<\"String getString(String) throws java.sql.SQLException\"\>"}, {"\<\"java.sql.Time getTime(int, java.util.Calendar) throws \ java.sql.SQLException\"\>"}, {"\<\"java.sql.Time getTime(int) throws \ java.sql.SQLException\"\>"}, {"\<\"java.sql.Timestamp getTimestamp(int, java.util.Calendar) \ throws java.sql.SQLException\"\>"}, {"\<\"java.sql.Timestamp getTimestamp(int) throws \ java.sql.SQLException\"\>"}, {"\<\"java.sql.Timestamp getTimestamp(String, java.util.Calendar) \ throws java.sql.SQLException\"\>"}, {"\<\"java.sql.Timestamp getTimestamp(String) throws \ java.sql.SQLException\"\>"}, {"\<\"java.sql.Time getTime(String, java.util.Calendar) throws \ java.sql.SQLException\"\>"}, {"\<\"java.sql.Time getTime(String) throws \ java.sql.SQLException\"\>"}, {"\<\"int getType() throws java.sql.SQLException\"\>"}, {"\<\"java.io.InputStream getUnicodeStream(int) throws \ java.sql.SQLException\"\>"}, {"\<\"java.io.InputStream getUnicodeStream(String) throws \ java.sql.SQLException\"\>"}, {"\<\"java.net.URL getURL(int) throws java.sql.SQLException\"\>"}, {"\<\"java.net.URL getURL(String) throws \ java.sql.SQLException\"\>"}, {"\<\"java.sql.SQLWarning getWarnings() throws \ java.sql.SQLException\"\>"}, {"\<\"int hashCode()\"\>"}, {"\<\"void insertRow() throws java.sql.SQLException\"\>"}, {"\<\"boolean isAfterLast() throws java.sql.SQLException\"\>"}, {"\<\"boolean isBeforeFirst() throws java.sql.SQLException\"\>"}, {"\<\"boolean isFirst() throws java.sql.SQLException\"\>"}, {"\<\"boolean isLast() throws java.sql.SQLException\"\>"}, {"\<\"boolean last() throws java.sql.SQLException\"\>"}, {"\<\"void moveToCurrentRow() throws java.sql.SQLException\"\>"}, {"\<\"void moveToInsertRow() throws java.sql.SQLException\"\>"}, {"\<\"boolean next() throws java.sql.SQLException\"\>"}, {"\<\"void notify()\"\>"}, {"\<\"void notifyAll()\"\>"}, {"\<\"boolean previous() throws java.sql.SQLException\"\>"}, {"\<\"boolean prev() throws java.sql.SQLException\"\>"}, {"\<\"void refreshRow() throws java.sql.SQLException\"\>"}, {"\<\"boolean relative(int) throws java.sql.SQLException\"\>"}, {"\<\"boolean rowDeleted() throws java.sql.SQLException\"\>"}, {"\<\"boolean rowInserted() throws java.sql.SQLException\"\>"}, {"\<\"boolean rowUpdated() throws java.sql.SQLException\"\>"}, {"\<\"void setConnection(com.mysql.jdbc.Connection)\"\>"}, {"\<\"void setFetchDirection(int) throws \ java.sql.SQLException\"\>"}, {"\<\"void setFetchSize(int) throws java.sql.SQLException\"\>"}, {"\<\"String toString()\"\>"}, {"\<\"void updateArray(int, java.sql.Array) throws \ java.sql.SQLException\"\>"}, {"\<\"void updateArray(String, java.sql.Array) throws \ java.sql.SQLException\"\>"}, {"\<\"void updateAsciiStream(int, java.io.InputStream, int) throws \ java.sql.SQLException\"\>"}, {"\<\"void updateAsciiStream(String, java.io.InputStream, int) \ throws java.sql.SQLException\"\>"}, {"\<\"void updateBigDecimal(int, java.math.BigDecimal) throws \ java.sql.SQLException\"\>"}, {"\<\"void updateBigDecimal(String, java.math.BigDecimal) throws \ java.sql.SQLException\"\>"}, {"\<\"void updateBinaryStream(int, java.io.InputStream, int) throws \ java.sql.SQLException\"\>"}, {"\<\"void updateBinaryStream(String, java.io.InputStream, int) \ throws java.sql.SQLException\"\>"}, {"\<\"void updateBlob(int, java.sql.Blob) throws \ java.sql.SQLException\"\>"}, {"\<\"void updateBlob(String, java.sql.Blob) throws \ java.sql.SQLException\"\>"}, {"\<\"void updateBoolean(int, boolean) throws java.sql.SQLException\ \"\>"}, {"\<\"void updateBoolean(String, boolean) throws \ java.sql.SQLException\"\>"}, {"\<\"void updateByte(int, byte) throws \ java.sql.SQLException\"\>"}, {"\<\"void updateBytes(int, byte[]) throws \ java.sql.SQLException\"\>"}, {"\<\"void updateBytes(String, byte[]) throws java.sql.SQLException\ \"\>"}, {"\<\"void updateByte(String, byte) throws \ java.sql.SQLException\"\>"}, {"\<\"void updateCharacterStream(int, java.io.Reader, int) throws \ java.sql.SQLException\"\>"}, {"\<\"void updateCharacterStream(String, java.io.Reader, int) \ throws java.sql.SQLException\"\>"}, {"\<\"void updateClob(int, java.sql.Clob) throws \ java.sql.SQLException\"\>"}, {"\<\"void updateClob(String, java.sql.Clob) throws \ java.sql.SQLException\"\>"}, {"\<\"void updateDate(int, java.sql.Date) throws \ java.sql.SQLException\"\>"}, {"\<\"void updateDate(String, java.sql.Date) throws \ java.sql.SQLException\"\>"}, {"\<\"void updateDouble(int, double) throws java.sql.SQLException\"\ \>"}, {"\<\"void updateDouble(String, double) throws \ java.sql.SQLException\"\>"}, {"\<\"void updateFloat(int, float) throws \ java.sql.SQLException\"\>"}, {"\<\"void updateFloat(String, float) throws \ java.sql.SQLException\"\>"}, {"\<\"void updateInt(int, int) throws java.sql.SQLException\"\>"}, {"\<\"void updateInt(String, int) throws \ java.sql.SQLException\"\>"}, {"\<\"void updateLong(int, long) throws \ java.sql.SQLException\"\>"}, {"\<\"void updateLong(String, long) throws \ java.sql.SQLException\"\>"}, {"\<\"void updateNull(int) throws java.sql.SQLException\"\>"}, {"\<\"void updateNull(String) throws java.sql.SQLException\"\>"}, {"\<\"void updateObject(int, Object, int) throws \ java.sql.SQLException\"\>"}, {"\<\"void updateObject(int, Object) throws java.sql.SQLException\"\ \>"}, {"\<\"void updateObject(String, Object, int) throws \ java.sql.SQLException\"\>"}, {"\<\"void updateObject(String, Object) throws \ java.sql.SQLException\"\>"}, {"\<\"void updateRef(int, java.sql.Ref) throws \ java.sql.SQLException\"\>"}, {"\<\"void updateRef(String, java.sql.Ref) throws \ java.sql.SQLException\"\>"}, {"\<\"void updateRow() throws java.sql.SQLException\"\>"}, {"\<\"void updateShort(int, short) throws \ java.sql.SQLException\"\>"}, {"\<\"void updateShort(String, short) throws \ java.sql.SQLException\"\>"}, {"\<\"void updateString(int, String) throws java.sql.SQLException\"\ \>"}, {"\<\"void updateString(String, String) throws \ java.sql.SQLException\"\>"}, {"\<\"void updateTime(int, java.sql.Time) throws \ java.sql.SQLException\"\>"}, {"\<\"void updateTimestamp(int, java.sql.Timestamp) throws \ java.sql.SQLException\"\>"}, {"\<\"void updateTimestamp(String, java.sql.Timestamp) throws \ java.sql.SQLException\"\>"}, {"\<\"void updateTime(String, java.sql.Time) throws \ java.sql.SQLException\"\>"}, {"\<\"void wait(long, int) throws InterruptedException\"\>"}, {"\<\"void wait(long) throws InterruptedException\"\>"}, {"\<\"void wait() throws InterruptedException\"\>"}, {"\<\"boolean wasNull() throws java.sql.SQLException\"\>"} }, RowSpacings->1, ColumnSpacings->3, RowAlignments->Baseline, ColumnAlignments->{Left}], TableForm[ {"boolean absolute(int) throws java.sql.SQLException", "void afterLast() throws java.sql.SQLException", "void beforeFirst() throws java.sql.SQLException", "void cancelRowUpdates() throws java.sql.SQLException", "void clearWarnings() throws java.sql.SQLException", "void close() throws java.sql.SQLException", "void deleteRow() throws java.sql.SQLException", "boolean equals(Object)", "int findColumn(String) throws java.sql.SQLException", "boolean first() throws java.sql.SQLException", "java.sql.Array getArray(int) throws java.sql.SQLException", "java.sql.Array getArray(String) throws java.sql.SQLException", "java.io.InputStream getAsciiStream(int) throws \ java.sql.SQLException", "java.io.InputStream getAsciiStream(String) throws \ java.sql.SQLException", "java.math.BigDecimal getBigDecimal(int, int) throws \ java.sql.SQLException", "java.math.BigDecimal getBigDecimal(int) throws \ java.sql.SQLException", "java.math.BigDecimal getBigDecimal(String, int) throws \ java.sql.SQLException", "java.math.BigDecimal getBigDecimal(String) throws \ java.sql.SQLException", "java.io.InputStream getBinaryStream(int) throws \ java.sql.SQLException", "java.io.InputStream getBinaryStream(String) throws \ java.sql.SQLException", "java.sql.Blob getBlob(int) throws java.sql.SQLException", "java.sql.Blob getBlob(String) throws java.sql.SQLException", "boolean getBoolean(int) throws java.sql.SQLException", "boolean getBoolean(String) throws java.sql.SQLException", "byte getByte(int) throws java.sql.SQLException", "byte[] getBytes(int) throws java.sql.SQLException", "byte[] getBytes(String) throws java.sql.SQLException", "byte getByte(String) throws java.sql.SQLException", "java.io.Reader getCharacterStream(int) throws java.sql.SQLException", "java.io.Reader getCharacterStream(String) throws \ java.sql.SQLException", "Class getClass()", "java.sql.Clob getClob(int) throws java.sql.SQLException", "java.sql.Clob getClob(String) throws java.sql.SQLException", "int getConcurrency() throws java.sql.SQLException", "String getCursorName() throws java.sql.SQLException", "java.sql.Date getDate(int, java.util.Calendar) throws \ java.sql.SQLException", "java.sql.Date getDate(int) throws java.sql.SQLException", "java.sql.Date getDate(String, java.util.Calendar) throws \ java.sql.SQLException", "java.sql.Date getDate(String) throws java.sql.SQLException", "double getDouble(int) throws java.sql.SQLException", "double getDouble(String) throws java.sql.SQLException", "int getFetchDirection() throws java.sql.SQLException", "int getFetchSize() throws java.sql.SQLException", "float getFloat(int) throws java.sql.SQLException", "float getFloat(String) throws java.sql.SQLException", "int getInt(int) throws java.sql.SQLException", "int getInt(String) throws java.sql.SQLException", "long getLong(int) throws java.sql.SQLException", "long getLong(String) throws java.sql.SQLException", "java.sql.ResultSetMetaData getMetaData() throws \ java.sql.SQLException", "Object getObject(int, java.util.Map) throws java.sql.SQLException", "Object getObject(int) throws java.sql.SQLException", "Object getObject(String, java.util.Map) throws \ java.sql.SQLException", "Object getObject(String) throws java.sql.SQLException", "java.sql.Ref getRef(int) throws java.sql.SQLException", "java.sql.Ref getRef(String) throws java.sql.SQLException", "int getRow() throws java.sql.SQLException", "short getShort(int) throws java.sql.SQLException", "short getShort(String) throws java.sql.SQLException", "java.sql.Statement getStatement() throws java.sql.SQLException", "String getString(int) throws java.sql.SQLException", "String getString(String) throws java.sql.SQLException", "java.sql.Time getTime(int, java.util.Calendar) throws \ java.sql.SQLException", "java.sql.Time getTime(int) throws java.sql.SQLException", "java.sql.Timestamp getTimestamp(int, java.util.Calendar) throws \ java.sql.SQLException", "java.sql.Timestamp getTimestamp(int) throws java.sql.SQLException", "java.sql.Timestamp getTimestamp(String, java.util.Calendar) throws \ java.sql.SQLException", "java.sql.Timestamp getTimestamp(String) throws \ java.sql.SQLException", "java.sql.Time getTime(String, java.util.Calendar) throws \ java.sql.SQLException", "java.sql.Time getTime(String) throws java.sql.SQLException", "int getType() throws java.sql.SQLException", "java.io.InputStream getUnicodeStream(int) throws \ java.sql.SQLException", "java.io.InputStream getUnicodeStream(String) throws \ java.sql.SQLException", "java.net.URL getURL(int) throws java.sql.SQLException", "java.net.URL getURL(String) throws java.sql.SQLException", "java.sql.SQLWarning getWarnings() throws java.sql.SQLException", "int hashCode()", "void insertRow() throws java.sql.SQLException", "boolean isAfterLast() throws java.sql.SQLException", "boolean isBeforeFirst() throws java.sql.SQLException", "boolean isFirst() throws java.sql.SQLException", "boolean isLast() throws java.sql.SQLException", "boolean last() throws java.sql.SQLException", "void moveToCurrentRow() throws java.sql.SQLException", "void moveToInsertRow() throws java.sql.SQLException", "boolean next() throws java.sql.SQLException", "void notify()", "void notifyAll()", "boolean previous() throws java.sql.SQLException", "boolean prev() throws java.sql.SQLException", "void refreshRow() throws java.sql.SQLException", "boolean relative(int) throws java.sql.SQLException", "boolean rowDeleted() throws java.sql.SQLException", "boolean rowInserted() throws java.sql.SQLException", "boolean rowUpdated() throws java.sql.SQLException", "void setConnection(com.mysql.jdbc.Connection)", "void setFetchDirection(int) throws java.sql.SQLException", "void setFetchSize(int) throws java.sql.SQLException", "String toString()", "void updateArray(int, java.sql.Array) throws java.sql.SQLException", "void updateArray(String, java.sql.Array) throws \ java.sql.SQLException", "void updateAsciiStream(int, java.io.InputStream, int) throws \ java.sql.SQLException", "void updateAsciiStream(String, java.io.InputStream, int) throws \ java.sql.SQLException", "void updateBigDecimal(int, java.math.BigDecimal) throws \ java.sql.SQLException", "void updateBigDecimal(String, java.math.BigDecimal) throws \ java.sql.SQLException", "void updateBinaryStream(int, java.io.InputStream, int) throws \ java.sql.SQLException", "void updateBinaryStream(String, java.io.InputStream, int) throws \ java.sql.SQLException", "void updateBlob(int, java.sql.Blob) throws java.sql.SQLException", "void updateBlob(String, java.sql.Blob) throws java.sql.SQLException", "void updateBoolean(int, boolean) throws java.sql.SQLException", "void updateBoolean(String, boolean) throws java.sql.SQLException", "void updateByte(int, byte) throws java.sql.SQLException", "void updateBytes(int, byte[]) throws java.sql.SQLException", "void updateBytes(String, byte[]) throws java.sql.SQLException", "void updateByte(String, byte) throws java.sql.SQLException", "void updateCharacterStream(int, java.io.Reader, int) throws \ java.sql.SQLException", "void updateCharacterStream(String, java.io.Reader, int) throws \ java.sql.SQLException", "void updateClob(int, java.sql.Clob) throws java.sql.SQLException", "void updateClob(String, java.sql.Clob) throws java.sql.SQLException", "void updateDate(int, java.sql.Date) throws java.sql.SQLException", "void updateDate(String, java.sql.Date) throws java.sql.SQLException", "void updateDouble(int, double) throws java.sql.SQLException", "void updateDouble(String, double) throws java.sql.SQLException", "void updateFloat(int, float) throws java.sql.SQLException", "void updateFloat(String, float) throws java.sql.SQLException", "void updateInt(int, int) throws java.sql.SQLException", "void updateInt(String, int) throws java.sql.SQLException", "void updateLong(int, long) throws java.sql.SQLException", "void updateLong(String, long) throws java.sql.SQLException", "void updateNull(int) throws java.sql.SQLException", "void updateNull(String) throws java.sql.SQLException", "void updateObject(int, Object, int) throws java.sql.SQLException", "void updateObject(int, Object) throws java.sql.SQLException", "void updateObject(String, Object, int) throws java.sql.SQLException", "void updateObject(String, Object) throws java.sql.SQLException", "void updateRef(int, java.sql.Ref) throws java.sql.SQLException", "void updateRef(String, java.sql.Ref) throws java.sql.SQLException", "void updateRow() throws java.sql.SQLException", "void updateShort(int, short) throws java.sql.SQLException", "void updateShort(String, short) throws java.sql.SQLException", "void updateString(int, String) throws java.sql.SQLException", "void updateString(String, String) throws java.sql.SQLException", "void updateTime(int, java.sql.Time) throws java.sql.SQLException", "void updateTimestamp(int, java.sql.Timestamp) throws \ java.sql.SQLException", "void updateTimestamp(String, java.sql.Timestamp) throws \ java.sql.SQLException", "void updateTime(String, java.sql.Time) throws java.sql.SQLException", "void wait(long, int) throws InterruptedException", "void wait(long) throws InterruptedException", "void wait() throws InterruptedException", "boolean wasNull() throws java.sql.SQLException"}]]], "Output", CellLabel->"Out[16]//TableForm="] }, Closed]], Cell[TextData[{ "We can iterate through the resultSet using a While loop with ", StyleBox["next", FontSlant->"Italic", FontColor->RGBColor[1, 0, 0]], StyleBox["()", FontSlant->"Italic"], " and ", StyleBox["getString", FontSlant->"Italic", FontColor->RGBColor[1, 0, 0]], StyleBox["()", FontSlant->"Italic"], " methods to obtain the species names with species ID less than 30" }], "Text"], Cell[CellGroupData[{ Cell[BoxData[{\(s = {};\), "\[IndentingNewLine]", RowBox[{"While", "[", RowBox[{ RowBox[{ StyleBox["myResultSet", FontColor->RGBColor[0, 0, 1]], "[", RowBox[{ StyleBox["next", FontColor->RGBColor[1, 0, 0]], "[", "]"}], "]"}], ",", RowBox[{"AppendTo", "[", RowBox[{"s", ",", RowBox[{ StyleBox["myResultSet", FontColor->RGBColor[0, 0, 1]], "[", RowBox[{ StyleBox["getString", FontColor->RGBColor[1, 0, 0]], "[", "1", "]"}], "]"}]}], "]"}]}], "]"}], "\[IndentingNewLine]", "s"}], "Input", CellLabel->"In[32]:="], Cell[BoxData[ \({"Benzene", "Acetone", "Cyclohexane", "Acetaldehyde", "AceticAcid", "Acetonitrile", "Ammonia", "Aniline", "CarbonDioxide", "CarbonTet", "CarbonDisulfide", "Acetylene", "Biphenyl", "ChloroBenzene", "Chloroform", "cis-2-Butene", "cis-3-Pentene", "CycloPentane"}\)], "Output", CellLabel->"Out[34]="] }, Open ]], Cell["\<\ In our next example we prepare a query that will extract the \ Antoine parameters for species called \"Benzene\"\ \>", "Text"], Cell[CellGroupData[{ Cell[BoxData[ RowBox[{ StyleBox["statement2", FontColor->RGBColor[0, 0, 1]], "=", RowBox[{ StyleBox["conn", FontColor->RGBColor[0, 0, 1]], "[", RowBox[{ StyleBox["prepareStatement", FontColor->RGBColor[1, 0, 0]], "[", "\"\<select A,B,C from antoineeqn2 where \ species_name='Benzene'\>\"", "]"}], "]"}]}]], "Input", CellLabel->"In[59]:="], Cell[BoxData[ InterpretationBox[\(\[LeftGuillemet]JavaObject[ "com.mysql.jdbc.PreparedStatement"] \[RightGuillemet]\), JLink`Objects`JavaObject31575005724673]], "Output", CellLabel->"Out[59]="] }, Open ]], Cell[CellGroupData[{ Cell[BoxData[ RowBox[{ StyleBox["myResultSet", FontColor->RGBColor[0, 0, 1]], "=", RowBox[{ StyleBox["statement2", FontColor->RGBColor[0, 0, 1]], "[", RowBox[{ StyleBox["executeQuery", FontColor->RGBColor[1, 0, 0]], "[", "]"}], "]"}]}]], "Input", CellLabel->"In[60]:="], Cell[BoxData[ InterpretationBox[\(\[LeftGuillemet]JavaObject[ "com.mysql.jdbc.ResultSet"] \[RightGuillemet]\), JLink`Objects`JavaObject100909317095425]], "Output", CellLabel->"Out[60]="] }, Open ]], Cell["\<\ Note that the resultSet array contains 3 entries for each row of \ the table antoineeqn2, Here are the results\ \>", "Text"], Cell[CellGroupData[{ Cell[BoxData[{\(s = {};\), "\[IndentingNewLine]", RowBox[{"While", "[", RowBox[{ RowBox[{ StyleBox["myResultSet", FontColor->RGBColor[0, 0, 1]], "[", \(next[]\), "]"}], ",", RowBox[{"AppendTo", "[", RowBox[{"s", ",", RowBox[{"{", RowBox[{ RowBox[{ StyleBox["myResultSet", FontColor->RGBColor[0, 0, 1]], "[", \(getDouble[1]\), "]"}], ",", RowBox[{ StyleBox["myResultSet", FontColor->RGBColor[0, 0, 1]], "[", \(getDouble[2]\), "]"}], ",", RowBox[{ StyleBox["myResultSet", FontColor->RGBColor[0, 0, 1]], "[", \(getDouble[2]\), "]"}]}], "}"}]}], "]"}]}], "]"}], "\[IndentingNewLine]", "s"}], "Input", CellLabel->"In[61]:="], Cell[BoxData[ \({{14.1603`, 2948.78`, 2948.78`}}\)], "Output", CellLabel->"Out[63]="] }, Open ]], Cell["\<\ In our final example we extract both the species ID and species \ name from the table for all entries that have a species ID<30\ \>", "Text"], Cell[CellGroupData[{ Cell[BoxData[ RowBox[{ StyleBox["statement3", FontColor->RGBColor[0, 0, 1]], "=", RowBox[{ StyleBox["conn", FontColor->RGBColor[0, 0, 1]], "[", RowBox[{ StyleBox["prepareStatement", FontColor->RGBColor[1, 0, 0]], "[", "\"\<select species_id,Species_name from antoineeqn2 where \ species_id<30\>\"", "]"}], "]"}]}]], "Input", CellLabel->"In[67]:="], Cell[BoxData[ InterpretationBox[\(\[LeftGuillemet]JavaObject[ "com.mysql.jdbc.PreparedStatement"] \[RightGuillemet]\), JLink`Objects`JavaObject96209196810241]], "Output", CellLabel->"Out[67]="] }, Open ]], Cell[CellGroupData[{ Cell[BoxData[ RowBox[{ StyleBox["myResultSet", FontColor->RGBColor[0, 0, 1]], "=", RowBox[{ StyleBox["statement3", FontColor->RGBColor[0, 0, 1]], "[", RowBox[{ StyleBox["executeQuery", FontColor->RGBColor[1, 0, 0]], "[", "]"}], "]"}]}]], "Input", CellLabel->"In[68]:="], Cell[BoxData[ InterpretationBox[\(\[LeftGuillemet]JavaObject[ "com.mysql.jdbc.ResultSet"] \[RightGuillemet]\), JLink`Objects`JavaObject195727330377729]], "Output", CellLabel->"Out[68]="] }, Open ]], Cell["Here are the results contained in our resultSet object", "Text"], Cell[CellGroupData[{ Cell[BoxData[{\(s = {};\), "\[IndentingNewLine]", RowBox[{"While", "[", RowBox[{ RowBox[{ StyleBox["myResultSet", FontColor->RGBColor[0, 0, 1]], "[", RowBox[{ StyleBox["next", FontColor->RGBColor[1, 0, 0]], "[", "]"}], "]"}], ",", RowBox[{"AppendTo", "[", RowBox[{"s", ",", RowBox[{"{", RowBox[{ RowBox[{ StyleBox["myResultSet", FontColor->RGBColor[0, 0, 1]], "[", RowBox[{ StyleBox["getInt", FontColor->RGBColor[1, 0, 0]], "[", "1", "]"}], "]"}], ",", RowBox[{ StyleBox["myResultSet", FontColor->RGBColor[0, 0, 1]], "[", RowBox[{ StyleBox["getString", FontColor->RGBColor[1, 0, 0]], "[", "2", "]"}], "]"}]}], "}"}]}], "]"}]}], "]"}], "\[IndentingNewLine]", "s"}], "Input",\ CellLabel->"In[69]:="], Cell[BoxData[ \({{12, "Benzene"}, {4, "Acetone"}, {27, "Cyclohexane"}, {2, "Acetaldehyde"}, {3, "AceticAcid"}, {5, "Acetonitrile"}, {9, "Ammonia"}, {10, "Aniline"}, {16, "CarbonDioxide"}, {19, "CarbonTet"}, {17, "CarbonDisulfide"}, {7, "Acetylene"}, {13, "Biphenyl"}, {22, "ChloroBenzene"}, {23, "Chloroform"}, {25, "cis-2-Butene"}, {26, "cis-3-Pentene"}, {28, "CycloPentane"}}\)], "Output", CellLabel->"Out[71]="] }, Open ]], Cell["\<\ Finally we close down the statement, resultSet, and connection to \ our database\ \>", "Text"], Cell[BoxData[ RowBox[{ RowBox[{"{", RowBox[{ RowBox[{ StyleBox["statement3", FontColor->RGBColor[0, 0, 1], Background->None], "[", StyleBox["close", FontColor->RGBColor[1, 0, 0]], "]"}], ",", RowBox[{ StyleBox["myResultSet", FontColor->RGBColor[0, 0, 1]], "[", RowBox[{ StyleBox["close", FontColor->RGBColor[1, 0, 0]], "[", "]"}], "]"}], ",", RowBox[{ StyleBox["conn", FontColor->RGBColor[0, 0, 1]], "[", RowBox[{ StyleBox["close", FontColor->RGBColor[1, 0, 0]], "[", "]"}], "]"}]}], "}"}], "\[IndentingNewLine]"}]], "Input"] }, Open ]], Cell[CellGroupData[{ Cell["References", "Section"], Cell["\<\ I found the following textbooks helpful in preparing these notes.\ \ \>", "Text"], Cell[TextData[{ "1. Ivor Horton, ", StyleBox["Beginning Java 2 JDK 1.3 Edition", Link to the forum page for this post: http://www.mathematica-users.org/webMathematica/wiki/wiki.jsp?pageName=Special:Forum_ViewTopic&pid=10464#p10464 Posted through http://www.mathematica-users.org [[postId=10464]]