dBASE

 

dBASE is a database management system (DBMS) that was one of the first widely-used database systems in the personal computing era. Developed originally in the late 1970s and gaining prominence in the 1980s, dBASE has evolved over the years but retains its significance in various applications. Here’s a detailed overview of what dBASE is used for:

### 1. **Database Management**

- **Data Storage**: dBASE is primarily used for storing structured data in a format that can be easily accessed and manipulated.

- **Data Retrieval**: It allows users to query and retrieve data efficiently using its built-in query language, which is similar to SQL.

### 2. **Application Development**

- **Rapid Application Development**: dBASE provides a programming language (dBASE language) that allows developers to create applications quickly for data entry, reporting, and other database operations.

- **User Interfaces**: Developers can create forms and reports to interact with the database, making it user-friendly for data entry and retrieval.

### 3. **Data Manipulation**

- **CRUD Operations**: Users can perform Create, Read, Update, and Delete (CRUD) operations on the data stored in dBASE.

- **File Handling**: dBASE supports various file operations, allowing users to manage data files effectively.

### 4. **Reporting**

- **Report Generation**: dBASE has built-in capabilities for generating reports based on the data stored in the database. Users can create custom reports to display data in a structured format.

- **Data Export**: Data can be exported from dBASE to other formats (like text files, spreadsheets) for analysis or reporting purposes.

### 5. **Legacy Applications**

- **Legacy Systems**: Many legacy applications still rely on dBASE due to its historical significance and the amount of data and functionality that has been built around it.

- **Data Migration**: Organizations with existing dBASE systems may use it as an intermediary for data migration to more modern databases or applications.

### 6. **Integration**

- **Interoperability**: dBASE can integrate with other systems and languages, allowing for the use of its database capabilities within larger applications or workflows.

- **File Formats**: dBASE files (DBF files) are supported by many other database systems and tools, facilitating data exchange.

### 7. **Desktop and Small Business Applications**

- **Personal Database Management**: dBASE can be used by individuals for personal data management, such as address books, inventories, and project management.

- **Small Business Applications**: Small businesses often utilize dBASE for managing customer information, sales records, and inventory due to its simplicity and low cost.

### Conclusion

In summary, dBASE is used for a variety of purposes related to database management, application development, data manipulation, and reporting. While its popularity has declined with the rise of more advanced database systems, it still serves as a valuable tool for specific use cases, especially in legacy environments and small applications. The enduring nature of dBASE can be attributed to its foundational role in database technology and its continued relevance in certain contexts.

===

Yes, dBASE is still being used, though its popularity has significantly declined compared to its peak in the 1980s and 1990s. Here are some key points regarding the current usage of dBASE:

### 1. **Legacy Applications**

- **Existing Systems**: Many organizations still operate legacy applications built on dBASE due to the significant investment in these systems over the years. Transitioning to newer platforms can be costly and complex, so some businesses continue to use dBASE for specific applications.

- **Maintenance and Support**: Companies may maintain dBASE applications for critical business functions, especially if the applications are stable and meet their needs.

### 2. **Niche Markets**

- **Small Businesses**: Some small businesses and individual users still use dBASE for personal database management, inventory tracking, or simple business applications due to its low cost and ease of use.

- **Custom Solutions**: Developers may create custom solutions using dBASE for specific projects where a lightweight database system is sufficient.

### 3. **dBASE Plus**

- **Modern Versions**: The dBASE product has evolved, with newer versions like **dBASE Plus** being released. dBASE Plus supports modern Windows operating systems, has enhanced features, and includes support for web and mobile applications.

- **Development Tools**: dBASE Plus provides tools for rapid application development, allowing developers to build new applications or modernize existing ones using the dBASE language and its IDE.

### 4. **File Format Compatibility**

- **DBF Files**: The .DBF file format used by dBASE is still widely recognized and supported by many modern database systems, spreadsheets (like Excel), and data analysis tools. This interoperability facilitates data sharing between dBASE and other applications.

- **Data Migration**: Organizations often use dBASE files as a stepping stone for migrating data to newer database systems, allowing them to maintain some level of access to their historical data.

### 5. **Community and Resources**

- **User Community**: There remains a small but dedicated community of developers and users who continue to share knowledge, tools, and support for dBASE. Online forums and resources can still be found for those seeking help with dBASE-related issues.

