EZY ODBC - sample application

Coloring Conventions:

ezy_window_create - standard EZY Prolog predicate

syspath - standard Prolog predicate

initialize_button - user predicate

WINDOW - variable

GUI layout structure: ->>>

Created by EZY Designer

File name:

 ezy_sql_explorer.dcl

Main program:

Loads visual interface declarations from DCL file and assigns buttons handlers.

prolog_main():-

syspath ( PATH , _ ),

format ( DCL_FILE , "%ezy_sql_explorer.dcl" , PATH ),

ezy_form_load ( DCL_FILE , WINDOW ),

initialize_list_boxes( WINDOW ),

CRITERIA_ADDR =[ "ezy_odbc" , "ezy_query_panel" , "ezy_sql_stmt" ],

ezy_get_object ( WINDOW , CRITERIA_ADDR , CRITERIA_OBJ ),

ezy_editor_string ( CRITERIA_OBJ , "select * from table" ),

BUTTON_SELECT_DSN =[ "ezy_odbc" , "ezy_dsn_panel" , "Select DSN" ],

initialize_button( WINDOW , BUTTON_SELECT_DSN ),

BUTTON_OPEN_TABLE =[ "ezy_odbc" , "ezy_dsn_panel" , "Open Table" ],

initialize_button( WINDOW , BUTTON_OPEN_TABLE ),

BUTTON_FIELDS =[ "ezy_odbc" , "ezy_query_panel" , "Select Fields" ],

initialize_button( WINDOW , BUTTON_FIELDS ),

BUTTON_CRITERIA =[ "ezy_odbc" , "ezy_query_panel" , "Run SQL Criteria" ],

initialize_button( WINDOW , BUTTON_CRITERIA ),

BUTTON_MODIFIED =[ "ezy_odbc" , "ezy_query_panel" , "Display Modified Cells" ],

initialize_button( WINDOW , BUTTON_MODIFIED ),

write ( "Initialize grid headers" ),nl ,

GRID_ADDRESS =[ "ezy_odbc" , "ezy_query_panel" , "ezy_sql_grid" ],

grid_initialize( WINDOW , GRID_ADDRESS ),

! .

Button initialization code - assign button handler

initialize_button( WINDOW , ADDRESS ):-

ezy_get_object ( WINDOW , ADDRESS , OBJECT ),

ezy_object_handler ( OBJECT , my_button_handler ),

! .

initialize_button( _ , ADDRESS ):-

write ( "Exception: failed to initialize button " , ADDRESS ),nl .

List Boxes initialization code:

initialize_list_boxes( WINDOW ):-

DSN_ADDR =[ "ezy_odbc" , "ezy_dsn_panel" , "ezy_dsn_list" ],

ezy_get_object ( WINDOW , DSN_ADDR , DSN_OBJ ),

ezy_lbox_multiselect ( DSN_OBJ , 0 ),

odbc_sources ( DSN_LIST ),

ezy_lbox_init ( DSN_OBJ , DSN_LIST ),

TABLE_ADDR =[ "ezy_odbc" , "ezy_dsn_panel" , "ezy_dsn_table_list" ],

ezy_get_object ( WINDOW , TABLE_ADDR , TABLE_OBJ ),

ezy_lbox_multiselect ( TABLE_OBJ , 0 ),

ezy_lbox_init ( TABLE_OBJ ,[]),

FIELDS_ADDR =[ "ezy_odbc" , "ezy_query_panel" , "ezy_dsn_field_list" ],

ezy_get_object ( WINDOW , FIELDS_ADDR , FIELDS_OBJ ),

ezy_lbox_init ( FIELDS_OBJ ,[]),

! .

initialize_list_boxes( _ )

Grid Initialization code

grid_initialize( WINDOW , GRID_ADDRESS ):-

ezy_get_object ( WINDOW , GRID_ADDRESS , GRID_OBJECT ),

MAXROW = 1 ,

MAXCOLUMN = 3 ,

ezy_grid_rows ( GRID_OBJECT , MAXROW ),

COLUMN_LIST =[ "COLUMN1" , "COLUMN2" , "COLUMN3" ],

ezy_grid_columns ( GRID_OBJECT , COLUMN_LIST ),

for ( ROW , 1 , MAXROW ),

for ( COL , 1 , MAXCOLUMN ),

format ( GRID_CELL_DATA , "data(%,%)" , ROW , COL ),

ezy_grid_cell ( GRID_OBJECT , ROW , COL , GRID_CELL_DATA ),

