DBCOMMAND procedure

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

Updated on March 8, 2019

Was this article helpful?