1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
|
.TH sqlsharp 1 "25 December 2005"
.SH NAME
sqlsharp \- Mono SQL Query command-line tool
.SH SYNOPSIS
.B sqlsharp
[\-f filename] [\-o filename] [\-s]
.SH DESCRIPTION
sqlsharp is a Mono SQL tool used for entering SQL queries
to a database using Mono data providers.
.PP
.SH OPTIONS
The following options are supported:
.TP
.I "-f filename"
Output file to load SQL commands from.
.TP
.I "-o filename"
Output file to send results.
.TP
.I "-s"
Silent mode.
.PP
.SH HOW TO USE
The SQL tool accepts commands via its command line interface. Commands
begin with a backslash followed by the command name.
.PP
Example:
.nf
\\open
.fi
.PP
Basically, there are five commands a user should know:
\\provider, \\connectionstring, \\open, \\quit, and \\help
.PP
To connect to a database, you need to do the following:
.PP
1. set your data provider via \\provider
.PP
.nf
Example:
SQL# \\provider mysql
.fi
.PP
2. set your connection string via \\connectionstring
.PP
.nf
Example:
SQL# \\connectionstring Database=test
.fi
.PP
3. open a connection to the database via \\open
.PP
.nf
Example:
SQL# \\open
.fi
.PP
.SH CONNECTION AND PROVIDER COMMANDS
These commands are used to setup the provider,
connection string, and open/close the database connnection
.TP
.I "ConnectionString"
Sets the Connection String
.nf
Example:
SQL# \\ConnectionString Database=testdb
For more examples, see section CONNECTION STRING EXAMPLES.
.fi
.TP
.I "Provider"
Sets the Provider of the Data Source. For list of Providers, see section PROVIDERS.
.nf
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
.fi
.TP
.I "LoadExtProvider"
ASSEMBLY CLASS to load an external provider. Use the complete name
of its assembly and its Connection class.
.nf
Example: to load the MySQL provider Mono.Data.MySql
SQL# \\loadextprovider Mono.Data.MySql Mono.Data.MySql.MySqlConnection
.fi
.TP
.I "Open"
Opens a connection to the database
.nf
Example:
SQL# \\open
.fi
.TP
.I "Close"
Closes the connection to the database
.nf
Example:
SQL# \\close
.fi
.TP
.I "Default"
show default variables, such as, Provider and ConnectionString.
.nf
Example:
SQL# \\defaults
.fi
.TP
.I "Q"
Quit
.nf
Example:
SQL# \\q
.fi
.SH SQL EXECUTION COMMANDS
Commands to execute SQL statements
.PP
.TR
.I "e"
execute SQL query (SELECT)
.nf
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;
.fi
.TP
.I "exenonquery"
execute a SQL non query (not a SELECT)
.nf
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.
.fi
.TP
.I "exescalar"
execute SQL to get a single row and single column.
.nf
Example: to execute a Maxium aggregate
SQL# SELECT MAX(grade) FROM class
SQL# \\exescalar
.fi
.TP
.I "exexml"
FILENAME to execute SQL and save output to XML file
.nf
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
.fi
.TP
.SH FILE COMMANDS
Commands for importing commands from file to SQL# and vice versa
.TP
.I "f"
FILENAME to read a batch of SQL# commands from file
.nf
Example:
SQL# \\f batch.sql#
Note: the SQL# commands are interpreted as they are read. If there is
any SQL statements, the are executed.
.fi
.TP
.I "o"
FILENAME to write result of commands executed to file.
.nf
Example:
SQL# \\o result.txt
.fi
.TP
.I "load"
FILENAME to load from file SQL commands into SQL buffer.
.nf
Example:
SQL# \\load commands.sql
.fi
.TP
.I "save"
FILENAME to save SQL commands from SQL buffer to file.
.nf
Example:
SQL# \\save commands.sql
.fi
.SH GENERAL PURPOSE COMMANDS
General commands to use.
.TP
.I "h"
show help (all commands).
.nf
Example:
SQL# \\h
.fi
.TP
.I "s"
TRUE, FALSE to silent messages.
.nf
Example 1:
SQL# \\s true
Example 2:
SQL# \\s false
.fi
.TP
.I "r"
reset or clear the query buffer.
.nf
Example:
SQL# \\r
.fi
.TP
.I "print"
show what's in the SQL buffer now.
.nf
Example:
SQL# \\print
.fi
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.
.TP
.I "set"
NAME VALUE to set an internal variable.
.nf
Example:
SQL# \\set sFirstName John
.fi
.TP
.I "unset"
NAME to remove an internal variable.
.nf
Example:
SQL# \\unset sFirstName
.fi
.TP
.I "variable"
NAME to display the value of an internal variable.
.nf
Example:
SQL# \\variable sFirstName
.fi
.SH PROVIDER SUPPORT OPTIONS
Enable or Disble support for a particular provider option
.TP
.I "UseParameters"
TRUE,FALSE to use parameters when executing SQL which
use the variables that were set.
.PP
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.
.nf
Example:
SQL# \\useparameter true
.fi
.PP
Default: false
.TP
.I "UseSimpleReader"
TRUE,FALSE to use simple reader when displaying results.
.nf
Example:
SQL# \\usesimplereader true
.fi
.PP
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.
.PP
.SH PROVIDERS
.nf
PROVIDER NAME NAMESPACE ASSEMBLY
oracle Oracle 8i System.Data.OracleClient System.Data.OracleClient
postgresql NetPostgreSQL Npgsql Npgsql
bytefx ByteFX MySQL ByteFX.Data.MySqlClient ByteFX.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
olebb OLE DB System.Data.OleDb System.Data
tds TDS Generic Mono.Data.TdsClient Mono.Data.TdsClient
msodbc MS ODBC Microsoft.Data.Odbc Microsoft.Data.Odbc
firebird Firebird SQL FirebirdSql.Data.FirebirdSql FirebirdSql.Data.Firebird
mysql MySQL AB MySql.Data.MySqlClient MySql.Data
NOTES:
npgsql maps to postgresql above.
mysqlnet maps to mysql above.
odbc is treated as an external provider for .NET 1.1 and above.
msodbc is an external provider for compatibility with .NET 1.0
MySql.Data has replaced the mysql provider. If you still need to use ByteFX.Data, then
use bytefx.
MySql.Data is not included with Mono. You need to
download the MySQL Connector/Net from MySQL AB at
http://dev.mysql.com/downloads/connector/net/1.0.html
.fi
.SH CONNECTION STRING SAMPLES
Example connection strings for various providers to be used via the
command \\ConnectionString
.nf
Example of usage:
\\connectionstring Database=testdb
Connection String examples:
Microsoft SQL Server via System.Data.SqlClient
or Mono.Data.TdsClient provider:
Server=DANPC;Database=pubs;User ID=saPassword=
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
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
OLE DB via System.Data.OleDb provider
which connects to a PostgreSQL database:
Provider=PostgreSQL;Addr=127.0.0.1;Database=rodrigo
Oracle via System.Data.OracleClient
Data Source=testdb;User ID=scott;Password=tiger
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
Server=localhost;Database=test;User ID=mysql;Password=
FirebirdSql via FirebirdSql.Data.Firebird (not included with Mono)
Database=C:\\FIREBIRD\\EXAMPLES\\EMPLOYEE.FDB;User=SYSDBA;Password=masterkey;Dialect=3;Server=localhost
MySQL (MySQL AB) from http://www.mysql.com/
Server=localhost;Database=test;User ID=mysql;Password=mypass;Pooling=false
.fi
.SH TRACING SUPPORT
No support for tracing right now.
.SH AUTHORS
The Mono SQL Query Tool was written
.nf
by Daniel Morgan <danielmorgan@verizon.net>
.fi
.PP
.SH LICENSE
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.
.SH BUGS
To report bugs in the compiler, you can use `bug-buddy', or you can
file bug reports in our bug tracking system:
.nf
http://bugzilla.ximian.com.
.fi
.PP
.SH MAILING LISTS
For details, visit:
.nf
http://lists.ximian.com/mailman/listinfo/mono-devel-list
.fi
.SH WEB SITE
For details, visit:
.nf
http://www.mono-project.com
.fi
.PP
.SH SEE ALSO
mono(1), mint(1)
|