1 .TH sqlsharp 1 "9 September 2008"
3 sqlsharp \- Mono SQL Query command-line tool
6 [\-f filename] [\-o filename] [\-s]
8 sqlsharp is a Mono SQL tool used for entering SQL queries
9 to a database using Mono data providers.
12 The following options are supported:
15 Output file to load SQL commands from.
18 Output file to send results.
24 The SQL tool accepts commands via its command line interface. Commands
25 begin with a backslash followed by the command name.
33 Basically, there are five commands a user should know:
34 \\provider, \\connectionstring, \\open, \\quit, and \\help
36 To connect to a database, you need to do the following:
38 1. set your data provider via \\provider
46 2. set your connection string via \\connectionstring
50 SQL# \\connectionstring Database=test
54 3. open a connection to the database via \\open
62 .SH CONNECTION AND PROVIDER COMMANDS
63 These commands are used to setup the provider,
64 connection string, and open/close the database connnection
67 Sets the Connection String
71 SQL# \\ConnectionString Database=testdb
73 SQL# \\cs Database=testdb
75 For more examples, see section CONNECTION STRING EXAMPLES.
80 Sets the Provider of the Data Source. For list of Providers, see section PROVIDERS.
83 Example: to set the provider for MySQL:
88 Note: if you need to load an external provider in SQL#,
89 see the SQL# command \\loadextprovider
94 List ADO.NET 2.0 Providers available
105 Prompts you for building each connection parameter and builds the connection string
106 and also allows you to enter a password wich does not echo.
112 ConnectionString Option: Data Source [] SQL# blazer
114 ConnectionString Option: Persist Security Info [False] SQL#
116 ConnectionString Option: Integrated Security [False] SQL#
118 ConnectionString Option: User ID [] SQL# scott
122 ConnectionString Option: Enlist [False] SQL#
124 ConnectionString Option: Pooling [True] SQL#
126 ConnectionString Option: Min Pool Size [0] SQL#
128 ConnectionString Option: Max Pool Size [100] SQL#
130 ConnectionString Option: Unicode [False] SQL#
132 ConnectionString Option: Load Balance Timeout [0] SQL#
134 ConnectionString Option: Omit Oracle Connection Name [False] SQL#
135 ConnectionString is set.
140 ASSEMBLY CLASS to load an external provider. Use the complete name
141 of its assembly and its Connection class.
144 Example: to load the MySQL provider Mono.Data.MySql
145 SQL# \\loadextprovider Mono.Data.MySql Mono.Data.MySql.MySqlConnection
150 Opens a connection to the database
159 Closes the connection to the database
168 show default variables, such as, Provider and ConnectionString.
184 .SH SQL EXECUTION COMMANDS
185 Commands to execute SQL statements
189 execute SQL query (SELECT)
192 Example: to execute a query
194 SQL# SELECT * FROM EMPLOYEE
197 Note: to get \\e to automatically work after entering a query, put a
198 semicolon ; at the end of the query.
200 Example: to enter and exectue query at the same time
202 SQL# SELECT * FROM EMPLOYEE;
207 execute a SQL non query (not a SELECT)
210 Example: to insert a row into a table:
212 SQL# INSERT INTO SOMETABLE (COL1, COL2) VALUES('ABC','DEF')
215 Note: this can be used for those providers that are new and do not have
216 the ability to execute queries yet.
221 execute SQL to get a single row and single column.
224 Example: to execute a Maxium aggregate
225 SQL# SELECT MAX(grade) FROM class
231 FILENAME to execute SQL and save output to XML file
235 SQL# SELECT fname, lname, hire_date FROM employee
236 SQL# \\exexml employee.xml
238 Note: this depends on DataAdapter, DataTable, and DataSet
239 to be working properly
244 Commands for importing commands from file to SQL# and vice versa
247 FILENAME to read a batch of SQL# commands from file
253 Note: the SQL# commands are interpreted as they are read. If there is
254 any SQL statements, the are executed.
259 FILENAME to write result of commands executed to file.
268 FILENAME to load from file SQL commands into SQL buffer.
272 SQL# \\load commands.sql
277 FILENAME to save SQL commands from SQL buffer to file.
281 SQL# \\save commands.sql
284 .SH GENERAL PURPOSE COMMANDS
285 General commands to use.
288 show help (all commands).
297 TRUE, FALSE to silent messages.
309 reset or clear the query buffer.
318 show what's in the SQL buffer now.
325 SH VARIABLES WHICH CAN BE USED AS PARAMETERS
326 Commands to set variables which can be used as Parameters in an SQL statement. If the
327 SQL contains any parameters, the parameter does not have a variable set, the
328 user will be prompted for the value for each missing parameter.
331 NAME VALUE to set an internal variable.
335 SQL# \\set sFirstName John
340 NAME to remove an internal variable.
344 SQL# \\unset sFirstName
349 NAME to display the value of an internal variable.
353 SQL# \\variable sFirstName
356 .SH PROVIDER SUPPORT OPTIONS
357 Enable or Disble support for a particular provider option
360 TRUE,FALSE to use parameters when executing SQL which
361 use the variables that were set.
363 If this option is true, the SQL
364 contains parameters, and for each parameter
365 which does not have a SQL# variable set, the
366 user will be prompted to enter the value
371 SQL# \\useparameter true
378 TRUE,FALSE to use simple reader when displaying results.
382 SQL# \\usesimplereader true
386 Default: false. Mostly, this is dependent on the provider. If the provider
387 does not have enough of IDataReader implemented to have
388 the normal reader working, then the simple reader can be used.
389 Providers like SqlClient, MySQL, and PostgreSQL have this
390 ption defaulting to true.
395 PROVIDER NAME NAMESPACE ASSEMBLY
397 oracle Oracle 8i-11g System.Data.OracleClient System.Data.OracleClient
398 postgresql NetPostgreSQL Npgsql Npgsql
399 bytefx ByteFX MySQL ByteFX.Data.MySqlClient ByteFX.Data
400 sqlclient MS SQL 7-2008 System.Data.SqlClient System.Data
401 odbc ODBC System.Data.Odbc System.Data
402 sqlite SQL Lite Mono.Data.SqliteClient Mono.Data.SqliteClient
403 sybase Sybase Mono.Data.SybaseClient Mono.Data.SybaseClient
404 firebird Firebird SQL FirebirdSql.Data.FirebirdSql FirebirdSql.Data.Firebird
405 mysql MySQL AB MySql.Data.MySqlClient MySql.Data
409 Ngsql is the .Net Data Provider for PosgreSQL. The
410 latest version can be downloaded from
411 http://npgsql.projects.postgresql.org/
413 MySql.Data is the MySQL Connector/Net for connecting to MySql databases.
414 For MySQL, it is strongly recommend to use MySql.Data instead of the old
415 ByteFX.Data provider. Unfortunately, MySql.Data is not included with Mono.
416 You can download the latest MySQL Connector/Net from MySQL AB at
417 http://dev.mysql.com/downloads/
419 FirebirdSql.Data.Firebird can be downloaded from here:
420 http://www.firebirdsql.org/index.php?op=files&id=netprovider
423 .SH CONNECTION STRING SAMPLES
424 Example connection strings for various providers to be used via the
425 command \\ConnectionString
429 \\connectionstring Database=testdb
432 Connection String examples:
435 Microsoft SQL Server via System.Data.SqlClient
437 Server=DANPC;Database=pubs;User ID=saPassword=;
439 For Integrated Security, bear in mind that Mono is not
440 integrated with Windows, SQL Server client nor server, nor
441 Windows Server. Therefore, you must provide the Windows Domain
442 name and domain user name and password for this user.
444 Server=DANPC;Database=pubs;User ID=DOMAIN\user;Password=pass;Integrated Security=SSPI
446 For a server locally, you can use localhost.
448 ODBC via System.Data.Odbc provider using
449 a DSN named "MSSQLDSN" I set up
450 in the Windows control panel's ODBC Data Sources
451 which connects to Microsoft SQL Server 2000:
453 DSN=MSSQLDSN;UID=danmorg;PWD=freetds
455 To use ODBC ON Unix, consider unixODBC from http://www.unixodbc.org/
456 or use iODBC from http://www.iodbc.org/
458 SQL Lite via Mono.Data.SqliteClient
459 provider which connects to the
460 database file SqliteTest.db; if not found,
463 URI=file:SqliteTest.db
465 Oracle via System.Data.OracleClient
467 Data Source=testdb;User ID=scott;Password=tiger
469 If you prefer to not use a tnsnames.ora file, you can
470 use a connection string which allows a
471 TNS network description that is parentheses delimited
472 like the following which has the host, port, and
473 service name. For host, you can specify an IP address
474 instead of a hostname.
478 Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.101)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=TESTDB)))
481 Npgsql (.NET PostgreSQL) from
482 http://gborg.postgresql.org/project/npgsql/projdisplay.php
484 Server=localhost;Database=test;User ID=postgres;Password=fun2db
487 ByteFX (ByteFX MySQL) from
489 Please use MySql.Data instead.
491 Server=localhost;Database=test;User ID=mysql;Password=
494 FirebirdSql via FirebirdSql.Data.Firebird (download latest form FirebirdSql.org)
496 Database=C:\\FIREBIRD\\EXAMPLES\\EMPLOYEE.FDB;User=SYSDBA;Password=masterkey;Dialect=3;Server=localhost
499 MySQL via (MySql.Data) MySQL Connector/Net from http://www.mysql.com/
501 Server=localhost;Database=test;User ID=mysql;Password=mypass;Pooling=false
506 No support for tracing right now.
508 The Mono SQL Query Tool was written
510 by Daniel Morgan <monodanmorg@yahoo.com>
514 The Mono SQL Query Tool is released under the terms of the GNU GPL.
515 Please read the accompanying `COPYING' file for details. Alternative
516 licenses are available from Novell or Daniel Morgan.
518 To report bugs in the compiler, you can
519 file bug reports in our bug tracking system:
521 https://github.com/mono/mono/issues
527 http://lists.ximian.com/mailman/listinfo/mono-devel-list
532 http://www.mono-project.com