COL = MAXCOLUMN ,

ROW = MAXROW ,

ezy_grid_callback ( GRID_OBJECT , my_grid_callback ),

write ( "Grid Initialized:" , GRID_ADDRESS , "\n" ),

! .

grid_initialize( _ , _ ).

Grid callback code - processes user actions in grid

my_grid_callback( _ ,user_action(modified( ROW , COL , DATA ))):-

format ( MSG , "modified cell(%,%)=%\n" , ROW , COL , DATA ),

write ( MSG ),

! .

my_grid_callback( _ , GRID_ACTION ):-

write ( "my_grid_callback(" , GRID_ACTION , ")\n" ),

fail .

Service functions:

list_length([], 0 ):-! .

list_length([ _ |T], LEN ):-! ,

list_length( T , TEMP ),LEN = TEMP + 1.

Navigation between controls. For any given control - find top window and locate child via symbolic path.

get_object_by_address( WINDOW , ADDRESS , OBJECT ):-

ezy_get_parent ( WINDOW , "ezy_odbc" , FIRST_WINDOW ),

ezy_get_object ( FIRST_WINDOW , ADDRESS , OBJECT ),

! .

ODBC record set support clauses.

recordset_create( BUTTON_OBJECT , FIELDS , CRITERIA , RECORDSET ):-

ezy_get_parent ( BUTTON_OBJECT , "ezy_odbc" , FIRST_WINDOW ),

TABLE_ADDR =[ "ezy_odbc" , "ezy_dsn_panel" , "ezy_dsn_table_list" ],

ezy_get_object ( FIRST_WINDOW , TABLE_ADDR , TABLE_OBJ ),

ezy_lbox_get_selected ( TABLE_OBJ , TABLE_LIST_SELECTED ),

TABLE_LIST_SELECTED =[ TABLE |_],

write ( "Selected TABLE " , TABLE ),

nl ,

current_odbc_connection( ODBC_HANDL),

odbc_recordset ( ODBC_HANDL , TABLE , FIELDS , CRITERIA , RECORDSET ),

! .

Filling grid with results from ODBC table

fill_grid( FIRST_WINDOW , RECORDSET ):-

GRID_ADDRESS =[ "ezy_odbc" , "ezy_query_panel" , "ezy_sql_grid" ],

ezy_get_object ( FIRST_WINDOW , GRID_ADDRESS , GRID_OBJECT ),

START_ROW = 1 ,

ezy_grid_clear ( GRID_OBJECT ),

fill_grid_rows( RECORDSET , START_ROW , GRID_OBJECT ),

! .

fill_grid_rows( RECORDSET , ROW , GRID_OBJECT ):-

odbc_recordset_record ( RECORDSET , DB_RECORD ),

STARTCOL = 1 ,

fill_grid_cells( GRID_OBJECT , ROW , STARTCOL , DB_RECORD ),

odbc_recordset_move_next ( RECORDSET ),! ,

NEXTROW = ROW + 1 ,

fill_grid_rows( RECORDSET , NEXTROW , GRID_OBJECT ).

fill_grid_rows( _ , MAXROW , GRID_OBJECT ):-! ,

ezy_grid_rows ( GRID_OBJECT , MAXROW ).

fill_grid_cells( _ , _ , _ ,[]):-! .

fill_grid_cells( GRID_OBJECT , ROW , COL ,[ DATA |DB_RECORD]):-! ,

ezy_grid_cell ( GRID_OBJECT , ROW , COL , DATA ),

NEXTCOL = COL + 1,

fill_grid_cells( GRID_OBJECT , ROW , NEXTCOL , DB_RECORD ).

run_sql_criteria( BUTTON_OBJECT ):-

ezy_get_parent ( BUTTON_OBJECT , "ezy_odbc" , FIRST_WINDOW ),

CRITERIA_ADDR =[ "ezy_odbc" , "ezy_query_panel" , "ezy_sql_stmt" ],

ezy_get_object ( FIRST_WINDOW , CRITERIA_ADDR , CRITERIA_OBJ ),

ezy_editor_string ( CRITERIA_OBJ , SQL_CRITERIA ),

write ( "ENTERED SQL CRITERIA:\n" , SQL_CRITERIA ),

nl ,

FIELDS_ADDR =[ "ezy_odbc" , "ezy_query_panel" , "ezy_dsn_field_list" ],

ezy_get_object ( FIRST_WINDOW , FIELDS_ADDR , FIELDS_OBJ ),

