CamelotSql - the SharePoint SQL Command-Line Tool

CamelotSql is a simple, yet quite powerful command line tool for executing SQL statements into SharePoint 2007 through 2013 including Office 365. It is bundled with the Camelot .NET Connector 3.0 and above and available for .NET 4.

It supports interactive and noninteractive use enabling scripting and backup/export possibilities.

Using CamelotSql is very easy, just start it from the Windows command prompt or find it in the startup meny under the Camelot .NET Connector folder.

shell> camelotsql.exe

or

shell> camelotsql.exe connect stored_connection

or

shell> camelotsql.exe connect -server hostname -user username -password pwd

Once connected you can type SQL statements, end with ";" and press Enter.

camelotsql> select * from animals limit 5;
+----+-----------+---------+-----+-------+------------+
| ID | LinkTitle | Species | Age | Alive | Attributes |
+----+-----------+---------+-----+-------+------------+
| 1  | Leia      | Dog     | 7   | True  | A,B        |
| 2  | Max       | Cat     | 19  | False | C          |
| 3  | Bosse     | Fish    | 16  | True  | B          |
| 4  | Janne     | Dog     | 10  | True  | A          |
| 12 | Nisse     | Zebra   | 10  | True  | A          |
+----+-----------+---------+-----+-------+------------+
5 row(s) in set (0.13 sec)
camelotsql>

There are several options available when executing statements, for example you can show results in vertical display using the -v option.

camelotsql> select * from animals limit 5,1; -v
******************** Row 1 ********************
ID:         13
LinkTitle:  Nisse
Species:    Zebra
Age:        10
Alive:      True
Attributes: A
1 row(s) in set (0.19 sec)
camelotsql>

To list all available commands and options, type help or ?.

camelotsql> ?

List of all CamelotSql commands:
Note that all sql commands (SELECT, INSERT, UPDATE, etc) must be first on line and end with ";"

?                   Synonym for 'help'.
clear               Clear window and reset cursor position.
cls                 Synonym for 'clear'.
config              Show stored connections.
connect             Connect to server using named connection or with specified options. Type
                    'help connect' for details.
                        'connect myconnection'
                        'connect -server 192.168.0.1 -user foo -password -bar'
delete              Delete connection from configuration.
                        'delete myconnection'
exit                Same as quit.
help                Displays this help.
pager               Set default page size or disable/enable pager.
                        'pager 20'
                        'pager off'
quit                Quit CamelotSql.
save                Save named connection with specified options. Type 'help save' for details.

For sql execution options, type 'help options'.
camelotsql>

To get SQL execution options type help options, or for a specific command help <command>.

camelotsql> ? options

List of sql command options:
Note that all sql commands (SELECT, INSERT, UPDATE, etc) must be first on line and end with ";"

-authentication     Authentication method, e.g. NTLM, Basic, 365.
-bin                Write binary content. Use this to download binary content.
-cs                 Connect to named connection.
-csv                Write rows in CSV format.
-db                 Change database.
                        '-db home'
-defaultlimit       Default row limit (number of rows returned from server).
-f                  Dump output to file. If no format is provided, the extension will be
                    matched.
                        '-f c:\rows.csv'
-html               Write rows in HTML format.
-p                  Set command page size.
                        '-p 20'
-password           Password for authentication.
-q                  Quit CamelotSql after command has been executed.
-s                  Silent mode. Less output is printed.
-server             Connect to host.
-tsv                Write rows in TSV format.
-user               User name for authentication.
-v                  Print result rows vertically (one line per column value).
-xml                Write rows in XML format.

Example: SELECT ID, Title, Created FROM `Shared Documents` LIMIT 20,1; -server 192.168.0.1 -user foo -pwd bar

Example: SELECT * FROM Tasks; -f c:\tasks.csv

Example: CALL DOWNLOAD('Shared Documents', 'logo.bmp'); -bin -f c:\logo.bmp

For more connection options, see /documentation.
camelotsql>

You can execute SQL statements (CRUD) interactively through batch scripts or similar like this.

shell> camelotsql.exe select * from animals; -server hostname -user username -password password -f c:\animals.csv -q -s

There are several export formats available, incuding CSV and XML. For further reading check the sub sections of this documentation.


Table of Contents

Visa     MasterCard