GRIDPLUS2 - Using GRIDPLUS With (TDBC) SQL Databases
Home Reference Manpage Examples Download License Contact

Accessing a TDBC/SQL database

GRIDPLUS provides the gpdb command to simplify creating applications which access a TDBC based SQL database.

GRIDPLUS item values can be mapped to/from SQL queries. SQL query results can also be mapped to a Tcl dict or list.

Syntax

gridplus::gpdb window query ?tablelist|prefix|dict|=?
gridplus::gpdb window query foreach body ?prefix?

window
Name of default window/grid used for GRIDPLUS value mapping.

Note: Not all queries need to use window for value mapping. Were this is the case it is recommended that the root window (".") be specified.

query
SQL query to be run.

Where the last, optional, parameter is specified the following checks are made to identify the type:-

When the foreach option is specified, the body script is evaluated for each row returned. The column values for each row returned will be put into GRIDPLUS (null named array) items indexed by the column name prefixed with the specified prefix. It is recommended that, when foreach is used, that a prefix be specified. See this Example.

Mapping GRIDPLUS items to/from SQL queries

Mapping GRIDPLUS items to SQL queries

The SQL code may contain bound variables, which are GRIDPLUS item identifiers, enclosed in parenthesis, prefixed with a colon ":".

For Example:

:(firstname)

The GRIDPLUS item identifer is used in combination with the specified window to define the (full) name of the GRIDPLUS item.

Window Formats:

window
Default window/grid for GRIDPLUS items.

window,
Default window/grid prefix for GRIDPLUS items.

Item Identifier Formats:

:(item)
Value of GRIDPLUS item called window,item (Use with "window,").

:(,item)
Value of GRIDPLUS item in window which has "basename" item (Use with "window").

Note: If there is more than one item in window with "basename" item an "Ambiguous item" error is generated.

:(.item)
Value of GRIDPLUS item called .item.

:(.item:column)
Value of column with name column of GRIDPLUS tablelist item called .item.

If item contains "@"
Value of GRIDPLUS item called item.

Item Identifier Wildcard Option:

To simplify use of the SQL "%" wildcard when using the "LIKE" operator, the item identifier can optionally have a "%" prefix and/or suffix.

Note: This can be used with any of the above item formats.

Examples:-

%:(item)
:(item)%
%:(item)%

Mapping SQL Query Results to GRIDPLUS items

The column name of the SQL query result is used in combination with the specified window to define the (full) name of the GRIDPLUS item. The full name of the GRIDPLUS item to which a result column is matched can also be specified using the "@(item)" syntax in an SQL "SELECT" where "AS" is valid to specify the name of a returned column.

Examples:-

Window Format: window
If the value of window is ".mywindow" and the returned column is "mycolumn1", the returned value for the column will be mapped to the GRIDPLUS item in ".mywindow" with "basename" "mycolumn1".

Note: If there is more than one item in".mywindow" with "basename" "mycolumn1" an "Ambiguous item" error is generated.

Window Format: window,
If the value of window is ".mywindow," and the returned column is "mycolumn1", the returned value for the column will be mapped to the GRIDPLUS item called ".mywindow,mycolumn1".

Using "@(item)" Syntax
"@(.mytoplevel.mygrid,myitem)" will map the value of a returned column to the GRIDPLUS item ".mytoplevel.mygrid,myitem".

Error Handling

By default if an error occurs an error is generated using the Tcl error command.

An alternative error handling procedure can be specified using the "*Gridplus.sqlErrorProc" option database option. One parameter, containing the SQL error text, is passed to the procedure.

For Example:

gpoptions sqlErrorProc mySqlErrorHandler

In this case the "mySqlErrorHandler" procedure is invoked when an error occurs.

Examples

This section contains examples which illustrate the main GRIDPLUS gpdb command features.

The following examples assume that the reader is familiar with SQL syntax and the contents of the GRIDPLUS Grid/Layout, Entry/Widget, Tablelist and Text. Information given on those pages will not be duplicated here.

