Table of Contents

Introduction

EuSQL v0.76.8 by Matt Lewis
matthewwalkerlewis@yahoo.com
http://www14.brinkster.com/matthewlewis

OVERVIEW

This is an attempt to bring some added power to EDS by turning EDS files into Relational Databases. EuSQL implements a fairly large subset of standard SQL. Ultimately, I'd like to build EuSQL into a full fledged RDBMS (Relational Database Management System), including transactions, record locks, stored procedures and triggers, and write an ODBC driver for it. For now, though, this is what you've got. :)

EuSQL requires the version of database.e that comes with Euphoria 2.4.

EuSQL will run on any platform that runs Euphoria. There is no platform specific code in EuSQL.

LICENSE AND DISCLAIMER

This software is freeware, but you must give me credit for any applications developed with EuSQL. You can modify any of the code, so long as you do not take credit for any of the original source, and any modifications must be marked as such.

THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFIT; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

FILES INCLUDED

Main Library files: eusql.e : Main include file eusql.htm : You're reading it. hlist.e : Hybrid associative list routines by Jiri Babor. Used by EuSQL. sprint.e : Adopted from Gabriel Boehme's print.e

Matheval files (used for comparisons and calculations by EuSQL): matheval.e : Main matheval lib file. booleval.e : Evaluates boolean expressions. ppeval.e : Formats matheval sequences for display. misceval.e : Used by matheval.e symeval.e : Symbolic and algebraic simplification parseval.e : Parses mathematical and logical expressions scripteval.e : More complex operators texteval.e : Text manipulation

Demo Files: archive.edb : RDS's archive of user contributions sql.ex : Demo that creates sql.edb from archive.edb sqldemo.ex : Demo that runs several queries on sql.edb print.e : Gabriel Boehme's smart print routines (used in the demo)


Table of Contents

Topic

Description

IndexAlphabetical list of all items.
APIImportant notes on using the EuSQL API
Change HistorySummary of changes made to the library
DataFunctions for manipulating records.
DB ManagementNavigation, creation and deletion of databases and tables
ErrorsTrapping and using EuSQL error information
Known BugsStill gotta squash these...
Miscellaneous NotesHow it works, helpful hints
QueriesThese functions are used for executing SQL statements.
SQLSummary of supported SQL syntax
TODOWhat's next?
UtilitiesHelper routines used by the library that may be useful

Table of Contents

API

Important notes on using the EuSQL API


All functions return sequences when no errors occur. They will return the integer EUSQL_ERR code on errors (see Errors for details). The only exception to this is for action queries (UPDATE/INSERT/DELETE) when the action could not be performed. Error status is returned on a record by record basis as described in the SQL syntax entry for each type of query. Also note that you may pass empty sequences for db_name and table_name, and EuSQL will use the current database and table.

It is important to distinguish between a field index and an index created on a table for query purposes. When a function requires a field index, it is not asking for the name of an index created for that field. The required parameter is actually the necessary subscript that would be used to refer to the field as a part of the record if the record were stored in a sequence. If the structure of the table were:


Table of Contents

Change History

Summary of changes made to the library


v0.76.8
  8/03/04 
          * Fixed bug with update queries where only one field would be updated
          * Fixed bug where duplicate literals were not parsed correctly
          * Fixed bug in insert_record2() (did the earlier bug creep back in?)
          * Fixed bug in update_record2() for updating an index
          * Fixed parsing bug for SELECT COUNT(*) queries
v0.76.7
  6/1/04 
          * Properly handles semicolons at the end of a SQL statement
          * Bug fixes
v0.76.6
  3/16/04 
          * Fixed bug in insert_record2()
v0.76.5
  1/30/04 
          * Fixed bug with UPDATE queries
 v0.76.4
  1/27/04 
          * insert_record2() optimized to update indices at the end, rather than
            for each individual record
          * Bug fixes:
            + Updated parse_delete() to add the correct number of elements
              to query[QN_VALUES] (4)
            + Incorrect definition of EUSQL_CONCAT
            + EUSQL_MULTIPLY was conflicting with EUSQL_ASTERISK, and broke
              'SELECT * FROM TABLE'-type functionality
            + Fixed indexing errors when deleting records
            + Fixed some parsing errors introduced into other query types by
              modifications to SELECT parsing
 v0.76.3
  12/04/03 
          * Bug fixes:
            + Parameter and matheval variable handling
            + Parser was missing <=, >= and incorrectly parsing quoted literals
            + ORDER BY didn't properly take into account non-displayed fields
            + UPDATE query wouldn't update with an atom value
            + select_current() didn't always change the current table
          * Uses matheval v1.61
            + Added scripteval.e to use matheval's IF() operator
            + Added texteval.e for string manipulation
          * Calculated fields for queries (arithmetic, IF, text operations)

v0.76.2 11/17/03 * Optimized and fixed bugs in select queries, parameter handling

v0.76.1 10/27/03 * insert_record() and insert_record2() will call blank_record() if an atom is passed for the blank record. * Better parsing of INSERT queries. No longer have to prepend the table name to each field to be inserted. * Added validate_field2() for easier field validation. * Can change the way EuSQL opens databases using eusql_exclusive().

v0.76 10/08/03 * insert_record2() returns sequence with error codes for records * Fixed insert bug when a parameter's value had not been set.

v0.75 8/4/03 * Fixed delete_record() bugs * Added AUTONUMBER datatype

