OleDbCmd Usage Notes

Purpose:

This program allows you to execute SQL commands against an OleDB Database, eg a Microsoft Access database, an Excel spreadsheet, etc.

This can be a powerful adjunct to LGX Report (see www.freereporting.com) as it allows database creation to be scripted rather than manually creating a database with Microsoft Access.

Another useful free tool to load data into your database is Microsoft's LogParser 2.2 - search their site for the latest download location for this tool.

Usage: OleDBCmd [/help] [/ver] [/extendedSQL | /ODBC]
                [/connect="OleDB or Odbc connect string"| /file=filename]
                [/create] 
                [/script="sql script filename" |/cmd="sql command"]
                [/quiet] [/CSV|/XML[=style]] [/nohead] [/starComments] 
                [/dateFormat="date format"] [/boolText=TrueText+FalseText]
                [/quote=C] [/sep=C] [/delim=C]
                [/binOffset=n] [/binPrefix="file prefix"] [/binType=type]
		[/xmlRoot=ncname] [/xmlRow=ncname] [/xmlItem=ncname]
		[/outputEncoding=encodingname] [/outputFile=filename] 
		[/flush]

Parameters:

/binOffset=n /binPrefix="file prefix" /binType=type	These parameters control how binary data is dumped to a file and the file name is output as the field's value. Binary data is dumped to a file and the file's name is output as the field's value. Files are named: {binfix}[{record number}]{field name}.{binType} The binOffset parameter specifies the offset in the binary data to start dumping from. The defaults are: /binOffset=78 /binPrefix=BinData /binType=gif
/boolText	Text to output for boolean fields in recordsets. Format is TrueText+FalseText, and the default is 1+0.
/cmd	Specifies a single SQL command to execute.
/connect	Specifies the OleDB connection string for the database, eg: 'Provider=Microsoft.Jet.OLEDB.4.0; Data Source=test.mdb'
/create	The program will attempt to create the database.
/CSV	SQL results are output in CSV format rather than the default TSV format.
/dateFormat	Date format specifier for date fields in recordsets, default is "yyyy-MM-dd hh:mm:ss tt". See C# documentation for allowablecharacters.
/delim	Delimiter used to terminate SQL commands in batches or interactive mode. Default is ;
/emitEmpty	Emit field header for empty result sets.
/extendedSQL	Allow extended SQL statements to manage VIEW and P
/file	Specifies a file name, the provider will be set to Microsoft.Jet.OLEDB.4.0. Both .mdb and .xls files are handled.
/help	Print the usage details to stderr.
/nohead	Excludes the header row from the results.
/ODBC	Use ODBC connection rather than an OleDb connection. This can not be used with extendedSQL flag.
/quiet	Suppresses command echo and status messages. Only SQL output is written to the console.
/quote	Quote character for CSV output, default is ".
/script	Specifies a script file for the SQL commands to be read from. Note: Commands may span multiple lines and must be terminated with a semi-colon. Remark lines must start with --following any leading whitespace. Lines starting with --? are treated as prompts - ie the text following on the rest of the line is written to stdout and the user needs to press any key to proceed.
/sep	Separator character for CSV output, default is comma.
/starComments	Allows embedded and multi-line comments using /* ... */ format.
/ver	Print the version and license details to stdout.
/XML[=style]	SQL results are output in XML format rather than the default TSV format. If style is missing or 1 then items are identifed by and ID a table of column names is provided in the output. If style=2 then column names are used as item element names, with spaces mapped to underscore characters.
/xmlRoot	Name for the document root element, default is oledbcmdOutput.
/xmlRow	Name for the document row element, default is row.
/xmlItem	Name for item element when XML style set to 1.
/outputFile	If specified the results are written to the specified file, otherwise they are written to stdout.
/outputEncoding	Specifies the encoding to use for the output file file, default is utf-8.
/flush	If specified a "flush" is performed between each SQL command to ensure any database changes have been committed.
	Note, if neither the /script nor /cmd arguments are given commands are read from the console and comment processing is NOT performed.
	If no parameters are given the usage, version and license information is output. Note - Usage information is written to stderr, whereas version and license information is written to stdout.

Exit codes (check with ERRORLEVEL):

0 - All okay.
1 - No parameters.
2 - Program exception.
3 - Parameter error.

Custom SQL Extensions

The following features have been added to OleDbCmd by way of custom SQL extensions when the extendedSQL switch is used.

These features use Microsoft's ADOX library.

CREATE / ALTER / DROP VIEW	These support the ANSI-92 extensions available in MS-Access.
CREATE / ALTER / DROP PROCEDURE	Allows MS-Access procedures to be defined, modified and deleted. These are essentially parameterised queries.
ALTER PROPERTYOFTABLE	Allows custom table properties to be altered. Syntax is ALTER PROPERTYOFTABLE [table name] SET [property] = value;
ALTER PROPERTYOFFIELDIN	Allows custom field properties to be altered. Syntax is ALTER PROPERTYOFFIELDIN [table name] SET [property] = value FOR [field name]; eg to reset the seed for an auto-number field use: ALTER PROPERTYOFFIELDIN [my table] SET Seed = 25 FOR [my ID field];
CREATE LINKEDTABLE	Allows linked tables to be created. Syntax is CREATE LINKEDTABLE [local table name] FOR [remote table] WITH [DATASOURCE\|PROVIDER] 'connection string'; Where the connection string is either a Data sourcer string or a Provider string for the remote database.
ALTER LINKEDTABLE	Allows linked tables to be altered. Syntax is ALTER LINKEDTABLE [local table name] WITH [DATASOURCE\|PROVIDER] 'connection string'; Where the connection string is either a Data sourcer string or a Provider string for the remote database.
ALTER TABLEFIELD	Allows table data fields to be set from a file contents. Syntax is ALTER TABLEFIELD [table name] SET [field] = FILE 'file name' WHERE {select clause};
DROP LINKEDTABLE	Allows linked tables to be dropped. Syntax is DROP LINKEDTABLE [local table name];
SHOW TABLES	Lists all tables in the database.
SHOW VIEWS	Lists all views in the database with their definitions.
SHOW LINKEDTABLES	Lists linked tables in the database.
SHOW PROCEDURES	Lists all procedures in the database with their definitions.
Note, the brackets [] are only required around table, column or property names if the names include spaces.
Note: you can drop an object only if it exists using the syntax: DROP [TABLE\|VIEW\|PROCEDURE\|LINKEDTABLE] [object name] IF EXISTS;

Origin:

The orginal OleDBCmd was created by Oliver Duis - see http://www.oliverduis.de. 
			
Subsequently this has been extended by Ian Hogan - see http://THINKronicity.com.au.
			
Command line processing uses YACLAP by Sean Michael Murphy - see http://www.codeproject.com/csharp/YACLAP.asp