sqlsharp - Mono SQL Query command-line tool

sqlsharp [-f filename] [-o filename] [-s]

sqlsharp is a Mono SQL tool used for entering SQL queries to a database using Mono data providers.

The following options are supported:

-f filename

Output file to load SQL commands from.

-o filename

Output file to send results.

-s

Silent mode.

The SQL tool accepts commands via its command line interface. Commands begin with a backslash followed by the command name.

Example:

	\open

Basically, there are five commands a user should know: \provider, \connectionstring, \open, \quit, and \help To connect to a database, you need to do the following:

1. set your data provider via \provider

	Example:
		SQL# \provider mysql
		

2. set your connection string via \connectionstring

	Example:
		SQL# \connectionstring Database=test
		

	Example:
		SQL# \open
		

These commands are used to setup the provider, connection string, and open/close the database connnection

ConnectionString

Sets the Connection String

Example: SQL# \ConnectionString Database=testdb

For more examples, see section CONNECTION STRING EXAMPLES.

Provider

Sets the Provider of the Data Source. For list of Providers, see section PROVIDERS.

	
Example: to set the provider for MySQL:
	SQL# \provider mysql
		
Note: if you need to load an external provider in SQL#, 
      see the SQL# command \loadextprovider 
      

LoadExtProvider

ASSEMBLY CLASS to load an external provider. Use the complete name of its assembly and its Connection class.

Example: to load the MySQL provider Mono.Data.MySql SQL# \loadextprovider Mono.Data.MySql Mono.Data.MySql.MySqlConnection

Open

Opens a connection to the database

Example: SQL# \open

Close

Closes the connection to the database

Example: SQL# \close

Default

show default variables, such as, Provider and ConnectionString.

Example: SQL# \defaults

Q

Quit

Example: SQL# \q

Commands to execute SQL statements

e execute SQL query (SELECT)

Example: to execute a query SQL# SELECT * FROM EMPLOYEE SQL# \e

Note: to get \e to automatically work after entering a query, put a semicolon ; at the end of the query. Example: to enter and exectue query at the same time

SQL# SELECT * FROM EMPLOYEE;

exenonquery

execute a SQL non query (not a SELECT)

Example: to insert a row into a table: SQL# INSERT INTO SOMETABLE (COL1, COL2) VALUES('ABC','DEF') SQL# \exenonquery Note: this can be used for those providers that are new and do not have the ability to execute queries yet.

exescalar

execute SQL to get a single row and single column.

Example: to execute a Maxium aggregate SQL# SELECT MAX(grade) FROM class SQL# \exescalar

exexml

FILENAME to execute SQL and save output to XML file

Example: SQL# SELECT fname, lname, hire_date FROM employee SQL# \exexml employee.xml Note: this depends on DataAdapter, DataTable, and DataSet to be working properly

Example: SQL# \f batch.sql# Note: the SQL# commands are interpreted as they are read. If there is any SQL statements, the are executed.

o

FILENAME to write result of commands executed to file.

Example: SQL# \o result.txt

load

FILENAME to load from file SQL commands into SQL buffer.

Example: SQL# \load commands.sql

save

FILENAME to save SQL commands from SQL buffer to file.

Example:
	SQL# \save commands.sql

General commands to use.

h

show help (all commands).

Example: SQL# \h

s

TRUE, FALSE to silent messages.

Example 1: SQL# \s true Example 2: SQL# \s false

r

reset or clear the query buffer.

Example: SQL# \r

print

show what's in the SQL buffer now.

Example: SQL# \print

SH VARIABLES WHICH CAN BE USED AS PARAMETERS Commands to set variables which can be used as Parameters in an SQL statement. If the SQL contains any parameters, the parameter does not have a variable set, the user will be prompted for the value for each missing parameter.

set

NAME VALUE to set an internal variable.

Example: SQL# \set sFirstName John

unset

NAME to remove an internal variable.

Example: SQL# \unset sFirstName

variable

NAME to display the value of an internal variable.

Example: SQL# \variable sFirstName

Enable or Disble support for a particular provider option

UseParameters

TRUE,FALSE to use parameters when executing SQL which use the variables that were set.

