Wiki source for CommandXsql
======Command xsql======
====Name====
**xsql** queries a a relational database using jdbc
====Synopsis====
xsql [ [[SerializationOptions serialization options]] ] [options]
====Options====
||-cp,-classpath path|| Specifies an additional classpath to use to load the jdbc driver||
||-d,-driver class|| Specifies the jdbc driver classname||
||-u,-user user|| Username||
||-p,-password password|| Password||
||-root root|| Root element for generated xml||
||-row row||Row element name for generated xml||
||-attr||Use attribute normal form instead of element normal form for column data||
||-c,-connect string||Connect string for jdbc connections||
||-q query||SQL query to run||
||-o,-option name=value||Specifiy additional connection options, may be repeated (-o opt1=value1 -o opt2=value2)||
||-insert||Perform an insert operation instead of a select||
||-update,-execute||Perform an Update or Execute instead of select||
||-table table||The table to use for insert, otherwise the root element name is used||
||-tableAttr attr||Use the argument ("attr") to indicate which table to insert/update otherwise the element name is used as the table name||
||-fieldAttr attr||Use the argumet ("fieldAttr") to indicate which attribute specifies the field names otherwise the element name is used as the field name||
||-fetch rows||Sets the JDBC Statement fetch size ||
||-fetchmin|| Equivalent to -fetch Integer.MIN_VALUE which is useful for MySQL to stream results||
||-pool,-pooldriver driver||Connection pool driver classname. Alternative to -driver||
||-jdbc,-jdbcconnection connection||Specify a java.sql.Connection object to use for the connection||
||-cache||Maintain a thread local cache of the driver and connection until -close is supplied||
||-close||After the operation release any cached connection and unregister the driver. (implies -release)||
||-release||After the operation release the connection||
||-batch n||With -cache, executes all pending cached statements every 'n' rows. Used with -insert and -update||
Supports the standard [ [[SerializationOptions serialization options]] ]
====Description====
xsql connects to a database using the java jdbc interface, executes an sql statement and returns the result as an xml document. The xml document is formatted similarly to [[CommandCsv2xml csv2xml]].
Example using the mysql JDBC connector
%%(shell)
xsql -cp mysql-connector-java-5.1.7-bin.jar -c jdbc:mysql://host.com/xmlsh -u xmlsh -p password -d org.gjt.mm.mysql.Driver -q 'select * from books'
%%
Example using the csvjdbc driver from http://csvjdbc.sourceforge.net/
%%
xsql -cp csvjdbc.jar -d org.relique.jdbc.csv.CsvDriver -c "jdbc:relique:csv:$PWD" "select * from books"
%%
Added suppressHeaders option
%%
xsql -cp csvjdbc.jar -d org.relique.jdbc.csv.CsvDriver -o suppressHeaders=true -c "jdbc:relique:csv:$PWD" "select * from books"
%%
Oracle Example using ojdbc14.jar with failover
%%
xsql -cp ojdbc14.jar -driver oracle.jdbc.OracleDriver \
-connect "jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)(FAILOVER=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=myhost.com)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=myhost2.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MYSERVICE)))" \
-user myuser -password mypass \
-q "SELECT * from TABLE" > drugs.xml
%%
Create an SQLIte database
%%
xsql -cp $CP -c jdbc:sqlite:sqlite.db -d org.sqlite.JDBC -execute 'CREATE table test (TITLE , AUTHOR , PUBLISHER , `PUB-DATE` , LANGUAGE, PRICE , QUANTITY , ISBN , PAGES , DIMENSIONS , WEIGHT )' > /dev/null
%%
Add data to a databae
%%
_TEST=<[
document {
<root>
<test>
<row>
<TITLE>Pride and Prejudice</TITLE>
<AUTHOR>Jane Austen</AUTHOR>
<PUBLISHER>Modern Library</PUBLISHER>
<PUB-DATE>2002-12-31</PUB-DATE>
<LANGUAGE>English</LANGUAGE>
<PRICE>4.95</PRICE>
<QUANTITY>187</QUANTITY>
<ISBN>679601686</ISBN>
<PAGES>352</PAGES>
<DIMENSIONS>8.3 5.7 1.1</DIMENSIONS>
<WEIGHT>6.10</WEIGHT>
</row>
<row>
<TITLE>Wuthering Heights</TITLE>
<AUTHOR>Charlotte Bront</AUTHOR>
<PUBLISHER>Penguin Classics</PUBLISHER>
<PUB-DATE>2002-12-31</PUB-DATE>
<LANGUAGE>English</LANGUAGE>
<PRICE>6.58</PRICE>
<QUANTITY>113</QUANTITY>
<ISBN>141439556</ISBN>
<PAGES>430</PAGES>
<DIMENSIONS>1.0 5.2 7.8</DIMENSIONS>
<WEIGHT>11.20</WEIGHT>
</row>
</test>
</root>
}
]>
xsql -cp $CP -c jdbc:sqlite:sqlite.db -d org.sqlite.JDBC -insert <{_TEST} > /dev/null
%%
====Return Value====
Returns 0 if the command executed successfully, 1 if there was an error.
----
[[Commands]]
[[CategoryCommands]]
====Name====
**xsql** queries a a relational database using jdbc
====Synopsis====
xsql [ [[SerializationOptions serialization options]] ] [options]
====Options====
||-cp,-classpath path|| Specifies an additional classpath to use to load the jdbc driver||
||-d,-driver class|| Specifies the jdbc driver classname||
||-u,-user user|| Username||
||-p,-password password|| Password||
||-root root|| Root element for generated xml||
||-row row||Row element name for generated xml||
||-attr||Use attribute normal form instead of element normal form for column data||
||-c,-connect string||Connect string for jdbc connections||
||-q query||SQL query to run||
||-o,-option name=value||Specifiy additional connection options, may be repeated (-o opt1=value1 -o opt2=value2)||
||-insert||Perform an insert operation instead of a select||
||-update,-execute||Perform an Update or Execute instead of select||
||-table table||The table to use for insert, otherwise the root element name is used||
||-tableAttr attr||Use the argument ("attr") to indicate which table to insert/update otherwise the element name is used as the table name||
||-fieldAttr attr||Use the argumet ("fieldAttr") to indicate which attribute specifies the field names otherwise the element name is used as the field name||
||-fetch rows||Sets the JDBC Statement fetch size ||
||-fetchmin|| Equivalent to -fetch Integer.MIN_VALUE which is useful for MySQL to stream results||
||-pool,-pooldriver driver||Connection pool driver classname. Alternative to -driver||
||-jdbc,-jdbcconnection connection||Specify a java.sql.Connection object to use for the connection||
||-cache||Maintain a thread local cache of the driver and connection until -close is supplied||
||-close||After the operation release any cached connection and unregister the driver. (implies -release)||
||-release||After the operation release the connection||
||-batch n||With -cache, executes all pending cached statements every 'n' rows. Used with -insert and -update||
Supports the standard [ [[SerializationOptions serialization options]] ]
====Description====
xsql connects to a database using the java jdbc interface, executes an sql statement and returns the result as an xml document. The xml document is formatted similarly to [[CommandCsv2xml csv2xml]].
Example using the mysql JDBC connector
%%(shell)
xsql -cp mysql-connector-java-5.1.7-bin.jar -c jdbc:mysql://host.com/xmlsh -u xmlsh -p password -d org.gjt.mm.mysql.Driver -q 'select * from books'
%%
Example using the csvjdbc driver from http://csvjdbc.sourceforge.net/
%%
xsql -cp csvjdbc.jar -d org.relique.jdbc.csv.CsvDriver -c "jdbc:relique:csv:$PWD" "select * from books"
%%
Added suppressHeaders option
%%
xsql -cp csvjdbc.jar -d org.relique.jdbc.csv.CsvDriver -o suppressHeaders=true -c "jdbc:relique:csv:$PWD" "select * from books"
%%
Oracle Example using ojdbc14.jar with failover
%%
xsql -cp ojdbc14.jar -driver oracle.jdbc.OracleDriver \
-connect "jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)(FAILOVER=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=myhost.com)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=myhost2.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MYSERVICE)))" \
-user myuser -password mypass \
-q "SELECT * from TABLE" > drugs.xml
%%
Create an SQLIte database
%%
xsql -cp $CP -c jdbc:sqlite:sqlite.db -d org.sqlite.JDBC -execute 'CREATE table test (TITLE , AUTHOR , PUBLISHER , `PUB-DATE` , LANGUAGE, PRICE , QUANTITY , ISBN , PAGES , DIMENSIONS , WEIGHT )' > /dev/null
%%
Add data to a databae
%%
_TEST=<[
document {
<root>
<test>
<row>
<TITLE>Pride and Prejudice</TITLE>
<AUTHOR>Jane Austen</AUTHOR>
<PUBLISHER>Modern Library</PUBLISHER>
<PUB-DATE>2002-12-31</PUB-DATE>
<LANGUAGE>English</LANGUAGE>
<PRICE>4.95</PRICE>
<QUANTITY>187</QUANTITY>
<ISBN>679601686</ISBN>
<PAGES>352</PAGES>
<DIMENSIONS>8.3 5.7 1.1</DIMENSIONS>
<WEIGHT>6.10</WEIGHT>
</row>
<row>
<TITLE>Wuthering Heights</TITLE>
<AUTHOR>Charlotte Bront</AUTHOR>
<PUBLISHER>Penguin Classics</PUBLISHER>
<PUB-DATE>2002-12-31</PUB-DATE>
<LANGUAGE>English</LANGUAGE>
<PRICE>6.58</PRICE>
<QUANTITY>113</QUANTITY>
<ISBN>141439556</ISBN>
<PAGES>430</PAGES>
<DIMENSIONS>1.0 5.2 7.8</DIMENSIONS>
<WEIGHT>11.20</WEIGHT>
</row>
</test>
</root>
}
]>
xsql -cp $CP -c jdbc:sqlite:sqlite.db -d org.sqlite.JDBC -insert <{_TEST} > /dev/null
%%
====Return Value====
Returns 0 if the command executed successfully, 1 if there was an error.
----
[[Commands]]
[[CategoryCommands]]
