PGACCESS 0.82 1 March 1998

PGACCESS 0.83 11 March 1998

================================

I dedicate this program to my little 4 year daughter Ana-Maria and my wife

for their understanding. I hope they will forgive me for spending so many

Forms

- open user defined forms

- form design module available

- query widgetqlowing access to a recordset

- query widgetallowing access to a recordset

Scripts

- define, modify and call user defined scripts

CREATETABLEpga_queries (querynamevarchar(64), querytypechar(1), querycommandtext);

CREATETABLEpga_forms (formnamevarchar(64), formsourcetext);

CREATETABLEpga_scripts (scriptnamevarchar(64), scriptsourcetext);

CREATETABLEpga_reports (reportnamevarchar(64), reportsourcetext, reportbodytext, reportprocstext, reportoptionstext);

CREATETABLEphonebook (namevarchar(32), phone_nrvarchar(16), cityvarchar(32), company bool, continent char16);

CREATETABLEpga_layout (tablenamevarchar(64), nrcols int2, colnamestext, colwidthtext);

COPY pga_queriesFROM stdin;

\.

COPY pga_formsFROM stdin;

Phone bookpb26 {1234567891011121314151617181920212223242526} 444x310+284+246 {label label1 {33126330} {} Name {}} {entry name_entry {87921730} {} entry2 pbqs(name)} {label label3 {33397354} {} Phone {}} {entry entry4 {873619557} {} entry4 pbqs(phone_nr)} {label label5 {33667884} {} City {}} {entry entry6 {876319584} {} entry6 pbqs(city)} {query qs {363333} {} query7 {}} {button button8 {126177198203} {.pb.qs:setsql"select oid,* from phonebook where name ~* '$what' order by name"\

.pb.qs:open\

set nrecs [.pb.qs:nrecords]\

.pb.qs:updatecontrols\

.pb.qs:fill .pb.allnames name} Find {}} {button button9 {159276229302} {.pb.qs:close\

.pb.qs:clearcontrols\

set nrecs {}\

set what {}\

destroy .pb\

} Exit {}} {button button10 {102249124269} {.pb.qs:movefirst\

.pb.qs:updatecontrols\

} |< {}} {button button11 {129249151269} {.pb.qs:moveprevious\

.pb.qs:updatecontrols\

}<< {}} {button button12 {156249178269} {.pb.qs:movenext\

.pb.qs:updatecontrols}>> {}} {button button13 {183249205269} {.pb.qs:movelast\

.pb.qs:updatecontrols\

}>| {}} {checkbox checkbox14 {3387126105} {} {Is it a company ?} pbqs(company)} {radio usa {63108201120} {}U.S.A. pbqs(continent)} {radio europe {63126204141} {} Europe pbqs(continent)} {radio africa {63144210159} {} Africa pbqs(continent)} {entry entry18 {30180117198} {} entry18 what} {label label19 {108219188234} {} {records found} {}} {label label20 {90219105234} {} { } nrecs} {label label21 {325233267} {}OID= {}} {label label22 {3925287267} {} { } pbqs(oid)} {button button23 {927679302} {setoid {}\

catch {setoid $pbqs(oid)}\

if {[stringtrim $oid]!=""} {\

sql_exec noquiet"update phonebook set name='$pbqs(name)', phone_nr='$pbqs(phone_nr)',city='$pbqs(city)',company='$pbqs(company)',continent='$pbqs(continent)' where oid=$oid"\

} else {\

tk_messageBox-title Error-message"No record is displayed!"\

}\

\

}Update {}} {button button24 {84276154302} {set thisname {}\

catch {set thisname $pbqs(name)}\

if {[stringtrim $thisname]!=""} {\

sql_exec noquiet"insert into phonebook values ('$pbqs(name)','$pbqs(phone_nr)','$pbqs(city)','$pbqs(company)','$pbqs(continent)')"\

tk_messageBox-title Information-message"A new record has been added!"\

} else {\

tk_messageBox-title Error-message"This one doesn't have a name ?\

<METAHTTP-EQUIV="Content-Type"CONTENT="text/html; charset=iso-8859-1">

<METANAME="GENERATOR"CONTENT="Mozilla/4.04 [en] (X11; I; Linux 2.0.32 i586) [Netscape]">

<METANAME="GENERATOR"CONTENT="Mozilla/3.04Gold (X11; I; Linux 2.0.32 i586) [Netscape]">

<BODYTEXT="#000000"BGCOLOR="#FFEBCD"LINK="#0000EF"VLINK="#51188E"ALINK="#FF0000">

FORMS</H1>

<H1>FORMS</H1>

<HRWIDTH="100%">

<HRWIDTH="100%"></P>

<P>This version (0.82) of PgAccess introduce the visual form builder.

<P>This version (0.83) of PgAccess has changed a little the visual form

builder introduced since 0.82 version : variable handling, query-results

interface and control bindings naming convention. Please read it carefully,

download the database demo and practice a while before trying to design

your own forms.</P>

<P>For the moment, it has only some basic widgets : labels, entries, buttons

, listboxes , checkboxes and radiobuttons.

, listboxes , checkboxes and radiobuttons.</P>

<P>Also there is a pseudo query widget that allows you yo have access to

a query results.</P>

<P><B>How do you generate widgets :</B></P>

<LI>select a widget from the toolbox by clicking the appropriate radiobutton</LI>

<LI>move to the canvas , point with the mouse at the desired location and

click the mouse button to begin</LI>

<LI>keeping the mouse-button pressed move the mouse in order to draw a

rectangle that will hold the widget</LI>

<P>In a manner very similar with Visual Tcl or Visual Basic, the user must

select a widget from the toolbar and drags on the canvas the rectangle

that would define the widget. It can also specify some attributes in a

separate window. Renaming, resizing items are possible modifying parameters

in attribute window. Do not forget to press Enter in the edit field after

changing a value in order to be accepted.

<P>In the rectangle that you have designed it will appear the selected

object.<BR>

Move now to the attribute window to change some of its properties.</P>

<P>Renaming, resizing items are possible (for the moment) only by modifying

appropriate parameters in attribute window. You<B>must</B>press Enter

in the edit field after changing a value in order to be accepted.</P>

<P>You can also move items by dragging them or delete them by pressing

Del key.

Del key after selecting them.</P>

<P>In attribute window, there are some fields named<B><TT><FONTSIZE=+1>Command

</FONT></TT></B>and<B><TT><FONTSIZE=+1>Variable</FONT></TT></B>.

</FONT></TT></B>and<B><TT><FONTSIZE=+1>Variable</FONT></TT></B>.</P>

<P>The field<B><TT><FONTSIZE=+1>Command</FONT></TT></B>have meaning

only for Button widgets and holds the command that will be invoked when

the button is pressed.

<P>The field<B><TT><FONTSIZE=+1>Variable</FONT></TT></B>have meaning

only for EditField , Label widgets and checkboxes and it is the name of

the global variable that will hold the value for that widget. For checkboxes

the values are 1 or 0.

<P>In order to make a simple test, put an entry field and set it's variable

to<B>v1</B> and a button who's command is "set v1 whisky". Press the button

"Test form" and click on the button. In that entry should appear whisky.

<BR>Another test is defining in Script module a script called "My first

script" having the following commands:

<BR><TT><FONTSIZE=+1>tk_messageBox -title Warning -message "This is my

first message!"</FONT></TT>

<BR>and then define a button who's command is<B><TT><FONTSIZE=+1>execute_script

"My first script"</FONT></TT></B>.

Database manipulation</H2>

Let's presume that our form have the internal name<B><TT>mf</TT></B>(my

form). He wil be referred inside the Tcl/Tk source as<B><TT>.mf</TT></B>

<BR>If you want to close the form in run-time you have to issue the command

<B><TT>destroy .mf</TT></B>

the button is pressed.</P>

<P>&nbsp;&nbsp;&nbsp;&nbsp;The field<B><TT><FONTSIZE=+1>Variable</FONT></TT></B>have

meaning only for EditField , Label widgets , checkboxes and radiobuttons

and it is the name of the global variable that will hold the value for

that widget. For checkboxes the values are<B>t</B> and<B>f</B> (from

true and false) in order to simplify binding to logical data fields (PgAccess

0.82 used 0 and 1).</P>

<P>&nbsp;&nbsp;&nbsp;&nbsp;For radiobuttons, it is usual to assign the

same variable to the same radiobuttons within the same group. That variable

will contain the name of the widget of the radiobutton that has been pressed.

Let's presume that you have entered 3 radiobuttons named red, green and

blue, all of them having the same variable named color. If you will press

them, they will assign their names to global variable.</P>

<P>&nbsp;&nbsp;&nbsp;&nbsp;In order to make a simple test, put an entry

field and set it's variable to<B>v1</B> and a button who's command is

&quot;set v1 whisky&quot;. Press the button &quot;Test form&quot; and click

on the button. In that entry should appear whisky.<BR>

Another test is defining in Script module a script called &quot;My first

script&quot; having the following commands:<BR>

<TT><FONTSIZE=+1>tk_messageBox -title Warning -message &quot;This is my

first message!&quot;</FONT></TT><BR>

and then define a button who's command is<B><TT><FONTSIZE=+1>execute_script

&quot;My first script&quot;</FONT></TT></B>.</P>

<H2>Database manipulation</H2>

<P>Let's presume that our form have the internal name<B><TT>mf</TT></B>(my

form). He will be referred inside the Tcl/Tk source as<B><TT>.mf</TT></B>

If you want to close the form in run-time you have to issue the command

<B><TT>destroy .mf</TT></B></P>

<P>Also, any widget will have the name prefixed by<B><TT>.mf&nbsp;</TT></B>&nbsp;&nbsp;&nbsp;&nbsp;

We will have<B><TT>.mf.button1</TT></B> or<B><TT>.mf.listbox1</TT></B>

.

.</P>

<P>We can name the query widget<B><TT>qry</TT></B> for example. The complete

name will be<B><TT>.mf.qry</TT></B> then.

<BR>The<B><TT>Command</TT></B>property of the query widget must contain

the SQL command that will be executed.

<BR>When the form will be in run-time, automatically you will have acces

to the following methods :

<P><TT>.mf.qry:execute</TT> - opens the connection and execute the query

(returns nothing)

<BR><TT>.mf.qry:nrecords</TT> - returns the number of records in the selected

query

<BR><TT>.mf.qry:fields</TT> - returns a list of the fields in the result

set

<BR><TT>.mf.qry:movefirst</TT> - move the cursor to the first record in

the recordset

<BR><TT>.mf.qry:movelast , .mf.qry:movenext , .mf.qry:moveprevious</TT>-

moves the cursor

<BR><TT>.mf.qry:updatecontrols</TT> - update the variables inside the designed

form that have a particular name (I'll explain later)

<BR><TT>.mf.qry:close</TT> - close the connection (<B><FONTCOLOR="#FF0000">if

you don't close the query result, you will loose memory</FONT></B>)

name will be<B><TT>.mf.qry</TT></B> then.<BR>

The<B><TT>Command</TT></B>property of the query widget must contain the

SQL command that will be executed.<BR>

When the form will be in run-time, automatically you will have acces to

the following procedures and functions :</P>

<P><B><TT>.mf.qry:open</TT></B> - opens the connection and execute the

query (returns nothing)<BR>

<B><TT>.mf.qry:setsql newsql</TT></B> - set the command query that will

be executed at the next<B><TT>.mf.qry:open<BR>

.mf.qry:nrecords</TT></B> - returns the number of records in the selected

query<BR>

<B><TT>.mf.qry:crtrecord</TT></B>- returns the current record number inside

the query result<BR>

<B><TT>.mf.qry:fields</TT></B> - returns a list of the fields in the result

set<BR>

<B><TT>.mf.qry:movefirst</TT></B> - move the cursor to the first record

in the recordset<BR>

<TT><B>.mf.qry:movelast</B> ,<B>.mf.qry:movenext</B> ,<B>.mf.qry:moveprevious</B>

</TT>- moves the cursor there<BR>

<B><TT>.mf.qry:moveto newrecno</TT></B> - move the cursor to that new record

number (first is 0)<BR>

<B><TT>.mf.qry:updatecontrols</TT></B> - update the variables inside the

designed form that have a particular name (I'll explain later)<BR>

<B><TT>.mf.qry:clearcontrols</TT></B> - clear the variables inside the

designed form binded to a query result<BR>

<TT><B>.mf.qry:fill listbox field</B></TT>- fill the named listbox (whole

widget name as<B><TT>.mf.listbox1</TT></B>) with the all the values of

that field in the current queryresult<BR>

<B><TT>.mf.qry:close</TT></B> - close the connection (<B><FONTCOLOR="#FF0000">if

you don't close the query result, you will loose some memory</FONT></B>)

<P>It's no need to close a query-result if you want to assign it a new

SQL command and open it again. That will be done automatically.</P>

<P>If you want to bound some controls to the fields of the recordset, you

will have to name their associate variable like that :

<P><TT>.mf.qry.salary</TT> to get the "salary" field , or<TT>.mf.qry.name</TT>

to get the "name" field.

<P>It's simple, isn't it ? It's just like a new widget that have some properties

and methods that can be accesed.

<BR>Also, the name convention is just like in Tcl/Tk.

will have to name their associate variable like that :</P>

<P><B><TT>mfqry(salary)</TT></B> to get the &quot;salary&quot; field ,

or<B><TT>mfqry(name)</TT></B> to get the &quot;name&quot; field. So, you

take the internale name of the form (without the leading point) and merge

it with the query-widget name and you will get a associative arrayname.

The values are given using the field name as a key in this associative

array.<BR>

<B><FONTCOLOR="#FF0000">* WARNING *</FONT></B> The naming style has been

changed from 0.82 version of PgAccess and I cannot guarantee that it not

be changed again in the future. I'm just trying to make it as simple it

could be. The old style naming convention had some incompatibilities with

Tcl/Tk syntax.</P>

<P><B><FONTSIZE=+1><FONTCOLOR="#9400D3">QUESTION</FONT> :</FONT></B>Do

you think that it would be more clear if the above functions and procedures

regarding a query-widget would be named in a similar manner as binded variable

controls ? That mean,<B><TT>mfqry:open</TT></B> and<B><TT>mfqry:updatecontrols</TT></B>

instead of<B><TT>.mf.qry:open</TT></B> and<B><TT>.mf.qry:updatecontrols</TT></B>

?</P>

<P>The only advantage of<B><TT>.mf.qry:open</TT></B> is that is much closer

to the Tk method of referring objects within a window and it may give to

the programmer the feeling that he is using a object-oriented widget. Since

I cannot use further for naming binded variables a similar manner (<B><TT>.mf.qry.salary)</TT></B>

it might be possible to abandon that naming convention .</P>

<P>Here it is a dumped<B><AHREF="formdemo.sql">sample database</A></B>

that contains a demo database. What should you do ?<BR>

Shift-click the above URL in order to download that tiny file (4 Kb). Create

a empty database and<B><TT>psql yourdatabase &lt;formdemo.sql</TT></B></P>

<P>You should find a single table called &quot;phonebook&quot; a form called

&quot;Phone book&quot; and another &quot;A simple demo form&quot;.</P>

<P>First of all enter and view the phonebook table in table view. Note

the fields and their values.<BR>

Open the &quot;Phone book&quot; form and enter a letter (a, e or i) in

the field to the left of &quot;Find&quot; button then press Find. It's

fine to enter one letter in order to get more records in query result.

You will get information about the number of records selected, in the listbox

you will see all the values of field &quot;name&quot; from the current

data set. Use buttons to move to first, next, previous or last record within

the record set.</P>

<P>In order to add a new record, press the &quot;New&quot; button in order

to get new, clean entries. Fill them with your data and press &quot;Add

new&quot; button. A new phonebook record will be added. Also, if you want

to update a record, change it's values in the displayed fields after finding

it and press &quot;Update&quot; button. The values will be updated in the

database BUT NOT IN THE CURRENT QUERY RESULT . If you want to see them

modified, make a new query trying to find it again.</P>

<P><FONTCOLOR="#000080">Before using the results from a query you should

know that the information that has been retrieved could be found only in

your computer client memory. It has<B>no live connection</B> to the data

from the database. That's why it isn't possible to develop a simple update

function as interface to that query-result widget. More than that : a query

result could be obtained from a SQL command that return a non-updatable

data set !!! For example fields gathered from multiple tables or summary

fields. It isn't just simple to make an automatic update procedure. The

programmer must know how to make the update or the append procedure, sometimes

using key fields to point to the desired record or an OID. There are examples

in the demo database in &quot;Phone book&quot; form. It may be possible

that in the future, I will develop another pseudo-widget describing a table.

It would be more simple than to implement an update or append or even a

delete procedure.</FONT></P>

<P>There is in the demo database also another simple form called &quot;A

simple demo form&quot;. It will show you how to handle variables from checkboxes,

radiobuttons, how to use listboxes, open another forms and so on. I think

they will help you.</P>

<P>In order to avoid naming user defined forms with&nbsp; a particular

name of another PgAccess form, I would recommend naming them as udf0, udf1

(user defined form 0 , 1 )

(user defined form 0 , 1 )</P>

<HRWIDTH="25%">

<HRWIDTH="25%"></P>

<P>Please feel free to send me your oppinion at<B>teo@flex.ro</B> on forms

designing and usage.

designing and usage.</P>

<P><B><FONTSIZE=+1>KEEP IN MIND ! &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;THE

FORM API WILL CHANGE IN ORDER TO BE MORE SIMPLE AND BETTER!&nbsp;&nbsp;&nbsp;&nbsp;SEND

ME YOUR WISHES, YOUR IDEAS, YOUR OPINIONS !<BR>

ALSO ... DON'T BLAME ME IF YOU WILL HAVE TO RE-DESIGN YOUR OLD FORMS DUE

TO SOME INCOMPATIBILITIES WITH NEWER PGACCESS VERSIONS.</FONT></B></P>