14.3 Database commands

These commands are useful to manage the databases by scripting languages.

 

 

DBCLOSE   (UINT)Handle
Close the database and release its resources.

Parameters:
Handle    It's the database handle returned by DBOPEN command.

Return values:
If it fails (e.g. the database is already closed or the handle is invalid), 0 (false) is returned, otherwise 1 (true) is set.

 

Example:
DBCLOSE   13423

 

See also:
DBCREATE, DBOPEN, DBGET, DBGETID, DBPUT, DBREOPEN.

DBCREATE   (CHAR)FileName   (CHAR)DbaseFormat   (CHAR)MolFormat   (CHAR)MolCompression   (INT)Flags
Create a new empty database. Please remember that you need to call the DBOPEN to change the database contents after its creation.

Parameters:
FileName    File or directory name with or without full path. The extension is added automatically if needed.
DbaseFormat  Database format. The allowed keywords are:
  
Keyword Description
Access Microsoft Access (.mdb)
Access2007 Microsoft Access 2007 (.accdb)
File Directory/file database.
Mol2 Sybyl Mol2 multimodel.
Sdf SD file.
SQLite SQLite database.
Zip Zip archive.
  
MolFormat

Default molecule format (see -f command line option). This field is ignored by Access, Access 2007, SD file and SQLite database formats.

MolCompression

Default molecule compression (NONE, BZIP2, GZIP, POWERPACKER, ZCOMPRESS). This field is ignored by Access, Access 2007, Mol2, SD file, SQLite and Zip database formats.

Flags Default database flags. These flags can be combined by OR operator.
Value Description
0 None.
1 Write the connectivity, if the molecule file format supports it.
2 Write the constraints, if the molecule file format supports it.

Return values:
Error code if it fails.

 

Example:
DBCREATE   "NewDatabase"   zip   pdb   none   0

 

See also:
DBCLOSE, DBOPEN, DBGET, DBGETID, DBPUT, DBREOPEN.

DBGET   (UINT)Handle   (CHAR)MolName   (MCHAR)Mode
Extract a molecule from the database specifying its name.

Parameters:
Handle    It's the database handle returned by DBOPEN command.
MolName Name of the molecule to extract (with file extension).
Mode It's the extraction mode, that can be:
Mode Description
Add Append the extracted molecule to the current workspace.
Replace Replace the current molecule.
Workspace Create a new workspace and extract the molecule to it.

Return values:
If it fails (e.g. the database handle is invalid or the molecule name is wrong), 0 (false) is returned, otherwise 1 (true) is set.

 

Example:
DBGET   13423   Water.iff   Replace

 

See also:
DBOPEN, DBCLOSE, DBGETID, DBGETROWID, DBPUT, DBREOPEN.

DBGETID   (UINT)Handle   (UINT)MolID   (MCHAR)Mode
Extract a molecule from the database specifying its identification number (ID). Each database record has an ID that is a progressive number starting from zero to the total number of records minus one. To obtain the total number of molecules in the database, see DBINFO command.

Parameters:
Handle    It's the database handle returned by the DBOPEN command.
MolID Molecule identification number.
Mode It's the extraction mode, that can be:
Mode Description
Add Append the extracted molecule to the current workspace.
Replace Replace the current molecule.
Workspace Create a new workspace and extract the molecule to it.

Return values:
If it fails (e.g. the database handle is invalid or the molecule ID is wrong), 0 (false) is returned, otherwise 1 (true) is set.

 

Example:
DBGETID   13423   4   Replace

 

See also:
DBOPEN, DBCLOSE, DBGET, DBGETROWID, DBINFO, DBPUT, DBREOPEN.

DBGETMOLNAME   (UINT)Handle   (UINT)MolID
Get the molecule name by database handle and molecule ID.

Parameters:
Handle    Database handle returned by DBOPEN command.
MolID   Molecule identification number.

Return values:
Error code if it fails.

 

Example:
DBMOLNAME  12432  34

 

See also:
DBCLOSE, DBCREATE, DBOPEN, DBREOPEN, DBSETMOLNAME.

DBGETROWID   (UINT)Handle   (CHAR)RowID   (MCHAR)Mode
Extract a molecule from the database specifying its primary key (Row ID). This command is useful for SQL databases in which each record has an unique ID. If the database is not SQL, the command is automatically translated to DBGETID.

Parameters:
Handle    It's the database handle returned by the DBOPEN command.
RowID Row identification number.
Mode It's the extraction mode, that can be:
Mode Description
Add Append the extracted molecule to the current workspace.
Replace Replace the current molecule.
Workspace Create a new workspace and extract the molecule to it.

Return values:
If it fails (e.g. the database handle is invalid or the row ID is invalid), 0 (false) is returned, otherwise 1 (true) is set.

 

Example:
DBGETROWID   13423   98   Add

 

See also:
DBOPEN, DBCLOSE, DBGET, DBGETID, DBINFO, DBPUT, DBREOPEN.

DBINFO   (UINT)Handle   (MCHAR)Variable
Obtain the value of a database variable.   

Parameters:
Handle    Database handle returned by DBOPEN command.
Variable Database system variable. It can be:
Variable Description
CurID

The ID of the last extracted molecule. For SQL databases, it is the primary key (value of ID field in Molecules table), for file-based databases (e.g. ARC, Mol2, SDF, etc), it is the offset in bytes and for directory database, it returns always 0.
CurMolName The name of the latest extracted molecule.
FileExt File name extension of the database.
FileName Database file name with full path.
IsSQL Check if the database supports SQL. Return values are: 0 = SQL not supported; 1 = SQL supported.
Format Database format (it could be: UNKNOWN, ACCESS, ACCESS2007, FILE, MMD, MOL2, SDF, SQLITE and ZIP).
Molecules Number of molecules stored in the database.
MolFlags Flags for the default file formats:
1 = write the connectivity;
2 = write the constraints.
These flags can be combined with the OR logical operator.
MolFormat Default file format of stored molecules. It uses the same naming format of -f command line option.
QuoteCl Closing quote for SQL commands.
QuoteOp Opening quote for SQL commands.

