Wiki source for CommandXsql

Show raw source

======Command xsql======

**xsql** queries a a relational database using jdbc

xsql [ [[SerializationOptions serialization 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]] ]

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

xsql -cp mysql-connector-java-5.1.7-bin.jar -c jdbc:mysql:// -u xmlsh -p password -d -q 'select * from books'

Example using the csvjdbc driver from
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 \
-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

document {
<TITLE>Pride and Prejudice</TITLE>
<AUTHOR>Jane Austen</AUTHOR>
<TITLE>Wuthering Heights</TITLE>
<AUTHOR>Charlotte Bront</AUTHOR>

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.

Valid XHTML :: Valid CSS: :: Powered by WikkaWiki