### Conclusion

While dBASE may not be as prevalent as it once was, it continues to have a presence in certain niche markets and among users with legacy systems. Its longevity can be attributed to the substantial investments made in dBASE applications, the simplicity it offers for small-scale database management, and its compatibility with modern technologies. As organizations continue to modernize their systems, some may choose to phase out dBASE, while others will retain it for specific needs.

===

dBASE, once a leading database management system, has seen its usage decline over the years as various technologies and systems emerged to provide more advanced and robust database solutions. Here are some of the key database systems and technologies that have largely replaced dBASE:

### 1. **Relational Database Management Systems (RDBMS)**

- **Oracle Database**: A powerful and feature-rich RDBMS widely used in enterprise environments for large-scale applications.

- **Microsoft SQL Server**: A popular database system known for its integration with Microsoft products, ease of use, and robust performance.

- **MySQL**: An open-source RDBMS that is widely adopted for web applications, known for its reliability and speed.

- **PostgreSQL**: An advanced open-source RDBMS that supports complex queries, large databases, and various data types, offering extensibility and compliance with standards.

### 2. **NoSQL Databases**

- **MongoDB**: A document-oriented NoSQL database that provides flexibility in data modeling and is popular for handling unstructured data and scalability.

- **Cassandra**: A distributed NoSQL database designed for high availability and scalability, often used in large-scale applications.

- **Redis**: An in-memory data structure store that can be used as a database, cache, or message broker, known for its speed and performance.

### 3. **Cloud-Based Database Solutions**

- **Amazon RDS (Relational Database Service)**: A managed database service that supports various database engines like MySQL, PostgreSQL, SQL Server, and Oracle, simplifying database management.

- **Google Cloud Firestore**: A NoSQL document database built for automatic scaling, high performance, and ease of application development.

- **Azure SQL Database**: A managed cloud database service from Microsoft that supports SQL Server, providing automatic scaling, backups, and high availability.

### 4. **Modern Development Frameworks and Tools**

- **Microsoft Access**: A desktop database application that provides a user-friendly interface and integration with other Microsoft Office tools, often used for small to medium-sized database applications.

- **SQLite**: A lightweight, serverless RDBMS that is embedded into applications, making it suitable for mobile and web applications.

### 5. **Development Platforms**

- **FileMaker Pro**: A cross-platform relational database application that provides a user-friendly environment for creating custom database solutions.

- **Airtable**: A cloud-based platform that combines the features of a spreadsheet and a database, offering ease of use for project management and collaboration.

### 6. **Web-Based Applications**

- **Custom Web Applications**: Many organizations have moved towards custom web applications built with modern programming languages (like Python, Ruby, JavaScript) and frameworks (like Django, Ruby on Rails, Node.js) that leverage robust databases as a backend.

### Conclusion

dBASE has been largely replaced by a variety of modern database technologies that offer greater scalability, performance, and functionality. Organizations today have a wide range of options depending on their specific needs, whether they require traditional relational databases, NoSQL solutions for handling unstructured data, or cloud-based systems that provide flexibility and ease of management. The shift from dBASE reflects the evolving landscape of database technology and the growing complexity of data management in today's applications.

===

In dBASE, data is stored in a structured format that enables efficient retrieval, manipulation, and management. The primary components involved in data storage within dBASE include **data files**, **index files**, and **structure definitions**. Here’s a detailed explanation of how data was stored in dBASE:

### 1. **Data Files (.dbf)**

- **File Format**: The main data storage format in dBASE is the **DBF (dBASE File)** format. Each DBF file contains records that store data in a structured manner.

- **Structure**: Each DBF file consists of:

  - **Header**: Contains metadata about the file, such as the version of dBASE, the number of records, field descriptors, and more.

  - **Field Descriptions**: A list of fields (columns) in the table, including the field name, data type, field length, and any associated attributes.

  - **Records**: The actual data entries, stored in rows where each row corresponds to a single record. Each field in the record corresponds to the structure defined in the header.

- **Data Types**: dBASE supports various data types, including:

  - **Character**: For text data.

  - **Numeric**: For numeric values.

  - **Date**: For date values.

  - **Logical**: For Boolean values (true/false).

### 2. **Index Files (.ndx, .mdx)**

