Runs an SQL command on an ODBC database, PC Windows only (D.B. Baird).
Options
WARNINGDIALOGS = string token |
Whether dialogs giving ODBC error and warning messages are presented (display, omit); default disp |
|---|---|
DRIVER = scalar |
Driver version (either 32 or 64) to use with the 64-bit version of Genstat; default 64 |
Parameters
COMMAND = texts |
Specifies SQL commands to run on the database |
|---|---|
DB = texts |
Database connection string for each command |
GDBFILE = texts |
Name of GDB file to be used in specifying the database for each command |
EXIT = scalars |
The exit code (0=success, 1=failure) from each command |
Description
DBCOMMAND runs a SQL command on an ODBC database. SQL commands like CREATE TABLE, DROP TABLE, ALTER TABLE, INSERT INTO and DELETE FROM can be used to modify the database. However, the command cannot have parameters or return data. The EXIT parameter can specify a scalar to save a code indicating whether the command was successful (0) or failed (1). If the command fails, an SQL error message will be printed.
The ODBC database can be specified by using the DB parameter to supply a text containing a database connection string. Note that any file names in the string must use \\ rather than / for the directory separator: i.e. the file name
C:\\WORK\\MYDATA.MDB
must be specified as
C:\WORK\MYDATA.MDB
rather than as
C:/WORK/MYDATA.MDB
Alternatively, you can use the GDBFILE parameter to specify an existing GDB file that contains an ODBC query. This can be created in Genstat for Windows using the ODBC Data Query menu (accessible from the New option of the Spread menu on the menu bar). The DSN line in this text file can be used to connect to the same database as specified by the DB parameter.
You can set option DRIVER=32 to use 32-bit ODBC drivers when you are running the 64-bit Genstat.
Options: WARNINGDIALOGS, DRIVER.
Parameters: COMMAND, DB, GDBFILE, EXIT.
Method
The SQL command is sent to the ODBCLOAD.DLL library which runs the command and returns an exit code and any error messages.
See also
Procedures: DBEXPORT, DBIMPORT, DBINFORMATION, IMPORT.
Commands for: Input and output.
Example
CAPTION 'DBCOMMAND Examples'; STYLE=meta
"Create Temporary copy of Cars Access database"
SET [WORKINGDIR='%TMP%']
GET [WORKINGDIR=WDir]
FCOPY '%DATA%/Cardata.mdb'; 'CarsTemp.mdb'; OVERWRITE=yes
"Create Database connection string"
CONCAT [NEWTEXT=DB] 'DBQ=',WDir,'\\CarsTemp.mdb;',\
'Driver={Microsoft Access Driver (*.mdb, *.accdb)};',\
'DriverId=25;FIL=MS Access;'
DBIMPORT DB=DB; SQL='SELECT * FROM CarData'
PRINT SUM(Ncylinder > 6); DECI=0
"Remove 8 and 12 cylinder cars"
DBCOMMAND 'DELETE FROM CarData WHERE Ncylinder > 6'; DB=DB
"Check these are gone"
DBIMPORT [PRINT=*] DB=DB; SQL='SELECT * FROM CarData'
PRINT SUM(Ncylinder > 6)
"Remove a column from the CarData table"
DBCOMMAND 'ALTER TABLE CarData DROP COLUMN Ins_GP'; DB=DB
"Add a column to the CarData table"
DBCOMMAND 'ALTER TABLE CarData ADD COLUMN Year INT'; DB=DB
"Create a new table"
CONCAT [NEWTEXT=NewTable] 'CREATE TABLE Persons ',\
'(PersonID INT, FirstName CHAR, LastName CHAR, Address CHAR, ',\
'City CHAR, Age INT, BirthDate DATETIME);'
DBCOMMAND NewTable; DB=DB
"Add a row of data to the table"
CONCAT [NEWTEXT=InsertRow] 'INSERT INTO Persons ',\
'(PersonID, FirstName, LastName, Address, City, Age, BirthDate)',\
' VALUES (1, ''David'', ''Baird'', ''8 Mariposa Cres'',',\
' ''Christchurch'', 55, ''1/1/1960'' )'
DBCOMMAND InsertRow; DB=DB
DBIMPORT [PRINT=*] DB=DB; SQL='SELECT * FROM Persons'; ISAVE=Data
PRINT Data[]; FIELD=3(9),2(12),6,9; DECI=0; DREP=6(*),1
"Check the changes in columns"
DBINFORMATION [INFO=columns] DB
"Remove a table"
DBCOMMAND 'DROP TABLE Persons'; DB=DB
DBINFORMATION DB