Table of Contents

Errors

Trapping and using EuSQL error information

• EUSQL_FAIL = 1
• EUSQL_ERR_WHERE = 2
• EUSQL_ERR_FIELD = 3
• EUSQL_ERR_JOIN = 4
• EUSQL_ERR_TABLE = 5
• EUSQL_ERR_SYNTAX = 6

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:

Table of Contents

Known Bugs

Still gotta squash these...

• I think there may be some lingering bugs regarding field parsing when the table name is or is not prepended.
• If you receive an error saying that db_rename_table() is not defined, then you need to upgrade your copy of database.e. EuSQL requires the version of database.e that comes with Euphoria 2.4. This version of database.e will run under Euphoria 2.3. It is a faster and more complete version of the Euphoria Database System, is 100% backwards compatible with earlier versions, and is highly recommended.

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).

• Create an index on fields that are part of joins. When an index exists, EuSQL doesn't have to search the entire table to find the correct records. An index will slow update and insert operations, but increase SELECT queries.
• Use WHERE clauses, especially when joining two tables. In conjunction with an index or two EuSQL can avoid loading and looking at lots of records.

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.

Table of Contents

Queries

These functions are used for executing SQL statements.

Table of Contents

SQL

Summary of supported SQL syntax

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.

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.

• Must have
  • Optimization
    • Smarter (binary) index search when WHERE clause exists
  • Self joins
  • ALTER queries
  • Allow CREATE/DROP INDEX in SQL statements
• Would be nice to have
  • Views
  • Callback/progress option for long processing
  • Constraints
  • Unique indices
  • Return partial results
  • Multi-user (EuSQL Server? ODBC Driver?)
    • Record locking
    • Security

Table of Contents

Utilities

Helper routines used by the library that may be useful

Table of Contents

[punctuation]
&

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

Used to create an alias for a field.

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 ()

Returns the average of records selected.

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 )

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 )

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 ()

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 )

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 ()

Returns a count of the number of records selected.

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

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 )

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 )

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:

• EUSQL_EU_ATOM
• EUSQL_EU_INTEGER
• EUSQL_EU_TEXT
• EUSQL_EU_SEQUENCE
• EUSQL_EU_BINARY
• EUSQL_EU_OBJECT
• EUSQL_AUTONUMBER

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 )

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 )

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

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 )

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

Query will not return duplicate values.

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

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 )

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 )

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

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 )

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 )

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 )

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

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 )

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 )

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

Specifies table from which fields are selected.

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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

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

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 )

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

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 ()

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

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 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 )

Inserts a record into specified table.

• db_name
• table_name
• blank_record tells insert_record() what the structure of the record is. You can call blank_record() to generate the record yourself. This is recommended if you will be calling insert_record() multiple times. If blank_record is an atom, insert_record() will call blank_record() for you.
• data is a sequence of field values
• index is a sequence of field indices, or the equivalent subscript that would be used if the record were a sequence.
  If the structure of the table were:
  • ID
  • NAME.FIRST
  • NAME.LAST
  • EMAIL
  Then the record as a sequence would be:

  { ID, { NAME.FIRST, NAME.LAST }, EMAIL }

  So the corresponding indices would be:

  Field	Index
  ID	{1}
  NAME	{2}
  NAME.FIRST	{2,1}
  NAME.LAST	{2,2}
  EMAIL	{3}

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 )

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.

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

Table of Contents

[keyword]
JOIN:

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).

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] )

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] )

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

Used for wildcard string comparisons.

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 )

• field: blank structure of the record (see blank_record() and blank_field())
• data: sequence of fields
• index: sequence of field indices

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

• NAME.FIRST
• NAME.LAST
• PHONE_NUMBER
• ADDRESS.STREET
• ADDRESS.COUNTRY

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 ()

Returns the largest value from the records selected

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] )

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 ()

Returns the smallest value from the records selected

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

Table of Contents

[func]
open_db ( sequence db_name )

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

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.

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 )

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)

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 )

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 )

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] )

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 )

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 )

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

Used to query a database to return values

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 )

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 )

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 )

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)

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 )

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] )

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 ()

Returns the sum of records selected.

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 )

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 )

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

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

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 )

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 )

Update a record.

• key specifies which record is to be updated
• index specifies the field indices to update
• data is a sequence of field values to update
• key_changing should be 1 if the record key is to be changed, 0 otherwise

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

Table of Contents

[keyword]
VAL ( [string] )

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 )

• field is the tokenized version of a field name
• struct is from get_record_struct()

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 )

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

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.

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]
[...]

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.

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