======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]]