1sqlsharp(1)                 General Commands Manual                sqlsharp(1)
2
3
4

NAME

6       sqlsharp - Mono SQL Query command-line tool
7

SYNOPSIS

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

DESCRIPTION

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

OPTIONS

16       The following options are supported:
17
18       -f filename
19              Output file to load SQL commands from.
20
21       -o filename
22              Output file to send results.
23
24       -s     Silent mode.
25

HOW TO USE

27       The SQL tool accepts commands via its command line interface.  Commands
28       begin with a backslash followed by the command name.
29
30       Example:
31            \open
32
33
34       Basically,  there are five commands a user should know:      \provider,
35       \connectionstring, \open, \quit, and \help
36
37       To connect to a database, you need to do the following:
38
39       1. set your data provider via \provider
40
41            Example:
42                 SQL# \provider mysql
43
44
45       2. set your connection string via \connectionstring
46
47            Example:
48                 SQL# \connectionstring Database=test
49
50
51       3. open a connection to the database via \open
52
53            Example:
54                 SQL# \open
55
56

CONNECTION AND PROVIDER COMMANDS

58       These commands are used to setup the provider, connection  string,  and
59       open/close the database connnection
60
61       ConnectionString
62              Sets the Connection String
63
64              Example:
65                   SQL# \ConnectionString Database=testdb
66                   or
67                   SQL# \cs Database=testdb
68
69              For more examples, see section CONNECTION STRING EXAMPLES.
70
71
72       Provider
73              Sets  the  Provider  of the Data Source.  For list of Providers,
74              see section PROVIDERS.
75
76              Example: to set the provider for MySQL:
77                   SQL# \provider mysql
78                         or
79                   SQL# \p mysql
80
81              Note: if you need to load an external provider in SQL#,
82                    see the SQL# command \loadextprovider
83
84
85       ListProviders
86              List ADO.NET 2.0 Providers available
87
88              Example:
89                   SQL# \ListProviders
90                      or
91                   SQL# \listp
92
93
94       BCS    Prompts you for building each connection  parameter  and  builds
95              the  connection  string  and also allows you to enter a password
96              wich does not echo.
97
98              Example:
99                   SQL# \bcs
100
101                   ConnectionString Option: Data Source [] SQL# blazer
102
103                   ConnectionString Option: Persist Security Info [False] SQL#
104
105                   ConnectionString Option: Integrated Security [False] SQL#
106
107                   ConnectionString Option: User ID [] SQL# scott
108
109                   Password: *****
110
111                   ConnectionString Option: Enlist [False] SQL#
112
113                   ConnectionString Option: Pooling [True] SQL#
114
115                   ConnectionString Option: Min Pool Size [0] SQL#
116
117                   ConnectionString Option: Max Pool Size [100] SQL#
118
119                   ConnectionString Option: Unicode [False] SQL#
120
121                   ConnectionString Option: Load Balance Timeout [0] SQL#
122
123                   ConnectionString Option: Omit Oracle Connection Name [False] SQL#
124                   ConnectionString is set.
125
126
127       LoadExtProvider
128              ASSEMBLY CLASS to load an external provider.  Use  the  complete
129              name of its assembly and its Connection class.
130
131              Example: to load the MySQL provider Mono.Data.MySql
132                   SQL# \loadextprovider Mono.Data.MySql Mono.Data.MySql.MySqlConnection
133
134
135       Open   Opens a connection to the database
136
137              Example:
138                   SQL# \open
139
140
141       Close  Closes the connection to the database
142
143              Example:
144                   SQL# \close
145
146
147       Default
148              show default variables, such as, Provider and ConnectionString.
149
150              Example:
151                   SQL# \defaults
152
153
154       Q      Quit
155
156              Example:
157                   SQL# \q
158
159

SQL EXECUTION COMMANDS