The comments below concentrate on the GRIDPLUS gpdb command.

Note: In addition to the examples below, GRIDPLUS Examples 5 and 6 also make use of the gpdb command.

GPDB Example 1

A simple contact database query/display application demonstrating basic SQL/GRIDPLUS mapping.

The application window is divided into three sections:-

Window:

When the application is started...


Press the down cursor key -or- select/click the second row...


Enter "sm" into the Last name field then press Execute...

Source Code:

#==============#
# Main display #
#==============#

proc mainDisplay {} {

   gridplus entry .query -stretch 0 -title "Query" {
     {"^First name " .firstname:ew}
     {"^Last name "  .lastname:ew}
     {"^Category "   || |(&r *category) . "Business" -business |: . "Personal" -personal |: . "All" +%}
     =
     {|&b "Execute" .execute |> "Clear" .clear}
   }

   gridplus tablelist .contactlist \
      -action       single         \
      -command      displayContact \
      -tableoptions stripe         \
      -scroll       y              \
      -title        "Contact List" {
      0 id    hide
      0 Name
      0 Phone
   }

   gridplus text .contact,notes -height 3 -state disabled -scroll y

   gridplus entry .contact -state disabled -title "Contact" {
     {"^First name " .firstname:ew}
     {"^Last name "  .lastname:ew}
     {"^Phone"       .phone 20}
     {"^Category"    || |(&r *category <) . "Business" -business |: . "Personal" -personal}
     {"^Current "    &c .current}
     {"^Notes:nw"    @contact,notes}
   }

   gridplus layout .main -wtitle "Example" {
      .query:ew
      .contactlist:ew
      .contact:ew
   }

   pack .main
}

#===============#
# Execute query #
#===============#

proc query,execute {} {

   gpdb exampledb .query, {
      select id,
             firstname || ' ' || lastname,
             phone
        from contacts
       where firstname like %:(firstname)%
         and lastname  like %:(lastname)%
         and category  like  :(category)
   } .contactlist
   
   gpselect .contactlist -first -focus 
}

#=============#
# Clear query #
#=============#

proc query,clear {} {

   gpset {
      .query,firstname {}
      .query,lastname  {}
      .query,category  %
   }
}

#=================#
# Display contact #
#=================#

proc displayContact {} {

   gpdb exampledb .contact, {
      select firstname,
             lastname,
             phone,
             category,
             current,
             notes
        from contacts
       where id = :(.contactlist:id)
   }
}

#=======================#
# Start The Application #
#=======================#

package require gridplus 2.11
namespace import -force gridplus::*

# Require TDBC with Sqlite3 driver.
package require tdbc::sqlite3

# Open database
tdbc::sqlite3::connection create exampledb "C:/GRIDPLUS/gridplus211/gpdb-example.db"

# Start
mainDisplay

after idle query,execute


Comments:

proc mainDisplay {} {

   gridplus entry .query -stretch 0 -title "Query" {
     {"^First name " .firstname:ew}
     {"^Last name "  .lastname:ew}
     {"^Category "   || |(&r *category) . "Business" -business |: . "Personal" -personal |: . "All" +%}
     =
     {|&b "Execute" .execute |> "Clear" .clear}
   }

   gridplus tablelist .contactlist \
      -action       single         \
      -command      displayContact \
      -tableoptions stripe         \
      -scroll       y              \
      -title        "Contact List" {
      0 id    hide
      0 Name
      0 Phone
   }

   gridplus text .contact,notes -height 3 -state disabled -scroll y

   gridplus entry .contact -state disabled -title "Contact" {
     {"^First name " .firstname:ew}
     {"^Last name "  .lastname:ew}
     {"^Phone"       .phone 20}
     {"^Category"    || |(&r *category <) . "Business" -business |: . "Personal" -personal}
     {"^Current "    &c .current}
     {"^Notes:nw"    @contact,notes}
   }

   gridplus layout .main -wtitle "Example" {
      .query:ew
      .contactlist:ew
      .contact:ew
   }

   pack .main
}

