1 Mono SQL Query - Command Line Interface
2 =======================================
4 Running SQL Query on Mono:
7 Use this tool to test connection strings and enter SQL queries
8 to different ADO.NET providers in Mono.
10 Basically, there are five commands a user should know:
11 \provider, \connectionstring, \open, \quit, and \help
13 To connect to a database, you need to do the following:
14 1. set your data provider via \provider
18 2. set your connection string via \connectionstring
20 SQL# \connectionstring Server=localhost;Database=test;User ID=someuser;Password=somepass
22 3. open a connection to the database via \open
26 Here are the SQL# Commands taken from the help command: \h
27 SQL# Commands are case insensitive, so \Q and \q work the same.
29 CONNECTION AND PROVIDER COMMANDS
30 ================================
32 \ConnectionString to set the ConnectionString
34 Example connection strings for various providers:
36 Microsoft SQL Server via System.Data.SqlClient or Mono.Data.TdsClient provider:
37 SQL# \ConnectionString Server=DANPC;Database=pubs;User ID=danmorg;Password=freetds
39 PostgreSQL via Npgsql provider:
40 SQL# \ConnectionString Server=localhost;Database=test;User ID=postgres;Password=fun2db
42 MySQL via ByteFX.Data.MySqlClient provider:
43 SQL# \ConnectionString Server=localhost;Database=test;User ID=mysql;Password=
45 ODBC via System.Data.Odbc provider using a DSN named "MSSQLDSN" I set up
46 in the Windows control panel's ODBC Data Sources which connects
47 to Microsoft SQL Server 2000:
48 SQL# \ConnectionString DSN=MSSQLDSN;UID=danmorg;PWD=freetds
50 SQL Lite via Mono.Data.SqliteClient provider which connects to the
51 database file SqliteTest.db; if not found, the file is created:
52 SQL# \ConnectionString URI=file:SqliteTest.db
54 OLE DB via System.Data.OleDb provider which connects to a PostgreSQL database:
55 SQL# \ConnectionString Provider=PostgreSQL;Addr=127.0.0.1;Database=rodrigo
57 Oracle via System.Data.OracleClient
58 SQL# \ConnectionString Data Source=testdb;User ID=scott;Password=tiger
60 FirebirdSql via FirebirdSql.Data.Firebird (not included with Mono)
61 SQL# \ConnectionString Database=C:\FIREBIRD\EXAMPLES\EMPLOYEE.FDB;User=SYSDBA;Password=masterkey;Dialect=3;Server=localhost
64 \Provider to set the Provider:
66 Provider Name Namespace Assembly
67 =========== ============= ========================== ==========================
68 OleDb OLE DB System.Data.OleDb System.Data
69 SqlClient MS SQL 7/2000 System.Data.SqlClient System.Data
70 Odbc ODBC System.Data.Odbc System.Data
71 Sqlite SQL Lite Mono.Data.SqliteClient Mono.Data.SqliteClient
72 Sybase Sybase Mono.Data.SybaseClient Mono.Data.SybaseClient
73 Tds TDS Generic Mono.Data.TdsClient Mono.Data.TdsClient
74 Oracle Oracle 8i System.Data.OracleClient System.Data.OracleClient
75 PostgreSql NET Postgres Npgsql Npgsql
76 MySql ByteFX MySQL ByteFX.Data.MySqlClient ByteFX.Data
77 Firebird Firebird FirebirdSql.Data.Firebird FirebirdSql.Data.Firebird
79 Example: to set the provider for MySQL:
82 Note: if you need to load an external provider in SQL#,
83 see the SQL# command \loadextprovider
85 \loadextprovider ASSEMBLY CLASS to load an external provider
86 use the complete name of its assembly and
89 Example: to load the MySQL provider Mono.Data.MySql
90 SQL# \loadextprovider Mono.Data.MySql Mono.Data.MySql.MySqlConnection
92 \Open to open the connection
97 \Close to close the connection
102 \defaults to show default variables, such as, Provider and ConnectionString.
111 SQL EXECUTION COMMANDS
112 ======================
114 \e to execute SQL query (SELECT)
116 Example: to execute a query
118 SQL# SELECT * FROM EMPLOYEE
121 Note: to get \e to automatically work after entering a query, put a
122 semicolon ; at the end of the query.
124 Example: to enter and exectue query at the same time
126 SQL# SELECT * FROM EMPLOYEE;
128 \exenonquery to execute an SQL non query (not a SELECT)
130 Example: to insert a row into a table:
132 SQL# INSERT INTO SOMETABLE (COL1, COL2) VALUES('ABC','DEF')
135 Note: this can be used for those providers that are new and do not have
136 the ability to execute queries yet.
138 \exescalar to execute SQL to get a single row and single column.
140 Example: to execute a Maxium aggregate
141 SQL# SELECT MAX(grade) FROM class
144 \exexml FILENAME to execute SQL and save output to XML file
147 SQL# SELECT fname, lname, hire_date FROM employee
148 SQL# \exexml employee.xml
150 Note: this depends on DataAdapter, DataTable, and DataSet
151 to be working properly
156 \f FILENAME to read a batch of SQL# commands from file
161 Note: the SQL# commands are interpreted as they are read. If there is
162 any SQL statements, they are executed.
164 \o FILENAME to write result of commands executed to file.
169 \load FILENAME to load from file SQL commands into SQL buffer.
172 SQL# \load commands.sql
174 \save FILENAME to save SQL commands from SQL buffer to file.
177 SQL# \save commands.sql
179 GENERAL PURPOSE COMMANDS
180 ========================
182 \h to show help (all commands).
187 \s {TRUE, FALSE} to silent messages.
195 \r to reset or clear the query buffer.
200 \print - show what's in the SQL buffer now.
205 VARIABLES WHICH CAN BE USED AS PARAMETERS
206 =========================================
208 \set NAME VALUE to set an internal variable.
211 SQL# \set sFirstName John
213 \unset NAME to remove an internal variable.
216 SQL# \unset sFirstName
218 \variable NAME to display the value of an internal variable.
221 SQL# \variable sFirstName
223 PROVIDER SUPPORT OPTIONS
224 ========================
226 \UseParameters (TRUE,FALSE) to use parameters when executing SQL which
227 use the variables that were set.
229 If this option is true, the SQL
230 contains parameters, and for each parameter
231 which does not have a SQL# variable set, the
232 user will be prompted to enter the value
236 SQL# \useparameter true
240 \UseSimpleReader (TRUE,FALSE) to use simple reader when displaying results.
243 SQL# \usesimplereader true
245 Default: false. Mostly, this is dependent on the provider. If the provider
246 does not have enough of IDataReader implemented to have
247 the normal reader working, then the simple reader can be used.