SQLDATA Object +
Updated: July 13, 2012

The SQLDATA Object database tool uses Microsoft Windows "Data Sources Open
Database Connectivity (ODBC) to access data from a variety of database
management systems."  SQL commands or "queries" may be used to work with
databases created by a number of database software makers.

In general,
(1) .DataSource and .Description are used to enumerate on-line data sources.
(2) A data source is opened with .Connect (alias .Open).
(3) A SQL query loop may issue one or more queries and retrieve results.
(4) .Close frees memory and the database.

The query loop is a three step cycle:  submit query with .Command (alias
.Query), get one or more columns (fields) and rows of results and finally,
free system resources with .FreeMemory.

Hopefully, typical code in hotquery.bas in the HotQuery download will make
these procedures appear easy.

HotBasic makes current SQLDATA handles available with .EnvHandle, .DBhandle
and .QueryHandle, so the object is easily extensible to other related,
declared API functions in the Windows Data Sources (ODBC) set.

The following SQLDATA properties and methods are thread-safe.


PROPERTIES (Read Only String):
~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~
Column     (col, attribute)  Get field result data for 1-based column
           index with attribute.

           MyString = MyDSN.Column(1,1)  'attribute 1 is name

  Note: Some (or all?) of these values may be used as attribute:

  $DEFINE SQL_COLUMN_COUNT 0
  $DEFINE SQL_COLUMN_NAME 1
  $DEFINE SQL_COLUMN_TYPE 2
  $DEFINE SQL_COLUMN_LENGTH 3
  $DEFINE SQL_COLUMN_PRECISION 4
  $DEFINE SQL_COLUMN_SCALE 5
  $DEFINE SQL_COLUMN_DISPLAY_SIZE 6
  $DEFINE SQL_COLUMN_NULLABLE 7
  $DEFINE SQL_COLUMN_UNSIGNED 8
  $DEFINE SQL_COLUMN_MONEY 9
  $DEFINE SQL_COLUMN_UPDATABLE 10
  $DEFINE SQL_COLUMN_AUTO_INCREMENT 11
  $DEFINE SQL_COLUMN_CASE_SENSITIVE 12
  $DEFINE SQL_COLUMN_SEARCHABLE 13
  $DEFINE SQL_COLUMN_TYPE_NAME 14
  $DEFINE SQL_COLUMN_TABLE_NAME 15
  $DEFINE SQL_COLUMN_OWNER_NAME 16
  $DEFINE SQL_COLUMN_QUALIFIER_NAME 17
  $DEFINE SQL_COLUMN_LABEL 18

DataSource An available database name.  DataBase$ = MyDSN.DataSource  

  Note: .DataSource will always return a string, cycling the list.

Description Alias for .Driver

Driver     Used after .DataSource to get data source driver

           Driver$ = MyDSN.Driver

Field      (col, attribute)  .Field is an alias for .Column

RowValue   (col, attribute)  Get row result data for 1-based column
           index with attribute.  See .Column for attribute values.

           MyString = MyDSN.RowValue(1,1)  'attribute 1 is name


PROPERTIES (Read Only Numeric):
~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~
ColumnCount Number of columns in query result.  ncols = MyDSN.ColumnCount

DBHandle   Handle of open database.

EnvHandle  Handle of current DSN environment.

Error      Result of last SQLDATA call.  IF MyDSN.Error THEN ...

FieldCount Number of columns in query result.  Alias for .ColumnCount.

QueryHandle Handle of current query.

Row        Fetch row of query result; returns 100 if no data found.

           IF MyDSN.Row = 100 THEN Goto Finished_getting_rows


METHODS    Comments
~~~~~~~    ~~~~~~~~
Close      Closes previously opened database.  MyDSN.CLose

Command    (Query$)  Query database.  MyDSN.Command("select *;")

Connect    (DataBase$, User$, Passwd$)  Connect to database.

           MyDSN.Connect(DataBase$, null, null)

           DataBase$ may be a result of .DataSource or defined as
           you already know it.  Note that some data sources do
           not require user ID and password (null arguments above).
           [null is a HotBasic keyword for a 0-length string.]

FreeMemory Frees system resources used in last .Command.

           MyDSN.FreeMemory  'used at end of a .Command cycle

Open       (DataBase$, User$, Passwd$)  Alias for .Connect

Query      (Query$)  Query database; alias for .Command


+ Penthouse (registered) version

Copyright 2004-2012 James J Keene PhD
Original Publication: Nov 7, 2004