Table of Contents

Introduction

Euphoria Database Browser v1.26.5

by Matt Lewis matthewwalkerlewis@yahoo.com

The Euphoria Database Browser (EDB) is an application for viewing, editing and creating Euphoria Database System (EDS) databases. It recognizes any EDS database, and has added functionality when viewing a EuSQL database (a specialized EDS database created using EuSQL). My goal in creating EDB was to provide a MS Access-like application for Euphoria. It's not there yet, but I think you'll find it to be a very useful and powerful program.

EDB is designed to run under Euphoria v2.4. It should work with Euphoria v2.3, however, you must have the version of database.e that is included with v2.4. This version is 100% compatible with the earlier version, but has some additional functions and optimizations. (You can download the free version of Euphoria v2.4 to get an updated version of database.e.)

LICENSE:
This software is freeware, but you must give me credit for any applications developed with
EDB.  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.

DISCLAIMER: 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.

Source Files * edb.exw : Main application for Windows * edbform.ew : User forms * edbform_ui.ew: User forms design interface * edblib.ew : Unshrouded include for deployment * edbscript.e : Scripting * edbutils.e : Common routines used by EDB files * eusql.e : SQL engine for EDS + hlist.e : Hybrid associative list routines by Jiri Babor. Used 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 : IF() function for queries - texteval.e : Text manipulation for queries * print.e : A great pretty print utility for arbitrary Euphoria objects * sprint.e : Pretty sprint, based on print.e * ini.e : INI file utility by Mike * eugrid130.zip: EuGrid v1.3 * xControls.ew : Geometry manager (with some modifications) by Don Phillips - geometry.ew : Geometry manager (with some modifications) by Don Phillips * euscript.e : Euphoria scripting engine (originally by David Cuny) * odbc.ew : ODBC wrapper * progress.ew : Progress dialog * prop.ew : Properties control by Martin Stachon

Demo files * sql.edb : Sample EuSQL database converted from RDS provided ARCHIVE.EDB. * archive.prj : Sample Win32Lib project file with importable forms

Icons : * icon1.ico * icon2.ico * Euphoria.ico * CLSDFOLD.ICO * OPENFOLD.ICO

Documentation : * edb.htm : This help file * eusql.htm : EuSQL documentation * Geometry.htm : Geometry manager documentation * EuGrid.htm : EuGrid documentation

Additional files (not included): * Win32Lib (developed with v0.60.1) * Standard Euphoria v2.4 include files


Table of Contents

Topic

Description

IndexAlphabetical list of all items.
Change LogThe history of EDB
CreditsPeople who made this possble
DeployingDistribute your database as an application
Editing a databaseChanging the contents of a database
EuSQL databasesA powerful addition to EDS
Future VersionsWhat's in store for EDB
Importing and Exporting DataUsing text files and other EDS databases
Known BugsWhy aren't these fixed?!
ODBC and EDBConnecting to other databases
QueriesRunning SQL commands
User FormsAdding an interface to your data
Viewing a databaseExamining the contents of a database

Table of Contents

Change Log

The history of EDB


 * v1.26.5 - Added namespace qualifiers to work with Win32Lib v0.60.4
           - Preserves quotes and sequence braces around fields in regular EDS database
             and for fields with datatype Object in EuSQL database
           - Queries and recent files stored as plain text in ini file
           - Uses EuSQL v0.76.8 (see EuSQL.htm for details) 
           - Eugrid included as a zip file.  Only install if you do not already have EuGrid v1.3
             installed in a EUINC directory

* v1.26.4 - EuSQL tables created with one field now save properly - AUTONUMBER fields are properly set to disallow editing - Changes in Table Definition Grid are saved now when either Check Table or verify table are clicked. - Use EuGrid v1.3 - Uses EuSQL v0.76.7 (see EuSQL.htm for details)

* v1.26.3 - The recent query list on the SQL tab now allows horizontal scrolling for reading the entire text of the query - Uses EuSQL v0.76.6 (see EuSQL.htm for details)

* v1.26.2 - Uses EuSQL v0.76.5 (see EuSQL.htm for details) - Resizes the row headers in the data grid to make sure entire record number is displayed after a table is opened into the data grid - EuGrid modifications: + Added getScrollInfo() to allow 32-bit scrolling + Added check for valid grid in grid event handler

* v1.26.1 - Bug fixes: + Open dialog using "All Files" now uses "*.*" as filter + Fixed bug where adding a new record and deleting it before it was updated would cause a crash in an EDS database + Multiple open forms could sometimes cause the wrong script to be run + Saving changes to new EuSQL table would cause division by zero in progress dialog code + Form tab wasn't cleared when another database was opened - Uses EuSQL v0.76.4 (see EuSQL.htm for details)

