Revision [1839]

This is an old revision of CommandXsql made by DavidLee on 2012-05-25 13:43:18.

 

Command xsql


Name

xsql queries a a relational database using jdbc


Synopsis

xsql [ 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 rowRow element name for generated xml
-attrUse attribute normal form instead of element normal form for column data
-c,-connect stringConnect string for jdbc connections
-q query SQL query to run
-o,-option name=valueSpecifiy additional connection options, may be repeated (-o opt1=value1 -o opt2=value2)
-insertPerform an insert operation instead of a select
-update,-executePerform an Update or Execute instead of select
-tableAttr attrUse the argument ("attr") to indicate which table to insert/update otherwise the element name is used as the table name
-fieldAttr attrUse the argumet ("fieldAttr") to indicate which attribute specifies the field names otherwise the element name is used as the field name
-fetch rowsSets the JDBC Statement fetch size
-fetchmin Equivalent to -fetch Integer.MIN_VALUE which is useful for MySQL to stream results
-pool,-pooldriver driverConnection pool driver classname. Alternative to -driver
-jdbc,-jdbcconnection connectionSpecify a java.sql.Connection object to use for the connection
-cacheMaintain a thread local cache of the driver and connection until -close is supplied
-closeAfter the operation release any cached connection and unregister the driver




Supports the standard [ 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 csv2xml.



Example using the mysql JDBC connector

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

There are no comments on this page.
Valid XHTML :: Valid CSS: :: Powered by WikkaWiki