- **Indexing**: To improve the speed of data retrieval, dBASE uses index files. These index files are separate from the DBF files and are used to maintain order and provide quick access to records.

- **Types of Index Files**:

  - **.NDX**: A standard index file format used in earlier versions of dBASE.

  - **.MDX**: Used for multi-key indexes, allowing multiple indexes on a single DBF file.

- **Functionality**: When a query is executed that requires sorted data, the index files are used to quickly locate records without scanning the entire DBF file.

### 3. **Record Structure**

- **Fixed-Length Records**: Each record in a DBF file has a fixed length, as defined by the field lengths specified in the header. This structure ensures that data is stored compactly and efficiently.

- **Record Delimiter**: Each record is terminated with a special byte that indicates the end of that record.

### 4. **File Organization**

- **Flat File Structure**: dBASE uses a flat file structure for its DBF files. This means that all records are stored sequentially, and each record can be accessed directly based on its position in the file.

- **Data Integrity**: dBASE supports basic data integrity features, such as ensuring that data types are consistent with field definitions, but it lacks the advanced relational integrity features found in modern relational database systems.

### 5. **Multi-File Structure**

- **Related Tables**: While dBASE is not a fully relational database, it allows for the management of multiple DBF files that can be related. However, relationships between tables are not enforced by the system; they must be managed by the application logic.

- **Data Linking**: Users can link related DBF files in their applications, allowing for simple relational-like functionality.

### 6. **File Extensions**

- **Common Extensions**: Apart from DBF files, other common file extensions associated with dBASE include:

  - **.FPT**: Memo file that stores large text fields or binary data linked to records in the DBF file.

  - **.NTX**: An additional type of index file used in certain versions of dBASE.

### Conclusion

In summary, dBASE stores data in DBF files, organized in a straightforward structure comprising a header, field definitions, and fixed-length records. It uses index files to enhance data retrieval speeds, and while it supports basic relationships between files, it is not a fully relational database management system. This architecture, while limited compared to modern database systems, was highly effective in its time and laid the foundation for many subsequent database technologies.

====

Yes, data stored in dBASE can be extracted and exported into various other formats. This capability is useful for data analysis, reporting, migration to different database systems, and integration with other applications. Here are some common formats into which dBASE data can be exported:

### 1. **CSV (Comma-Separated Values)**

- **Description**: A widely used text format where data fields are separated by commas.

- **Usage**: Easy to import into spreadsheet applications like Microsoft Excel or Google Sheets and can be used for data interchange between different systems.

### 2. **Excel (XLS or XLSX)**

- **Description**: The file formats used by Microsoft Excel to store spreadsheet data.

- **Usage**: Allows for more complex data manipulation, visualization, and analysis within Excel.

### 3. **Text Files (TXT)**

- **Description**: Data can be exported as plain text files, either in a fixed-width format or with delimiters (like tabs or spaces).

- **Usage**: Useful for simple data sharing or for importing into text-based applications.

### 4. **SQL Scripts**

- **Description**: SQL scripts can be generated to recreate the database structure and insert the data into another SQL-based RDBMS (like MySQL, PostgreSQL, or SQL Server).

- **Usage**: Facilitates migration of data to a more robust relational database system.

### 5. **XML (eXtensible Markup Language)**

- **Description**: Data can be exported in XML format, which is a flexible markup language used for representing structured data.

- **Usage**: Useful for data interchange between systems, especially in web services and applications.

### 6. **HTML (HyperText Markup Language)**

- **Description**: Data can be converted into HTML tables for display on web pages.

- **Usage**: Suitable for creating web-based reports or data views.

### 7. **DBF Formats for Other DBMS**

- **Description**: Some modern database management systems can directly read dBASE DBF files, allowing for data extraction without conversion.

- **Usage**: Enables integration with modern applications and databases that support the DBF file format.

### 8. **Other Database Formats**

- **Description**: Data can also be exported to other database formats using intermediary tools or scripts.

- **Usage**: Facilitates migration to other systems like Oracle, SQL Server, or MySQL, either through direct export features or by using database migration tools.

### Tools for Data Extraction

- **dBASE Application**: The dBASE application itself provides built-in tools for exporting data to various formats.

- **Third-Party Tools**: Many third-party data management tools support importing DBF files and exporting them to different formats.