v0.74 4/21/03 * Major optimization for SELECT queries * Changed order of precedence for boolean logic so that it makes more sense (fewer parenthesis are needed) * IN() keyword now works in where clauses * create_db() didn't update db_list * Bug fixes to DELETE, UPDATE parsing and execution * rename_table() added v0.73 4/10/03 * Improved error checking and reporting in several places * Fixed bug where eusql missed fields with the same name as a table * update and delete functions now update indices * Fixes to boolean evaluation * Can now use IS [NOT] NULL in WHERE clauses * More parameter bug fixes--/UPDATE queries * Other UPDATE query bug fixes * Fixed bugs regarding parameters--values not being set and read properly v0.72 1/16/03 * Fixed several bugs regarding outer joins v0.71 11/22/02 * Select now uses indices, dramatic speed increase * Outer joins working * Can alias an aggregate function: SUM(TABLE1.FIELD1) AS FIELD1 v0.7 4/19/02 * Literals now case sensitive. * Condition and single, multiple join work now. * Need to test multiple conditionals with joins. * Indices are being created, but not sure if correct, since they're not used anywhere else. v0.62 6/14/01 * added matheval:booleval for comparison and calculations v0.61 5/2/01 * create_index(): need to allow queries to do this, too * get_record2(): Retrieve a record based on non-key values. Also had to add index_wild() to hlist.e to search on wildcard values. v0.6 3/6/01 * Added CREATE, DROP TABLE queries. * Modularized parsing routines. v0.5 3/2/01 * Added API commands to update, insert, retrieve records, get the structure of a table's records, create tables and fields, select current database, table * When create_table and create_field are used, TABLEDEF is automatically updated. * "SELECT *" now returns 'flat' records * Fixed bug regarding condition handling. Can now use conditions with "SELECT *" * Initial support for datatypes -- they're stored, but not actually used for verification yet * Fixed bug with UPDATE queries v0.4 2/8/01: * Better error handling. Added get_sql_err() for more verbose error reporting. * Many bug fixes. * DELETE queries supported. Returns keys of deleted records. * UNION queries supported. * "SELECT *" supported. * INSERT queries supported. * UPDATE queries supported. v0.3a 11/2/00: * Column functions COUNT, SUM, AVG, GROUP BY * Added support for SELECT DISTINCT to get unique values * field struct converted to uppercase in table_list() * Changed from absolute referencing of compiled query to using constants to allow flexibility in sequence structure.


Table of Contents

Data

