Command xsql
Name
xsql queries a a relational database using jdbcSynopsis
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 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 [ 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/nullReturn Value
Returns 0 if the command executed successfully, 1 if there was an error.Commands
CategoryCommands