161       Commands to execute SQL statements
162
163       e execute SQL query (SELECT)
164
165       Example: to execute a query
166
167            SQL# SELECT * FROM EMPLOYEE
168            SQL# \e
169
170       Note: to get \e to automatically work after entering a query, put a
171             semicolon ; at the end of the query.
172
173       Example: to enter and exectue query at the same time
174
175            SQL# SELECT * FROM EMPLOYEE;
176
177
178       exenonquery
179              execute a SQL non query (not a SELECT)
180
181              Example: to insert a row into a table:
182
183                   SQL# INSERT INTO SOMETABLE (COL1, COL2) VALUES('ABC','DEF')
184                   SQL# \exenonquery
185
186              Note: this can be used for those providers that are new and do not have
187                    the ability to execute queries yet.
188
189
190       exescalar
191              execute SQL to get a single row and single column.
192
193              Example: to execute a Maxium aggregate
194                        SQL# SELECT MAX(grade) FROM class
195                        SQL# \exescalar
196
197
198       exexml FILENAME to execute SQL and save output to XML file
199
200              Example:
201                   SQL# SELECT fname, lname, hire_date FROM employee
202                   SQL# \exexml employee.xml
203
204              Note: this depends on DataAdapter, DataTable, and DataSet
205                    to be working properly
206
207
208
209       FILE COMMANDS
210              Commands for importing commands from file to SQL# and vice versa
211
212       f      FILENAME to read a batch of SQL# commands from file
213
214              Example:
215                   SQL# \f batch.sql#
216
217              Note: the SQL# commands are interpreted as they are read.  If there is
218                    any SQL statements, the are executed.
219
220
221       o      FILENAME to write result of commands executed to file.
222
223              Example:
224                   SQL# \o result.txt
225
226
227       load   FILENAME to load from file SQL commands into SQL buffer.
228
229              Example:
230                   SQL# \load commands.sql
231
232
233       save   FILENAME to save SQL commands from SQL buffer to file.
234
235              Example:
236                   SQL# \save commands.sql
237
238

GENERAL PURPOSE COMMANDS

240       General commands to use.
241
242       h      show help (all commands).
243
244              Example:
245                   SQL# \h
246
247
248       s      TRUE, FALSE to silent messages.
249
250              Example 1:
251                   SQL# \s true
252
253              Example 2:
254                   SQL# \s false
255
256
257       r      reset or clear the query buffer.
258
259              Example:
260                   SQL# \r
261
262
263       print  show what's in the SQL buffer now.
264
265              Example:
266                   SQL# \print
267
268              SH  VARIABLES  WHICH  CAN  BE USED AS PARAMETERS Commands to set
269              variables which can be used as Parameters in an  SQL  statement.
270              If  the SQL contains any parameters, the parameter does not have
271              a variable set, the user will be prompted for the value for each
272              missing parameter.
273
274       set    NAME VALUE to set an internal variable.
275
276              Example:
277                   SQL# \set sFirstName John
278
279
280       unset  NAME to remove an internal variable.
281
282              Example:
283                   SQL# \unset sFirstName
284
285
286       variable
287              NAME to display the value of an internal variable.
288
289              Example:
290                   SQL# \variable sFirstName
291
292

PROVIDER SUPPORT OPTIONS

294       Enable or Disble support for a particular provider option
295
296       UseParameters
297              TRUE,FALSE  to  use  parameters when executing SQL which use the
298              variables that were set.
299
300       If this option is true, the  SQL  contains  parameters,  and  for  each
301       parameter  which  does  not  have a SQL# variable set, the user will be
302       prompted to enter the value For that parameter.
303
304       Example:
305            SQL# \useparameter true
306
307
308       Default: false
309
310       UseSimpleReader
311              TRUE,FALSE to use simple reader when displaying results.
312
313              Example:
314                   SQL# \usesimplereader true
315
316
317       Default: false.  Mostly, this is dependent on  the  provider.   If  the
318       provider  does  not  have enough of IDataReader implemented to have the
319       normal reader working, then the simple reader can be  used.   Providers
320       like  SqlClient,  MySQL,  and  PostgreSQL have this ption defaulting to
321       true.
322

PROVIDERS