Creates a procedure called "mainDisplay" to display the application window content.



proc query,execute {} {

Creates a procedure called "query,execute" to execute the specified query.

   gpdb exampledb .query, {
      select id,
             firstname || ' ' || lastname,
             phone
        from contacts
       where firstname like %:(firstname)%
         and lastname  like %:(lastname)%
         and category  like  :(category)
   } .contactlist

Query to populate the ".contactlist" tablelist.

The query window is set to ".query,". As the window name ends with a comma this is used as a default prefix to define the full names of the GRIDPLUS items for the purpose of item/variable/result mapping.

For Example: ":(firstname)" will be replaced by the value of the ".query,firstname" entry.

The "firstname" and "lastname" item identifiers use the "%" prefix/suffix option. In this case a "%" character will be added to the begining and end of the GRIDPLUS item values.

   gpselect .contactlist -first -focus
}

Select the first ".contactlist" row. This command also gives focus to the tablelist so that the up/down cursor keys can be used to select the contacts.



proc query,clear {} {

Creates a procedure called "query,clear" to set the query to default.

   gpset {
      .query,firstname {}
      .query,lastname  {}
      .query,category  %
   }
}

Set the default query (select all contacts).


proc displayContact {} {

Creates a procedure called "displayContact" to display the contact details.

   gpdb exampledb .contact, {
      select firstname,
             lastname,
             phone,
             category,
             current,
             notes
        from contacts
       where id = :(.contactlist:id)
   }
}

Query to populate the Contact section of the display with details of the selected contact.

The query window is set to ".contact,". As the window name ends with a comma this is used as a default prefix to define the full names of the GRIDPLUS items for the purpose of item/variable/result mapping.

For Example: The value of the result "firstname" column will be used to set the value of the ".contact,firstname" entry.

The query selects the details for the contact "where id = :(.contactlist:id)". As the name of the item identifier begins with "." it must specify the full name of the GRIDPLUS item. In this case ".contactlist". The ":id" suffix specifies that ":(.contactlist:id)" will be replaced with the value of the "id" column of the selected row in the ".contactlist" tablelist.



package require gridplus 2.11
namespace import -force gridplus::*

# Require TDBC with Sqlite3 driver.
package require tdbc::sqlite3

# Open database
tdbc::sqlite3::connection create exampledb "C:/GRIDPLUS/gridplus211/gpdb-example.db"

# Start
mainDisplay

after idle query,execute

Start the application. The comments in the source code for this part of the application provide a suitable description of its function.

GPDB Example 2

An example demonstrating several item/variable and result/item mapping methods using multiple (toplevel) windows.

Window:

When the application is started...


Enter "john" into the Firstname field, "smith" into the Lastname field then press Execute...

Source Code:

#==============#
# Main display #
#==============#

proc mainDisplay {} {

   #--------------------------
   # Set default widget width.

   gpoptions entryWidth 25


   #----------------------------
   # Create root window content.

   gridplus entry .query -stretch 0 -title "Query" {
      {   "Firstname" .firstname}
      {   "Lastname"  .lastname}
      =
      {&b "Execute"   .execute:ew}
   }

   gridplus entry .result1 -stretch 0 -title "Result 1" {
      {   "Name"    .name:ew}
      {   "Phone"   .phone:ew}
      {&c "Current" .current}
   }

   gridplus layout .main -wtitle "Root" {
      .query
      .result1:ew
   }

   pack .main


   #------------------------------
   # Create toplevel1 and content.

   gridplus window .toplevel1 -wtitle "Toplevel1"

   gridplus entry .toplevel1.result2a -title "Result 2A" {
      {"Firstname" .firstname}
      {"Lastname"  .lastname}
   }
   
   gridplus entry .toplevel1.dummy -title "Dummy" {
      {"Firstname" .firstname =Dummy}
   }

   gridplus layout .toplevel1.main {
      .toplevel1.result2a
      .toplevel1.dummy
   }

   pack .toplevel1.main


   #------------------------------
   # Create toplevel2 and content.

   gridplus window .toplevel2 -wtitle "Toplevel2"

   gridplus entry .toplevel2.result2b -title "Result 2B" {
      {   "Name"    .name}
      {   "Phone"   .phone}
      {&c "Current" .current}
   }

   pack .toplevel2.result2b
   
}