Return values:
If the database exists, the variable value is returned.

 

Example:
DBINFO   26123   MOLECULES

 

See also:
DBCLOSE, DBGET, DGETID, DBOPEN, DBPUT, DBREOPEN, DBSQLTABLEINFO.

DBLOCK   (UINT)Handle   (BOOL)Lock
Lock/unlock an existing database. The lock status avoids to close accidentally the database.

Parameters:
Handle    Database handle returned by DBOPEN command.  
Lock   0 (false) = unlock the database.
1 (true) = lock the database.		  

Return values:
Error code if it fails.

 

Example:
DBLOCK  12432  1

 

See also:
DBCLOSE, DBCREATE, DBOPEN, DBREOPEN.

DBOPEN   (CHAR)FileName
Open an existing database. This command is required to access (read and write operation) to an existing database.

Parameters:
DbName    Database file name whit full path.

Return values:
If the database exists, its handle is returned, otherwise the function returns 0 (error condition).

 

Example:
DBOPEN   "Vega\Data\Fragments\Solvents.zip"

 

See also:
DBCLOSE, DBCREATE, DBGET, DBGETID, DBINFO, DBPUT, DBREOPEN.

DBPUT   (UINT)Handle   (CHAR)MolName
Put the current molecule into the specified database.

Parameters:
Handle    It's the database handle returned by the DBOPEN command.
NolName   Name of the molecule to put with the file extension, if the database format requires it (e.g. file and zip).

Return values:
If it fails (e.g. the database handle is invalid), 0 (false) is returned, otherwise 1 (true) is set.

 

Example:
DBPUT   13182   "MyMolecule"

 

See also:
DBCLOSE, DBCREATE, DBGET, DBGETID, DBINFO, DBOPEN, DBREOPEN, DBUPDATE.

DBREOPEN   (CHAR)DbName
Open a database already open and return the handle.

Parameters:
DbName    Database name whit file extension.

Return values:
If the database exists, its handle is returned, otherwise the function returns 0 (error condition).

 

Example:
DBREOPEN   "Solvents.zip"

 

See also:
DBCLOSE, DBCREATE, DBGET, DBGETID, DBINFO, DBOPEN, DBPUT.

DBSETMOLNAME   (UINT)Handle   (UINT)MolID (CHAR)MolName
Set the molecule name by database handle and molecule  ID.

Parameters:
Handle    Database handle returned by DBOPEN command.
MolD   Molecule identification number.
MolName   Molecule name.

WARNING:

After the change the name of one or more molecule at the same time, you must call DbSortMolNames to sort alphabetically the molecule list. Don't call DbSortMolNames when you are looping the molecule list, because it reassigns the IDs to the molecules !

 

Return values:
Error code if it fails.

 

Example:
DBSETMOLNAME  12432  34  "New molecule name"

 

See also:
DBCLOSE, DBCREATE, DBGETMOLNAME, DBOPEN, DBREOPEN, DBSORTMOLNAMES.

DBSORTMOLNAMES   (UINT)Handle
Sort alphabetically the molecule names of a database.

Parameters:
Handle    Database handle returned by DBOPEN command.

WARNING:

Don't call this command when you are looping the molecule list, because it reassigns the IDs to the molecules !

 

Return values:
Error code if it fails.

 

Example:
DBSORTMOLNAMES  12432

 

See also:
DBCLOSE, DBCREATE, DBOPEN, DBREOPEN, DBSETMOLNAME.

DBSQLEXEC   (UINT)Handle   (CHAR)SqlCode
Execute SQL commands. This command is applicable only to SQL databases.

Parameters:
Handle    Database handle returned by DBOPEN command.
SqlCode   SQL code.

Return values:
Error code if it fails.

 

Example:
DBSQLEXEC  12432   "SELECT Name FROM Molecules;"

 

This example returns the list of all molecule in the database.

 

See also:
DBCLOSE, DBCREATE, DBOPEN, DBREOPEN, DBSQLTABLEINFO.

DBSQLTABLEINFO   (UINT)Handle   (CHAR)Table
Return the table structure of a SQL database.

Parameters:
Handle    Database handle returned by DBOPEN command.
Table   Table name

Return values:
Error code if it fails, otherwise the list of the fields included in the table (one for each line). Each line includes semicolon separated: the field name, the SQL data type (Unknown, Blob, Date, Integer, Real, VarChar and Time) and size of the field in bits (e.g. Integer, Real, etc.) or bytes (e.g. VarChar) according to the data type.

 

Example:
DBSQLTABLEINFO  12432   "Molecules"

 

This example returns the structure of the Molecules table.

 

See also:

DBCLOSE, DBCREATE, DBOPEN, DBREOPEN, DBSQLEXEC.

DBUPDATE   (UINT)Handle   (CHAR)MolName
Update the current molecule in the specified database.

Parameters:
Handle    It's the database handle returned by the DBOPEN command..
NolName   Name of the molecule to update with the file extension, if the database format requires it (e.g. file and zip).

Return values:
If it fails (e.g. the database handle is invalid), 0 (false) is returned, otherwise 1 (true) is set.

 

Example:
DBUPDATE   13182   "MyMolecule"

 

See also:
DBCLOSE, DBCREATE, DBGET, DBGETID, DBINFO, DBOPEN, DBPUT, DBREOPEN.

 

 

Back to the command index