If this option is true, the SQL contains parameters, and for each parameter which does not have a SQL# variable set, the user will be prompted to enter the value For that parameter.

Example: SQL# \useparameter true

Default: false

UseSimpleReader

TRUE,FALSE to use simple reader when displaying results.

Example: SQL# \usesimplereader true

Default: false. Mostly, this is dependent on the provider. If the provider does not have enough of IDataReader implemented to have the normal reader working, then the simple reader can be used. Providers like SqlClient, MySQL, and PostgreSQL have this ption defaulting to true.

PROVIDER NAME NAMESPACE ASSEMBLY

oracle Oracle 8i System.Data.OracleClient System.Data.OracleClient postgresql NetPostgreSQL Npgsql Npgsql bytefx ByteFX MySQL ByteFX.Data.MySqlClient ByteFX.Data sqlclient MS SQL 7/2000 System.Data.SqlClient System.Data odbc ODBC System.Data.Odbc System.Data sqlite SQL Lite Mono.Data.SqliteClient Mono.Data.SqliteClient sybase Sybase Mono.Data.SybaseClient Mono.Data.SybaseClient olebb OLE DB System.Data.OleDb System.Data tds TDS Generic Mono.Data.TdsClient Mono.Data.TdsClient msodbc MS ODBC Microsoft.Data.Odbc Microsoft.Data.Odbc firebird Firebird SQL FirebirdSql.Data.FirebirdSql FirebirdSql.Data.Firebird mysql MySQL AB MySql.Data.MySqlClient MySql.Data

NOTES:

npgsql maps to postgresql above. mysqlnet maps to mysql above. odbc is treated as an external provider for .NET 1.1 and above. msodbc is an external provider for compatibility with .NET 1.0

MySql.Data has replaced the mysql provider. If you still need to use ByteFX.Data, then use bytefx.

MySql.Data is not included with Mono. You need to download the MySQL Connector/Net from MySQL AB at http://dev.mysql.com/downloads/connector/net/1.0.html

Example connection strings for various providers to be used via the command \ConnectionString

Example of usage: \connectionstring Database=testdb

Connection String examples:

Microsoft SQL Server via System.Data.SqlClient or Mono.Data.TdsClient provider:

Server=DANPC;Database=pubs;User ID=saPassword=

ODBC via System.Data.Odbc provider using a DSN named "MSSQLDSN" I set up in the Windows control panel's ODBC Data Sources which connects to Microsoft SQL Server 2000:

DSN=MSSQLDSN;UID=danmorg;PWD=freetds

SQL Lite via Mono.Data.SqliteClient provider which connects to the database file SqliteTest.db; if not found, the file is created:

URI=file:SqliteTest.db

OLE DB via System.Data.OleDb provider which connects to a PostgreSQL database:

Provider=PostgreSQL;Addr=127.0.0.1;Database=rodrigo

Oracle via System.Data.OracleClient

Data Source=testdb;User ID=scott;Password=tiger

Npgsql (.NET PostgreSQL) from http://gborg.postgresql.org/project/npgsql/projdisplay.php

Server=localhost;Database=test;User ID=postgres;Password=fun2db

ByteFX (ByteFX MySQL) from

Server=localhost;Database=test;User ID=mysql;Password=

FirebirdSql via FirebirdSql.Data.Firebird (not included with Mono)

Database=C:\FIREBIRD\EXAMPLES\EMPLOYEE.FDB;User=SYSDBA;Password=masterkey;Dialect=3;Server=localhost

MySQL (MySQL AB) from http://www.mysql.com/

Server=localhost;Database=test;User ID=mysql;Password=mypass;Pooling=false

No support for tracing right now.

The Mono SQL Query Tool was written

by Daniel Morgan <danielmorgan@verizon.net>

The Mono SQL Query Tool is released under the terms of the GNU GPL. Please read the accompanying `COPYING' file for details. Alternative licenses are available from Novell or Daniel Morgan.

To report bugs in the compiler, you can use `bug-buddy', or you can file bug reports in our bug tracking system:

http://bugzilla.ximian.com.

For details, visit:

http://lists.ximian.com/mailman/listinfo/mono-devel-list

For details, visit:

http://www.mono-project.com 

mono(1), mint(1)