#===============#
# Execute query #
#===============#

proc query,execute {} {

   #--------------------
   # Query for result 1.
   
   gpdb exampledb . {
      select firstname || ' ' || lastname as name,
             phone,
             current
        from contacts
       where firstname = :(,firstname)
         and lastname  = :(,lastname)
   }
  
   #--------------------
   # Query for result 2.
      
   gpdb exampledb .toplevel1.result2a, {
      select firstname,
             lastname,
             firstname || ' ' || lastname @(.toplevel2.result2b,name),
             phone                        @(.toplevel2.result2b,phone),
             current                      @(.toplevel2.result2b,current)
        from contacts
       where firstname = :(.query,firstname)
         and lastname  = :(.query,lastname)
   }
   
}

#=======================#
# Start The Application #
#=======================#

package require gridplus 2.11
namespace import -force gridplus::*

# Require TDBC with Sqlite3 driver.
package require tdbc::sqlite3

# Open database
tdbc::sqlite3::connection create exampledb "C:/GRIDPLUS/gridplus211/gpdb-example.db"

# Start
mainDisplay


Comments:

proc mainDisplay {} {

   #--------------------------
   # Set default widget width.

   gpoptions entryWidth 25


   #----------------------------
   # Create root window content.

   gridplus entry .query -stretch 0 -title "Query" {
      {   "Firstname" .firstname}
      {   "Lastname"  .lastname}
      =
      {&b "Execute"   .execute:ew}
   }

   gridplus entry .result1 -stretch 0 -title "Result 1" {
      {   "Name"    .name:ew}
      {   "Phone"   .phone:ew}
      {&c "Current" .current}
   }

   gridplus layout .main -wtitle "Root" {
      .query
      .result1:ew
   }

   pack .main


   #------------------------------
   # Create toplevel1 and content.

   gridplus window .toplevel1 -wtitle "Toplevel1"

   gridplus entry .toplevel1.result2a -title "Result 2A" {
      {"Firstname" .firstname}
      {"Lastname"  .lastname}
   }
   
   gridplus entry .toplevel1.dummy -title "Dummy" {
      {"Firstname" .firstname =Dummy}
   }

   gridplus layout .toplevel1.main {
      .toplevel1.result2a
      .toplevel1.dummy
   }

   pack .toplevel1.main


   #------------------------------
   # Create toplevel2 and content.

   gridplus window .toplevel2 -wtitle "Toplevel2"

   gridplus entry .toplevel2.result2b -title "Result 2B" {
      {   "Name"    .name}
      {   "Phone"   .phone}
      {&c "Current" .current}
   }

   pack .toplevel2.result2b
   
}

Creates a procedure called "mainDisplay" to display the application windows and content.

In addition to the root window two toplevel windows (".toplevel1" and ".toplevel2") are created.



proc query,execute {} {

Creates a procedure called "query,execute" to execute the specified query.

   #--------------------
   # Query for result 1.
   
   gpdb exampledb . {
      select firstname || ' ' || lastname as name,
             phone,
             current
        from contacts
       where firstname = :(,firstname)
         and lastname  = :(,lastname)
   }

Query to populate the "Result 1" section of the root window.

The query window is set to ".". As window name does not end with a comma this is used as a default window name to define the full names of the GRIDPLUS items for the purpose of item/variable/result mapping. This is used in conjunction with the comma prefixed item name format to map to an item in the specified window which has a name ending with the specified item name. When using this method the "basename" of the item must be unique within the specified window.

For Example: ":(,firstname)" will be replaced by the value of the ".query,firstname" entry.

   #--------------------
   # Query for result 2.
      
   gpdb exampledb .toplevel1.result2a, {
      select firstname,
             lastname,
             firstname || ' ' || lastname @(.toplevel2.result2b,name),
             phone                        @(.toplevel2.result2b,phone),
             current                      @(.toplevel2.result2b,current)
        from contacts
       where firstname = :(.query,firstname)
         and lastname  = :(.query,lastname)
   }
   
}