- **Programming Languages**: Languages like Python, C#, or Java can be used to write custom scripts to read DBF files and extract data into desired formats.

### Conclusion

In summary, dBASE data can be extracted into various formats, including CSV, Excel, SQL scripts, XML, and others, facilitating data sharing, reporting, and migration to modern database systems. This versatility is essential for integrating legacy dBASE applications with contemporary technologies and applications.

====

dBASE uses a command-driven language that allows users to interact with the database, manipulate data, and design forms and reports. Here are some essential dBASE commands categorized by functionality:

 

 1. Data Management Commands

   USE: Opens a DBF file (table) for data manipulation.

     - `USE customer`

   CLOSE: Closes the currently open DBF file.

     - `CLOSE DATABASES`

   APPEND: Adds a new record to the end of the table.

     - `APPEND BLANK`

   DELETE: Marks records for deletion.

     - `DELETE FOR condition`

     - `DELETE ALL`

   RECALL: Recovers records marked for deletion.

     - `RECALL ALL`

   PACK: Permanently removes deleted records from the table.

     - `PACK`

   ZAP: Deletes all records and permanently empties the table.

     - `ZAP`

 

 2. Data Retrieval and Display Commands

   LIST: Displays records in the table.

     - `LIST`

     - `LIST FOR condition`

   DISPLAY: Shows selected records on screen with specific fields.

     - `DISPLAY ALL`

     - `DISPLAY NEXT 10`

   BROWSE: Opens a grid view of the data, allowing for easy editing.

     - `BROWSE`

   FIND: Searches for the first occurrence of a specified record.

     - `FIND "John"`

   LOCATE: Searches for a record that matches a specific condition.

     - `LOCATE FOR age > 30`

   INDEX: Creates an index file to speed up data retrieval.

     - `INDEX ON lastname TO nameindex`

 

 3. Data Manipulation Commands

   REPLACE: Updates values in fields for selected records.

     - `REPLACE lastname WITH "Smith" FOR firstname = "John"`

   GO: Moves the record pointer to a specific record.

     - `GO TOP` (First record)

     - `GO BOTTOM` (Last record)

     - `GO n` (Move to record number n)

   SKIP: Moves the record pointer by a specified number of records.

     - `SKIP 1` (Next record)

     - `SKIP -1` (Previous record)

 

 4. Filtering and Sorting Commands

   SET FILTER TO: Applies a filter to view only specific records.

     - `SET FILTER TO age > 30`

   SET ORDER TO: Selects a specific index order for viewing records.

     - `SET ORDER TO nameindex`

 

 5. Programming and Control Flow Commands

   DO WHILE: Executes commands while a condition is true.

     - ```plaintext

       DO WHILE NOT EOF()

          DISPLAY

          SKIP

       ENDDO

       ```

   IF / ELSE / ENDIF: Conditional command structure.

     - ```plaintext

       IF age > 18

          DISPLAY "Adult"

       ELSE

          DISPLAY "Minor"

       ENDIF

       ```

   FOR / ENDFOR: Loops through a specified range.

     - ```plaintext

       FOR i = 1 TO 10

          DISPLAY i

       ENDFOR

       ```

   SCAN / ENDSCAN: Iterates through records that meet a condition.

     - ```plaintext

       SCAN FOR age > 18

          DISPLAY age

       ENDSCAN

       ```

 

 6. Form and Report Commands

   CREATE FORM: Starts the creation of a form for data entry.

     - `CREATE FORM CustomerForm`

   MODIFY FORM: Edits an existing form.

     - `MODIFY FORM CustomerForm`

   CREATE REPORT: Begins the creation of a report.

     - `CREATE REPORT SalesReport`

   MODIFY REPORT: Edits an existing report.

     - `MODIFY REPORT SalesReport`

 

 7. File and System Commands

   SET PATH: Specifies the search path for files.

     - `SET PATH TO C:\dbase\`

   COPY TO: Exports data to another file format.

     - `COPY TO customers.csv TYPE CSV`

   QUIT: Exits dBASE.

     - `QUIT`

 

 Example Workflow of Commands

 

Here's an example of how a simple workflow might look in dBASE:

 

```plaintext

USE customer          && Open the customer table

LIST                  && Display all records

APPEND BLANK          && Add a new empty record