* v1.26 - Import Form will import EuGrid controls created from within the IDE - Fixed bug during importing forms--new property AUTOREFRESH wasn't initialized properly, leading to an out of bounds error on a record change - Fixed bug in event handling that could lead to wrong event handler name - Data grid and grids in form should always start with the first row displayed when refreshed with new data if it was scrolled to a lower line before the refresh - Form UI close code tried to set Autorefresh property as an empty sequence but the property is a checkbox, and needs to be an atom - Properties of current form being edited were being deleted when the db was closed - Uses EuSQL v0.76.3 (see EuSQL.htm for details) + Calculated fields (simple arithmetic, IF, text operations) + Text operations (LEN, RIGHT, LEFT, MID, &)

* v1.25 - Added open, close, print, printf, puts to scripting - Fixed bugs in user forms - Added Auto-refresh property for controls in forms - EuGrid modifications: + Fixed to display '&' characters properly + Calls to EGW_SetCurrentCell now trigger an EGW_CELLCHANGE message

* v1.24 - Fixed import bug when not creating a primary key from a text file. - Fixed display bug where field Data 2 was skipped when looking at a table in a normal EDS database.

* v1.23 - Deployed app will now properly close if an invalid form is specified. - Added progress dialog for saving changes to a table - Can specify column widths for Grid controls - Forms didn't handle combo's correctly - Added more detailed error reporting when connecting to an ODBC database - Fixed bug when the record key in a normal EDS database was an atom - Properly displays records with single field of text in normal EDS database - User is prompted to save unsaved editing when closing the database or changing the view to a different table - Export defaults to text file if no extension is entered

* v1.22 - Lookup fields for data grid - Only rewrites table if it has to after Save Table is clicked on table definition page - Returns data grid to top when viewing a new table - Option for EDB to not create a primary key when importing data from text files. Errors are logged in separate table. - EuGrid modifications: + Send EGW_EDITCELL notifications when DropDownLists are clicked to notify EDB when records change

* v1.21 - AUTONUMBER datatype now supported for EuSQL databases - A newly created table is now selected upon creation - Option to hide system tables - Checks to make sure there is an open db before doing anything with a form - Can import from an EDB file (EuSQL or EDS) - EuGrid modifications: + Modified eugrid.ew to allow tabbing from DropDownLists + EuGrid now passes EGW_EDITCELL notifications when backspace or delete keys are pressed - Import form only imports code for that form instead of entire project