Query to populate the "Result 2A" section of ".toplevel1" and the "Result 2B" section of ".toplevel2" toplevel windows.

The query window is set to ".toplevel1.result2a,". As the window name ends with a comma this is used as a default prefix to define the full names of the GRIDPLUS items for the purpose of item/variable/result mapping.

For Example: The value of the result "firstname" column will be used to set the value of the ".toplevel1.result2a,firstname" entry.

Three of the result columns use the "@(item)" syntax to specify the full name of the item to which the result is to be mapped.

For Example: The value of the result "phone" column will be used to set the value of the ".toplevel2.result2b,phone" entry.

In the SQL select "where" clause the item names begin with "." and therefore must specify the full name of the GRIDPLUS item.

For Example: ":(.query,firstname)" will be replaced by the value of the ".query,firstname" entry in the root window.



package require gridplus 2.11
namespace import -force gridplus::*

# Require TDBC with Sqlite3 driver.
package require tdbc::sqlite3

# Open database
tdbc::sqlite3::connection create exampledb "C:/GRIDPLUS/gridplus211/gpdb-example.db"

# Start
mainDisplay

Start the application. The comments in the source code for this part of the application provide a suitable description of its function.

GPDB Example Database

Below is the script used to create the database used for the examples on this page.



package require tdbc::sqlite3

tdbc::sqlite3::connection create exampledb "C:/GRIDPLUS/gridplus211/gpdb-example.db"

set sql [exampledb prepare {
   create table contacts (id INTEGER PRIMARY KEY AUTOINCREMENT,firstname TEXT collate nocase, lastname TEXT collate nocase, phone TEXT, category TEXT collate nocase, current TEXT collate nocase, notes TEXT collate nocase);
   insert into contacts (firstname,lastname,phone,category,current,notes) values('Alan','Markham','555 9912','personal','1','None.');
   insert into contacts (firstname,lastname,phone,category,current,notes) values('Debbie','Hibbert','555 0192','business','1','A good contact for technical questions. Always helpful and seems to be available 24/7.

Has excellent Tcl/Tk, C, HTML, PHP and Java skills.

Currently working in Matthews team.');
   insert into contacts (firstname,lastname,phone,category,current,notes) values('Fredrick','Blinkington-Smythe','555 9876','personal','1','None.');
   insert into contacts (firstname,lastname,phone,category,current,notes) values('John','Smith','555 4321','personal','1','None.');
   insert into contacts (firstname,lastname,phone,category,current,notes) values('Margaret','Clarke','555 6677','business','0','None.');
   insert into contacts (firstname,lastname,phone,category,current,notes) values('Mary','Jones','555 1234','business','1','None.');
   insert into contacts (firstname,lastname,phone,category,current,notes) values('Matthew','Wolfe','555 8449','personal','1','None.');
   insert into contacts (firstname,lastname,phone,category,current,notes) values('Peter','Huntington','555 6758','business','0','Left the company back in 2010. Now working for SuperWidgets.');
   insert into contacts (firstname,lastname,phone,category,current,notes) values('Robert','Irving','555 7621','personal','1','Birthday: 22nd March.');
   insert into contacts (firstname,lastname,phone,category,current,notes) values('Robert','Smith','555 0133','personal','0','None.');
   insert into contacts (firstname,lastname,phone,category,current,notes) values('Simon','Lambert','555 6789','business','1','None.');
   }]
   
$sql execute

$sql close

exampledb close

Copyright © 2014
Adrian Davis.