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 3. open a connection to the database via \open 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 or SQL# \cs 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 or SQL# \p mysql Note: if you need to load an external provider in SQL#, see the SQL# command \loadextprovider ListProviders List ADO.NET 2.0 Providers available Example: SQL# \ListProviders or SQL# \listp BCS Prompts you for building each connection parameter and builds the connection string and also allows you to enter a password wich does not echo. Example: SQL# cs ConnectionString Option: Data Source  SQL# blazer ConnectionString Option: Persist Security Info [False] SQL# ConnectionString Option: Integrated Security [False] SQL# ConnectionString Option: User ID  SQL# scott Password: ***** ConnectionString Option: Enlist [False] SQL# ConnectionString Option: Pooling [True] SQL# ConnectionString Option: Min Pool Size  SQL# ConnectionString Option: Max Pool Size  SQL# ConnectionString Option: Unicode [False] SQL# ConnectionString Option: Load Balance Timeout  SQL# ConnectionString Option: Omit Oracle Connection Name [False] SQL# ConnectionString is set. 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 FILE COMMANDS Commands for importing commands from file to SQL# and vice versa 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, 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-11g System.Data.OracleClient System.Data.OracleClient postgresql NetPostgreSQL Npgsql Npgsql bytefx ByteFX MySQL ByteFX.Data.MySqlClient ByteFX.Data sqlclient MS SQL 7-2008 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 firebird Firebird SQL FirebirdSql.Data.FirebirdSql FirebirdSql.Data.Firebird mysql MySQL AB MySql.Data.MySqlClient MySql.Data NOTES: Ngsql is the .Net Data Provider for PosgreSQL. The latest verison can be downloaded from http://npgsql.projects.postgresql.org/ MySql.Data is the MySQL Connector/Net for connecting to MySql databases. For MySQL, it is strongly recommend to use MySql.Data instead of the old ByteFX.Data provider. Unfortunately, MySql.Data is not included with Mono. You can download the latest MySQL Connector/Net from MySQL AB at http://dev.mysql.com/downloads/ FirebirdSql.Data.Firebird can be downloaded from here: http://www.firebirdsql.org/index.php?op=files&id=netprovider
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 Server=DANPC;Database=pubs;User ID=saPassword=; For Integrated Security, bear in mind that Mono is not integrated with Windows, SQL Server client nor server, nor Windows Server. Therefore, you must provide the Windows Domain name and domain user name and password for this user. Server=DANPC;Database=pubs;User ID=DOMAINser;Password=pass;Integrated Security=SSPI For a server locally, you can use localhost. 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 To use ODBC ON Unix, consider unixODBC from http://www.unixodbc.org/ or use iODBC from http://www.iodbc.org/ 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 Oracle via System.Data.OracleClient Data Source=testdb;User ID=scott;Password=tiger If you prefer to not use a tnsnames.ora file, you can use a connection string which allows a TNS network description that is parentheses delimited like the following which has the host, port, and service name. For host, you can specify an IP address instead of a hostname. User ID=SCOTT; Password=TIGER; Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.101)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=TESTDB))) 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 Please use MySql.Data instead. Server=localhost;Database=test;User ID=mysql;Password= FirebirdSql via FirebirdSql.Data.Firebird (download latest form FirebirdSql.org) Database=C:\FIREBIRD\EXAMPLES\EMPLOYEE.FDB;User=SYSDBA;Password=masterkey;Dialect=3;Server=localhost MySQL via (MySql.Data) MySQL Connector/Net 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 <email@example.com>
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.xamarin.com.
For details, visit: http://lists.ximian.com/mailman/listinfo/mono-devel-list
For details, visit: http://www.mono-project.com
Personal Opportunity - Free software gives you access to billions of dollars of software at no cost. Use this software for your business, personal use or to develop a profitable skill. Access to source code provides access to a level of capabilities/information that companies protect though copyrights. Open source is a core component of the Internet and it is available to you. Leverage the billions of dollars in resources and capabilities to build a career, establish a business or change the world. The potential is endless for those who understand the opportunity.
Business Opportunity - Goldman Sachs, IBM and countless large corporations are leveraging open source to reduce costs, develop products and increase their bottom lines. Learn what these companies know about open source and how open source can give you the advantage.
Free Software provides computer programs and capabilities at no cost but more importantly, it provides the freedom to run, edit, contribute to, and share the software. The importance of free software is a matter of access, not price. Software at no cost is a benefit but ownership rights to the software and source code is far more significant.
Free Office Software - The Libre Office suite provides top desktop productivity tools for free. This includes, a word processor, spreadsheet, presentation engine, drawing and flowcharting, database and math applications. Libre Office is available for Linux or Windows.
The Free Books Library is a collection of thousands of the most popular public domain books in an online readable format. The collection includes great classical literature and more recent works where the U.S. copyright has expired. These books are yours to read and use without restrictions.
Source Code - Want to change a program or know how it works? Open Source provides the source code for its programs so that anyone can use, modify or learn how to write those programs themselves. Visit the GNU source code repositories to download the source.
Study at Harvard, Stanford or MIT - Open edX provides free online courses from Harvard, MIT, Columbia, UC Berkeley and other top Universities. Hundreds of courses for almost all major subjects and course levels. Open edx also offers some paid courses and selected certifications.
Linux Manual Pages - A man or manual page is a form of software documentation found on Linux/Unix operating systems. Topics covered include computer programs (including library and system calls), formal standards and conventions, and even abstract concepts.