ezy_lbox_get_selected ( FIELDS_OBJ , FIELDS_SELECTED ),

recordset_create( BUTTON_OBJECT , FIELDS_SELECTED , SQL_CRITERIA , RECORDSET ),

fill_grid( FIRST_WINDOW , RECORDSET ),

odbc_recordset_release ( RECORDSET ),

! .

run_open_table( BUTTON_OBJECT ):-

recordset_create( BUTTON_OBJECT ,[], "" , RECORDSET ),

odbc_recordset_properties ( RECORDSET , SOURCE , DRIVER , DBFILE , TABLE , SLISTFIELDS ),

FIELDS_ADDR =[ "ezy_odbc" , "ezy_query_panel" , "ezy_dsn_field_list" ],

ezy_get_parent ( BUTTON_OBJECT , "ezy_odbc" , FIRST_WINDOW ),

ezy_get_object ( FIRST_WINDOW , FIELDS_ADDR , FIELDS_OBJ ),

ezy_lbox_init ( FIELDS_OBJ , SLISTFIELDS ),

write ( "Created Recordset:\nSource=" , SOURCE ,

"\nDRIVER=" , DRIVER ,

"\nDBFILE=" , DBFILE ,

"\nTABLE=" , TABLE ,"\n"),

odbc_recordset_release ( RECORDSET ),

! .

run_open_connection( _ ):-

retract (current_odbc_connection( ODBC_CONNECTION )),

odbc_disconnect ( ODBC_CONNECTION , _ ),

write ( "Destroyed ODBC Connection " , ODBC_CONNECTION ),

nl ,

fail .

run_open_connection( BUTTON_OBJECT ):-

ezy_get_parent ( BUTTON_OBJECT , "ezy_odbc" , FIRST_WINDOW ),

DSN_ADDR =[ "ezy_odbc" , "ezy_dsn_panel" , "ezy_dsn_list" ],

ezy_get_object ( FIRST_WINDOW , DSN_ADDR , DSN_OBJ ),

write ( "Getting selected DSN:\n" ),

ezy_lbox_get_selected ( DSN_OBJ , DSN_LIST_SELECTED ),

write ( "Selected DSN " , DSN_LIST_SELECTED ),

nl ,

DSN_LIST_SELECTED =[ DSN_WITH_DRIVER |_],

searchstring ( DSN_WITH_DRIVER , "(" , POSITION ),

CUTPOS = POSITION - 1,

frontstr ( CUTPOS , DSN_WITH_DRIVER , DSN , _ ),

odbc_connect ( DSN , "" , "" , ODBC_CONNECTION ),

assert(current_odbc_connection( ODBC_CONNECTION )),

TABLE_ADDR =[ "ezy_odbc" , "ezy_dsn_panel" , "ezy_dsn_table_list" ],

ezy_get_object ( FIRST_WINDOW , TABLE_ADDR , TABLE_OBJ ),

odbc_tables ( ODBC_CONNECTION , TABLES ),

ezy_lbox_init ( TABLE_OBJ , TABLES ),

FIELDS_ADDR =[ "ezy_odbc" , "ezy_query_panel" , "ezy_dsn_field_list" ],

ezy_get_object ( FIRST_WINDOW , FIELDS_ADDR , FIELDS_OBJ ),

ezy_lbox_init ( FIELDS_OBJ ,[]),

CRITERIA_ADDR =[ "ezy_odbc" , "ezy_query_panel" , "ezy_sql_stmt" ],

ezy_get_object ( FIRST_WINDOW , CRITERIA_ADDR , CRITERIA_OBJ ),

ezy_editor_string ( CRITERIA_OBJ , "" ),

fail .

run_open_connection( _ ).

run_select_fields( WINDOW ):-

ezy_get_parent ( WINDOW , "ezy_odbc" , FIRST_WINDOW ),

FIELDS_ADDR =[ "ezy_odbc" , "ezy_query_panel" , "ezy_dsn_field_list" ],

ezy_get_object ( FIRST_WINDOW , FIELDS_ADDR , FIELDS_OBJ ),

ezy_lbox_get_selected ( FIELDS_OBJ , FIELDS_SELECTED ),

GRID_ADDRESS =[ "ezy_odbc" , "ezy_query_panel" , "ezy_sql_grid" ],

ezy_get_object ( FIRST_WINDOW , GRID_ADDRESS , GRID_OBJECT ),