REPLACE firstname WITH "Jane", lastname WITH "Doe"  && Update fields

INDEX ON lastname TO nameindex  && Create an index for faster searches

SET FILTER TO age > 18          && Filter to view only adults

DISPLAY                         && Display filtered results

PACK                  && Remove deleted records

QUIT                  && Exit dBASE

```

dBASE commands provide a range of capabilities for data management, display, filtering, and report generation. These commands offer powerful options for both simple and complex data operations, which made dBASE a foundational tool for early database management systems.

====

In dBASE, the `SELECT` query is used to retrieve data from one or more tables, similar to how SQL works in modern relational database systems. However, dBASE has its own syntax and capabilities for `SELECT` queries, which can be limited compared to SQL in more advanced databases. Here’s how to use the `SELECT` command in dBASE:

 

 Basic Syntax of `SELECT` in dBASE

 

```plaintext

SELECT field1, field2, … FROM table_name [WHERE condition] [ORDER BY field] [GROUP BY field]

```

 

Here’s a breakdown of each part:

 

- `SELECT field1, field2, …`: Specifies the fields (columns) to retrieve. You can also use `*` to select all fields.

- `FROM table_name`: Specifies the table from which to select data.

- `WHERE condition`: (Optional) Filters the data to meet certain conditions.

- `ORDER BY field`: (Optional) Sorts the results by a specific field.

- `GROUP BY field`: (Optional) Groups the results by a specific field, useful for aggregate functions.

 

 Examples of dBASE `SELECT` Queries

 

# 1. Selecting All Records

To select all fields from a table:

 

```plaintext

SELECT * FROM customer

```

 

# 2. Selecting Specific Fields

To select only specific fields, such as `firstname` and `lastname`:

 

```plaintext

SELECT firstname, lastname FROM customer

```

 

# 3. Using the WHERE Clause

To filter records with specific conditions, such as retrieving customers older than 30:

 

```plaintext

SELECT firstname, lastname FROM customer WHERE age > 30

```

 

# 4. Sorting Results with ORDER BY

To sort the retrieved records by `lastname`:

 

```plaintext

SELECT firstname, lastname FROM customer ORDER BY lastname

```

 

# 5. Grouping Results with GROUP BY

To group results by a specific field, such as counting customers in each city:

 

```plaintext

SELECT city, COUNT(*) FROM customer GROUP BY city

```

 

# 6. Using Aggregate Functions

To retrieve the average age of customers:

 

```plaintext

SELECT AVG(age) FROM customer

```

 

Or to get the maximum and minimum age:

 

```plaintext

SELECT MAX(age), MIN(age) FROM customer

```

 

# 7. Complex Query Example

To select all customers over the age of 30, grouped by city, and ordered by lastname:

 

```plaintext

SELECT firstname, lastname, city FROM customer WHERE age > 30 GROUP BY city ORDER BY lastname