* v1.2 - Deploy a database with forms as an application - Forms may contain menus (EDB will import a form's menus from the Win32Lib IDE) - Fixed bug with finding proper parent control when creating form - Edited changes to a form are saved when the database is closed - Tabbing from last cell in last row in data grid now creates new record - Selecting a table in the table page makes it the current table without having to click view.

* v1.11 - Edit table grid now uses DropDownLists for data type and old field to prevent data validation errors. This required modifications to EuGrid. - EuGrid modifications: + Added cell type EGW_DROPDOWN (Win32Lib DropDownList) + Fixed bug that didn't properly set Combo/DropdownList text when cell was clicked - Bug fixes to: + Export code + SQL Page: insert field button could cause an infinite loop if no field was selected when clicked

* v1.1 - Can connect to ODBC data sources to for viewing, editing and querying - Can export ODBC data to text or EDS - Minor bug fixes - Delete table now DROPs ODBC tables

* v1.0 - Saves current form before creating new form - Added Auto-Open feature for "AUTO" form

* 8/01/03 - Bug fixes - More controls supported - Changed the way SQL queries are updated within forms - Grid control can be bound to a field - All Win32Lib events added - Can import a form from the Win32Lib IDE - Using Win32Lib IDE properties control for forms

* 7/22/03 - EDB Converted to Win32Lib - User forms and event scripting

* 4/14/03 - First beta release


Table of Contents

Control Data


There are multiple ways to put data into a control in a form. Probably the most common method is to simply bind the form to a table, and bind individual controls to fields in the table. Then, as a user navigates through the underlying table, the controls will be updated with the data from the table, and any user edits will be saved to the table.

Controls such as Lists, Combos (including all the different variations, plus DropDownLists) and Grids offer a more complex way to present data. They can display a list of values for the user to select. As the user navigates the data, instead of changing the data in the control, the bound data could be highlighted to display the current record. There are two different ways to get a list of data into one of these controls.

Using a SQL statement

Using a list of Values

For List and Grid controls, you must specify the bound column if the control is bound to a field in the underlying table. A Combo type control always uses the first column as its bound column. You may also optionally specify column widths for a Grid control.



Supertopics:
  • Controls
  • User Forms

    Table of Contents

    Controls


    Any Win32Lib IDE supported control (with the exception of the emulated "Table" control) is supported by EDB, however, the only controls that currently may be bound to a field are: A list or a grid must also have its Bound Column property set. This identifies which column's data will actually be bound to the field. Columns in a list are simple tab stops, so columns might not always line up properly if the text is too long. If this is the case, you should probably use a grid. Also, there is no way to have built in column headers for a list.

    In a relational database, you may store data in only one table, even though it will be referred to by many different tables. For instance, in the Euphoria Recent Contributions database supplied with EDB, each contributor's name is stored only once (in the AUTHORS table), even though they may have submitted many records. In the ARCHIVE table, only the author's ID is stored. This saves space and reduces errors. However, this is not very useful for entering data, or looking at the raw table. A SQL statement is required to join the author data with the archive data:

      ex.
          SELECT AUTHORS.NAME, ARCHIVE.TITLE FROM AUTHORS INNER JOIN ARCHIVE
          ON AUTHORS.ID = ARCHIVE.NAME
    
    This statement returns a list of authors along with their contributions. If you import the 'archive' form into sql.edb from archive.prj, you can see how this can work in a form. Instead of an ID, the Grid lists each author and his or her ID from the AUTHORS table. A row in the grid will be highlighted corresponding to the author of the current record. To change the author of a contribution, simply click another row of the grid. Notice that the highlighted row changes as you navigate among records (using the navigation buttons at the bottom of the form).

    Note that you can't change the spelling of a name using this form. You would need to use the authors form (also included in archive.prj). In this way, you could change the spelling of the author's name in one place, and have the change take effect for all of the author's contributions.

    Take a look at the properties of the 'author' control. Of interest here are the 'Bound Field', 'Data Source', 'Data' and 'Bound Column' properties. EDB knows that it needs to evaluate a SQL statement because the Data Source is identified as SQL. The SQL statement is what fills the grid with data. The Bound Field tells EDB that it should highlight the appropriate row based on the field supplied, and that it should look at the specified column in the grid. This value will be used for the value of the field, so that a user can edit based on the author's name, but EDB can update the table based on the author's ID.

    You can specify the width of Grid control columns in pixels by entering them as a sequence. The columns will be resized each time the form is refreshed. The following entry in the Column Widths property field will set the first, second and third columns to 100, 50 and 30 pixels in width, respectively:

        {100, 50, 30}
    
    The sum of the columns' widths may be larger than the size of the control itself. If you do not specify column widths, EDB will evenly divide the columns into the visible grid space remaining. For instance, if you have four columns in a Grid that has a CX property set to 100, and you set the column widths to {50, 10}, EDB will the third and fourth columns to 10 pixels each (20 pixels are used by the control border and scrollbar, leaving 20 pixels to be divided evenly among the two remaining columns).

    You might have a control with data generated by a SQL statement that does not need to be updated each time a new record is shown. The grid example above is one such case. However, sometimes the data in the grid is dependent on the current record, and the SQL statement should be executed every time a new record is selected. Check the box for Auto Refresh in the control's properties, and the SQL will be re-evaluated at every record change. Alternatively, you can force a refresh of the entire form by calling refresh().



    Supertopics:
  • User Forms

    Subtopics:
  • Control Data

    Table of Contents

    Credits

    People who made this possble



    Table of Contents

    Deploying

    Distribute your database as an application


    Once you have created forms within a database, you can create a stand alone application based on your forms and database. This allows you to use EDB as a database app extension to the Win32Lib IDE.

    To deploy an application, select the Form->Deploy menu, and the deploy dialog will appear. Select a form to be opened at startup (it does not need to be named "AUTO") and the file name for the application.

    You also have the option of using a shrouded version of all the include files required. This allows you to ship your application as four files: a small startup file, the shrouded version of all the includes (about 370K), your .edb file and a copy of the Euphoria interpreter. If you opt to use the unshrouded version, you will need to ship all the include files required (unless you plan to shroud, bind or translate it yourself). The shrouded file includes win32lib.

    The executable part of a deployed app is really a slimmed down version of EDB. That only contains the form portion of the code. In fact, the only difference between two deployed apps is the target .edb file and the initial form to open. All form information and data are still contained within the database itself. This means that even after you have deployed a database, you can still edit it as normal using EDB.

    Please note that as of v1.21 of EDB, the shrouded include will not be included in the main download. You can get it from my site.


    Table of Contents

    Editing a database

    Changing the contents of a database


    EDB allows you to edit the contents of the database in the data grid. For EuSQL tables, you can also edit the definition of a table (creating, deleting and altering fields).

    To create a new table, enter the name of the table in the text box next to the "New" button on the Tables tab and click new. If you are viewing a EuSQL database, you will be taken to the table definition tab.

    To rename a table, select the table on the Tables tab, and enter the new name in the text box right of the Rename button. Then click on the Rename button, and the table name will be changed.

    To delete a table, select the table in the list box on the Tables tab, and click the Delete button. If an ODBC table is selected in the list, EDB will run a "DROP TABLE" query.

    To reindex a table in a EuSQL database, select the table in the list box on the Tables tab and click the Reindex button.

    EDB will also compact an EDS database, reclaiming any unused space in the file. As records are created, deleted and edited, it is normal for a database to accumulate chunks of unused data. This is not unlike a fragmented hard drive. Select the Database->Compact menu to compact the open database.

    You may not normally edit either INDEXDEF or TABLEDEF tables in a EuSQL database. This is to prevent corruption of the database, as EuSQL relies on these tables for its operation. If, for some reason, something (possibly even EuSQL itself :) has damaged the database, you can edit a EuSQL database just like any other EDS database by selecting the Database->View as EDS menu. When this menu item is checked, EDB regards EuSQL databases as normal EDS databases. This option should be used with caution.



    Subtopics:
  • Editing a table's definition
  • Editing Records

    Table of Contents

    Editing a table's definition


    This is only possible with an EuSQL database, since a normal EDS file doesn't really have a definition for the data in a table. You can run SQL statements on an ODBC database to change a table in an ODBC database (i.e., CREATE, ALTER, DROP queries).

    To edit a table's definition, click on the Table Definition tab. In the grid there, you will see a list of the fields in the selected table. Each field has a name, datatype, possibly an index, indicator as to whether it is part of the key, the "Old Field" indicator and a checkbox indicating whether the field has a lookup defined for it when the table is displayed in the data grid.

    To add a new field, simply enter its specification immediately below the existing fields. To delete a field, you can select the field, and repeatedly click the "Move Down" button until there is a blank row between the field to be deleted and the rest of the fields. A description of each of the columns follows:

    You can click the "Check Table" button to see whether the definition has any errors. EDB will ensure that all datatypes are correct, and will warn you whether any fields will be deleted. This will not ensure that you have a viable primary key, however. That can only be done by clicking the "Save Table" button. The same checks are done to ensure that proper datatypes are used, and if any errors are present, the changes will not be saved. If there are any warnings, you will be prompted to continue. Once you confirm the changes, they will be saved, unless there is a problem with the primary key. If you change the fields that make up the primary key (by deleting one or more, or switching fields altogether), EDB will not make any changes to the table. All changes are first done in a temporary database, so no data or structure will be lost.

    Hitting the "Cancel" button reverts the table definition to the last saved definition before any editing.

    After you save, some fields may end up in a different order. This is because all subfields must be grouped together. There is no effect on queries. In order to change a table, EDB writes the new table to a temporary file, and then copies the data back to your database. This can be a very time consuming process for a large database, however, if no fields are added or deleted, and the primary key fields are the same, EDB will not rewrite the table. Name changes, changes to indices or datatypes and changes to lookup definitions are done by modifying TABLEDEF, INDEXDEF and LOOKUPDEF as appropriate.



    Supertopics:
  • Editing a database

    Subtopics:
  • Lookup Fields

    Table of Contents

    Editing Records


    You must have a table loaded into the data grid, and be viewing the Data tab. Click on any cell to edit the data there. When you are finished editing a field, hit TAB to move one field to the right, or arrow keys to move to a different row. When you move to a different record, EDB will update the record.

    Only as many columns are shown as are needed to display the data. If, in a normal EDS database, you need another column, click on the Add Field button. In a EuSQL table, you must edit the table definition by adding a field on the Table Definition tab.

    Clicking the Delete button will delete the record wherever the cursor is in the grid. Clicking the New Record button will add a row to the table, enabling you to add a record.

    Empty fields are truncated when editing a normal EDS database, so no space is wasted when one record is longer than another.



    Supertopics:
  • Editing a database
  • Viewing a table

    Subtopics:
  • Lookup Fields

    Table of Contents

    EuSQL databases

    A powerful addition to EDS


    Please see the EuSQL documentation, which should have come with EDB.


    Table of Contents

    Events


    You can customize event handling in your forms using euscript. To catch an event, you define a procedure whose name is the control or form's name combined with the name of the event (see below for valid events). For instance, to create an event handler for when button1 is clicked:
      procedure button1_onClick( integer self, atom event, sequence params )
          atom ok
          ok = message_box( "You clicked button1", "Click Event", MB_OK )
      end procedure
    
    Events should take three parameters, an integer, an atom and a sequence. These are the same as a normal Win32Lib event handler. Some events currently pass zero ( BeforeUpdate and AfterUpdate), for the event parameter, since they are generated by EDB, not the operating system.

    Valid Events

    All Win32Lib w32Hxxx events are valid. To set up an event handler, the form of the procedure should be the control name, underscore, and the name of the event, minus the "w32H":
       procedure aButton_onClick( integer self, atom event, sequence params )
       procedure aForm_onActivate( integer self, atom event, sequence params )
          etc...
    
    In addition to Win32Lib events, EDB adds the AfterUpdate and BeforeUpdate events, which are generated by EDB when a form is updated by moving to a new record, or when the form is refreshed.

  • proc control_onClick( integer self, atom event, sequence params )   
  • proc form_onActivate( integer self, atom event, sequence params )   
  • proc form_onAfterUpdate( integer self, atom event, sequence params )   
  • proc form_onBeforeUpdate( integer self, atom event, sequence params )   
  • proc form_onClose( integer self, atom event, sequence params )   

    Supertopics:
  • User Forms

    Subtopics:
  • Scripting

    Table of Contents

    Future Versions

    What's in store for EDB


    These are the things that I'd like to add to EDB, not necessarily in any priority, although I usually push User Suggestions to the top of the list, so if there's anything that you find missing from EDB, or if you'd like to see something already on this list, please let me know (the more detail you supply, the better, or you'll just have to be happy with what I come up with :-).


    Table of Contents

    Importing a Form


    You can import a form from the Win32Lib IDE (v0.17 or higher). It will import all the controls. Additionally, you can write your event code in the IDE, and set certain control properties.

    To set properties from the IDE (aside from positioning or parent properties), you can use the Win32Lib procedure setUserProperty(). The properties that may be set using code are:

     ex:
        -- bind MYTABLE to myForm
        setUserProperty( myForm, "Bound Table", "MYTABLE" )
    

    -- bind MYTABLE.NAME to nameList setUserProperty( nameList, "Bound", "NAME" )

    -- set the data source type to SQL for nameList setUserProperty( nameList, "Data Source Type", "SQL" )

    -- set the SQL statement for nameList setUserProperty( nameList, "Data", "SELECT NAME, ID FROM CUSTOMERS ORDER BY NAME" )

    -- set the bound column to column 1 for nameList setUserProperty( nameList, "Bound Col", "1" )

    You can also write your event code from the IDE, however be careful to use only those routines that are available. There is no way to create a grid from the IDE and import it to EDB, however, you can create the control as a list and change the control type to Grid after importing the form to EDB.

    Take a look at the sample project (archive.prj) that comes with EDB. You can import the forms into the sample database (sql.edb). The AUTO form has buttons that will open the other forms when clicked. Each form can be used to edit and view data in one of the tables in the database.

    If you import a form with the same name as an existing form, a suffix will be attached, since all forms must have unique names. This may cause problems if you have event handlers for the form itself, or refer to the form in any of your scripted code. These instances must be manually changed to reflect the correct form name.



    Supertopics:
  • User Forms

    Table of Contents

    Importing and Exporting Data

    Using text files and other EDS databases


    EDB allows you to import data from a tab delimited text file and to export an EDS table or a EuSQL table or query result to a tab delimited file or another EDS database.

    Exporting



    Importing
    Once a table is either imported or exported into a database, you can change the name, definition and data of the table just like any other table.


    Table of Contents

    Known Bugs

    Why aren't these fixed?!


    I have supplied patched versions of xControls.ew and Geometry.ew You can see the changes in the source, as they have been marked by my initials, mwl. There were either bugs or missing features that EDB needed in order to work.

    If you receive an error like "db_rename_table undefined" it means that you don't have the latest version of database.e. EDB requires the version of EDB that is included with Euphoria v2.4. This version is 100% compatible with the version included with Euphoria v2.3, but has some additional functions and optimizations.

    Also supplied is EuGrid130.zip (v1.3). If you do not have EuGrid v1.3 installed in a EUINC directory, you will need to unzip EuGrid130.zip into either a EUINC folder, or directly into your EDB directory.

    Under Win32Lib v0.60.4, TreeViews may not display properly, due to some changes made to handle manifests. On about line 5950, change the assignment back to COLOR_WINDOW:

        classBGColor [TreeView] = COLOR_WINDOW -- w32NoColor
    
    This will prevent the lines in the TreeView from having a greyed out background.


    Table of Contents

    Lookup Fields


    You can set up fields in your EuSQL database to be either a DropDownList or a Combo when editing the table in the data grid. There are a couple of reasons why this is useful.

    You might want to restrict data to certain values. For instance, if the field is really a foreign key that holds the id for records in another table, you could restrict the values that can be entered into the field to only existing keys. For instance, if the field refers to a person defined in another table, you could store the id of the person in the current table, but have the user select the name of the person from a list, and let EDB find the corresponding id. In fact, you might only display the name in the data grid, rather than the id, even though EDB will actually store the id in your table. It could also make data entry easier, requiring the user to select from a list as an option to typing data into the grid.

    There are three main ways to generate the data for a list. You can supply a SQL statement, identify a table and field, or supply a list of values. When you identify a table and field, the data stored in your table will always be the data that is displayed. Using a SQL statement or a list of values allows you to display one value, but store another.

    To create a lookup field, check the box for the field in the Lookup column on the Table Definition page. To edit an existing lookup, click on the check box twice (the first will uncheck it, and the second will re-check it). This will pop open the Edit Lookup dialog window. On the left of the dialog, you can select among the three methods for defining your lookup. If you select either SQL or Values, you will be able to enter your SQL statement or list of values in the edit box to the right. If you supply more than one column, you can specify which column will be the bound column. This is the column that will actually be stored in your table. So for the SQL statement:

        SELECT NAME, ID FROM PERSON ORDER BY NAME
    
    You will have two columns, NAME and ID. If you only want to store the ID, you would set the bound column to 2. Only the first column will be displayed in the list and data grid when editing the table. You may supply multiple columns or just one in a list of values. For just one column, enter the values, separated by commas. Strings must be enclosed in double quotes:
       "Me", "You", 5, 13.6
    
    To supply multiple columns, supply the rows in sequence braces:
       {"Me", 1}, {"You", 2}
    
    If you select Field for your data, you must select a table and a field from that table in the DropDownLists to the right. You do not need to specify a bound column, since there will only be one column.

    Finally, you must decide whether to restrict the values to the values provided in the list. If you have a bound column other than the first, then the values will be automatically restricted. You should be careful if you are creating a lookup field for a table with existing data. If you edit a record with a field that is restricted to the list values, and the existing value doesn't exist in the list, that field will then contain a null value. Restricted fields will be edited as a DropDownList in the data grid, while non-restricted fields will be ComboBoxes (i.e., they will have a drop down list and an edit box for changing the value of the field).

    The lists are only generated when the table is first loaded into the data grid. In order to refresh the lists, you need to re-open the table in the data grid. This could happen because you ran a SQL query that changed some things, or possibly changed some data in a form.

    When you create a lookup on a field, EDB will create a new system table (if it hasn't already) called "LOOOKUPDEF".

    The demo database, sql.edb has two lookups defined for the ARCHIVE table. One is for the AUTHOR field, and the other is for the MONTH field. The data grid displays the name of the author and the name of the month, but stores each as an id.



    Supertopics:
  • Editing a table's definition
  • Editing Records

    Table of Contents

    ODBC and EDB

    Connecting to other databases


    You can connect to any ODBC data source using EDB. You must have a EuSQL database open in EDB before connecting. To connect to a new database, select the Database->Connect menu option. You will be prompted to enter the connection information regarding the data source to be connected. This information is stored in a table called ODBCDEF, and you will be automatically connected to all ODBC data sources stored in this database when you open the EuSQL database.

    ODBC tables are listed in the table list with "ODBC:[db-name]:" prefixed to the table. You can view and edit these tables like a normal EDS table, except that you cannot dynamically sort the data by columns.

    If you only want to use EDB to work with an ODBC database, you will need to create an empty EuSQL database before connecting. Then, any ODBC data sources to which you connect will automatically be reconnected whenever you open the EuSQL database.


    Table of Contents

    Queries

    Running SQL commands


    This is only available when a EuSQL database is opened. Queries are built and executed on the SQL tab. There are three main controls on the tab: You may use the list of words and tables to automatically insert text into a SQL command, either by double clicking on the list, hitting enter to insert the selected field, or using the Insert buttons below each list.

    Clicking the Parse button will cause EDB to attemp to parse the query, reporting any errors. All whitespace is ignored by EuSQL. Clicking the Run button causes EuSQL to parse and execute the query, displaying the results on the Data tab. Note that any editing done is ignored when viewing the results of a query.

    To run a query on an ODBC data source, enter the text of the SQL command in the edit box. Select the ODBC data source from the drop down list at the bottom of the page. You can click the Parse Remote button to prepare the SQL command, however, an ODBC driver may not indicate errors at this stage. You may need to actually run the query to detect errors. Run a query on an ODBC data source by clicking the Run Remote button. The data will be displayed in the data page like a normal EuSQL query.

    Right of the table and field tree is a list of recently run SQL commands. Either double clicking on an item or selecting an item and clicking the Insert button will replace the curent SQL command with the selected command. These are remembered across sessions.


    Table of Contents

    Scripting


    You can use euscript to customize your forms. The code is entered into the text box in the lower right corner of the forms page. Most Euphoria commands are supported, however input/output, and machine level access commands are not supported.

    Selected routines from EuSQL, Win32Lib, EuGrid and xControls are supported, however, so you will be able to manipulate your forms. Some specialized EDB commands are also available.

    Constants will be automatically defined for each control and your form. There will be a constant with the same name as each control that will contain the Win32Lib id for the control. Assuming that you have myButton defined in form myForm, you could assume that the following constants are already declared:

      constant myForm = 1234
      constant myButton = 1235,
    
    Then, to move to the next record, you could use:
      procedure myButton_onClick( integer self, atom event, sequence params )
          next_record( myForm )
      end procedure
    

    You may specify a form to open automatically when the database is opened by EDB by naming the form "AUTO" (case-sensitive).

  • proc change_current_record( sequence form, object key )   
  • proc close_form( sequence form )   
  • proc EuGrid   Routines from EuGrid
  • proc EuSQL   Routines from EuSQL
  • func field_value( atom form, sequence field )   
  • proc Miscellaneous   Routines from various include files
  • proc next_record( sequence form )   
  • proc open_form( sequence form )   
  • proc prev_record( sequence form )   
  • proc refresh( atom form )   
  • proc set_value( integer form, integer control, object value )   
  • proc veto( integer self, atom event )   
  • proc Win32Lib   Routines from Win32Lib
  • proc xControls   Routines from xControls
  • proc [deleted]   Built-in euphoria routines that have been deleted

    Supertopics:
  • Events
  • User Forms

    Table of Contents

    User Forms

    Adding an interface to your data


    Forms provide a more user friendly way to enter and view data than a data grid. You can create and edit forms and controls from the form page. Additionally, you can import forms from the Win32Lib IDE v0.18.x. To import a form, open a saved IDE project file (*.prj). If there are multiple windows defined, you will be prompted to select one.

    A form is just a window that is (usually) 'bound' to a table in your database. It gives you a more user-friendly way to work with data than a simple data grid. Controls in the form may be bound to fields in the table. A form has an inherent notion of its place in the table. It will automatically display the data for the current record, will update the database when changes are made, and will allow the user to navigate among the records in a table. A form can be as simple as a series of EditText boxes, or can have grids, lists and combo boxes that respond and react to user input.

    When a form is bound to a table, some additional controls are automatically placed at the bottom of the form. These can be used for record navigation. A single 'arrow' moves one record backward or forward, and a double arrow moves to either the first or the last record. The edit box will always display the current record number. You can enter a record number directly into the box to jump to a specific record. The 'X' will delete a record. The arrow with an asterisk creates a new record. Note that this record is created as the last record number, but it may not end up there, since EDS records are based on the sorted list of record keys. Any time a key changes, the record number for a particular record can also change, and therefore its record number with respect to form navigation.

    The values of the current record in the form are easily available to you for SQL statements in the form of parameters. Parameter names are formed by prepending the name of the form followed by an underscore to the name of the field in the bound table.

     ex:
         [myForm_SOME_FIELD]: references the field "SOME_FIELD" in the current record
    



    Subtopics:
  • Control Data
  • Controls
  • Events
  • Importing a Form
  • Scripting

    Table of Contents

    Viewing a database

    Examining the contents of a database


    The database must first be opened using the File->Open command, or after creating the database using the File->Create command. The list of tables is shown in the list box on the Tables tab. When a table is selected, some statistics are displayed regarding the table. First, the number of records is displayed below the buttons on the tab. Also, if the database is a EuSQL database, the number of indices defined for that table is shown.

    It is possible to view a EuSQL database like a normal EDS database by checking the Database->View as EDS menu item. This should be done with caution, however, as you could corrupt the database. Uncheck the menu to return to normal viewing. This setting is saved for future sessions.

    You may hide all system tables while in EuSQL mode by selecting the \b "Database->Hide System Tables" menu. When this item is checked, TABLEDEF, INDEXDEF, EDBFORMDEF and ODBCDEF tables will not appear in any table selection lists (they will remain in the field tree on the SQL page, however). Note that EDBFORMDEF and ODBCDEF are EDB specific system tables, while TABLEDEF and INDEXDEF are common to all EuSQL databases.



    Subtopics:
  • Viewing a table

    Table of Contents

    Viewing a table


    There are several ways to view the contents of a table. From the Tables tab, either double click on a table name in the list, or select a table name and click the View button.

    From the Data tab or the Table Definition tab, select the table to view from the combo box.

    EDB will start loading the records, and will display the Data tab. Records are loaded in default batches of 1000, until all of the records have been entered into the grid. When all records have been loaded, the columns will adjust to the minimum size required to display all data, although some may not be visible on the screen without scrolling. You may change the batch size by entering a number in the box next to the view button. This value will be saved for future sessions.

    For a regular EDS database, the first cell in any row will be the key value (EuSQL tables may have more than one field defined as the primary key). The following columns will be the contents of each record. If you created a record where the record data was a string of text, then it will be displayed and stored that way in an EDS database. EuSQL data will be displayed according to the defined fields for each table.

    Escape Characters

    It is important to note that escape characters will be displayed the same way that you would need to type them into a string in Euphoria. In other words, a tab (9) would look like "\t" in the grid.



    Supertopics:
  • Viewing a database

    Subtopics:
  • Editing Records

    Table of Contents

    [proc]
    change_current_record
    ( sequence form, object key )

    Category: Scripting

    Change the current record in the form form to the record with the key = key.

    See Also: close_form, EuGrid, EuSQL, field_value, Miscellaneous, next_record, open_form, prev_record, refresh, set_value, veto, Win32Lib, xControls, [deleted]


    Table of Contents

    [proc]
    close_form
    ( sequence form )

    Category: Scripting

    Closes the specified form.

    See Also: change_current_record, EuGrid, EuSQL, field_value, Miscellaneous, next_record, open_form, prev_record, refresh, set_value, veto, Win32Lib, xControls, [deleted]


    Table of Contents

    [proc]
    control_onClick
    ( integer self, atom event, sequence params )

    Category: Events

    Called when a control in a form is clicked.

    See Also: form_onActivate, form_onAfterUpdate, form_onBeforeUpdate, form_onClose


    Table of Contents

    [proc]
    EuGrid

    Routines from EuGrid

    Category: Scripting

    These routines from EuGrid may be used within a form's script. Please refer to the EuGrid documentation for full descriptions of the routines, however, you should always refer to the column index (as counted from left to right in the grid) rather than the column id (which can normally be retrieved from the EuGrid function EGW_EnumColumns). EDB will automatically convert the value for those functions that require it.

    See Also: change_current_record, close_form, EuSQL, field_value, Miscellaneous, next_record, open_form, prev_record, refresh, set_value, veto, Win32Lib, xControls, [deleted]


    Table of Contents

    [proc]
    EuSQL

    Routines from EuSQL

    Category: Scripting

    These routines from EuSQL may be used within a form's script. Please refer to the EuSQL documentation for full descriptions of the routines.

    See Also: change_current_record, close_form, EuGrid, field_value, Miscellaneous, next_record, open_form, prev_record, refresh, set_value, veto, Win32Lib, xControls, [deleted]


    Table of Contents

    [func]
    field_value
    ( atom form, sequence field )

    Category: Scripting

    Get the value of the field field of the current record in form form.

    See Also: change_current_record, close_form, EuGrid, EuSQL, Miscellaneous, next_record, open_form, prev_record, refresh, set_value, veto, Win32Lib, xControls, [deleted]


    Table of Contents

    [proc]
    form_onActivate
    ( integer self, atom event, sequence params )

    Category: Events

    Called when the form is opened, before any data is processed.

    See Also: control_onClick, form_onAfterUpdate, form_onBeforeUpdate, form_onClose


    Table of Contents

    [proc]
    form_onAfterUpdate
    ( integer self, atom event, sequence params )

    Category: Events

    This event is called after all bound fields have been updated when the user navigates to a new record.

    See Also: control_onClick, form_onActivate, form_onBeforeUpdate, form_onClose


    Table of Contents

    [proc]
    form_onBeforeUpdate
    ( integer self, atom event, sequence params )

    Category: Events

    This event is called before all bound fields have been updated when the user navigates to a new record.

    See Also: control_onClick, form_onActivate, form_onAfterUpdate, form_onClose


    Table of Contents

    [proc]
    form_onClose
    ( integer self, atom event, sequence params )

    Category: Events

    Called when the form is closed. To prevent the form from actually closing, you can use the veto procedure:

      procedure myForm_onClose( integer self, atom event, sequence params )
          integer void
          if not_done then
              void = message_box( "We're not done yet!", "Error", MB_ICONERROR )
              veto( self, event )
          end if
    

    end procedure

    See Also: control_onClick, form_onActivate, form_onAfterUpdate, form_onBeforeUpdate


    Table of Contents

    [proc]
    Miscellaneous

    Routines from various include files

    Category: Scripting

    Routines

    These routines come from various files included by EDB, and may be used from within a form's script.

    See Also: change_current_record, close_form, EuGrid, EuSQL, field_value, next_record, open_form, prev_record, refresh, set_value, veto, Win32Lib, xControls, [deleted]


    Table of Contents

    []
    Miscellaneous Notes

    Category: Miscellaneous Notes


    Table of Contents

    [proc]
    next_record
    ( sequence form )

    Category: Scripting

    See Also: change_current_record, close_form, EuGrid, EuSQL, field_value, Miscellaneous, open_form, prev_record, refresh, set_value, veto, Win32Lib, xControls, [deleted]


    Table of Contents

    [proc]
    open_form
    ( sequence form )

    Category: Scripting

    Opens another form.

    See Also: change_current_record, close_form, EuGrid, EuSQL, field_value, Miscellaneous, next_record, prev_record, refresh, set_value, veto, Win32Lib, xControls, [deleted]


    Table of Contents

    [proc]
    prev_record
    ( sequence form )

    Category: Scripting

    Moves to the previous record in the form.

    See Also: change_current_record, close_form, EuGrid, EuSQL, field_value, Miscellaneous, next_record, open_form, refresh, set_value, veto, Win32Lib, xControls, [deleted]


    Table of Contents

    [proc]
    refresh
    ( atom form )

    Category: Scripting

    Causes the data in the form to refresh.

    See Also: change_current_record, close_form, EuGrid, EuSQL, field_value, Miscellaneous, next_record, open_form, prev_record, set_value, veto, Win32Lib, xControls, [deleted]


    Table of Contents

    [proc]
    set_value
    ( integer form, integer control, object value )

    Category: Scripting

    Set the value of a control in the form.

    See Also: change_current_record, close_form, EuGrid, EuSQL, field_value, Miscellaneous, next_record, open_form, prev_record, refresh, veto, Win32Lib, xControls, [deleted]


    Table of Contents

    [proc]
    veto
    ( integer self, atom event )

    Category: Scripting

    Used to cancel some action. To prevent a form from closing, for instance:

      procedure myForm_onClose( integer self, atom event, sequence params )
          integer void
          if not_done then
              void = message_box( "We're not done yet!", "Error", MB_ICONERROR )
              veto( self, event )
          end if
    

    end procedure

    If the form is closed by a user or a call to close_form, calling veto() will cancel the close action.

    See Also: change_current_record, close_form, EuGrid, EuSQL, field_value, Miscellaneous, next_record, open_form, prev_record, refresh, set_value, Win32Lib, xControls, [deleted]


    Table of Contents

    [proc]
    Win32Lib

    Routines from Win32Lib

    Category: Scripting

    These routines from Win32Lib may be used within a form's script. Please refer to the Win32Lib documentation for full descriptions of the routines.