Functions for manipulating records.


  • func delete_record( sequence db_name, sequence table_name, object key )   
  • func get_record( sequence db_name, sequence table_name, object key, sequence field, sequence index )   
  • func get_record2( sequence db_name, sequence table_name, sequence search_field, object search, sequence field, sequence field_index )   
  • func get_record_mult_fields( sequence db_name, sequence table_name, object key, sequence field, sequence index )   
  • func insert_record( sequence db_name, sequence table_name, object blank_record, sequence data, sequence index )   
  • func insert_record2( sequence db_name, sequence table_name, sequence blank_record, sequence data, sequence index )   
  • func update_record( sequence db_name, sequence table_name, object key, sequence index, sequence data )   
  • func update_record2( sequence db_name, sequence table_name, object key, sequence index, sequence data, integer key_changing )   

    Subtopics:
  • Datatypes

    Table of Contents

    Datatypes


    All fields must have a specified data type. Any writes to the field will be type checked based on the data type. A non-conforming value will cause an error to be returned. It is possible, however, that a field of type INTEGER or ATOM will return an empty sequence. This actually means that the field is NULL--no value has yet to be assigned to it.



    Supertopics:
  • Data
  • DB Management

    Table of Contents

    DB Management

    Navigation, creation and deletion of databases and tables


  • proc close_db( sequence db_name )   
  • func create_db( sequence db_name )   
  • func create_field( sequence db_name, sequence table_name, sequence field_name, integer datatype, integer build_index )   
  • func create_index( sequence db_name, sequence table_name, sequence ix_name, sequence field, integer unique )   
  • func create_table( sequence db_name, sequence table_name )   
  • func drop_field( sequence db_name, sequence table_name, sequence field_name )   
  • func drop_table( sequence db_name, sequence table_name )   
  • proc eusql_exclusive( integer flag )   
  • func open_db( sequence db_name )   
  • func reindex(sequence db_name, sequence table_name, sequence ix_name)   
  • func rename_field( sequence db_name, sequence table_name, sequence old_name, sequence new_name )   
  • func rename_table( sequence db_name, sequence table_name, sequence new_name )   
  • func select_current( sequence db_name, sequence table_name )   
  • func select_db( sequence db )   
  • func select_table( sequence table )   

    Subtopics:
  • Datatypes

    Table of Contents

    Errors

    Trapping and using EuSQL error information


    When everything works, EuSQL returns a sequence. If there is a problem, a positive integer error code is returned. Following is the descriptions of errors:

    The best method for retrieving error information is through the function get_sql_err(), which returns extended error information, as well as the standard error message (see description in API). It is recommended that you call get_sql_err() rather than accessing EUSQL_ERR_MSG (see below), since you'll get a more detailed description of the error.

    EuSQL has predefined error strings, which can be accessed through EUSQL_ERR_MSG:

    global constant
    EUSQL_ERR_MSG =  {
                    "EuSQL Error: OPERATION FAILED",
                    "EuSQL Error: ERROR IN 'WHERE' CLAUSE",
                    "EuSQL Error: INVALID FIELD",
                    "EuSQL Error: JOIN ERROR",
                    "EuSQL Error: INVALID TABLE",
                    "EuSQL Error: SYNTAX ERROR"
                    }
    

    ex:

    object sql sql = "SELECT ID FROM EMPLOYEES;" sql = parse_sql( sql ) if atom(sql) then puts(1, get_sql_err( sql ) ) abort(1) end if

  • type EUSQLRESULT( object e )   User defined type
  • proc eusql_err_out( integer fn )   
  • proc eusql_true_typecheck( integer new_setting )   
  • func get_sql_err( integer errnum )   

    Table of Contents

    Known Bugs

    Still gotta squash these...



    Table of Contents

    Miscellaneous Notes

    How it works, helpful hints


    Tools you can use
    If you haven't already, I recommend using my Euphoria Database Browser (EDB). It runs on Windows. Check out my web page or the RDS archives for downloads and updates.

    Speed
    EuSQL is not extremely fast relative to commercial databases, however, there are some things you can do to speed up operations (meanwhile, I'll work on optimizing everything).

    How it works
    EuSQL needs to have an extra table, named "TABLEDEF" created in the database it is to use. This stores the structures of records in your tables. This is automatically created when you use create_db. It will also create the table "INDEXDEF", which is used to store indices created on tables.

    The keys [of TABLEDEF] are the names of the tables, and the first field contains a sequence which describes the structure and field names for each table. Each field is described a sequence with two elements:

    1. The field name
    2. The contents/subfields

    The contents of the field could be described by either an empty sequence, or by one or more field names. This way, we preserve the flexibility of deeply nested sequences, while making it easy to use the data.

    Other fields [in TABLEDEF] keep track of field datatypes and indices for each table.

    Example: Suppose table MAIL contains information about people on a mailing list, where the records have the structure (the first field is actually the table/EDS key, which can also be composed of multiple subfields):

    {
      { "ID",   {} }
      { "Name",
            {
             {"First", {}},
             {"Last",  {}},
            }
      },
      { "Address",
              {
               {"Street", {}},
               {"City",   {}},
               {"State",  {}},
               {"ZIP",    {}}
              }
      }
    }
    
    Then to refer to a field in a SQL statement (EuSQL is case insensitive):

    First Name :  "name.first"
        Street :  "address.street"
       Address :  "address"
              -- actually a sequence:
              -- { Address.Street, Address.City, Address.State, Address.ZIP }
          etc.
    
    Using this method, it is possible to grab a single field, or a more complex, nested sequence of fields.

  • Miscellaneous Notes   EuSQL.e v0.76.8

    Table of Contents

    Queries

    These functions are used for executing SQL statements.


  • proc clear_params()   
  • func get_parameter( sequence param )   
  • proc init_params()   
  • func parse_sql( sequence sql )   
  • func run_query( sequence query )   
  • func run_sql( sequence sql )   
  • proc set_parameter( sequence param_name, object param_value )   

    Table of Contents

    SQL

    Summary of supported SQL syntax


    This is intended to give a basic overview of the SQL language and how EuSQL has implemented it. Here are some resources you can try if you'd like to learn more about SQL (there are many, many more):

    http://w3.one.net/~jhoffman/sqltut.htm
    http://msdn.microsoft.com/library/wcedoc/vbce/sql.htm
    http://www.spnc.demon.co.uk/ora_sql/sqlmain.htm

    There are several strategies for optimizing queries using EuSQL. First, try to focus you queries using a WHERE clause, especially in queries with a join, as this allows EuSQL to eliminate many records from its search. Another way to improve performance is to create indices on fields in your tables. You should try to create an index on a field that is likely to be part of a join, or that will likely be included in a WHERE clause. An index on a field will slow down INSERT, DELETE and UPDATE operations, but can dramatically improve SELECT operations. Since SELECT is usually done on records more often than operations that change the data, this is usually a very profitable trade off.

  • punctuation &   
  • keyword AS   
  • aggregate AVG()   
  • aggregate COUNT()   
  • command CREATE   
  • command DELETE   
  • keyword DISTINCT   
  • command DROP   
  • keyword FROM   
  • aggregate GROUP   
  • keyword IF( condition, true value, false value )   
  • keyword IN   
  • command INSERT   
  • keyword JOIN:   
  • keyword LEFT( [string], [chars] )   
  • keyword LEN( [string] )   
  • keyword LIKE   
  • aggregate MAX()   
  • keyword MID( [string], [start], [chars] )   
  • aggregate MIN()   
  • keyword ORDER   
  • keyword RIGHT ( [string], [chars] )   
  • command SELECT   
  • keyword STR( [number] )   
  • aggregate SUM()   
  • keyword UNION   
  • command UPDATE   
  • keyword VAL( [string] )   
  • keyword WHERE   
  • punctuation [...]   

    Table of Contents

    TODO

    What's next?


    Please let me know if any of these are important to you, and they'll probably be implemented sooner than later.


    Table of Contents

    Utilities

    Helper routines used by the library that may be useful


  • func blank_field( sequence record_struct )   
  • func blank_record( sequence db, sequence table )   
  • func flat_field_names( sequence db, sequence table )   
  • func flat_index( sequence struct, sequence index )   
  • func flat_record( sequence record, sequence struct )   
  • func get_field_datatype( sequence db, sequence table, sequence field )   
  • func get_next_autonumber( sequence db, sequence table, sequence field )   
  • func get_record_struct( sequence db_name, sequence table_name )   
  • func map_field( sequence field, sequence data, sequence index )   
  • func seq_store(object a, object b, object c)   
  • func tokenize( sequence text, integer separator )   
  • func type_check( object o, integer datatype )   
  • func validate_field( sequence field, sequence struct )   
  • func validate_field2( sequence db_name, sequence table_name, sequence field_name )   

    Table of Contents

    [punctuation]
    &

    Category: SQL

    Concatenates two strings into one string.

     SELECT THE.FIELD, 'FIELD: ' & THE.FIELD AS FIELD FROM MY_TABLE
    

    THE.FIELD FIELD --------- ----------- ID FIELD: ID NAME FIELD: NAME

    See Also: AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]


    Table of Contents

    [keyword]
    AS

    Category: SQL

    Used to create an alias for a field.

    ex:

    SELECT FNAME AS NAME FROM EMPLOYEES WHERE NAME LIKE "A*";
    

    See Also: &, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]


    Table of Contents

    [aggregate]
    AVG
    ()

    Category: SQL

    Returns the average of records selected.

    ex:

    SELECT AVG(SALARY) FROM EMPLOYEES;
    

    See Also: &, AS, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]


    Table of Contents

    [func]
    blank_field
    ( sequence record_struct )

    Category: Utilities

    Pass the result of get_record_struct() to blank_field, and it will return a sequence with the correct structure, but with all empty sequences.

    See Also: blank_record, flat_field_names, flat_index, flat_record, get_field_datatype, get_next_autonumber, get_record_struct, map_field, seq_store, tokenize, type_check, validate_field, validate_field2


    Table of Contents

    [func]
    blank_record
    ( sequence db, sequence table )

    Category: Utilities

    Returns an empty record for specified table where all fields are empty sequences. This wraps blank_field() and get_record_struct() into one call.

    See Also: blank_field, flat_field_names, flat_index, flat_record, get_field_datatype, get_next_autonumber, get_record_struct, map_field, seq_store, tokenize, type_check, validate_field, validate_field2


    Table of Contents

    [proc]
    clear_params
    ()

    Category: Queries

    Deletes all parameters previously set.

    See Also: get_parameter, init_params, parse_sql, run_query, run_sql, set_parameter


    Table of Contents

    [proc]
    close_db
    ( sequence db_name )

    Category: DB Management

    Closes the specified database.

    See Also: create_db, create_field, create_index, create_table, drop_field, drop_table, eusql_exclusive, open_db, reindex, rename_field, rename_table, select_current, select_db, select_table


    Table of Contents

    [aggregate]
    COUNT
    ()

    Category: SQL

    Returns a count of the number of records selected.

    ex:

    SELECT COUNT(ID) FROM EMPLOYEES;
    

    See Also: &, AS, AVG, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]


    Table of Contents

    [command]
    CREATE

    Category: SQL

    Create a table:

     CREATE TABLE EMPLOYEE, NAME AS TEXT, SSN AS TEXT
    

    See Also: &, AS, AVG, COUNT, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]


    Table of Contents

    [func]
    create_db
    ( sequence db_name )

    Category: DB Management

    Creates a EuSQL database, including TABLEDEF and INDEXDEF tables.

    See Also: close_db, create_field, create_index, create_table, drop_field, drop_table, eusql_exclusive, open_db, reindex, rename_field, rename_table, select_current, select_db, select_table


    Table of Contents

    [func]
    create_field
    ( sequence db_name, sequence table_name, sequence field_name, integer datatype, integer build_index )

    Category: DB Management

    You do not have to create a field before you can create a subfield. EuSQL will automatically create a parent field for a subfield if it doesn't exist.

    Valid datatypes:

    Atoms, integers objects and sequences are the same as the standard Eu definitions. Text is just a one dimensional sequence filled with integers in range 0-255. The binary datatype is a one dimensional sequence filled with bytes.

    build_index should be 1 if you wish to create an index (you cannot create an index for the primary key, although you can for subfields of a primary key), or 0 if not.

    See Also: close_db, create_db, create_index, create_table, drop_field, drop_table, eusql_exclusive, open_db, reindex, rename_field, rename_table, select_current, select_db, select_table


    Table of Contents

    [func]
    create_index
    ( sequence db_name, sequence table_name, sequence ix_name, sequence field, integer unique )

    Category: DB Management

    Creates an index (stored in INDEXDEF) on a field in a table. Each index must have a unique name (ix_name). It is recommended that you create an index on any field that is likely to be used as a foreign key, as this will speed up queries. The primary key itself may not be indexed, however, subfields of the primary key may be indexed.

    See Also: close_db, create_db, create_field, create_table, drop_field, drop_table, eusql_exclusive, open_db, reindex, rename_field, rename_table, select_current, select_db, select_table


    Table of Contents

    [func]
    create_table
    ( sequence db_name, sequence table_name )

    Category: DB Management

    Creates a table and generates an entry in TABLEDEF.

    See Also: close_db, create_db, create_field, create_index, drop_field, drop_table, eusql_exclusive, open_db, reindex, rename_field, rename_table, select_current, select_db, select_table


    Table of Contents

    [command]
    DELETE

    Category: SQL

    Delete records in one or more tables. Syntax is similar to to SELECT statement. Any table with a field mentioned between DELETE and FROM will have selected records deleted. Use the WHERE clause to specify records to delete.

    See Also: &, AS, AVG, COUNT, CREATE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]


    Table of Contents

    [func]
    delete_record
    ( sequence db_name, sequence table_name, object key )

    Category: Data

    Delete record with primary key 'key' from specified db/table.

    See Also: get_record, get_record2, get_record_mult_fields, insert_record, insert_record2, update_record, update_record2


    Table of Contents

    [keyword]
    DISTINCT

    Category: SQL

    Query will not return duplicate values.

    ex:

    SELECT DISTINCT LNAME FROM EMPLOYEES;
    

    See Also: &, AS, AVG, COUNT, CREATE, DELETE, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]


    Table of Contents

    [command]
    DROP

    Category: SQL

    Drop a table from the database:

     DROP TABLE EMPLOYEE
    

    See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]


    Table of Contents

    [func]
    drop_field
    ( sequence db_name, sequence table_name, sequence field_name )

    Category: DB Management

    Deletes a field and any index associated with the field. field_name can be one field, or a sequence of field names to be dropped.

    See Also: close_db, create_db, create_field, create_index, create_table, drop_table, eusql_exclusive, open_db, reindex, rename_field, rename_table, select_current, select_db, select_table


    Table of Contents

    [func]
    drop_table
    ( sequence db_name, sequence table_name )

    Category: DB Management

    Deletes a table and cleans up system tables to remove dropped table.

    See Also: close_db, create_db, create_field, create_index, create_table, drop_field, eusql_exclusive, open_db, reindex, rename_field, rename_table, select_current, select_db, select_table


    Table of Contents

    [type]
    EUSQLRESULT
    ( object e )

    User defined type

    Category: Errors

    Variables of type EUSQLRESULT are sequences, unless there is an error, at which point the variable will contain a positive integer. If 'with type' is specified, typechecking will fail, and the error will be output to either a user specified routine or to the output stream specified by eusql_err_out(). If you call eusql_true_typecheck( 0 ), then your program will not crash when a EUSQLRESULT is an integer. EuSQL will pass control back to you. If 'without type' is specified, EuSQL will not automatically catch errors.

    See Also: eusql_err_out, eusql_true_typecheck, get_sql_err


    Table of Contents

    [proc]
    eusql_err_out
    ( integer fn )

    Category: Errors

    Redirects EuSQL typecheck errors. If fn is positive, then errors are output to file number fn. If fn is negative, then -fn is the routine id of a user specified callback procedure. EuSQL will call this procedure if a typecheck fails. The parameter will be the integer EUSQL_ERR_ code. When the callback procedure returns, the typecheck will fail unless typechecking was previously turned off using eusql_true_typecheck(). The default value is 1, which ouputs error messages to the console.

    See Also: EUSQLRESULT, eusql_true_typecheck, get_sql_err


    Table of Contents

    [proc]
    eusql_exclusive
    ( integer flag )

    Category: DB Management

    If flag is zero, subsequenct calls to open_db() will open databases with the EDS flag DB_LOCK_NO. If flag is a non-zero value, subsequent calls to open_db() will use the EDS flag DB_LOCK_EXCLUSIVE. The default is to use DB_LOCK_EXCLUSIVE.

    See Also: close_db, create_db, create_field, create_index, create_table, drop_field, drop_table, open_db, reindex, rename_field, rename_table, select_current, select_db, select_table


    Table of Contents

    [proc]
    eusql_true_typecheck
    ( integer new_setting )

    Category: Errors

    Indicates whether typechecks for variables of type EUSQLRESULT will return a value of zero. This is independent of any calls to 'with type' or 'without type'. new_setting should be a boolean value. If set to 0, EUSQLRESULT typechecks that fail will not crash if typechecking is on.

    See Also: EUSQLRESULT, eusql_err_out, get_sql_err


    Table of Contents

    [func]
    flat_field_names
    ( sequence db, sequence table )

    Returns: flattened version of column names

    Category: Utilities

    Names for fields with subfields are not returned.

    See Also: blank_field, blank_record, flat_index, flat_record, get_field_datatype, get_next_autonumber, get_record_struct, map_field, seq_store, tokenize, type_check, validate_field, validate_field2


    Table of Contents

    [func]
    flat_index
    ( sequence struct, sequence index )

    Category: Utilities

    Returns a flattened version of indices for a record. Indices for fields with subfields are not returned. 'index' should be an empty sequence.

    See Also: blank_field, blank_record, flat_field_names, flat_record, get_field_datatype, get_next_autonumber, get_record_struct, map_field, seq_store, tokenize, type_check, validate_field, validate_field2


    Table of Contents

    [func]
    flat_record
    ( sequence record, sequence struct )

    Category: Utilities

    Returns the flattened version of the data of a record. Data for fields with subfields are not returned.

    See Also: blank_field, blank_record, flat_field_names, flat_index, get_field_datatype, get_next_autonumber, get_record_struct, map_field, seq_store, tokenize, type_check, validate_field, validate_field2


    Table of Contents

    [keyword]
    FROM

    Category: SQL

    Specifies table from which fields are selected.

    ex:

    SELECT NAME FROM EMPLOYEES;
    

    See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]


    Table of Contents

    [func]
    get_field_datatype
    ( sequence db, sequence table, sequence field )

    Category: Utilities

    Returns the datatype for the field. field can be either tokenized or untokenized.

    See Also: blank_field, blank_record, flat_field_names, flat_index, flat_record, get_next_autonumber, get_record_struct, map_field, seq_store, tokenize, type_check, validate_field, validate_field2


    Table of Contents

    [func]
    get_next_autonumber
    ( sequence db, sequence table, sequence field )

    Category: Utilities

    Returns the value of the next AUTONUMBER that will be assigned for the specified field.

    See Also: blank_field, blank_record, flat_field_names, flat_index, flat_record, get_field_datatype, get_record_struct, map_field, seq_store, tokenize, type_check, validate_field, validate_field2


    Table of Contents

    [func]
    get_parameter
    ( sequence param )

    Category: Queries

    Returns the value of a parameter.

    See Also: clear_params, init_params, parse_sql, run_query, run_sql, set_parameter


    Table of Contents

    [func]
    get_record
    ( sequence db_name, sequence table_name, object key, sequence field, sequence index )

    Category: Data

    Returns a field (or the whole field as a flat record) with primary key = key from table table_name. You may specify either the field name or the index (as returned by validate_field()). Data is returned as a sequence. To get the actual value, take the first element of the data returned. This is to allow error processing.

    See Also: delete_record, get_record2, get_record_mult_fields, insert_record, insert_record2, update_record, update_record2


    Table of Contents

    [func]
    get_record2
    ( sequence db_name, sequence table_name, sequence search_field, object search, sequence field, sequence field_index )

    Category: Data

    Allows record retrieval based on non-key field values. Returns only the first record found with the correct field value.

    See Also: delete_record, get_record, get_record_mult_fields, insert_record, insert_record2, update_record, update_record2


    Table of Contents

    [func]
    get_record_mult_fields
    ( sequence db_name, sequence table_name, object key, sequence field, sequence index )

    Category: Data

    Retrieves multiple fields from a record.

    See Also: delete_record, get_record, get_record2, insert_record, insert_record2, update_record, update_record2


    Table of Contents

    [func]
    get_record_struct
    ( sequence db_name, sequence table_name )

    Category: Utilities

    Returns the TABLEDEF definition of the structure of the tables records.

    See Also: blank_field, blank_record, flat_field_names, flat_index, flat_record, get_field_datatype, get_next_autonumber, map_field, seq_store, tokenize, type_check, validate_field, validate_field2


    Table of Contents

    [func]
    get_sql_err
    ( integer errnum )

    Category: Errors

    Use this to get a text representation of the error returned. It also includes any extended error information beyond the standard error.

    See Also: EUSQLRESULT, eusql_err_out, eusql_true_typecheck


    Table of Contents

    [aggregate]
    GROUP

    Category: SQL

    BYIf other aggregate functions are used (SUM, COUNT, AVG, etc), any field not within a function must be specified by GROUP BY.

    ex:

    SELECT JOB_DESC, AVG(SALARY) FROM EMPLOYEES GROUP BY JOB_DESC;
    

    This returns the average salary for each JOB_DESC:

    JOB_DESC    AVG OF SALARY
    SECRETARY    25000
    MANAGER        60000
    ...etc
    

    See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]


    Table of Contents

    [keyword]
    IF
    ( condition, true value, false value )

    Category: SQL

    IF can be used in a calculated field to do conditional calculations:

     SELECT NUM, NUM + IF( NUM > 10, 0, 10 ) AS TEST FROM NUMBERS
    

    NUM TEST --- ---- 1 10 10 10 11 0

    See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]


    Table of Contents

    [keyword]
    IN

    Category: SQL

    IN is used in a WHERE clause to select from a list of values:

    SELECT NAME, DEPARTMENT FROM EMPLOYEES WHERE DEPARTMENT IN(1,3,5)
    
    This would select the names of all employees in departments 1, 3, and 5.

    See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]


    Table of Contents

    [proc]
    init_params
    ()

    Category: Queries

    Initialize EuSQL parameters and store as Matheval variables.

    See Also: clear_params, get_parameter, parse_sql, run_query, run_sql, set_parameter


    Table of Contents

    [command]
    INSERT

    Category: SQL

    Insert records into a table. Values to be inserted can be specified either with a SELECT statement or explicity using VALUES( f1, f2, f3, ... ).

    The order in which fields are specified must match the order in which the values are given. If the number fields and values (supplied explicitly or implicitly) do not match, an error will be returned.

    INSERT INTO TABLE1 FIELD1, FIELD3, FIELD4, FIELD2 VALUES( VAL1, VAL3, VAL4, VAL2)
    

    FIELD1 <- VAL1 FIELD3 <- VAL3 FIELD4 <- VAL4 FIELD2 <- VAL2

    INSERT INTO TABLE1 FIELD1, FIELD3, FIELD4, FIELD2 SELECT FIELD1, FIELD3, FIELD4, FIELD2 FROM TABLE2

    Inserts resulting records of SELECT into TABLE1.

    The inserted records will be returned with a status field appended to each record, indicating either "OK" or "ERROR". The most common error is duplication of primary keys.

    See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]


    Table of Contents

    [func]
    insert_record
    ( sequence db_name, sequence table_name, object blank_record, sequence data, sequence index )

    Category: Data

    Inserts a record into specified table.

    See Also: delete_record, get_record, get_record2, get_record_mult_fields, insert_record2, update_record, update_record2


    Table of Contents

    [func]
    insert_record2
    ( sequence db_name, sequence table_name, sequence blank_record, sequence data, sequence index )

    Category: Data

    insert_record2() is for inserting multiple records at a time. It is much faster to do bulk inserts this way than using insert_record() or multiple INSERT statements.

    ex:
    EUSQLRESULT ok, void
    -- suppose that MY_TABLE and YOUR_TABLE are in myDB.edb and yourDB.edb
    -- both contain 2 fields: ID and NAME
    -- and we'd like to copy all the records from MY_TABLE to YOUR_TABLE
    

    -- get all the records from MY_TABLE void = open_db("myDB.edb") ok = run_sql("SELECT * FROM MY_TABLE")

    -- now insert them into YOUR_TABLE void = open_db("yourDB.edb") ok = insert_record2( "myDB.edb", "YOUR_TABLE", blank_record("myDB.edb","YOUR_TABLE"), ok[2], { {1}, {2} })

    insert_record2() now always returs a sequence containing the status for each of the records to be inserted. Each status indicator will be a two-element sequence. The first element will be a sequence if insertion worked, or an atom if insertion failed. If it failed, the second element will be a full error description as returned by get_sql_err().

    See Also: delete_record, get_record, get_record2, get_record_mult_fields, insert_record, update_record, update_record2


    Table of Contents

    [keyword]
    JOIN:

    Category: SQL

    LEFT/RIGHT/INNER JOIN ON Use relationship between two tables to 'join' into one table for querying purposes. You must specify either LEFT, RIGHT OR INNER. An INNER join requires that both fields be non-null, while a RIGHT or LEFT join can have either the 'left' or 'right' field, respectively, be null. Field names must have the table name preceding the field name (EMPLOYEES.NAME).

    ex:

    SELECT EMPLOYEE.ID, SCHEDULE.DATE FROM EMPLOYEE INNER JOIN SCHEDULE ON EMPLOYEE.ID = SCHEDULE.ID;
    
    SCHEDULE.ID is a foreign key to EMPLOYEE.ID, and both fields must be non-null
    SELECT EMPLOYEE.ID, MGR.NAME FROM EMPLOYEE LEFT JOIN MGR ON EMPLOYEE.MGR_ID = MGR.ID;
    
    EMPLOYEE.MGR_ID is the foreign key to MGR.ID. MGR_ID can be null (not all employees have managers)

    See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]


    Table of Contents

    [keyword]
    LEFT
    ( [string], [chars] )

    Category: SQL

    Returns the first chars characters of string.

     SELECT THE.FIELD, LEFT(THE.FIELD, 2) AS LEFT_2 FROM MY_TABLE
    

    THE.FIELD LEFT_2 --------- ------ JOHN JO MARY MA

    See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]


    Table of Contents

    [keyword]
    LEN
    ( [string] )

    Category: SQL

    Returns the length of string.

      SELECT THE.FIELD, LEN(THE.FIELD) AS LENGTH FROM MY_TABLE
    

    THE.FIELD LENGTH --------- ------ ABCDEFG 7 XYZ 3

    See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]


    Table of Contents

    [keyword]
    LIKE

    Category: SQL

    Used for wildcard string comparisons.

    ex:

    SELECT NAME FROM EMPLOYEES WHERE NAME LIKE "A*";
    

    See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]


    Table of Contents

    [func]
    map_field
    ( sequence field, sequence data, sequence index )

    Category: Utilities

    This is used by EuSQL to put field values into a structured EuSQL record. For instance, if your table had the fields:

    And you wanted to insert data for John Smith, with phone number 555-1234, living at 123 Main St, USA:
      ok = map_field( {{ {}, {} }, {}, { {}, {}}},    -- the record structure without any data
                       { "John", "Smith", "555-1234", -- the data to be stored
                         "123 Main St", "USA" },
                       {{1,1},{1,2},{2},{3,1},{3,2}}) -- the respective field indices for each
                                                      -- field
    

    ok = { {"John", "Smith"},"555-1234",{"123 Main St","USA"}}

    See Also: blank_field, blank_record, flat_field_names, flat_index, flat_record, get_field_datatype, get_next_autonumber, get_record_struct, seq_store, tokenize, type_check, validate_field, validate_field2


    Table of Contents

    [aggregate]
    MAX
    ()

    Category: SQL

    Returns the largest value from the records selected

    ex:

    SELECT MAX(SALARY) FROM EMPLOYEES;
    

    See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]


    Table of Contents

    [keyword]
    MID
    ( [string], [start], [chars] )

    Category: SQL

    Returns the chars characters of string, starting with character number start.

     SELECT THE.FIELD, MID(THE.FIELD, 2, 2) AS MID_2 FROM MY_TABLE
    

    THE.FIELD MID_2 --------- ----- JOHN OH MARY AR

    See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]


    Table of Contents

    [aggregate]
    MIN
    ()

    Category: SQL

    Returns the smallest value from the records selected

    ex:

    SELECT MIN(SALARY) FROM EMPLOYEES;
    

    See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]


    Table of Contents

    []
    Miscellaneous Notes

    EuSQL.e v0.76.8

    Category: Miscellaneous Notes


    Table of Contents

    [func]
    open_db
    ( sequence db_name )

    Category: DB Management

    Opens a EuSQL database. The default is to open the database with the EDS flag DB_LOCK_EXCLUSIVE. To open databases with DB_LOCK_NO, use eusql_exclusive().

    See Also: close_db, create_db, create_field, create_index, create_table, drop_field, drop_table, eusql_exclusive, reindex, rename_field, rename_table, select_current, select_db, select_table


    Table of Contents

    [keyword]
    ORDER

    Category: SQL

    BY [DESC]Set a sort order for the returned records. The default is to order in ascending order. Use DESC to order by descending order.

    ex:

    SELECT NAME, AGE FROM EMPLOYEES ORDER BY AGE DESC, NAME;
    

    See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]


    Table of Contents

    [func]
    parse_sql
    ( sequence sql )

    Category: Queries

    sql must be a string. This function will return a 'compiled' query, which can be passed to run_query. If there is an error, parse_sql will return an atom. If a query will be run multiple times (perhaps using different parameters each time), it is faster to parse the statement once, and use run_query() than to use run_sql() each time.

    See Also: clear_params, get_parameter, init_params, run_query, run_sql, set_parameter


    Table of Contents

    [func]
    reindex
    (sequence db_name, sequence table_name, sequence ix_name)

    Category: DB Management

    Rebuilds the specified index.

    See Also: close_db, create_db, create_field, create_index, create_table, drop_field, drop_table, eusql_exclusive, open_db, rename_field, rename_table, select_current, select_db, select_table


    Table of Contents

    [func]
    rename_field
    ( sequence db_name, sequence table_name, sequence old_name, sequence new_name )

    Category: DB Management

    Rename a field.

    See Also: close_db, create_db, create_field, create_index, create_table, drop_field, drop_table, eusql_exclusive, open_db, reindex, rename_table, select_current, select_db, select_table


    Table of Contents

    [func]
    rename_table
    ( sequence db_name, sequence table_name, sequence new_name )

    Category: DB Management

    Renames the table table_name to new_name and updates TABLEDEF and INDEXDEF with the new name.

    See Also: close_db, create_db, create_field, create_index, create_table, drop_field, drop_table, eusql_exclusive, open_db, reindex, rename_field, select_current, select_db, select_table


    Table of Contents

    [keyword]
    RIGHT
    ( [string], [chars] )

    Category: SQL

    Returns the last chars characters of string.

     SELECT THE.FIELD, RIGHT(THE.FIELD, 2) AS RIGHT_2 FROM MY_TABLE
    

    THE.FIELD RIGHT_2 --------- ------- JOHN HN MARY RY

    See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]


    Table of Contents

    [func]
    run_query
    ( sequence query )

    Category: Queries

    query must be compiled SQL returned from parse_sql(). This function returns a 3 element sequence:

    1. sequence of the field names
    2. records returned by the query
    3. datatypes of each field

    See Also: clear_params, get_parameter, init_params, parse_sql, run_sql, set_parameter


    Table of Contents

    [func]
    run_sql
    ( sequence sql )

    Category: Queries

    sql must be a SQL statement in a string. This function calls parse_sql and run_query for you, returning the result. If there is an error in the statement, an atom will be returned, otherwise, the result is a three element sequence:

    1. sequence of the field names
    2. records returned by the query
    3. datatypes of each field

    See Also: clear_params, get_parameter, init_params, parse_sql, run_query, set_parameter


    Table of Contents

    [command]
    SELECT

    Category: SQL

    Used to query a database to return values

    ex:

    SELECT NAME, AGE FROM EMPLOYEES;
    

    See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , STR, SUM, UNION, UPDATE, VAL, WHERE, [...]


    Table of Contents

    [func]
    select_current
    ( sequence db_name, sequence table_name )

    Category: DB Management

    Change the current database and table. An empty sequence for either parameter will use the current setting.

    See Also: close_db, create_db, create_field, create_index, create_table, drop_field, drop_table, eusql_exclusive, open_db, reindex, rename_field, rename_table, select_db, select_table


    Table of Contents

    [func]
    select_db
    ( sequence db )

    Category: DB Management

    Change the current database.

    See Also: close_db, create_db, create_field, create_index, create_table, drop_field, drop_table, eusql_exclusive, open_db, reindex, rename_field, rename_table, select_current, select_table


    Table of Contents

    [func]
    select_table
    ( sequence table )

    Category: DB Management

    Change the current table.

    See Also: close_db, create_db, create_field, create_index, create_table, drop_field, drop_table, eusql_exclusive, open_db, reindex, rename_field, rename_table, select_current, select_db


    Table of Contents

    [func]
    seq_store
    (object a, object b, object c)

    Category: Utilities

    Store a in b at subcript c

    See Also: blank_field, blank_record, flat_field_names, flat_index, flat_record, get_field_datatype, get_next_autonumber, get_record_struct, map_field, tokenize, type_check, validate_field, validate_field2


    Table of Contents

    [proc]
    set_parameter
    ( sequence param_name, object param_value )

    Category: Queries

    Sets the value of a parameter to be used by a query. Parameters must be enclosed in square brackets ( [param1] ) when declared in a SQL statement, but the brackets must be omitted when calling set_parameter.

    See Also: clear_params, get_parameter, init_params, parse_sql, run_query, run_sql


    Table of Contents

    [keyword]
    STR
    ( [number] )

    Category: SQL

    Converts an atom (number) into a string.

     SELECT THE.FIELD, STR(THE.FIELD) AS STRING FROM MY_TABLE
    

    THE.FIELD STRING --------- ------ 1 "1" 6.7 "6.7"

    See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, SUM, UNION, UPDATE, VAL, WHERE, [...]


    Table of Contents

    [aggregate]
    SUM
    ()

    Category: SQL

    Returns the sum of records selected.

    ex:

    SELECT SUM(SALARY) FROM EMPLOYEES;
    

    See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, UNION, UPDATE, VAL, WHERE, [...]


    Table of Contents

    [func]
    tokenize
    ( sequence text, integer separator )

    Category: Utilities

    Tokenizes a sequence based on a separator. Used to tokenize a field to be passed to validate_field(). tokenize function by F Dowling (check the archives at www.rapideuphoria.com)

    See Also: blank_field, blank_record, flat_field_names, flat_index, flat_record, get_field_datatype, get_next_autonumber, get_record_struct, map_field, seq_store, type_check, validate_field, validate_field2


    Table of Contents

    [func]
    type_check
    ( object o, integer datatype )

    Category: Utilities

    See Also: blank_field, blank_record, flat_field_names, flat_index, flat_record, get_field_datatype, get_next_autonumber, get_record_struct, map_field, seq_store, tokenize, validate_field, validate_field2


    Table of Contents

    [keyword]
    UNION

    Category: SQL

    Allows you to combine two SELECT statements into one query:

       SELECT NAME, ADDRESS FROM EMPLOYEES
       UNION
       SELECT NAME, ADDRESS FROM DEPENDENTS
    
    The resulting dataset would contain two fields (name and address) with the combined data from each SELECT statement.

    See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UPDATE, VAL, WHERE, [...]


    Table of Contents

    [command]
    UPDATE

    Category: SQL

    Update records in a table. The format for the UPDATE command is:

      UPDATE  SET  = , [ = ...] [WHERE ]
    
    For example, if you wanted to change all employees in department 3 to department 2:
      UPDATE EMPLOYEES SET DEPARTMENT = 2 WHERE DEPARTMENT = 3
    

    See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, VAL, WHERE, [...]


    Table of Contents

    [func]
    update_record
    ( sequence db_name, sequence table_name, object key, sequence index, sequence data )

    Category: Data

    Update a record. 'key' specifies which record is to be updated. 'index' specifies the field indices to update, and data is a sequence of field values to update.

    See Also: delete_record, get_record, get_record2, get_record_mult_fields, insert_record, insert_record2, update_record2


    Table of Contents

    [func]
    update_record2
    ( sequence db_name, sequence table_name, object key, sequence index, sequence data, integer key_changing )

    Category: Data

    Update a record.

    This can be quicker than update_record(), but the calling procedure must be aware of whether any element of the primary key is changing.

    See Also: delete_record, get_record, get_record2, get_record_mult_fields, insert_record, insert_record2, update_record


    Table of Contents

    [keyword]
    VAL
    ( [string] )

    Category: SQL

    Converts a string to an atom (number), or leaves an atom value alone.

     SELECT THE.FIELD, VAL( THE.FIELD ) AS NUM FROM MY_TABLE
    

    THE.FIELD NUM --------- --- "1" 1 "6.7" 6.7

    See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, WHERE, [...]


    Table of Contents

    [func]
    validate_field
    ( sequence field, sequence struct )

    Category: Utilities

    Returns the index of the field if valid.
    ex:
      -- Assume a table "PERSON":
      --      NAME.FIRST
      --      NAME.LAST
      --      EMAIL
    

    field = tokenize( "NAME.FIRST", '.' ) struct = get_record_struct( my_db, "PERSON" ) field_index = validate_field( field, struct )

    -- field_index = {1,1}

    See Also: blank_field, blank_record, flat_field_names, flat_index, flat_record, get_field_datatype, get_next_autonumber, get_record_struct, map_field, seq_store, tokenize, type_check, validate_field2


    Table of Contents

    [func]
    validate_field2
    ( sequence db_name, sequence table_name, sequence field_name )

    Category: Utilities

    Returns the field index the field for field_name in table table_name in the open database db_name. This function calls get_record_struct(), tokenize() and validate_field() for you. If you need to call this multiple times, it will be faster to store a local copy of the results from get_record_struct() and tokenize() and pass them directly to validate_field().

    ex:
      -- Assume a table "PERSON":
      --      NAME.FIRST
      --      NAME.LAST
      --      EMAIL
    

    field_index = validate_field( my_db, "PERSON", "NAME.FIRST" )

    -- field_index = {1,1}

    See Also: blank_field, blank_record, flat_field_names, flat_index, flat_record, get_field_datatype, get_next_autonumber, get_record_struct, map_field, seq_store, tokenize, type_check, validate_field


    Table of Contents

    [keyword]
    WHERE

    Category: SQL

    Set constraints on records selected. You can use =, >, < or LIKE for comparisons. String arguments must be enclosed by single quotes ' '. If you have multiple conditions (using AND and OR to tie them together, you should put the individual conditions in parentheses, since the precedence order may not be what you believe it to be.

    ex:

    SELECT NAME, AGE FROM EMPLOYEES WHERE (AGE > 40) AND (NAME LIKE 'A*');
    

    See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, [...]


    Table of Contents

    [punctuation]
    [...]

    Category: SQL

    Used to specify a parameter that can be set independently of parsing/compiling the query. Use set_parameter to set the value of a parameter. Parameters can be used as values for fields or for comparison purposes. This can be used for queries that are run often, but which need different values. Rather than building/parsing a new SQL command, the parameters in a compiled query can be modified.

    ex:

    SELECT NAME FROM EMPLOYEES WHERE NAME LIKE [NAME];
    

    set_parameter( "name", "A*" )

    See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE


    Index

    & [punctuation]
    API
    AS [keyword]
    AVG [aggregate]
    blank_field [func]
    blank_record [func]
    Change History
    clear_params [proc]
    close_db [proc]
    COUNT [aggregate]
    CREATE [command]
    create_db [func]
    create_field [func]
    create_index [func]
    create_table [func]
    Data
    Datatypes
    DB Management
    DELETE [command]
    delete_record [func]
    DISTINCT [keyword]
    DROP [command]
    drop_field [func]
    drop_table [func]
    Errors
    EUSQLRESULT [type] User defined type
    eusql_err_out [proc]
    eusql_exclusive [proc]
    eusql_true_typecheck [proc]
    flat_field_names [func]
    flat_index [func]
    flat_record [func]
    FROM [keyword]
    get_field_datatype [func]
    get_next_autonumber [func]
    get_parameter [func]
    get_record [func]
    get_record2 [func]
    get_record_mult_fields [func]
    get_record_struct [func]
    get_sql_err [func]
    GROUP [aggregate]
    IF [keyword]
    IN [keyword]
    init_params [proc]
    INSERT [command]
    insert_record [func]
    insert_record2 [func]
    JOIN: [keyword]
    Known Bugs
    LEFT [keyword]
    LEN [keyword]
    LIKE [keyword]
    map_field [func]
    MAX [aggregate]
    MID [keyword]
    MIN [aggregate]
    Miscellaneous Notes
    Miscellaneous Notes [] EuSQL.e v0.76.8
    open_db [func]
    ORDER [keyword]
    parse_sql [func]
    Queries
    reindex [func]
    rename_field [func]
    rename_table [func]
    RIGHT [keyword]
    run_query [func]
    run_sql [func]
    SELECT [command]
    select_current [func]
    select_db [func]
    select_table [func]
    seq_store [func]
    set_parameter [proc]
    SQL
    STR [keyword]
    SUM [aggregate]
    TODO
    tokenize [func]
    type_check [func]
    UNION [keyword]
    UPDATE [command]
    update_record [func]
    update_record2 [func]
    Utilities
    VAL [keyword]
    validate_field [func]
    validate_field2 [func]
    WHERE [keyword]
    [...] [punctuation]