mono/mcs/tools/sqlsharp/README

Mono SQL Query - Command Line Interface
=======================================

Running SQL Query on Mono:
	mono sqlsharp.exe

Use this tool to test connection strings and enter SQL queries 
to different ADO.NET providers in Mono.

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 Server=localhost;Database=test;User ID=someuser;Password=somepass
		
3. open a connection to the database via \open
	Example:
		SQL# \open

Here are the SQL# Commands taken from the help command: \h
SQL# Commands are case insensitive, so \Q and \q work the same.

CONNECTION AND PROVIDER COMMANDS
================================

\ConnectionString to set the ConnectionString
	
	Example connection strings for various providers:
	
	Microsoft SQL Server via System.Data.SqlClient or Mono.Data.TdsClient provider:
		SQL# \ConnectionString Server=DANPC;Database=pubs;User ID=danmorg;Password=freetds
		
	PostgreSQL via Npgsql provider:
		SQL# \ConnectionString Server=localhost;Database=test;User ID=postgres;Password=fun2db
		
	MySQL via ByteFX.Data.MySqlClient provider:
		SQL# \ConnectionString Server=localhost;Database=test;User ID=mysql;Password=

	MySQL via MySql.Data provider:
		SQL# \ConnectionString Server=localhost;Database=test;User ID=mysql;Password=mypass;Pooling=false
		
	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:
		SQL# \ConnectionString 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:
		SQL# \ConnectionString URI=file:SqliteTest.db
		
	OLE DB via System.Data.OleDb provider which connects to a PostgreSQL database:
		SQL# \ConnectionString Provider=PostgreSQL;Addr=127.0.0.1;Database=rodrigo
		
	Oracle via System.Data.OracleClient
		SQL# \ConnectionString Data Source=testdb;User ID=scott;Password=tiger

    FirebirdSql via FirebirdSql.Data.Firebird (not included with Mono)
 		SQL# \ConnectionString Database=C:\FIREBIRD\EXAMPLES\EMPLOYEE.FDB;User=SYSDBA;Password=masterkey;Dialect=3;Server=localhost
	

\Provider to set the Provider:

    Provider    Name          Namespace                  Assembly
    =========== ============= ========================== ==========================
	OleDb       OLE DB        System.Data.OleDb          System.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
	Tds         TDS Generic   Mono.Data.TdsClient        Mono.Data.TdsClient
	Oracle      Oracle 8i     System.Data.OracleClient   System.Data.OracleClient
	PostgreSql  NET Postgres  Npgsql                     Npgsql
	ByteFX      ByteFX MySQL  ByteFX.Data.MySqlClient    ByteFX.Data
	Firebird    Firebird      FirebirdSql.Data.Firebird  FirebirdSql.Data.Firebird
	MySql       MySQL AB      MySql.Data.MySqlClient     MySql.Data
	
	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.  No spaces in the assembly name.

	Example: to load the MySQL provider MySql.Data.dll from the GAC:
		SQL# \loadextprovider MySql.Data,Version=1.0.7.30073,Culture=neutral,PublicKeyToken=8e323390df8d9ed4 MySql.Data.MySqlCliet.MySqlConnection
	
\Open to open the connection

	Example:
		SQL# \open

\Close to close the connection

	Example:
		SQL# \close

\defaults to show default variables, such as, Provider and ConnectionString.

	Example:
		SQL# \defaults

\Q to quit
	Example:
		SQL# \q

SQL EXECUTION COMMANDS
======================
		
\e to 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 to execute an 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 to 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
	      
FILE COMMANDS
=============	           
	      
\f FILENAME to read a batch of SQL# commands from file

	Example:
		SQL# \f batch.sql#
		
	Note: the SQL# commands are interpreted as they are read.  If there is
	      any SQL statements, they 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 PURPOSE COMMANDS
========================

\h to show help (all commands).

	Example:
		SQL# \h

\s {TRUE, FALSE} to silent messages.

	Example 1:
		SQL# \s true
		
	Example 2:
		SQL# \s false
	
\r to reset or clear the query buffer.

	Example:
		SQL# \r

\print - show what's in the SQL buffer now.

	Example:
		SQL# \print

VARIABLES WHICH CAN BE USED AS PARAMETERS
=========================================

\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

PROVIDER SUPPORT OPTIONS
========================

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