xmlsh - XML Shell: Command xsql

Revision [1943]

Last edited on 2016-10-15 00:29:47 by DavidLee

Additions:

||-q query||SQL query to run||
||-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||

Deletions:

||-q query|| SQL query to run||
||-close||After the operation release any cached connection and unregister the driver||

Revision [1840]

Edited on 2012-05-25 20:49:17 by DavidLee

Additions:

||-table table||The table to use for insert, otherwise the root element name is used||

Revision [1839]

Edited on 2012-05-25 13:43:18 by DavidLee

Additions:

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

Revision [1838]

Edited on 2012-05-25 13:30:58 by DavidLee

Additions:

||-pool,-pooldriver driver||Connection pool driver classname. Alternative to -driver||

Deletions:

~~||-pool,-pooldriver driver||Connection pool driver classname||~~

Revision [1837]

Edited on 2012-05-25 13:30:23 by DavidLee

Additions:

||-pool,-pooldriver driver||Connection pool driver classname||
||-jdbc,-jdbcconnection connection||Specify a java.sql.Connection object to use for the connection||

Revision [1830]

Edited on 2012-05-11 12:50:42 by DavidLee

Additions:

||-fetchmin|| Equivalent to -fetch Integer.MIN_VALUE which is useful for MySQL to stream results||

Revision [1514]

Edited on 2010-09-28 07:18:00 by DavidLee

Additions:

||-fetch rows||Sets the JDBC Statement fetch size ||

Deletions:

~~||-fetch rows||Sets the fetch size [["http://download-llnw.oracle.com/javase/6/docs/api/java/sql/Statement.html#setFetchSize(int)" setFetchSize]] ||~~

Revision [1513]

Edited on 2010-09-28 07:17:20 by DavidLee

Additions:

||-fetch rows||Sets the fetch size [["http://download-llnw.oracle.com/javase/6/docs/api/java/sql/Statement.html#setFetchSize(int)" setFetchSize]] ||

Deletions:

~~||-fetch rows||Sets the fetch size || [["http://download-llnw.oracle.com/javase/6/docs/api/java/sql/Statement.html#setFetchSize(int)" setFetchSize]] ||~~

Revision [1512]

Edited on 2010-09-28 07:17:06 by DavidLee

Additions:

||-fetch rows||Sets the fetch size || [["http://download-llnw.oracle.com/javase/6/docs/api/java/sql/Statement.html#setFetchSize(int)" setFetchSize]] ||

Deletions:

~~||-fetch rows||Sets the fetch size || ||~~

Revision [1511]

Edited on 2010-09-28 07:16:58 by DavidLee

Additions:

||-fetch rows||Sets the fetch size || ||

Deletions:

~~||-fetch rows||Sets the fetch size ||~~

Revision [1510]

Edited on 2010-09-28 07:16:42 by DavidLee

Additions:

||-fetch rows||Sets the fetch size ||

Deletions:

~~||-fetch rows||Sets the fetch size [["http://download-llnw.oracle.com/javase/6/docs/api/java/sql/Statement.html#setFetchSize(int)" setFetchSize]] ||~~

Revision [1509]

Edited on 2010-09-28 07:16:23 by DavidLee

Additions:

||-fetch rows||Sets the fetch size [["http://download-llnw.oracle.com/javase/6/docs/api/java/sql/Statement.html#setFetchSize(int)" setFetchSize]] ||

Deletions:

~~||-fetch rows||Sets the fetch size (JDBC Statement ["http://download-llnw.oracle.com/javase/6/docs/api/java/sql/Statement.html#setFetchSize(int)" setFetchSize]||~~

Revision [1508]

Edited on 2010-09-28 07:15:47 by DavidLee

Additions:

||-fetch rows||Sets the fetch size (JDBC Statement ["http://download-llnw.oracle.com/javase/6/docs/api/java/sql/Statement.html#setFetchSize(int)" setFetchSize]||

Revision [1505]

Edited on 2010-09-22 11:15:05 by DavidLee

Additions:

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

Deletions:

||-tableAttr attr||Use the argument ("attr") to indicate which table to insert/update||
||-fieldAttr attr||Use the argumet ("fieldAttr") to indicate which attribute specifies the field names||

Revision [1504]

Edited on 2010-09-22 11:14:06 by DavidLee

Additions:

_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>
}
]>