324       PROVIDER   NAME          NAMESPACE                  ASSEMBLY
325
326       oracle     Oracle 8i-11g System.Data.OracleClient   System.Data.OracleClient
327       postgresql NetPostgreSQL Npgsql                     Npgsql
328       bytefx     ByteFX MySQL  ByteFX.Data.MySqlClient    ByteFX.Data
329       sqlclient  MS SQL 7-2008 System.Data.SqlClient      System.Data
330       odbc       ODBC          System.Data.Odbc           System.Data
331       sqlite     SQL Lite      Mono.Data.SqliteClient     Mono.Data.SqliteClient
332       sybase     Sybase        Mono.Data.SybaseClient     Mono.Data.SybaseClient
333       firebird   Firebird SQL  FirebirdSql.Data.FirebirdSql FirebirdSql.Data.Firebird
334       mysql      MySQL AB      MySql.Data.MySqlClient     MySql.Data
335
336       NOTES:
337
338       Ngsql is the .Net Data Provider for PosgreSQL.  The
339       latest verison can be downloaded from
340       http://npgsql.projects.postgresql.org/
341
342       MySql.Data is the MySQL Connector/Net for connecting to MySql databases.
343       For MySQL, it is strongly recommend to use MySql.Data instead of the old
344       ByteFX.Data provider.  Unfortunately, MySql.Data is not included with Mono.
345       You can download the latest MySQL Connector/Net from MySQL AB at
346       http://dev.mysql.com/downloads/
347
348       FirebirdSql.Data.Firebird can be downloaded from here:
349       http://www.firebirdsql.org/index.php?op=files&id=netprovider
350
351

CONNECTION STRING SAMPLES

353       Example connection strings for various providers to  be  used  via  the
354       command \ConnectionString
355
356       Example of usage:
357            \connectionstring Database=testdb
358
359
360       Connection String examples:
361
362
363       Microsoft SQL Server via System.Data.SqlClient
364
365            Server=DANPC;Database=pubs;User ID=saPassword=;
366
367            For Integrated Security, bear in mind that Mono is not
368            integrated with Windows, SQL Server client nor server, nor
369            Windows Server.  Therefore, you must provide the Windows Domain
370            name and domain user name and password for this user.
371
372            Server=DANPC;Database=pubs;User ID=DOMAINser;Password=pass;Integrated Security=SSPI
373
374            For a server locally, you can use localhost.
375
376       ODBC via System.Data.Odbc provider using
377       a DSN named "MSSQLDSN"   I set up
378       in the Windows control panel's ODBC Data Sources
379       which connects to Microsoft SQL Server 2000:
380
381            DSN=MSSQLDSN;UID=danmorg;PWD=freetds
382
383       To use ODBC ON Unix, consider unixODBC from http://www.unixodbc.org/
384       or use iODBC from http://www.iodbc.org/
385
386       SQL Lite via Mono.Data.SqliteClient
387       provider which connects to the
388       database file SqliteTest.db; if not found,
389       the file is created:
390
391            URI=file:SqliteTest.db
392
393       Oracle via System.Data.OracleClient
394
395            Data Source=testdb;User ID=scott;Password=tiger
396
397               If you prefer to not use a tnsnames.ora file, you can
398            use a connection string which allows a
399            TNS network description that is parentheses delimited
400            like the following which has the host, port, and
401            service name.  For host, you can specify an IP address
402               instead of a hostname.
403
404       User ID=SCOTT;
405       Password=TIGER;
406       Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.101)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=TESTDB)))
407
408
409       Npgsql (.NET PostgreSQL) from
410       http://gborg.postgresql.org/project/npgsql/projdisplay.php
411
412            Server=localhost;Database=test;User ID=postgres;Password=fun2db
413
414
415       ByteFX (ByteFX MySQL) from
416
417            Please use MySql.Data instead.
418
419            Server=localhost;Database=test;User ID=mysql;Password=
420
421
422       FirebirdSql via FirebirdSql.Data.Firebird (download latest form FirebirdSql.org)
423
424            Database=C:\FIREBIRD\EXAMPLES\EMPLOYEE.FDB;User=SYSDBA;Password=masterkey;Dialect=3;Server=localhost
425
426
427       MySQL via (MySql.Data) MySQL Connector/Net from http://www.mysql.com/
428
429            Server=localhost;Database=test;User ID=mysql;Password=mypass;Pooling=false
430
431
432

TRACING SUPPORT

434       No support for tracing right now.
435

AUTHORS

437       The Mono SQL Query Tool was written
438       by Daniel Morgan <monodanmorg@yahoo.com>
439

LICENSE

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

BUGS

446       To  report  bugs  in  the compiler, you can use `bug-buddy', or you can
447       file bug reports in our bug tracking system:
448       http://bugzilla.xamarin.com.
449

MAILING LISTS

451       For details, visit:
452       http://lists.ximian.com/mailman/listinfo/mono-devel-list
453

WEB SITE

455       For details, visit:
456       http://www.mono-project.com
457

SEE ALSO

459       mono(1)
460
461
462
463
464                               9 September 2008                    sqlsharp(1)