```

 

 Limitations of `SELECT` in dBASE

- Joins: dBASE’s ability to join multiple tables in a single `SELECT` query is limited compared to modern SQL.

- Advanced SQL Functions: While dBASE supports some aggregate functions, it lacks many advanced SQL functions and capabilities available in RDBMS like SQL Server or Oracle.

- Subqueries: dBASE does not support complex subqueries within a `SELECT` statement.

The `SELECT` command in dBASE allows you to retrieve data based on specific conditions, sort it, and perform basic aggregation. Though it lacks the advanced features of modern SQL, it provides enough flexibility for most data retrieval needs within dBASE.

====

The dbase Command

The dbase command is used to interact with database servers. While the command provides a generic, database-independent set of features, the actual interaction is performed via loadable database driver modules. These are either loaded explicitly (via the dbx load command) or automatically be referring a known database type name.

The dbase command provides the following subcommands:

dbase close

dbase close all

dbase close ?dbhandle?...

Close open database connections held by the specified database access objects and destroy the connection manager objects. The handles passed to this command are no longer valid after the command has been executed.

The magic handle name all can be used to close all currently opened database connections.

Example:

dbase close all

The return value is the number of closed database objects. For the sake of consistency with other object commands, the command dbase delete is an alias to this command. Both commands do not delete any database on the server.

dbase colquery

dbase colquery dbhandle sqlstatement ?tablehandle?

This command is a variant of the dbase query command. By default, the command returns a nested list of rows and columns. This command only returns the first result column, if any are produced, and omits the outer nesting level. This can make the processing of results easier. Example:

set smileslist [dbase colquery $dbhandle “select smiles from moltable”]

If a tareget table handle was supplied, the return value is a list of the table handle, the row count, and the column count, which is always one.

The command can also be accessed under the name columnquery .

dbase connect

dbase connect dbhandle

Establish a connection to the database server. An error is thrown if the connection does not succeed. This statement is primarily useful to verify the correctness of the attributes set by means of dbase create and dbase set commands. For the execution of database commands it is not required. In case a database connection was not yet established when communication with the server is required, an attempt to open the connection is made automatically.

If the dsn (data source name) attribute has been set, it has precedence over the connection parameters defined by the database host, port, database, user etc. attributes.

dbase create

dbase create ?attribute value?...

Create a new database access object. Any number of database access objects can be in existence at the same time, and be connected to the same or different databases, potentially using different database drivers. If no attributes are specified, a default database object is created. Some of the default values can be modified via elements in the ::cactvs() control array:

default_database

The name of the database. The default is the user name.

default_database_host

The database host. The default is localhost .

default_database_options

An option string for drivers which support this. Empty by default.

default_database_password

The database password, if required. Empty by default.

default_database_type

The database type. Set to mysql by default. The default port is automatically set to the default communication port of the database type (3306 for the mysql case).

default_database_user

The user name used to connect to the database. By default, this is the same as the user name.

The attributes which may be set by this command the same as in the dbase set command and explained there. The return value of the command is the database object handle, which is used to identify the object in all further operations.

Example:

dbase create dbtype mysql database samples host db3 user beaker password muppet

Note that this command only sets up the database interface configuration, but does not immediately open a connection. A connection to the database is only opened the first time there is a need to communication with the database server, of the dbase connect command is executed. Until then, it is for example possible to set additional parameters via the dbase set command which are used when the connection is finally established.

dbase disconnect

dbase disconnect dbhandle

Close the connection to the database established via a dbase connect command or implicitly by data retrieval commands. The interface object remains valid and can, potentially after a change of attributes, reconnect to a database.

In case the interface object was not yet connected, the command does nothing.

dbase dup

dbase dup dbhandle

Duplicate the attributes of an existing database interface object into a new object. The return value is the handle of the new interface object.

This command only copies the configuration options, but does not inherit the database connection, or any related state information. The new interface object is in the same state as if it were created via a dbase create statement with a complete set of attribute and value pairs.

dbase exec

dbase exec dbhandle sqlstatement ?tablehandle?

This command is a variant of the dbase query command. The difference is that any returned results are discarded and the return value is the current value of the iserror attribute. In case of severe errors. a Tcl error is generated.

dbase exists

dbase exists dbhandle ?database?

Test whether the specified database is visible via the current connection or not. If no database name is specified, the value of the database attribute of the interface object is used for the test. The return value is the boolean test result. If the connection cannot be established, an error is generated.

dbase flush

dbase flush ?dbhandle?

This command flushes the internal database caches globally or only those associated with the connection. The toolkit remembers certain information, such as database and table names, or database column types, in order to accelerate processing. In case the database content was modified by deleting, adding or altering tables, or full databases have been deleted, renamed, or created, it is advisable to use this command to make sure that the cached information does not become outdated and a source of error.

In circumstances where a database my be accessed via more than one connection, it is best to flush the caches globally, or on all connections which operate on that database if the exact set of affected interface objects is known. Extraneous flushing of the caches does not change any valid results, but can lead to performance degradation.

dbase get

dbase get dbhandle attribute

Read a database interface object attribute. The list of attributes is explained in the paragraph on the dbase set subcommand.

Example:

set id [dbase get $dbhandle insertid]

dbase itemquery

dbase itemquery dbhandle sqlstatement ?tablehandle?

This command is a variant of the dbase query command. By default, the query command returns a nested list of rows and columns. This command only returns the first result item, from the first row and first column, if any are returned, and omits the standard two layers of list wrappers. This can make the processing of results easier. Example:

set size [dbase itemquery $dbhandle “select count(*) from moltable”]

If a table handle is specified as target, the return value is a list of the table handle, row count and column count.

dbase list

dbase list ?pattern?

Return a list of all currently defined database connector handles.

dbase query

dbase query dbhandle sqlstatement ?tablehandle|new?

Execute an SQL statement on the database server. The allowed SQL syntax is dependent on the capabilities of the connected server.

The default return value is, in the absence of the optional table handle argument, a nested list of rows and columns, with the rows as the outer list level. The maximum number of returned rows can be controlled by means of the maxrows interface object attribute. If table column data type information is available, the internally used Tcl result objects are matched to the column type for increased performance. Otherwise, the returned items are strings.

This command is not limited to the execution of SQL select statements. Any supported statement can be executed. In case it does not return a result tuples, an empty set is returned.

In case the optional target table handle argument is supplied, the result is directly stored in the specified table object. When the argument is present and explicitly set to an empty string, or the magic value new , a new table is created, which is automatically destroyed in case the command fails. An attempt is made to map existing table columns to the names of the database query result columns. In case no matching table object columns can be found, they are added automatically on the right with suitable data types, names, precision, width, and so on. Existing table columns which do not receive data from the result set are set to NULL values in the new rows. Existing table object rows are not deleted when the command is run. The retrieved rows from the database result set are appended. In this mode, the return value of the command is a list of the table handle, the number of rows and the number of columns of the table after the operation.

Example:

set th [table create]

lassign [dbase query $dbhandle \

“select smiles,name from moltable where logp between 5.0 and 6.0” $th] \

dummy nrows ncols]

dbase rowquery

dbase rowquery dbhandle sqlstatement ?tablehandle?

This command is a variant of the query command. By default, that subcommand returns a nested list of rows and columns. This command only returns the first result row, if any are received, and omits the column nesting level. This can make the processing of results easier. Example:

dbase rowquery $dbhandle “select smiles,weight from moltable where cas=’71-43-2’”

If a target table handle was supplied, the return value is a list of the table handle, the table row count (usually one, but in case the command did not return a value, zero) and the column count.

dbase set

dbase set dbhandle ?attribute value?

Set one or more attributes of the database interface object. Not all attribute changes have an effect after the database connection has been established. Generally, attribute changes which would necessitate the closing and re-opening of the database connection to a different host, or with different access credentials, are ignored after the connection has become active. If such a change is needed, a dbase reset command should be issued, or the current interface object should be discarded and a new one created.

Some attributes are read-only. They are listed here nevertheless, because the dbase get command refers to this section.

The following attributes are currently supported:

appname

The application name, which may be of interest to the database interface driver. By default it is set to an empty string, and most database interfaces ignore this attribute.

blocksize

The transmission block size used for database communication. The default, and in anything by exceptional circumstances the only value ever needed, is -1, which means to use the driver-specific default. This attribute is only of interest to database drivers which support this concept, for example the TDS driver for connecting to MS SQL Server databases.

clientinfo

The client-info string provided by the database interface library, if it has such a feature. This attribute is read-only.

clientname

The client name, which may be of interest to the database interface driver. By default it is set to an empty string, and most database interfaces ignore this attribute.

connected

A boolean read-only value indicating whether the connection to the database server has been established or not.

connectionstring

For database interface libraries which support this concept, a connection string encapsulates all required access information into a single string. If it is not set, an attempt is made to construct a suitable connection string from the basic host, port, user, password etc. attributes if the interface requires it. An explicitly set connection string is reset if the host, port etc. attributes of the interface object are changed.

database

The name of the current database. If it is changed between calls to the database server, interface commands are automatically issued to synchronize the name of the current database before the next SQL command is executed. The name of the initial database is copied from the value of the control variable ::cactvs(default_database) if it has not been set explicitly by means of dbase create or dbase set statements. In case of the Oracle interface, the value is a service/schema name from the Oracle tnsnames.ora configuration file, since Oracle does not have a simple concept of a database.

This attribute is not the same as databases (plural).

databases

This is a read-only attribute. It returns a list of the databases which are visible via the current connection. In case of Oracle, the list only contains the current service/schema name. No attempt is made to parse the tnsnames.ora configuration file.

This attribute is not the same as database (singular).

dbtype

The type of the database to connect to. The default is copied from the value of the control variable ::cactvs(default_database_type) . If a driver for the specified database type is not yet loaded, an attempt is made to auto-load it. The possible values for this attribute depend on the set of available database interface modules. Examples are mysql , odbc , tds (to connect to MS SQL Server) , postgres and oracle .

description

A free-form string which can be used to add a descriptive text. The default is an empty string.

domain

The database domain, if the database driver uses this concept. The default is an empty string.

driver

The name of the driver module for meta-interfaces such as ODBC .

dsn

For the ODBC driver, the data source name. In a properly configured ODBC environment, this name is used to look up other required connection data, such as host name and applicable driver module, via a single identifier. In a Cactvs installation, the database source name definition file resides in the odbc subdirectory of the data directory (control variable ::cactvs(data_directory)) . The DSN string is reset when the host, port, user, password or database attributes are modified.

host

The name of the database host. The default database host is copied from the value of the control variable ::cactvs(default_database_host) .

hostinfo

The host-into string provided by the database interface library, if it has such a feature. This attribute is read-only.

insertid

The last automatically generated id received from the database, for example from inserts into database tables with auto-increment columns. This is not supported on all database interfaces - for example, Oracle requires you to query sequence data explicitly. The value is read-only.

iserror

A read-only boolean flag to indicate that the last database operation resulted in an error or warning. Not all database operation problems are translated into Tcl script language errors. In order to become aware of non-critical problems, is flag should be checked in robust applications.

language

The language used for database interface messages, if the interface library supports this.

logfile

The name of a file to use logging for this connection, if the database interface library supports such a feature.

maxrows

The maximum number of rows read in a result set from a query. If the database interface supports this function, this is enforced on the server side. In any case, the dbase query command variants honor this attribute on the client side. If this attribute is set to zero or a negative value (the default), no row limit applies.

message

The last message string received from the database. This attribute is read-only. msg is an alias for this attribute.

null

The string value used to represent NULL database values in Tcl return results. By default, it is am empty string, but this is indistinguishable from zero-length strings and therefore it is sometimes useful to change it to something else, such as the string “NULL”.

options

An option string which is passed to the database driver, if it supports this concept. The default option string is copied from the value of the control variable ::cactvs(default_database_options) .

password

The password sent to the database server as part of the credentials. If no password is needed, use an empty string. The default password is copied from the value of the control variable ::cactvs(default_database_password) .

port

The communication port used to talk to the database server. When the database type is set, it is automatically set to the default port for that database (i.e. 3306 for mysql ). If a custom port is used, you therefore need to set it after the database type has been specified.

protocolinfo

The protocol-info string provided by the database interface library, if it has such a feature. This attribute is read-only.

protocolversion

The protocol version used by the interface library, if it can supply this information. This attribute is read-only.

query

The last SQL query executed on the database. This attribute is automatically updated when dbase query/exec/rowquery/colquery/itemquery commands are run, but there are also some conditions when implicitly assembled SQL commands are run which are also tracked here. Setting this attribute is possible, but pointless in normal script environments.

serverinfo

The server-info string provided by the database interface library, if it has such a feature. This attribute is read-only.

socket

A the name of an Unix domain named socket, if it is used by the database interface. This attribute should be considered read-only except for database wizards who need to cope with non-standard database server installation settings on the local host.

table

The current table of interest. This is used for example in constructing database URLs. It has no direct influence on SQL query or command execution, and is not automatically updated when running SQL commands.

tables

This is a read-only attribute. It returns a list of the database tables visible in the current database via the current connection.

textsize

The maximum text block size used in database communication. The default, and in anything by exceptional circumstances the only value ever needed, is -1, which means to use the driver-specific default. This attribute is only of interest to database drivers which support this concept, for example the TDS driver for connecting to MS SQL Server databases.

trace

A boolean flag which is used to enable or disable SQL statement execution tracing. By default tracing is off. The output is written to the file specified in the tracefile attribute.

tracefile

The name of a file to write database access trace information to. The default is sql.log .

timeout

The time-out value in seconds. If a database response it not received in time, an error results. If the value is set to 0 (the default), no time-out is active.

url

A database URL constructed from the currently set database object attributes (host, port where applicable, database/schema/service name, user, password, table of interest, query). This argument is read-only.

user

The user name used to provide credentials to the database server. By default, it is the database user set in : :cactvs(default_database_user), which again by default is the same as the login user name.

====