Revision [1503]

Edited on 2010-09-22 11:10:07 by DavidLee

Additions:

||-insert||Perform an insert operation instead of a select||
||-update,-execute||Perform an Update or Execute instead of select||
||-tableAttr attr||Use the argument ("attr") to indicate which table to insert/update||
||-fieldAttr attr||Use the argumet ("fieldAttr") to indicate which attribute specifies the field names||
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
xsql -cp $CP -c jdbc:sqlite:sqlite.db -d org.sqlite.JDBC -insert <{_TEST} > /dev/null

Revision [1235]

Edited on 2010-02-15 10:25:50 by DavidLee

Additions:

||-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||
||-o,-option name=value||Specifiy additional connection options, may be repeated (-o opt1=value1 -o opt2=value2)||

Deletions:

||-cp path
-classpath path|| Specifies an additional classpath to use to load the jdbc driver||
||-d class
-driver class|| Specifies the jdbc driver classname||
||-u user
-user user|| Username||
||-p password
-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 string
-connect string|| Connect string for jdbc connections||
||-o name=value
-option name=value|| Specifiy additional connection options, may be repeated (-o opt1=value1 -o opt2=value2)||

Revision [1078]

Edited on 2009-12-03 06:57:40 by DavidLee

Additions:

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

Revision [960]

Edited on 2009-11-24 18:58:16 by DavidLee

Additions:

[[Commands]]

Deletions:

~~[[Commands]~~

Revision [959]

Edited on 2009-11-24 18:57:59 by DavidLee

Additions:

xsql [ [[SerializationOptions serialization options]] ] [options]
====Options====
||-cp path
-classpath path|| Specifies an additional classpath to use to load the jdbc driver||
||-d class
-driver class|| Specifies the jdbc driver classname||
||-u user
-user user|| Username||
||-p password
-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 string
-connect string|| Connect string for jdbc connections||
||-q query|| SQL query to run||
||-o name=value
-option name=value|| Specifiy additional connection options, may be repeated (-o opt1=value1 -o opt2=value2)||
Supports the standard [ [[SerializationOptions serialization options]] ]
[[Commands]

Deletions:

xsql [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)

Revision [824]

Edited on 2009-10-13 05:11:45 by DavidLee

Additions:

-o
-option name=value Specifiy additional connection options, may be repeated (-o opt1=value1 -o opt2=value2)
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"

Deletions:

~~xsql -cp mysql-connector-java-5.1.7-bin.jar -c jdbc:mysql://host.com/db -u dave -p pass1 -d org.gjt.mm.mysql.Driver -q 'select * from address'~~

Revision [773]

Edited on 2009-09-16 07:58:26 by DavidLee

Deletions:

~~[[CommandsInternal]]~~

Revision [714]

Edited on 2009-09-05 17:30:37 by DavidLee

Deletions:

~~$ 1'~~

Revision [713]

Edited on 2009-09-05 17:21:03 by DavidLee

Additions:

======Command xsql======
====Name====
**xsql** queries a a relational database using jdbc
====Synopsis====
xsql [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
====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/db -u dave -p pass1 -d org.gjt.mm.mysql.Driver -q 'select * from address'
%%
====Return Value====
Returns 0 if the command executed successfully, 1 if there was an error.
----
[[CategoryCommands]]
[[CommandsInternal]]
$ 1'

Deletions:

$ xsql -cp *.jar -c jdbc:mysql://lee.calldei.com -u dave -p pass1 -d org.gjt.mm.
mysql.Driver -q 'select 1'

Revision [712]

The oldest known version of this page was created on 2009-09-05 17:14:22 by DavidLee