ezy_grid_columns ( GRID_OBJECT , FIELDS_SELECTED ),

ezy_grid_clear ( GRID_OBJECT ),

! .

run_select_fields( _ ):-write ( "Exception: run_select_fields failed\n" ).

display_modified( WINDOW ):-

write ( "Display modified\n" ),

ezy_get_parent ( WINDOW , "ezy_odbc" , FIRST_WINDOW ),

GRID_ADDRESS =[ "ezy_odbc" , "ezy_query_panel" , "ezy_sql_grid" ],

ezy_get_object ( FIRST_WINDOW , GRID_ADDRESS , GRID_OBJECT ),

ezy_grid_rows ( GRID_OBJECT , MAXROW ),

ezy_grid_columns ( GRID_OBJECT , COLUMN_LIST ),

list_length( COLUMN_LIST , MAXCOLUMN ),

collect_modified_rows( GRID_OBJECT ,

MAXROW , MAXCOLUMN , MODIFIED_CELLS ),

format ( MESSAGE_STRING , "\nModified Cells:\n%" , MODIFIED_CELLS ),

CRITERIA_ADDR =[ "ezy_odbc" , "ezy_query_panel" , "ezy_sql_stmt" ],

ezy_get_object ( FIRST_WINDOW , CRITERIA_ADDR , CRITERIA_OBJ ),

ezy_editor_string ( CRITERIA_OBJ , MESSAGE_STRING ),

! .

collect_modified_rows( _ , 0 , _ , "\n" ):-! .

collect_modified_rows( GRID_OBJECT , ROW , MAXCOLUMN , RESULT ):-! ,

PREV_ROW =ROW - 1,

collect_modified_rows( GRID_OBJECT , PREV_ROW , MAXCOLUMN , TEMP ),

collect_modified_cells( GRID_OBJECT , ROW , MAXCOLUMN , ROW_DATA ),

format ( RESULT , "% {Row - %} %" , TEMP , ROW , ROW_DATA ).

collect_modified_cells( _ , _ , 0 , "\n" ):-! .

collect_modified_cells( GRID_OBJECT , ROW , COL , OUT ):-

ezy_grid_modified_cell ( GRID_OBJECT , ROW , COL ),! ,

NEXTCOL = COL - 1 ,

collect_modified_cells( GRID_OBJECT , ROW , NEXTCOL , TEMP ),

ezy_grid_cell ( GRID_OBJECT , ROW , COL , CELL_DATA ),

format ( OUT , "cell(%,%,%)%" , ROW , COL , CELL_DATA , TEMP ).

collect_modified_cells( GRID_OBJECT , ROW , COL , OUT ):-! ,

NEXTCOL = COL - 1 ,

collect_modified_cells( GRID_OBJECT , ROW , NEXTCOL , OUT ).

Button callback - activates when user presses a button

my_button_handler( _ ,e_control( _ , _ , BUTTON_OBJECT , _ )):-

ezy_object_name ( BUTTON_OBJECT , NAME ),

NAME = "Select DSN" ,

! ,

write ( "Processing DSN selection\n" ),

run_open_connection( BUTTON_OBJECT ).

my_button_handler( _ ,e_control( _ , _ , BUTTON_OBJECT , _ )):-

ezy_object_name ( BUTTON_OBJECT , NAME ),

NAME = "Open Table" ,

! ,

run_open_table( BUTTON_OBJECT ),

fail .

my_button_handler( _ ,e_control( _ , _ , BUTTON_OBJECT , _ )):-

ezy_object_name ( BUTTON_OBJECT , NAME ),

NAME = "Select Fields" ,

! ,

run_select_fields( BUTTON_OBJECT ),

fail .

my_button_handler( _ ,e_control( _ , _ , BUTTON_OBJECT , _ )):-

ezy_object_name ( BUTTON_OBJECT , NAME ),

NAME = "Run SQL Criteria" ,

! ,

run_sql_criteria( BUTTON_OBJECT ),

fail .

my_button_handler( _ ,e_control( _ , _ , BUTTON_OBJECT , _ )):-

ezy_object_name ( BUTTON_OBJECT , NAME ),

NAME = "Display Modified Cells" ,

! ,

display_modified( BUTTON_OBJECT ),

fail .

my_button_handler( _ ,e_control( _ , _ , BUTTON_OBJECT , _ )):-

ezy_object_name ( BUTTON_OBJECT , NAME ),

write ( "Button pressed: [" , NAME , "]\n" ),

fail .