|
a |
|
b/doc/sqlscreens.html |
|
|
1 |
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
|
|
|
2 |
|
|
|
3 |
<html>
|
|
|
4 |
<head>
|
|
|
5 |
<meta name="generator" content=
|
|
|
6 |
"HTML Tidy for Linux (vers 25 March 2009), see www.w3.org">
|
|
|
7 |
<meta http-equiv="Content-Type" content=
|
|
|
8 |
"text/html; charset=us-ascii">
|
|
|
9 |
|
|
|
10 |
<title>SQLScreens: a simple SQL screen generator</title>
|
|
|
11 |
<link rel="stylesheet" type="text/css" href="docbook-xsl.css">
|
|
|
12 |
<meta name="generator" content="DocBook XSL Stylesheets V1.76.1">
|
|
|
13 |
</head>
|
|
|
14 |
|
|
|
15 |
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084"
|
|
|
16 |
alink="#0000FF">
|
|
|
17 |
<div lang="en" class="book" title=
|
|
|
18 |
"SQLScreens: a simple SQL screen generator">
|
|
|
19 |
<div class="titlepage">
|
|
|
20 |
<div>
|
|
|
21 |
<div>
|
|
|
22 |
<h1 class="title"><a name="idp268176" id=
|
|
|
23 |
"idp268176"></a><span class=
|
|
|
24 |
"application">SQLScreens</span>: a simple SQL screen
|
|
|
25 |
generator</h1>
|
|
|
26 |
</div>
|
|
|
27 |
</div>
|
|
|
28 |
<hr>
|
|
|
29 |
</div>
|
|
|
30 |
|
|
|
31 |
<div class="toc">
|
|
|
32 |
<p><b>Table of Contents</b></p>
|
|
|
33 |
|
|
|
34 |
<dl>
|
|
|
35 |
<dt><span class="chapter">1. <a href=
|
|
|
36 |
"#idp2783584">Preface</a></span></dt>
|
|
|
37 |
|
|
|
38 |
<dd>
|
|
|
39 |
<dl>
|
|
|
40 |
<dt><span class="sect1">1.1. <a href=
|
|
|
41 |
"#PREFACE.QUICKDESCRIPTION">Quick
|
|
|
42 |
description</a></span></dt>
|
|
|
43 |
</dl>
|
|
|
44 |
</dd>
|
|
|
45 |
|
|
|
46 |
<dt><span class="chapter">2. <a href=
|
|
|
47 |
"#INSTALLATION">Installation</a></span></dt>
|
|
|
48 |
|
|
|
49 |
<dd>
|
|
|
50 |
<dl>
|
|
|
51 |
<dt><span class="sect1">2.1. <a href=
|
|
|
52 |
"#INSTALLATION.EXTERNAL">External software
|
|
|
53 |
needed</a></span></dt>
|
|
|
54 |
|
|
|
55 |
<dt><span class="sect1">2.2. <a href=
|
|
|
56 |
"#idp143456">Installation</a></span></dt>
|
|
|
57 |
|
|
|
58 |
<dd>
|
|
|
59 |
<dl>
|
|
|
60 |
<dt><span class="sect2">2.2.1. <a href=
|
|
|
61 |
"#idp5635776">Generating a wish interpreter with
|
|
|
62 |
<span class="application">MySQL</span>
|
|
|
63 |
support:</a></span></dt>
|
|
|
64 |
</dl>
|
|
|
65 |
</dd>
|
|
|
66 |
</dl>
|
|
|
67 |
</dd>
|
|
|
68 |
|
|
|
69 |
<dt><span class="chapter">3. <a href="#USING">Using
|
|
|
70 |
<span class="application">SQLScreens</span></a></span></dt>
|
|
|
71 |
|
|
|
72 |
<dd>
|
|
|
73 |
<dl>
|
|
|
74 |
<dt><span class="sect1">3.1. <a href=
|
|
|
75 |
"#USING.ENVIR">Environment variables</a></span></dt>
|
|
|
76 |
|
|
|
77 |
<dt><span class="sect1">3.2. <a href=
|
|
|
78 |
"#idp5682032">Buttons</a></span></dt>
|
|
|
79 |
|
|
|
80 |
<dt><span class="sect1">3.3. <a href=
|
|
|
81 |
"#idp5700560">Keyboard shortcuts</a></span></dt>
|
|
|
82 |
|
|
|
83 |
<dt><span class="sect1">3.4. <a href=
|
|
|
84 |
"#idp5713552">Special characters in
|
|
|
85 |
fields</a></span></dt>
|
|
|
86 |
</dl>
|
|
|
87 |
</dd>
|
|
|
88 |
|
|
|
89 |
<dt><span class="chapter">4. <a href=
|
|
|
90 |
"#PROGRAMMING">Programming Interface</a></span></dt>
|
|
|
91 |
|
|
|
92 |
<dd>
|
|
|
93 |
<dl>
|
|
|
94 |
<dt><span class="sect1">4.1. <a href=
|
|
|
95 |
"#PROGRAMMING.OVERVIEW">Overview</a></span></dt>
|
|
|
96 |
|
|
|
97 |
<dt><span class="sect1">4.2. <a href=
|
|
|
98 |
"#PROGRAMMING.INIT">Initialization and
|
|
|
99 |
termination</a></span></dt>
|
|
|
100 |
|
|
|
101 |
<dd>
|
|
|
102 |
<dl>
|
|
|
103 |
<dt><span class="sect2">4.2.1. <a href=
|
|
|
104 |
"#idp5738592">sqlscreen</a></span></dt>
|
|
|
105 |
|
|
|
106 |
<dt><span class="sect2">4.2.2. <a href=
|
|
|
107 |
"#idp5745984">sqlscreendelete</a></span></dt>
|
|
|
108 |
|
|
|
109 |
<dt><span class="sect2">4.2.3. <a href=
|
|
|
110 |
"#PROGRAMMING.INIT.FONT">Setting fonts for
|
|
|
111 |
<span class="application">SQLScreens</span>
|
|
|
112 |
applications</a></span></dt>
|
|
|
113 |
</dl>
|
|
|
114 |
</dd>
|
|
|
115 |
|
|
|
116 |
<dt><span class="sect1">4.3. <a href=
|
|
|
117 |
"#PROGRAMMING.ARRAYENTRIES">Interface array
|
|
|
118 |
entries</a></span></dt>
|
|
|
119 |
|
|
|
120 |
<dd>
|
|
|
121 |
<dl>
|
|
|
122 |
<dt><span class="sect2">4.3.1. <a href=
|
|
|
123 |
"#PROGRAMMING.ARRAYENTRIES.GENERAL">General
|
|
|
124 |
parameters</a></span></dt>
|
|
|
125 |
|
|
|
126 |
<dt><span class="sect2">4.3.2. <a href=
|
|
|
127 |
"#PROGRAMMING.ARRAYENTRIES.SATTRIBUTES">Screen
|
|
|
128 |
attributes</a></span></dt>
|
|
|
129 |
|
|
|
130 |
<dt><span class="sect2">4.3.3. <a href=
|
|
|
131 |
"#PROGRAMMING.ARRAYENTRIES.FATTRIBUTES">Field
|
|
|
132 |
attributes</a></span></dt>
|
|
|
133 |
|
|
|
134 |
<dt><span class="sect2">4.3.4. <a href=
|
|
|
135 |
"#idp5846912">Auxiliary list window</a></span></dt>
|
|
|
136 |
|
|
|
137 |
<dt><span class="sect2">4.3.5. <a href=
|
|
|
138 |
"#idp5870688">Miscellaneous array
|
|
|
139 |
entries</a></span></dt>
|
|
|
140 |
</dl>
|
|
|
141 |
</dd>
|
|
|
142 |
|
|
|
143 |
<dt><span class="sect1">4.4. <a href=
|
|
|
144 |
"#idp5880304">Global customization
|
|
|
145 |
variables</a></span></dt>
|
|
|
146 |
|
|
|
147 |
<dd>
|
|
|
148 |
<dl>
|
|
|
149 |
<dt><span class="sect2">4.4.1. <a href=
|
|
|
150 |
"#idp5880944">sqlscshowstmts</a></span></dt>
|
|
|
151 |
|
|
|
152 |
<dt><span class="sect2">4.4.2. <a href=
|
|
|
153 |
"#idp5882288">sqlscnobell</a></span></dt>
|
|
|
154 |
</dl>
|
|
|
155 |
</dd>
|
|
|
156 |
|
|
|
157 |
<dt><span class="sect1">4.5. <a href=
|
|
|
158 |
"#PROGRAMMING.LINKING">Linking screens</a></span></dt>
|
|
|
159 |
|
|
|
160 |
<dd>
|
|
|
161 |
<dl>
|
|
|
162 |
<dt><span class="sect2">4.5.1. <a href=
|
|
|
163 |
"#idp5885136">sqlmasterslave</a></span></dt>
|
|
|
164 |
|
|
|
165 |
<dt><span class="sect2">4.5.2. <a href=
|
|
|
166 |
"#idp5889584">sqlslavemaster</a></span></dt>
|
|
|
167 |
</dl>
|
|
|
168 |
</dd>
|
|
|
169 |
|
|
|
170 |
<dt><span class="sect1">4.6. <a href=
|
|
|
171 |
"#idp5892256">Controlling the number of button
|
|
|
172 |
sets</a></span></dt>
|
|
|
173 |
|
|
|
174 |
<dt><span class="sect1">4.7. <a href=
|
|
|
175 |
"#idp5896944">Callback routines:</a></span></dt>
|
|
|
176 |
|
|
|
177 |
<dt><span class="sect1">4.8. <a href=
|
|
|
178 |
"#idp5910496">Visible internal
|
|
|
179 |
interfaces</a></span></dt>
|
|
|
180 |
|
|
|
181 |
<dt><span class="sect1">4.9. <a href=
|
|
|
182 |
"#idp5918240">Small utility routines</a></span></dt>
|
|
|
183 |
</dl>
|
|
|
184 |
</dd>
|
|
|
185 |
|
|
|
186 |
<dt><span class="chapter">5. <a href="#SQLGENERATION">SQL
|
|
|
187 |
generation</a></span></dt>
|
|
|
188 |
|
|
|
189 |
<dd>
|
|
|
190 |
<dl>
|
|
|
191 |
<dt><span class="sect1">5.1. <a href=
|
|
|
192 |
"#idp5922880">Query</a></span></dt>
|
|
|
193 |
|
|
|
194 |
<dt><span class="sect1">5.2. <a href=
|
|
|
195 |
"#idp5926928">Add</a></span></dt>
|
|
|
196 |
|
|
|
197 |
<dt><span class="sect1">5.3. <a href=
|
|
|
198 |
"#idp5931936">Update</a></span></dt>
|
|
|
199 |
|
|
|
200 |
<dt><span class="sect1">5.4. <a href=
|
|
|
201 |
"#idp5936832">Delete</a></span></dt>
|
|
|
202 |
|
|
|
203 |
<dt><span class="sect1">5.5. <a href=
|
|
|
204 |
"#idp5939296">Update issues</a></span></dt>
|
|
|
205 |
</dl>
|
|
|
206 |
</dd>
|
|
|
207 |
|
|
|
208 |
<dt><span class="chapter">6. <a href="#idp5945728">The tcsq
|
|
|
209 |
low level database access layer</a></span></dt>
|
|
|
210 |
|
|
|
211 |
<dd>
|
|
|
212 |
<dl>
|
|
|
213 |
<dt><span class="sect1">6.1. <a href=
|
|
|
214 |
"#idp5950048">Environment variables</a></span></dt>
|
|
|
215 |
|
|
|
216 |
<dt><span class="sect1">6.2. <a href="#idp5951904">API
|
|
|
217 |
calls</a></span></dt>
|
|
|
218 |
|
|
|
219 |
<dd>
|
|
|
220 |
<dl>
|
|
|
221 |
<dt><span class="sect2">6.2.1. <a href=
|
|
|
222 |
"#idp5952544">tcsqconnect</a></span></dt>
|
|
|
223 |
|
|
|
224 |
<dt><span class="sect2">6.2.2. <a href=
|
|
|
225 |
"#idp5954848">tcsquse</a></span></dt>
|
|
|
226 |
|
|
|
227 |
<dt><span class="sect2">6.2.3. <a href=
|
|
|
228 |
"#idp5957392">tcsqconuse</a></span></dt>
|
|
|
229 |
|
|
|
230 |
<dt><span class="sect2">6.2.4. <a href=
|
|
|
231 |
"#idp5959168">tcsqopensel</a></span></dt>
|
|
|
232 |
|
|
|
233 |
<dt><span class="sect2">6.2.5. <a href=
|
|
|
234 |
"#idp5961824">tcsqrew</a></span></dt>
|
|
|
235 |
|
|
|
236 |
<dt><span class="sect2">6.2.6. <a href=
|
|
|
237 |
"#idp5964096">tcsqclosel</a></span></dt>
|
|
|
238 |
|
|
|
239 |
<dt><span class="sect2">6.2.7. <a href=
|
|
|
240 |
"#idp5965872">tcsqnext</a></span></dt>
|
|
|
241 |
|
|
|
242 |
<dt><span class="sect2">6.2.8. <a href=
|
|
|
243 |
"#idp5967824">tcsqexec</a></span></dt>
|
|
|
244 |
|
|
|
245 |
<dt><span class="sect2">6.2.9. <a href=
|
|
|
246 |
"#idp5969696">tcsqdiscon</a></span></dt>
|
|
|
247 |
|
|
|
248 |
<dt><span class="sect2">6.2.10. <a href=
|
|
|
249 |
"#idp5971936">tcsqtabinfo</a></span></dt>
|
|
|
250 |
|
|
|
251 |
<dt><span class="sect2">6.2.11. <a href=
|
|
|
252 |
"#idp5973808">tcsqcolinfo</a></span></dt>
|
|
|
253 |
|
|
|
254 |
<dt><span class="sect2">6.2.12. <a href=
|
|
|
255 |
"#idp5976432">tcsqinsertid</a></span></dt>
|
|
|
256 |
|
|
|
257 |
<dt><span class="sect2">6.2.13. <a href=
|
|
|
258 |
"#idp5978048">tcsqquotequote</a></span></dt>
|
|
|
259 |
|
|
|
260 |
<dt><span class="sect2">6.2.14. <a href=
|
|
|
261 |
"#idp5979904">tcsqquoteblob</a></span></dt>
|
|
|
262 |
</dl>
|
|
|
263 |
</dd>
|
|
|
264 |
|
|
|
265 |
<dt><span class="sect1">6.3. <a href=
|
|
|
266 |
"#idp5981808">Programming example</a></span></dt>
|
|
|
267 |
</dl>
|
|
|
268 |
</dd>
|
|
|
269 |
|
|
|
270 |
<dt><span class="chapter">7. <a href="#SAMPLES">Sample
|
|
|
271 |
scripts</a></span></dt>
|
|
|
272 |
</dl>
|
|
|
273 |
</div>
|
|
|
274 |
|
|
|
275 |
<div class="chapter" title="Chapter 1. Preface">
|
|
|
276 |
<div class="titlepage">
|
|
|
277 |
<div>
|
|
|
278 |
<div>
|
|
|
279 |
<h2 class="title"><a name="idp2783584" id=
|
|
|
280 |
"idp2783584"></a>Chapter 1. Preface</h2>
|
|
|
281 |
</div>
|
|
|
282 |
</div>
|
|
|
283 |
</div>
|
|
|
284 |
|
|
|
285 |
<p>This document describes <span class=
|
|
|
286 |
"application">SQLScreens</span> release 1.2.1</p>
|
|
|
287 |
|
|
|
288 |
<p><span class="application">SQLScreens</span> is a TCL/TK
|
|
|
289 |
package allowing the easy creation of screen forms, for
|
|
|
290 |
querying and updating a relational database.</p>
|
|
|
291 |
|
|
|
292 |
<p><span class="application">SQLScreens</span> was primarily
|
|
|
293 |
designed to work with <span class="application">MySQL</span>
|
|
|
294 |
as a backend. It also works with <span class=
|
|
|
295 |
"application">SQLite</span>, and <span class=
|
|
|
296 |
"application">ODBC</span>. It might still work with
|
|
|
297 |
<span class="application">INFORMIX</span> and <span class=
|
|
|
298 |
"application">MSQL</span> (untested for a looong time).</p>
|
|
|
299 |
|
|
|
300 |
<p><span class="application">SQLScreens</span> is no match
|
|
|
301 |
for commercial application development tools. It is a very
|
|
|
302 |
simple tool to create ad-hoc query screens. We found it very
|
|
|
303 |
handy for creating our data-entry utilities in
|
|
|
304 |
CDKIT/MusicMaker (R.I.P), which is why we decided to publish
|
|
|
305 |
it, partly also because we use so much free software that we
|
|
|
306 |
felt compelled to contribute a little.</p>
|
|
|
307 |
|
|
|
308 |
<p>If you have struggled with (Y,N,Y,N,Y,Y,...) lists in the
|
|
|
309 |
<span class="application">MySQL</span> grant tables, you may
|
|
|
310 |
find <span class="application">SQLScreens</span> useful
|
|
|
311 |
:-)</p>
|
|
|
312 |
|
|
|
313 |
<p>Still interested ? Details <a class="link" href=
|
|
|
314 |
"#PREFACE.QUICKDESCRIPTION" title=
|
|
|
315 |
"1.1. Quick description">follow</a>. If you are reading
|
|
|
316 |
this online, you can have a look at the <a class="ulink"
|
|
|
317 |
href="http://lesbonscomptes.com/sqlscreens/sqlscdumps.html"
|
|
|
318 |
target="_top">screen dumps</a>.</p>
|
|
|
319 |
|
|
|
320 |
<div class="sect1" title="1.1. Quick description">
|
|
|
321 |
<div class="titlepage">
|
|
|
322 |
<div>
|
|
|
323 |
<div>
|
|
|
324 |
<h2 class="title" style="clear: both"><a name=
|
|
|
325 |
"PREFACE.QUICKDESCRIPTION" id=
|
|
|
326 |
"PREFACE.QUICKDESCRIPTION"></a>1.1. Quick
|
|
|
327 |
description</h2>
|
|
|
328 |
</div>
|
|
|
329 |
</div>
|
|
|
330 |
</div>
|
|
|
331 |
|
|
|
332 |
<p>A typical <span class="application">SQLScreens</span>
|
|
|
333 |
application will have a number of screens, each with
|
|
|
334 |
several entry/display fields. Each screen will be linked to
|
|
|
335 |
one or several database tables, each field to a column.</p>
|
|
|
336 |
|
|
|
337 |
<p>You create each screen by listing the column names that
|
|
|
338 |
you want to use.</p>
|
|
|
339 |
|
|
|
340 |
<p>If you do not even list the column names, all columns
|
|
|
341 |
are used, so that creating a screen to look at a table is 5
|
|
|
342 |
lines of TCL code. There is a sample program in the package
|
|
|
343 |
to do just this: give the database and table name and up
|
|
|
344 |
comes the screen (<code class=
|
|
|
345 |
"filename">tablescreen.tcl</code>).</p>
|
|
|
346 |
|
|
|
347 |
<p>Once the screens are created, you can query, insert,
|
|
|
348 |
update, and delete records by entering data in the fields
|
|
|
349 |
and clicking on the appropriate button (or using a keyboard
|
|
|
350 |
shortcut).</p>
|
|
|
351 |
|
|
|
352 |
<p><span class="application">SQLScreens</span> provides an
|
|
|
353 |
easy method to link the screens so that a change in one
|
|
|
354 |
screen will trigger a query in another one (for
|
|
|
355 |
master-detail relationships), or so that it will just
|
|
|
356 |
update the join column.</p>
|
|
|
357 |
|
|
|
358 |
<p>You can also:</p>
|
|
|
359 |
|
|
|
360 |
<div class="itemizedlist">
|
|
|
361 |
<ul class="itemizedlist" type="disc">
|
|
|
362 |
<li class="listitem">
|
|
|
363 |
<p>Create multiline text widgets to edit text
|
|
|
364 |
blobs.</p>
|
|
|
365 |
</li>
|
|
|
366 |
|
|
|
367 |
<li class="listitem">
|
|
|
368 |
<p>Display query results as a list linked to a detail
|
|
|
369 |
screen.</p>
|
|
|
370 |
</li>
|
|
|
371 |
|
|
|
372 |
<li class="listitem">
|
|
|
373 |
<p>And do many other things described a little
|
|
|
374 |
further.</p>
|
|
|
375 |
</li>
|
|
|
376 |
</ul>
|
|
|
377 |
</div>
|
|
|
378 |
|
|
|
379 |
<p>As all values for the fields are stored in an accessible
|
|
|
380 |
TCL array, it is quite easy to add code for data validation
|
|
|
381 |
or to show computed fields. There are provisions in the
|
|
|
382 |
package for calling external routines before and after the
|
|
|
383 |
database operations.</p>
|
|
|
384 |
|
|
|
385 |
<p><span class="application">SQLScreens</span> can be used
|
|
|
386 |
to build standalone database access applications, or to
|
|
|
387 |
embed a database-access screen in another application. For
|
|
|
388 |
example, in CDKIT, we managed a big musical database. We
|
|
|
389 |
used <span class="application">SQLScreens</span> mainly for
|
|
|
390 |
data-entry screens, but we also embedded it in the
|
|
|
391 |
audio-acquisition application, to establish the link
|
|
|
392 |
between the database and the audio files.</p>
|
|
|
393 |
|
|
|
394 |
<p>There are many other bells and whistles, but also a few
|
|
|
395 |
drawbacks:</p>
|
|
|
396 |
|
|
|
397 |
<div class="itemizedlist">
|
|
|
398 |
<ul class="itemizedlist" type="disc">
|
|
|
399 |
<li class="listitem">
|
|
|
400 |
<p>You have little control over field placement.
|
|
|
401 |
Fields are placed in a row-column grid managed by
|
|
|
402 |
Tk's grid geometry manager.</p>
|
|
|
403 |
</li>
|
|
|
404 |
|
|
|
405 |
<li class="listitem">
|
|
|
406 |
<p>The generated SQL is very basic, and you have
|
|
|
407 |
little control over it.</p>
|
|
|
408 |
</li>
|
|
|
409 |
|
|
|
410 |
<li class="listitem">
|
|
|
411 |
<p>The package may be dependant on assumptions that
|
|
|
412 |
we made, which may not match your environment. Please
|
|
|
413 |
try on a test database, not your production one !
|
|
|
414 |
When there are no primary keys, the package is
|
|
|
415 |
crippled.</p>
|
|
|
416 |
</li>
|
|
|
417 |
|
|
|
418 |
<li class="listitem">
|
|
|
419 |
<p>There is no real support for structured fields
|
|
|
420 |
like date/time (that is, you can use date fields, but
|
|
|
421 |
the package will not check the format). As we mainly
|
|
|
422 |
used text and number fields in CDKIT, there are
|
|
|
423 |
probably more bugs with other types of fields (less
|
|
|
424 |
testing).</p>
|
|
|
425 |
</li>
|
|
|
426 |
|
|
|
427 |
<li class="listitem">
|
|
|
428 |
<p>You can't specify null values when querying (the
|
|
|
429 |
fields with no data are just not used). Operators
|
|
|
430 |
like '>' or '<' can only be used for non-text
|
|
|
431 |
fields .</p>
|
|
|
432 |
</li>
|
|
|
433 |
|
|
|
434 |
<li class="listitem">
|
|
|
435 |
<p>The screens are not pretty !</p>
|
|
|
436 |
</li>
|
|
|
437 |
|
|
|
438 |
<li class="listitem">
|
|
|
439 |
<p>Etc... Etc...</p>
|
|
|
440 |
</li>
|
|
|
441 |
</ul>
|
|
|
442 |
</div>
|
|
|
443 |
|
|
|
444 |
<p>This said, the software is free and we are open to
|
|
|
445 |
suggestions to improve it.</p>
|
|
|
446 |
|
|
|
447 |
<p>If you want a quick idea of what it does, do the
|
|
|
448 |
installation, have a look at the <code class=
|
|
|
449 |
"filename">tablescreen.tcl</code> file in the samples
|
|
|
450 |
directory, set the host and user name (depending on the
|
|
|
451 |
backend type), and point it to any table, like:</p>
|
|
|
452 |
<pre class="programlisting">
|
|
|
453 |
tablescreen.tcl dbname tablename
|
|
|
454 |
|
|
|
455 |
</pre>
|
|
|
456 |
|
|
|
457 |
<p>This will create a screen with fields for all columns in
|
|
|
458 |
the table (you may need to adjust the host and user names
|
|
|
459 |
in the script or the environment to get the right
|
|
|
460 |
permissions).</p>
|
|
|
461 |
</div>
|
|
|
462 |
</div>
|
|
|
463 |
|
|
|
464 |
<div class="chapter" title="Chapter 2. Installation">
|
|
|
465 |
<div class="titlepage">
|
|
|
466 |
<div>
|
|
|
467 |
<div>
|
|
|
468 |
<h2 class="title"><a name="INSTALLATION" id=
|
|
|
469 |
"INSTALLATION"></a>Chapter 2. Installation</h2>
|
|
|
470 |
</div>
|
|
|
471 |
</div>
|
|
|
472 |
</div>
|
|
|
473 |
|
|
|
474 |
<div class="sect1" title=
|
|
|
475 |
"2.1. External software needed">
|
|
|
476 |
<div class="titlepage">
|
|
|
477 |
<div>
|
|
|
478 |
<div>
|
|
|
479 |
<h2 class="title" style="clear: both"><a name=
|
|
|
480 |
"INSTALLATION.EXTERNAL" id=
|
|
|
481 |
"INSTALLATION.EXTERNAL"></a>2.1. External
|
|
|
482 |
software needed</h2>
|
|
|
483 |
</div>
|
|
|
484 |
</div>
|
|
|
485 |
</div>
|
|
|
486 |
|
|
|
487 |
<p>To use <span class="application">SQLScreens</span>, you
|
|
|
488 |
will need a number of external software packages:</p>
|
|
|
489 |
|
|
|
490 |
<div class="itemizedlist">
|
|
|
491 |
<ul class="itemizedlist" type="disc">
|
|
|
492 |
<li class="listitem">
|
|
|
493 |
<p>TCL/TK. Don't try to use anything earlier than
|
|
|
494 |
8.0. All later releases are supposed to work. If you
|
|
|
495 |
are running a recent FreeBSD or Linux, you just need
|
|
|
496 |
to install the packages. Else, you can get the source
|
|
|
497 |
distributions from <a class="ulink" href=
|
|
|
498 |
"http://tcl.activestate.com/" target="_top">the main
|
|
|
499 |
TCL site</a> . TCL and TK are very easy to build.</p>
|
|
|
500 |
</li>
|
|
|
501 |
</ul>
|
|
|
502 |
</div>
|
|
|
503 |
|
|
|
504 |
<p>In order to access the databases, the basic TCL
|
|
|
505 |
interpreter must be augmented with a database access
|
|
|
506 |
module:</p>
|
|
|
507 |
|
|
|
508 |
<div class="itemizedlist">
|
|
|
509 |
<ul class="itemizedlist" type="disc">
|
|
|
510 |
<li class="listitem">
|
|
|
511 |
<p>For <span class="application">MySQL</span>, a
|
|
|
512 |
modified version of the msqltcl package by Hakan
|
|
|
513 |
Soderstrom is included in the distribution
|
|
|
514 |
(<code class="filename">mysqltcl.c</code>).</p>
|
|
|
515 |
</li>
|
|
|
516 |
|
|
|
517 |
<li class="listitem">
|
|
|
518 |
<p>The original msqltcl can be used for accessing
|
|
|
519 |
MSQL databases.</p>
|
|
|
520 |
</li>
|
|
|
521 |
|
|
|
522 |
<li class="listitem">
|
|
|
523 |
<p>For UNIX ODBC you will need <a class="ulink" href=
|
|
|
524 |
"http://sourceforge.net/projects/tclodbc" target=
|
|
|
525 |
"_top">tclodbc</a>, and:</p>
|
|
|
526 |
|
|
|
527 |
<div class="itemizedlist">
|
|
|
528 |
<ul class="itemizedlist" type="circle">
|
|
|
529 |
<li class="listitem">
|
|
|
530 |
<p>An ODBC driver manager: under UNIX, we
|
|
|
531 |
tested <a class="ulink" href=
|
|
|
532 |
"http://www.iodbc.org/" target=
|
|
|
533 |
"_top">iODBC</a>, but <a class="ulink" href=
|
|
|
534 |
"http://www.unixodbc.org/" target=
|
|
|
535 |
"_top">unixODBC</a> should probably be OK
|
|
|
536 |
too.</p>
|
|
|
537 |
</li>
|
|
|
538 |
|
|
|
539 |
<li class="listitem">
|
|
|
540 |
<p>The driver for your database. For
|
|
|
541 |
<span class="application">MySQL</span>, this
|
|
|
542 |
would be <a class="ulink" href=
|
|
|
543 |
"http://www.mysql.com/products/connector/odbc/"
|
|
|
544 |
target="_top">myodbc</a>.</p>
|
|
|
545 |
</li>
|
|
|
546 |
</ul>
|
|
|
547 |
</div>
|
|
|
548 |
</li>
|
|
|
549 |
|
|
|
550 |
<li class="listitem">
|
|
|
551 |
<p>For INFORMIX you will need the isqltcl package by
|
|
|
552 |
Srinivas Kumar. It has become a little difficult to
|
|
|
553 |
find lately and there is a copy on the <a class=
|
|
|
554 |
"ulink" href=
|
|
|
555 |
"http://www.lesbonscomptes.com/sqlscreens/sqlscdownload.html"
|
|
|
556 |
target="_top">download page</a>.</p>
|
|
|
557 |
</li>
|
|
|
558 |
</ul>
|
|
|
559 |
</div>
|
|
|
560 |
|
|
|
561 |
<p>The <span class="application">SQLScreens</span>
|
|
|
562 |
<a class="ulink" href=
|
|
|
563 |
"http://www.lesbonscomptes.com/sqlscreens/sqlscdownload.html"
|
|
|
564 |
target="_top">download page</a> has pointers or copies for
|
|
|
565 |
some of these elements.</p>
|
|
|
566 |
|
|
|
567 |
<p>Only the direct <span class="application">MySQL</span>
|
|
|
568 |
and <span class="application">SQLite</span> backends have
|
|
|
569 |
been tested lately, and there may be minor problems with
|
|
|
570 |
the others.</p>
|
|
|
571 |
</div>
|
|
|
572 |
|
|
|
573 |
<div class="sect1" title="2.2. Installation">
|
|
|
574 |
<div class="titlepage">
|
|
|
575 |
<div>
|
|
|
576 |
<div>
|
|
|
577 |
<h2 class="title" style="clear: both"><a name=
|
|
|
578 |
"idp143456" id=
|
|
|
579 |
"idp143456"></a>2.2. Installation</h2>
|
|
|
580 |
</div>
|
|
|
581 |
</div>
|
|
|
582 |
</div>
|
|
|
583 |
|
|
|
584 |
<p>The package comes as a gzipped tar file named something
|
|
|
585 |
like <code class=
|
|
|
586 |
"filename">sqlscreens-X.Y.Z.tar.gz</code>.</p>
|
|
|
587 |
|
|
|
588 |
<p>Unpack the file:</p>
|
|
|
589 |
<pre class="programlisting">
|
|
|
590 |
gunzip < <em class=
|
|
|
591 |
"replaceable"><code>sqlscreens-X.Y.Z.tar.gz</code></em> | tar xvf -
|
|
|
592 |
</pre>
|
|
|
593 |
|
|
|
594 |
<p>This will create a top directory named <code class=
|
|
|
595 |
"filename">sqlscreens-X.Y.Z</code>.</p>
|
|
|
596 |
|
|
|
597 |
<p>X is the major release number. Y is the minor release. Z
|
|
|
598 |
is the bug fix release number. Don't make too much of
|
|
|
599 |
it...</p>
|
|
|
600 |
|
|
|
601 |
<p>First, if needed, compile and install TCL and TK (untar;
|
|
|
602 |
cd tcl8.../unix; configure; make; make install, same for
|
|
|
603 |
tk).</p>
|
|
|
604 |
|
|
|
605 |
<p>The next step is to add database-access capability to
|
|
|
606 |
the standard TCL/TK wish interpreter. This can be done in
|
|
|
607 |
several ways:</p>
|
|
|
608 |
|
|
|
609 |
<div class="itemizedlist">
|
|
|
610 |
<ul class="itemizedlist" type="disc">
|
|
|
611 |
<li class="listitem">By statically linking the database
|
|
|
612 |
access module (e.g. mysqltcl or isqltcl) with the
|
|
|
613 |
interpreter.</li>
|
|
|
614 |
|
|
|
615 |
<li class="listitem">By using the <span class=
|
|
|
616 |
"application">TCL</span> load facility and a shared
|
|
|
617 |
library. The dynamic version sometimes need some manual
|
|
|
618 |
tweaking to work.</li>
|
|
|
619 |
|
|
|
620 |
<li class="listitem">By loading an external package
|
|
|
621 |
that itself does whatever is needed (e.g. <code class=
|
|
|
622 |
"literal">package require sqlite3)</code></li>
|
|
|
623 |
</ul>
|
|
|
624 |
</div>
|
|
|
625 |
|
|
|
626 |
<p><span class="application">MySQL</span> support is
|
|
|
627 |
managed by the <code class="filename">Makefile</code> in
|
|
|
628 |
the <span class="application">SQLScreens</span> directory.
|
|
|
629 |
You can disable <span class="application">MySQL</span>
|
|
|
630 |
support (and the need to install the client library) by
|
|
|
631 |
using option <code class="literal">--disable-mysql</code>
|
|
|
632 |
to the <span class=
|
|
|
633 |
"command"><strong>configure</strong></span> script.</p>
|
|
|
634 |
|
|
|
635 |
<p>For <span class="application">SQLite</span>, just
|
|
|
636 |
install the <span class="application">SQLite</span> TCL
|
|
|
637 |
package (which may be named something like <code class=
|
|
|
638 |
"literal">libsqlite3-tcl</code>).</p>
|
|
|
639 |
|
|
|
640 |
<p>For <span class="application">ODBC</span>, you should
|
|
|
641 |
first install the driver manager, the driver(s) you need
|
|
|
642 |
and the tclodbc TCL extension. Follow the instructions in
|
|
|
643 |
each package.</p>
|
|
|
644 |
|
|
|
645 |
<p>For using <span class="application">isqltcl</span> and
|
|
|
646 |
INFORMIX, follow the installation instructions inside the
|
|
|
647 |
<span class="application">isqltcl</span> package to
|
|
|
648 |
generate the interpreter.</p>
|
|
|
649 |
|
|
|
650 |
<div class="sect2" title=
|
|
|
651 |
"2.2.1. Generating a wish interpreter with MySQL support:">
|
|
|
652 |
<div class="titlepage">
|
|
|
653 |
<div>
|
|
|
654 |
<div>
|
|
|
655 |
<h3 class="title"><a name="idp5635776" id=
|
|
|
656 |
"idp5635776"></a>2.2.1. Generating a wish
|
|
|
657 |
interpreter with <span class=
|
|
|
658 |
"application">MySQL</span> support:</h3>
|
|
|
659 |
</div>
|
|
|
660 |
</div>
|
|
|
661 |
</div>
|
|
|
662 |
|
|
|
663 |
<p>You do not need this if you are working with
|
|
|
664 |
<span class="application">SQLite</span> only. Just give a
|
|
|
665 |
<code class="literal">--disable-mysql</code> argument to
|
|
|
666 |
<span class=
|
|
|
667 |
"command"><strong>configure</strong></span>.</p>
|
|
|
668 |
|
|
|
669 |
<p>Both the static and dynamic load methods are supported
|
|
|
670 |
by the <span class="application">SQLScreens</span> build
|
|
|
671 |
tools. Only Linux, SOLARIS, and FreeBSD have been tested,
|
|
|
672 |
things are not guaranteed to work on other systems. The
|
|
|
673 |
Makefile generated by configure is small, it should be
|
|
|
674 |
easy to adjust if needed.</p>
|
|
|
675 |
|
|
|
676 |
<p>The configuration script use the <span class=
|
|
|
677 |
"command"><strong>mysql_config</strong></span> command to
|
|
|
678 |
locate the <span class="application">MySQL</span> client
|
|
|
679 |
library and include files. It should be accessible in
|
|
|
680 |
your <code class="literal">PATH</code>.</p>
|
|
|
681 |
|
|
|
682 |
<p>When you are ready:</p>
|
|
|
683 |
|
|
|
684 |
<div class="orderedlist">
|
|
|
685 |
<ol class="orderedlist" type="1">
|
|
|
686 |
<li class="listitem">
|
|
|
687 |
<p><code class="literal">cd</code> to the
|
|
|
688 |
<span class="application">SQLScreens</span>
|
|
|
689 |
directory, and type <code class=
|
|
|
690 |
"literal">./configure</code>.</p>
|
|
|
691 |
</li>
|
|
|
692 |
|
|
|
693 |
<li class="listitem">
|
|
|
694 |
<p>Type <code class="literal">make</code> to
|
|
|
695 |
compile and link the <span class=
|
|
|
696 |
"command"><strong>mysqlwish</strong></span>
|
|
|
697 |
interpreter and the shared library. The shared
|
|
|
698 |
library link may produce error messages, see
|
|
|
699 |
below.</p>
|
|
|
700 |
</li>
|
|
|
701 |
|
|
|
702 |
<li class="listitem">
|
|
|
703 |
<p>Type <code class="literal">make install</code>
|
|
|
704 |
to install the package. This will create a
|
|
|
705 |
<code class="filename">$TK_PREFIX/lib/sqlsc</code>
|
|
|
706 |
directory and copy the shared library and TCL code
|
|
|
707 |
there. It will also copy <code class=
|
|
|
708 |
"filename">mysqlwish</code> to <code class=
|
|
|
709 |
"filename">$TK_PREFIX/bin</code>. TK_PREFIX is
|
|
|
710 |
taken from the tkConfig.sh script for your
|
|
|
711 |
<span class="command"><strong>wish</strong></span>
|
|
|
712 |
interpreter. You can change it by typing</p>
|
|
|
713 |
<pre class="programlisting">
|
|
|
714 |
make install TK_PREFIX=<em class=
|
|
|
715 |
"replaceable"><code>yourdest</code></em>
|
|
|
716 |
</pre>
|
|
|
717 |
|
|
|
718 |
<p>instead, but you might then have to adjust your
|
|
|
719 |
TCLLIBPATH for the package to be found. If the
|
|
|
720 |
shared library link failed at the previous step, or
|
|
|
721 |
if you get error messages about unfound symbols
|
|
|
722 |
during installation, either type <code class=
|
|
|
723 |
"literal">make install-static</code> to just
|
|
|
724 |
install the static version, or review the
|
|
|
725 |
README-DYNAMIC file where there is some more
|
|
|
726 |
information about dynamic libraries issues. If you
|
|
|
727 |
are in a hurry or/and are not used to building
|
|
|
728 |
shared libraries, you might just want to use the
|
|
|
729 |
static version. And yes, I should use modern TCL
|
|
|
730 |
extension tools, and if someone wants to fix this,
|
|
|
731 |
I'll gladly welcome a patch.</p>
|
|
|
732 |
</li>
|
|
|
733 |
</ol>
|
|
|
734 |
</div>
|
|
|
735 |
|
|
|
736 |
<p>If you use TCL with other statically linked
|
|
|
737 |
extensions, and want to use the same interpreter with
|
|
|
738 |
<span class="application">SQLScreens</span>, you will
|
|
|
739 |
have to add the <code class=
|
|
|
740 |
"function">Mysqltcl_Init</code> call to your usual
|
|
|
741 |
<code class="filename">tkAppInit.c</code> file and modify
|
|
|
742 |
your <code class="filename">Makefile</code> to link with
|
|
|
743 |
<code class="filename">mysqltcl.o</code>. You have
|
|
|
744 |
probably been through this already. Have a look at the
|
|
|
745 |
included <code class="filename">tkAppinit.c</code></p>
|
|
|
746 |
</div>
|
|
|
747 |
</div>
|
|
|
748 |
</div>
|
|
|
749 |
|
|
|
750 |
<div class="chapter" title=
|
|
|
751 |
"Chapter 3. Using SQLScreens">
|
|
|
752 |
<div class="titlepage">
|
|
|
753 |
<div>
|
|
|
754 |
<div>
|
|
|
755 |
<h2 class="title"><a name="USING" id=
|
|
|
756 |
"USING"></a>Chapter 3. Using <span class=
|
|
|
757 |
"application">SQLScreens</span></h2>
|
|
|
758 |
</div>
|
|
|
759 |
</div>
|
|
|
760 |
</div>
|
|
|
761 |
|
|
|
762 |
<p>The following is organized more like a reference manual,
|
|
|
763 |
there is little tutorial material. You may want to look at
|
|
|
764 |
the <a class="link" href="#SAMPLES" title=
|
|
|
765 |
"Chapter 7. Sample scripts">sample scripts</a> to
|
|
|
766 |
get a quick idea.</p>
|
|
|
767 |
|
|
|
768 |
<div class="sect1" title="3.1. Environment variables">
|
|
|
769 |
<div class="titlepage">
|
|
|
770 |
<div>
|
|
|
771 |
<div>
|
|
|
772 |
<h2 class="title" style="clear: both"><a name=
|
|
|
773 |
"USING.ENVIR" id=
|
|
|
774 |
"USING.ENVIR"></a>3.1. Environment
|
|
|
775 |
variables</h2>
|
|
|
776 |
</div>
|
|
|
777 |
</div>
|
|
|
778 |
</div>
|
|
|
779 |
|
|
|
780 |
<p>The <code class="literal">SQLDBTYPE</code> environment
|
|
|
781 |
variable decides what database code is going to be used. It
|
|
|
782 |
has several possible values:</p>
|
|
|
783 |
|
|
|
784 |
<div class="itemizedlist">
|
|
|
785 |
<ul class="itemizedlist" type="disc">
|
|
|
786 |
<li class="listitem">
|
|
|
787 |
<p><code class="literal">MYSQL</code> to access a
|
|
|
788 |
<span class="application">MySQL</span> database.</p>
|
|
|
789 |
</li>
|
|
|
790 |
|
|
|
791 |
<li class="listitem">
|
|
|
792 |
<p><code class="literal">SQLITE3</code> to access a
|
|
|
793 |
<span class="application">SQLite</span> database.</p>
|
|
|
794 |
</li>
|
|
|
795 |
|
|
|
796 |
<li class="listitem">
|
|
|
797 |
<p><code class="literal">ODBC</code> to use an ODBC
|
|
|
798 |
driver manager.</p>
|
|
|
799 |
</li>
|
|
|
800 |
|
|
|
801 |
<li class="listitem">
|
|
|
802 |
<p><code class="literal">INFORMIX</code> to access an
|
|
|
803 |
INFORMIX database.</p>
|
|
|
804 |
</li>
|
|
|
805 |
|
|
|
806 |
<li class="listitem">
|
|
|
807 |
<p><code class="literal">MSQL</code> to access an
|
|
|
808 |
MSQL database.</p>
|
|
|
809 |
</li>
|
|
|
810 |
</ul>
|
|
|
811 |
</div>
|
|
|
812 |
|
|
|
813 |
<p>The default if the variable is not set is to use
|
|
|
814 |
<span class="application">MySQL</span>. Don't depend on
|
|
|
815 |
it.</p>
|
|
|
816 |
|
|
|
817 |
<p>This variable can be set inside the script, before the
|
|
|
818 |
first call to <code class="function">sqlscreen</code>. (It
|
|
|
819 |
is set to <code class="literal">MYSQL</code> inside the
|
|
|
820 |
sample scripts). Of course, the value of this variable must
|
|
|
821 |
be consistent with what extensions are available to the TCL
|
|
|
822 |
interpreter (See <a class="link" href="#INSTALLATION"
|
|
|
823 |
title="Chapter 2. Installation">installation</a>).</p>
|
|
|
824 |
|
|
|
825 |
<p>The <code class="literal">SQLSCLOG</code> environment
|
|
|
826 |
variable can be set to the name of a file where
|
|
|
827 |
<span class="application">SQLScreens</span> will log the
|
|
|
828 |
SQL statements it executes. The default is to log to
|
|
|
829 |
stdout.</p>
|
|
|
830 |
|
|
|
831 |
<p>The <code class="literal">SQLSCHOST</code>, <code class=
|
|
|
832 |
"literal">SQLSCUSER</code>, and <code class=
|
|
|
833 |
"literal">SQLSCPASSWORD</code> variables can be used to set
|
|
|
834 |
the connection parameters. These are only used by the
|
|
|
835 |
sample scripts, not the core package.</p>
|
|
|
836 |
|
|
|
837 |
<p><code class="literal">MYSQL_TCP_PORT</code> should be
|
|
|
838 |
used if you need to change the default <span class=
|
|
|
839 |
"application">MySQL</span> connection port.</p>
|
|
|
840 |
</div>
|
|
|
841 |
|
|
|
842 |
<div class="sect1" title="3.2. Buttons">
|
|
|
843 |
<div class="titlepage">
|
|
|
844 |
<div>
|
|
|
845 |
<div>
|
|
|
846 |
<h2 class="title" style="clear: both"><a name=
|
|
|
847 |
"idp5682032" id=
|
|
|
848 |
"idp5682032"></a>3.2. Buttons</h2>
|
|
|
849 |
</div>
|
|
|
850 |
</div>
|
|
|
851 |
</div>
|
|
|
852 |
|
|
|
853 |
<p>Each screen has a set of buttons to perform the
|
|
|
854 |
following operations:</p>
|
|
|
855 |
|
|
|
856 |
<div class="variablelist">
|
|
|
857 |
<dl>
|
|
|
858 |
<dt><span class="term">Query</span></dt>
|
|
|
859 |
|
|
|
860 |
<dd>
|
|
|
861 |
<p>starts a SELECT. The WHERE clause is built with
|
|
|
862 |
the values currently shown on the screen and the
|
|
|
863 |
nodisplay fields).</p>
|
|
|
864 |
</dd>
|
|
|
865 |
|
|
|
866 |
<dt><span class="term">Next</span></dt>
|
|
|
867 |
|
|
|
868 |
<dd>
|
|
|
869 |
<p>fetches the next record in the current query</p>
|
|
|
870 |
</dd>
|
|
|
871 |
|
|
|
872 |
<dt><span class="term">Rewind</span></dt>
|
|
|
873 |
|
|
|
874 |
<dd>
|
|
|
875 |
<p>gets back to the first record in the current
|
|
|
876 |
query. This has different effects depending on the
|
|
|
877 |
database: in INFORMIX this actually reruns the query
|
|
|
878 |
so that changes in the database will be visible. With
|
|
|
879 |
<span class="application">MySQL</span>, this just
|
|
|
880 |
rewinds the local result buffer.</p>
|
|
|
881 |
</dd>
|
|
|
882 |
|
|
|
883 |
<dt><span class="term">Reset</span></dt>
|
|
|
884 |
|
|
|
885 |
<dd>
|
|
|
886 |
<p>clears all visible and hidden fields in the
|
|
|
887 |
screen.</p>
|
|
|
888 |
</dd>
|
|
|
889 |
|
|
|
890 |
<dt><span class="term">Add</span></dt>
|
|
|
891 |
|
|
|
892 |
<dd>
|
|
|
893 |
<p>inserts the current values. There is special
|
|
|
894 |
handling for auto_increment fields, see <a class=
|
|
|
895 |
"link" href="#SQLGENERATION" title=
|
|
|
896 |
"Chapter 5. SQL generation">SQL
|
|
|
897 |
generation</a></p>
|
|
|
898 |
</dd>
|
|
|
899 |
|
|
|
900 |
<dt><span class="term">Update</span></dt>
|
|
|
901 |
|
|
|
902 |
<dd>
|
|
|
903 |
<p>updates the row according to the current values
|
|
|
904 |
(how the WHERE clause is built is described later,
|
|
|
905 |
see <a class="link" href=
|
|
|
906 |
"#PROGRAMMING.ARRAYENTRIES.FATTRIBUTES.UPDATEINDEX"
|
|
|
907 |
title="4.3.3.6. updateindex">updateindex</a> and
|
|
|
908 |
<a class="link" href="#SQLGENERATION" title=
|
|
|
909 |
"Chapter 5. SQL generation">Sql
|
|
|
910 |
generation</a>).</p>
|
|
|
911 |
</dd>
|
|
|
912 |
|
|
|
913 |
<dt><span class="term">Delete</span></dt>
|
|
|
914 |
|
|
|
915 |
<dd>
|
|
|
916 |
<p>deletes the row(s) selected by the current values.
|
|
|
917 |
It will prompt for confirmation if more than one row
|
|
|
918 |
would be affected.</p>
|
|
|
919 |
</dd>
|
|
|
920 |
</dl>
|
|
|
921 |
</div>
|
|
|
922 |
|
|
|
923 |
<p>The Update and Add buttons may not exist on all screens
|
|
|
924 |
(some screens may be set up only for querying).</p>
|
|
|
925 |
|
|
|
926 |
<p>Delete is not created by default (See <a class="link"
|
|
|
927 |
href="#PROGRAMMING.ARRAYENTRIES.SATTRIBUTES.ALLOWDELETE"
|
|
|
928 |
title="4.3.2.2. allowdelete">allowdelete</a> ).</p>
|
|
|
929 |
|
|
|
930 |
<p>The <a class="link" href="#SQLGENERATION" title=
|
|
|
931 |
"Chapter 5. SQL generation">SQL generation</a>
|
|
|
932 |
section describes how the SQL statements are generated for
|
|
|
933 |
the different actions.</p>
|
|
|
934 |
</div>
|
|
|
935 |
|
|
|
936 |
<div class="sect1" title="3.3. Keyboard shortcuts">
|
|
|
937 |
<div class="titlepage">
|
|
|
938 |
<div>
|
|
|
939 |
<div>
|
|
|
940 |
<h2 class="title" style="clear: both"><a name=
|
|
|
941 |
"idp5700560" id="idp5700560"></a>3.3. Keyboard
|
|
|
942 |
shortcuts</h2>
|
|
|
943 |
</div>
|
|
|
944 |
</div>
|
|
|
945 |
</div>
|
|
|
946 |
|
|
|
947 |
<p>Often, when doing data entry, it is inconvenient to have
|
|
|
948 |
to reach for the mouse to perform an action. A set of
|
|
|
949 |
keyboard shortcuts is provided to make things smoother:</p>
|
|
|
950 |
|
|
|
951 |
<div class="variablelist">
|
|
|
952 |
<dl>
|
|
|
953 |
<dt><span class="term"><TAB></span></dt>
|
|
|
954 |
|
|
|
955 |
<dd>
|
|
|
956 |
<p>goes to the next field in the screen.</p>
|
|
|
957 |
</dd>
|
|
|
958 |
|
|
|
959 |
<dt><span class="term"><CR></span></dt>
|
|
|
960 |
|
|
|
961 |
<dd>
|
|
|
962 |
<p>in any field will start a SELECT.</p>
|
|
|
963 |
</dd>
|
|
|
964 |
|
|
|
965 |
<dt><span class="term"><ESC>n</span></dt>
|
|
|
966 |
|
|
|
967 |
<dd>
|
|
|
968 |
<p>will fetch the next row.</p>
|
|
|
969 |
</dd>
|
|
|
970 |
|
|
|
971 |
<dt><span class="term"><ESC>r</span></dt>
|
|
|
972 |
|
|
|
973 |
<dd>
|
|
|
974 |
<p>will rewind the query.</p>
|
|
|
975 |
</dd>
|
|
|
976 |
|
|
|
977 |
<dt><span class="term"><ESC>a</span></dt>
|
|
|
978 |
|
|
|
979 |
<dd>
|
|
|
980 |
<p>will start an INSERT.</p>
|
|
|
981 |
</dd>
|
|
|
982 |
|
|
|
983 |
<dt><span class="term"><ESC>u</span></dt>
|
|
|
984 |
|
|
|
985 |
<dd>
|
|
|
986 |
<p>will start an UPDATE.</p>
|
|
|
987 |
</dd>
|
|
|
988 |
|
|
|
989 |
<dt><span class="term"><ESC>w</span></dt>
|
|
|
990 |
|
|
|
991 |
<dd>
|
|
|
992 |
<p>will reset the current screen (clear all
|
|
|
993 |
fields).</p>
|
|
|
994 |
</dd>
|
|
|
995 |
</dl>
|
|
|
996 |
</div>
|
|
|
997 |
|
|
|
998 |
<p>The Update and Add shortcuts will have no effects in
|
|
|
999 |
query only screens. There is no Delete shortcut.</p>
|
|
|
1000 |
</div>
|
|
|
1001 |
|
|
|
1002 |
<div class="sect1" title=
|
|
|
1003 |
"3.4. Special characters in fields">
|
|
|
1004 |
<div class="titlepage">
|
|
|
1005 |
<div>
|
|
|
1006 |
<div>
|
|
|
1007 |
<h2 class="title" style="clear: both"><a name=
|
|
|
1008 |
"idp5713552" id="idp5713552"></a>3.4. Special
|
|
|
1009 |
characters in fields</h2>
|
|
|
1010 |
</div>
|
|
|
1011 |
</div>
|
|
|
1012 |
</div>
|
|
|
1013 |
|
|
|
1014 |
<p>The '<' and '>' characters will be interpreted
|
|
|
1015 |
when entered as the first character in a non-string field.
|
|
|
1016 |
Actually, if such a field begins with '<', '>', or
|
|
|
1017 |
'=', whatever is entered in it will be included in the
|
|
|
1018 |
WHERE clause when querying. For example:</p>
|
|
|
1019 |
|
|
|
1020 |
<p>If you enter <code class="literal">>10</code> in a
|
|
|
1021 |
field named <code class="literal">quantity</code>, a
|
|
|
1022 |
<code class="literal">quantity > 10</code> condition
|
|
|
1023 |
will be inserted in the WHERE clause. You could also enter
|
|
|
1024 |
<code class="literal">>10 AND quantity < 20</code>,
|
|
|
1025 |
or whatever condition you need. (See also <a class="link"
|
|
|
1026 |
href="#SQLGENERATION" title=
|
|
|
1027 |
"Chapter 5. SQL generation">the paragraph about
|
|
|
1028 |
SQL generation</a> ).</p>
|
|
|
1029 |
</div>
|
|
|
1030 |
</div>
|
|
|
1031 |
|
|
|
1032 |
<div class="chapter" title=
|
|
|
1033 |
"Chapter 4. Programming Interface">
|
|
|
1034 |
<div class="titlepage">
|
|
|
1035 |
<div>
|
|
|
1036 |
<div>
|
|
|
1037 |
<h2 class="title"><a name="PROGRAMMING" id=
|
|
|
1038 |
"PROGRAMMING"></a>Chapter 4. Programming
|
|
|
1039 |
Interface</h2>
|
|
|
1040 |
</div>
|
|
|
1041 |
</div>
|
|
|
1042 |
</div>
|
|
|
1043 |
|
|
|
1044 |
<div class="sect1" title="4.1. Overview">
|
|
|
1045 |
<div class="titlepage">
|
|
|
1046 |
<div>
|
|
|
1047 |
<div>
|
|
|
1048 |
<h2 class="title" style="clear: both"><a name=
|
|
|
1049 |
"PROGRAMMING.OVERVIEW" id=
|
|
|
1050 |
"PROGRAMMING.OVERVIEW"></a>4.1. Overview</h2>
|
|
|
1051 |
</div>
|
|
|
1052 |
</div>
|
|
|
1053 |
</div>
|
|
|
1054 |
|
|
|
1055 |
<p>All exchanges between the package and the user
|
|
|
1056 |
application are made through TCL arrays that hold all data
|
|
|
1057 |
and parameters. There is one such array for every screen.
|
|
|
1058 |
The array name is not significant except that it will be
|
|
|
1059 |
used for the screen title. We often use the table name, but
|
|
|
1060 |
this is not mandatory.</p>
|
|
|
1061 |
|
|
|
1062 |
<p>The basic idea is that you set values in the array and
|
|
|
1063 |
then call <code class="literal">sqlscreen arrayname</code>
|
|
|
1064 |
to create the screen.</p>
|
|
|
1065 |
|
|
|
1066 |
<p>The <span class="emphasis"><em>application</em></span>
|
|
|
1067 |
can be reduced to a main program to initialize and call
|
|
|
1068 |
<span class="application">SQLScreens</span>, or it may more
|
|
|
1069 |
complex and use <span class="application">SQLScreens</span>
|
|
|
1070 |
as a utility module.</p>
|
|
|
1071 |
|
|
|
1072 |
<p>The array entries define what tables/columns will be
|
|
|
1073 |
used, how the screen will look like, etc... A minimal
|
|
|
1074 |
program to display a default query/entry screen for table
|
|
|
1075 |
<code class="literal">mytable</code> in database
|
|
|
1076 |
<code class="literal">test</code> on the local host might
|
|
|
1077 |
look like the following:</p>
|
|
|
1078 |
<pre class="programlisting">
|
|
|
1079 |
#!/usr/local/bin/wish8.4
|
|
|
1080 |
|
|
|
1081 |
package require sqlsc
|
|
|
1082 |
|
|
|
1083 |
set mytable(window) .t
|
|
|
1084 |
set mytable(database) test
|
|
|
1085 |
set mytable(table) mytable
|
|
|
1086 |
sqlscreen mytable
|
|
|
1087 |
</pre>
|
|
|
1088 |
|
|
|
1089 |
<p>Many more attributes and options can be set in the
|
|
|
1090 |
array. You could also define callback functions which will
|
|
|
1091 |
be called before and after the database accesses, to give
|
|
|
1092 |
you an opportunity for checking what's happening, possibly
|
|
|
1093 |
modify values, or block the operation if something is
|
|
|
1094 |
wrong.</p>
|
|
|
1095 |
|
|
|
1096 |
<p>In the following, we shall use the example of a database
|
|
|
1097 |
named “orderdb”, with a table named
|
|
|
1098 |
“customers”, with columns named
|
|
|
1099 |
“custid”, “custname”, and
|
|
|
1100 |
“custfirstname”, and a table named
|
|
|
1101 |
“orders” with “orderid” and
|
|
|
1102 |
“ordercustid”.</p>
|
|
|
1103 |
|
|
|
1104 |
<p><span class="application">SQLScreens</span> stores the
|
|
|
1105 |
values for the field corresponding to a column as
|
|
|
1106 |
<code class="literal"><em class=
|
|
|
1107 |
"replaceable"><code>arrayname</code></em>(sqlsc_<em class=
|
|
|
1108 |
"replaceable"><code>column</code></em>_value)</code> (Ex:
|
|
|
1109 |
<code class="literal">customer(sqlsc_custid_value)</code>).
|
|
|
1110 |
This makes collisions of other entries with your column
|
|
|
1111 |
names unlikely. You can access these variables to retrieve
|
|
|
1112 |
values into your application, and also to modify them
|
|
|
1113 |
(before an insert for example, if the user input needs
|
|
|
1114 |
processing, or if some values are automatically generated
|
|
|
1115 |
by the application).</p>
|
|
|
1116 |
|
|
|
1117 |
<p>The first release used to store the values as
|
|
|
1118 |
<code class="literal"><em class=
|
|
|
1119 |
"replaceable"><code>arrayname</code></em>(<em class=
|
|
|
1120 |
"replaceable"><code>column</code></em>)</code>. If you have
|
|
|
1121 |
written code based on this, I would suggest that you modify
|
|
|
1122 |
it. If you do not want or can not, you can set the global
|
|
|
1123 |
variable “sqlsc_names_compat_old” to 1 before
|
|
|
1124 |
the first call to get a compatible behaviour (this will go
|
|
|
1125 |
away in the near future).</p>
|
|
|
1126 |
|
|
|
1127 |
<p>The following paragraphs describe the function of the
|
|
|
1128 |
different array entries, beginning with the most basic and
|
|
|
1129 |
frequently used, then the different callback functions that
|
|
|
1130 |
you can use.</p>
|
|
|
1131 |
|
|
|
1132 |
<p>I am sorry for the many naming inconsistencies (like
|
|
|
1133 |
using or not the sqlsc prefix for array entries), this came
|
|
|
1134 |
over time and would just be too much work to change.</p>
|
|
|
1135 |
|
|
|
1136 |
<p>When you are finished with the screen, you can call</p>
|
|
|
1137 |
<pre class="programlisting">
|
|
|
1138 |
sqlscreendelete arrayname
|
|
|
1139 |
</pre>
|
|
|
1140 |
|
|
|
1141 |
<p>to cleanup and release all resources (array, windows,
|
|
|
1142 |
database connections). Most applications will exit instead.
|
|
|
1143 |
<code class="function">sqlscreendelete</code> is mostly
|
|
|
1144 |
useful in case you want to recreate the screen with
|
|
|
1145 |
different options (most options can't be changed once a
|
|
|
1146 |
screen is created).</p>
|
|
|
1147 |
</div>
|
|
|
1148 |
|
|
|
1149 |
<div class="sect1" title=
|
|
|
1150 |
"4.2. Initialization and termination">
|
|
|
1151 |
<div class="titlepage">
|
|
|
1152 |
<div>
|
|
|
1153 |
<div>
|
|
|
1154 |
<h2 class="title" style="clear: both"><a name=
|
|
|
1155 |
"PROGRAMMING.INIT" id=
|
|
|
1156 |
"PROGRAMMING.INIT"></a>4.2. Initialization and
|
|
|
1157 |
termination</h2>
|
|
|
1158 |
</div>
|
|
|
1159 |
</div>
|
|
|
1160 |
</div>
|
|
|
1161 |
|
|
|
1162 |
<div class="sect2" title="4.2.1. sqlscreen">
|
|
|
1163 |
<div class="titlepage">
|
|
|
1164 |
<div>
|
|
|
1165 |
<div>
|
|
|
1166 |
<h3 class="title"><a name="idp5738592" id=
|
|
|
1167 |
"idp5738592"></a>4.2.1. sqlscreen</h3>
|
|
|
1168 |
</div>
|
|
|
1169 |
</div>
|
|
|
1170 |
</div>
|
|
|
1171 |
|
|
|
1172 |
<p>To create a screen, you set values inside a TCL array
|
|
|
1173 |
(See the following section: <a class="link" href=
|
|
|
1174 |
"#PROGRAMMING.ARRAYENTRIES" title=
|
|
|
1175 |
"4.3. Interface array entries">Interface array
|
|
|
1176 |
entries</a> ), then perform creation as follows:</p>
|
|
|
1177 |
<pre class="programlisting">
|
|
|
1178 |
sqlscreen yourarrayname
|
|
|
1179 |
|
|
|
1180 |
</pre>
|
|
|
1181 |
|
|
|
1182 |
<p>Note that <code class="function">sqlscreen</code> will
|
|
|
1183 |
create and pack the screen's window, but not its parents,
|
|
|
1184 |
so that the screen will not be necessarily visible at
|
|
|
1185 |
this point. Ex:</p>
|
|
|
1186 |
<pre class="programlisting">
|
|
|
1187 |
frame .f
|
|
|
1188 |
set myarray(window) .f.scr
|
|
|
1189 |
... set other fields
|
|
|
1190 |
sqlscreen myarray
|
|
|
1191 |
# screen still not visible
|
|
|
1192 |
pack .f #screen appears
|
|
|
1193 |
|
|
|
1194 |
</pre>
|
|
|
1195 |
|
|
|
1196 |
<p>This can be useful if you do not want the screen to be
|
|
|
1197 |
visible at all times: you can use 'pack ' and 'pack
|
|
|
1198 |
forget' to make it appear and disappear as you wish.</p>
|
|
|
1199 |
|
|
|
1200 |
<p><code class="function">sqlscreen</code> optionally
|
|
|
1201 |
takes a second parameter. If the value is <code class=
|
|
|
1202 |
"literal">h</code>, the fields will be arranged
|
|
|
1203 |
horizontally instead of vertically. There are other ways
|
|
|
1204 |
to do this(see <a class="link" href=
|
|
|
1205 |
"#PROGRAMMING.ARRAYENTRIES.GENERAL.COLUMNS" title=
|
|
|
1206 |
"4.3.1.6. columns">columns</a>), but it can still be
|
|
|
1207 |
useful in some cases.</p>
|
|
|
1208 |
</div>
|
|
|
1209 |
|
|
|
1210 |
<div class="sect2" title="4.2.2. sqlscreendelete">
|
|
|
1211 |
<div class="titlepage">
|
|
|
1212 |
<div>
|
|
|
1213 |
<div>
|
|
|
1214 |
<h3 class="title"><a name="idp5745984" id=
|
|
|
1215 |
"idp5745984"></a>4.2.2. sqlscreendelete</h3>
|
|
|
1216 |
</div>
|
|
|
1217 |
</div>
|
|
|
1218 |
</div>
|
|
|
1219 |
|
|
|
1220 |
<p>This procedure will destroy all resources associated
|
|
|
1221 |
with an sqlscreen (windows, database connections and the
|
|
|
1222 |
array itself). Call it as:</p>
|
|
|
1223 |
<pre class="programlisting">
|
|
|
1224 |
sqlscreendelete arrayname
|
|
|
1225 |
|
|
|
1226 |
</pre>
|
|
|
1227 |
</div>
|
|
|
1228 |
|
|
|
1229 |
<div class="sect2" title=
|
|
|
1230 |
"4.2.3. Setting fonts for SQLScreens applications">
|
|
|
1231 |
<div class="titlepage">
|
|
|
1232 |
<div>
|
|
|
1233 |
<div>
|
|
|
1234 |
<h3 class="title"><a name="PROGRAMMING.INIT.FONT"
|
|
|
1235 |
id="PROGRAMMING.INIT.FONT"></a>4.2.3. Setting
|
|
|
1236 |
fonts for <span class=
|
|
|
1237 |
"application">SQLScreens</span> applications</h3>
|
|
|
1238 |
</div>
|
|
|
1239 |
</div>
|
|
|
1240 |
</div>
|
|
|
1241 |
|
|
|
1242 |
<p>The font used by the screen can be set by <a class=
|
|
|
1243 |
"link" href="#PROGRAMMING.ARRAYENTRIES.SATTRIBUTES.FONT"
|
|
|
1244 |
title="4.3.2.3. font">setting the <code class=
|
|
|
1245 |
"literal">font</code> array entry</a>. This will only
|
|
|
1246 |
adjust the font for the specific screen. It may be more
|
|
|
1247 |
convenient to set the font at the start of the
|
|
|
1248 |
application script, with a variation on the following
|
|
|
1249 |
example:</p>
|
|
|
1250 |
<pre class="programlisting">
|
|
|
1251 |
option add *font {Arial 10}
|
|
|
1252 |
option add *Button*font {Arial 10 bold}
|
|
|
1253 |
|
|
|
1254 |
</pre>
|
|
|
1255 |
|
|
|
1256 |
<p>Alternatively, the font could be set in the option
|
|
|
1257 |
database (ie: <code class="filename">.Xdefault</code>
|
|
|
1258 |
under Unix). Example:</p>
|
|
|
1259 |
<pre class="programlisting">
|
|
|
1260 |
wines*font: Arial 10
|
|
|
1261 |
wines*Button*font: Arial 10 bold
|
|
|
1262 |
|
|
|
1263 |
</pre>
|
|
|
1264 |
|
|
|
1265 |
<p>In the latter case, the program name should not
|
|
|
1266 |
include a <code class="literal">.tcl</code> extension,
|
|
|
1267 |
else the dot seems to cause problems in the options
|
|
|
1268 |
database (use <code class="literal">wines</code>, not
|
|
|
1269 |
<code class="literal">wines.tcl</code>).</p>
|
|
|
1270 |
</div>
|
|
|
1271 |
</div>
|
|
|
1272 |
|
|
|
1273 |
<div class="sect1" title="4.3. Interface array entries">
|
|
|
1274 |
<div class="titlepage">
|
|
|
1275 |
<div>
|
|
|
1276 |
<div>
|
|
|
1277 |
<h2 class="title" style="clear: both"><a name=
|
|
|
1278 |
"PROGRAMMING.ARRAYENTRIES" id=
|
|
|
1279 |
"PROGRAMMING.ARRAYENTRIES"></a>4.3. Interface
|
|
|
1280 |
array entries</h2>
|
|
|
1281 |
</div>
|
|
|
1282 |
</div>
|
|
|
1283 |
</div>
|
|
|
1284 |
|
|
|
1285 |
<div class="sect2" title="4.3.1. General parameters">
|
|
|
1286 |
<div class="titlepage">
|
|
|
1287 |
<div>
|
|
|
1288 |
<div>
|
|
|
1289 |
<h3 class="title"><a name=
|
|
|
1290 |
"PROGRAMMING.ARRAYENTRIES.GENERAL" id=
|
|
|
1291 |
"PROGRAMMING.ARRAYENTRIES.GENERAL"></a>4.3.1. General
|
|
|
1292 |
parameters</h3>
|
|
|
1293 |
</div>
|
|
|
1294 |
</div>
|
|
|
1295 |
</div>
|
|
|
1296 |
|
|
|
1297 |
<div class="sect3" title="4.3.1.1. window">
|
|
|
1298 |
<div class="titlepage">
|
|
|
1299 |
<div>
|
|
|
1300 |
<div>
|
|
|
1301 |
<h4 class="title"><a name="idp5758160" id=
|
|
|
1302 |
"idp5758160"></a>4.3.1.1. window</h4>
|
|
|
1303 |
</div>
|
|
|
1304 |
</div>
|
|
|
1305 |
</div>
|
|
|
1306 |
|
|
|
1307 |
<p>This defines the TK frame name where the screen will
|
|
|
1308 |
be created. Example:</p>
|
|
|
1309 |
<pre class="programlisting">
|
|
|
1310 |
frame .f1
|
|
|
1311 |
set customer(window) .f1.cust
|
|
|
1312 |
|
|
|
1313 |
</pre>
|
|
|
1314 |
|
|
|
1315 |
<p>or just the following to create the window in the
|
|
|
1316 |
top one:</p>
|
|
|
1317 |
<pre class="programlisting">
|
|
|
1318 |
set customer(window) .cust
|
|
|
1319 |
|
|
|
1320 |
</pre>
|
|
|
1321 |
|
|
|
1322 |
<p>This entry must be a valid TK window name: for
|
|
|
1323 |
exemple it cannot start with an upper case
|
|
|
1324 |
character.</p>
|
|
|
1325 |
|
|
|
1326 |
<p>The window must not exist before calling sqlscreen,
|
|
|
1327 |
which will create it. Its parents must exist.</p>
|
|
|
1328 |
</div>
|
|
|
1329 |
|
|
|
1330 |
<div class="sect3" title="4.3.1.2. database">
|
|
|
1331 |
<div class="titlepage">
|
|
|
1332 |
<div>
|
|
|
1333 |
<div>
|
|
|
1334 |
<h4 class="title"><a name="idp5762144" id=
|
|
|
1335 |
"idp5762144"></a>4.3.1.2. database</h4>
|
|
|
1336 |
</div>
|
|
|
1337 |
</div>
|
|
|
1338 |
</div>
|
|
|
1339 |
|
|
|
1340 |
<p>This defines the database name.</p>
|
|
|
1341 |
<pre class="programlisting">
|
|
|
1342 |
set customer(database) orderdb
|
|
|
1343 |
|
|
|
1344 |
</pre>
|
|
|
1345 |
</div>
|
|
|
1346 |
|
|
|
1347 |
<div class="sect3" title=
|
|
|
1348 |
"4.3.1.3. sqlcpasswd, sqlschost, sqlscuser">
|
|
|
1349 |
<div class="titlepage">
|
|
|
1350 |
<div>
|
|
|
1351 |
<div>
|
|
|
1352 |
<h4 class="title"><a name="idp5763920" id=
|
|
|
1353 |
"idp5763920"></a>4.3.1.3. sqlcpasswd,
|
|
|
1354 |
sqlschost, sqlscuser</h4>
|
|
|
1355 |
</div>
|
|
|
1356 |
</div>
|
|
|
1357 |
</div>
|
|
|
1358 |
|
|
|
1359 |
<p>These define the user name, host and password for
|
|
|
1360 |
the connection to the database server. These are all
|
|
|
1361 |
optional.</p>
|
|
|
1362 |
</div>
|
|
|
1363 |
|
|
|
1364 |
<div class="sect3" title="4.3.1.4. table">
|
|
|
1365 |
<div class="titlepage">
|
|
|
1366 |
<div>
|
|
|
1367 |
<div>
|
|
|
1368 |
<h4 class="title"><a name="idp5765216" id=
|
|
|
1369 |
"idp5765216"></a>4.3.1.4. table</h4>
|
|
|
1370 |
</div>
|
|
|
1371 |
</div>
|
|
|
1372 |
</div>
|
|
|
1373 |
|
|
|
1374 |
<p>This defines the table name. Example:</p>
|
|
|
1375 |
<pre class="programlisting">
|
|
|
1376 |
set customer(table) customers
|
|
|
1377 |
|
|
|
1378 |
</pre>
|
|
|
1379 |
|
|
|
1380 |
<p>It is also possible to display fields from several
|
|
|
1381 |
tables in one screen:</p>
|
|
|
1382 |
<pre class="programlisting">
|
|
|
1383 |
set custorder(table) {customers orders}
|
|
|
1384 |
|
|
|
1385 |
</pre>
|
|
|
1386 |
|
|
|
1387 |
<p>If you are using several tables, you will also need
|
|
|
1388 |
a join clause (see the following paragraph), and you
|
|
|
1389 |
will not be able to modify data through the screen.
|
|
|
1390 |
(You CAN update several tables in one application, but
|
|
|
1391 |
each table will need a separate screen, and the screen
|
|
|
1392 |
links will be through cascaded queries, not join
|
|
|
1393 |
clauses - See <a class="link" href=
|
|
|
1394 |
"#PROGRAMMING.LINKING" title=
|
|
|
1395 |
"4.5. Linking screens">Linking screens</a> ).</p>
|
|
|
1396 |
</div>
|
|
|
1397 |
|
|
|
1398 |
<div class="sect3" title="4.3.1.5. joinclause">
|
|
|
1399 |
<div class="titlepage">
|
|
|
1400 |
<div>
|
|
|
1401 |
<div>
|
|
|
1402 |
<h4 class="title"><a name="idp5769504" id=
|
|
|
1403 |
"idp5769504"></a>4.3.1.5. joinclause</h4>
|
|
|
1404 |
</div>
|
|
|
1405 |
</div>
|
|
|
1406 |
</div>
|
|
|
1407 |
|
|
|
1408 |
<p>In case fields from several tables are displayed in
|
|
|
1409 |
a screen, <span class="application">SQLScreens</span>
|
|
|
1410 |
needs to know how to join the tables when performing a
|
|
|
1411 |
SELECT. This is defined by the joinclause array entry.
|
|
|
1412 |
Exemple:</p>
|
|
|
1413 |
<pre class="programlisting">
|
|
|
1414 |
set custorder(joinclause) {customers.custid = orders.ordercustid}
|
|
|
1415 |
|
|
|
1416 |
</pre>
|
|
|
1417 |
</div>
|
|
|
1418 |
|
|
|
1419 |
<div class="sect3" title="4.3.1.6. columns">
|
|
|
1420 |
<div class="titlepage">
|
|
|
1421 |
<div>
|
|
|
1422 |
<div>
|
|
|
1423 |
<h4 class="title"><a name=
|
|
|
1424 |
"PROGRAMMING.ARRAYENTRIES.GENERAL.COLUMNS" id=
|
|
|
1425 |
"PROGRAMMING.ARRAYENTRIES.GENERAL.COLUMNS"></a>4.3.1.6. columns</h4>
|
|
|
1426 |
</div>
|
|
|
1427 |
</div>
|
|
|
1428 |
</div>
|
|
|
1429 |
|
|
|
1430 |
<p>This is a list to define the columns that you want
|
|
|
1431 |
included. If it is not set, sqlscreen will query the
|
|
|
1432 |
database for all the column names in the table, and
|
|
|
1433 |
build the screen with the result.</p>
|
|
|
1434 |
|
|
|
1435 |
<p>Example for specifying the column names:</p>
|
|
|
1436 |
<pre class="programlisting">
|
|
|
1437 |
set customer(columns) {custid custname}
|
|
|
1438 |
|
|
|
1439 |
</pre>
|
|
|
1440 |
|
|
|
1441 |
<p>If several tables are used, it may be necessary to
|
|
|
1442 |
qualify the column names if they are not unique:</p>
|
|
|
1443 |
<pre class="programlisting">
|
|
|
1444 |
set custorder(columns) {customers.custid customers.custname}
|
|
|
1445 |
|
|
|
1446 |
</pre>
|
|
|
1447 |
|
|
|
1448 |
<p>By default, all fields will be displayed in one
|
|
|
1449 |
column. You can get them to be displayed in one line by
|
|
|
1450 |
calling <code class="function">sqlscreen</code> as</p>
|
|
|
1451 |
<pre class="programlisting">
|
|
|
1452 |
sqlscreen <em class="replaceable"><code>arrayname</code></em> h
|
|
|
1453 |
</pre>
|
|
|
1454 |
|
|
|
1455 |
<p>You can also insert <span class="emphasis"><em>line
|
|
|
1456 |
breaks</em></span> by inserting newline caracters in
|
|
|
1457 |
the column list, like:</p>
|
|
|
1458 |
<pre class="programlisting">
|
|
|
1459 |
set arrayname(columns) {
|
|
|
1460 |
host "\n"
|
|
|
1461 |
user db "\n"
|
|
|
1462 |
select_priv insert_priv update_priv "\n"
|
|
|
1463 |
delete_priv create_priv drop_priv
|
|
|
1464 |
}
|
|
|
1465 |
|
|
|
1466 |
</pre>
|
|
|
1467 |
|
|
|
1468 |
<p>The field positions will be arranged by the grid
|
|
|
1469 |
geometry manager. In lines with less fields, the last
|
|
|
1470 |
field (and only the last) spans the remaining columns.
|
|
|
1471 |
You will probably need several tries to get it right
|
|
|
1472 |
(at least I usually do).</p>
|
|
|
1473 |
</div>
|
|
|
1474 |
</div>
|
|
|
1475 |
|
|
|
1476 |
<div class="sect2" title="4.3.2. Screen attributes">
|
|
|
1477 |
<div class="titlepage">
|
|
|
1478 |
<div>
|
|
|
1479 |
<div>
|
|
|
1480 |
<h3 class="title"><a name=
|
|
|
1481 |
"PROGRAMMING.ARRAYENTRIES.SATTRIBUTES" id=
|
|
|
1482 |
"PROGRAMMING.ARRAYENTRIES.SATTRIBUTES"></a>4.3.2. Screen
|
|
|
1483 |
attributes</h3>
|
|
|
1484 |
</div>
|
|
|
1485 |
</div>
|
|
|
1486 |
</div>
|
|
|
1487 |
|
|
|
1488 |
<div class="sect3" title="4.3.2.1. queryonly">
|
|
|
1489 |
<div class="titlepage">
|
|
|
1490 |
<div>
|
|
|
1491 |
<div>
|
|
|
1492 |
<h4 class="title"><a name="idp5781232" id=
|
|
|
1493 |
"idp5781232"></a>4.3.2.1. queryonly</h4>
|
|
|
1494 |
</div>
|
|
|
1495 |
</div>
|
|
|
1496 |
</div>
|
|
|
1497 |
|
|
|
1498 |
<p>If this is set, the screen will not have
|
|
|
1499 |
“add” and “update” buttons, you
|
|
|
1500 |
will only be able to select data. Example:</p>
|
|
|
1501 |
<pre class="programlisting">
|
|
|
1502 |
set customer(queryonly) {}
|
|
|
1503 |
|
|
|
1504 |
</pre>
|
|
|
1505 |
|
|
|
1506 |
<p>The value has no importance, just setting the array
|
|
|
1507 |
entry (even to <code class="literal">no</code>) creates
|
|
|
1508 |
a screen for query only.</p>
|
|
|
1509 |
</div>
|
|
|
1510 |
|
|
|
1511 |
<div class="sect3" title="4.3.2.2. allowdelete">
|
|
|
1512 |
<div class="titlepage">
|
|
|
1513 |
<div>
|
|
|
1514 |
<div>
|
|
|
1515 |
<h4 class="title"><a name=
|
|
|
1516 |
"PROGRAMMING.ARRAYENTRIES.SATTRIBUTES.ALLOWDELETE"
|
|
|
1517 |
id=
|
|
|
1518 |
"PROGRAMMING.ARRAYENTRIES.SATTRIBUTES.ALLOWDELETE">
|
|
|
1519 |
</a>4.3.2.2. allowdelete</h4>
|
|
|
1520 |
</div>
|
|
|
1521 |
</div>
|
|
|
1522 |
</div>
|
|
|
1523 |
|
|
|
1524 |
<p>If this entry is set, and <code class=
|
|
|
1525 |
"literal">queryonly</code> is not set a <span class=
|
|
|
1526 |
"guilabel">Delete</span> button will be created.</p>
|
|
|
1527 |
</div>
|
|
|
1528 |
|
|
|
1529 |
<div class="sect3" title="4.3.2.3. font">
|
|
|
1530 |
<div class="titlepage">
|
|
|
1531 |
<div>
|
|
|
1532 |
<div>
|
|
|
1533 |
<h4 class="title"><a name=
|
|
|
1534 |
"PROGRAMMING.ARRAYENTRIES.SATTRIBUTES.FONT" id=
|
|
|
1535 |
"PROGRAMMING.ARRAYENTRIES.SATTRIBUTES.FONT"></a>4.3.2.3. font</h4>
|
|
|
1536 |
</div>
|
|
|
1537 |
</div>
|
|
|
1538 |
</div>
|
|
|
1539 |
|
|
|
1540 |
<p>If this entry is set, the value will be used as a
|
|
|
1541 |
font definition for the screen elements. Any TK font
|
|
|
1542 |
definition can be used. There are <a class="link" href=
|
|
|
1543 |
"#PROGRAMMING.INIT.FONT" title=
|
|
|
1544 |
"4.2.3. Setting fonts for SQLScreens applications">
|
|
|
1545 |
several other ways</a> to set the application font.</p>
|
|
|
1546 |
</div>
|
|
|
1547 |
|
|
|
1548 |
<div class="sect3" title="4.3.2.4. notitle">
|
|
|
1549 |
<div class="titlepage">
|
|
|
1550 |
<div>
|
|
|
1551 |
<div>
|
|
|
1552 |
<h4 class="title"><a name="idp5789104" id=
|
|
|
1553 |
"idp5789104"></a>4.3.2.4. notitle</h4>
|
|
|
1554 |
</div>
|
|
|
1555 |
</div>
|
|
|
1556 |
</div>
|
|
|
1557 |
|
|
|
1558 |
<p>Suppresses the screen title. This spares a little
|
|
|
1559 |
space if your screen is crowded.</p>
|
|
|
1560 |
</div>
|
|
|
1561 |
|
|
|
1562 |
<div class="sect3" title="4.3.2.5. nobuttons">
|
|
|
1563 |
<div class="titlepage">
|
|
|
1564 |
<div>
|
|
|
1565 |
<div>
|
|
|
1566 |
<h4 class="title"><a name="idp5790368" id=
|
|
|
1567 |
"idp5790368"></a>4.3.2.5. nobuttons</h4>
|
|
|
1568 |
</div>
|
|
|
1569 |
</div>
|
|
|
1570 |
</div>
|
|
|
1571 |
|
|
|
1572 |
<p>If this is set, no buttons will be created in this
|
|
|
1573 |
particular screen. Note that this does not change what
|
|
|
1574 |
you can do in the screen, because the keyboard
|
|
|
1575 |
shortcuts are still available.</p>
|
|
|
1576 |
</div>
|
|
|
1577 |
|
|
|
1578 |
<div class="sect3" title="4.3.2.6. graphicbuttons">
|
|
|
1579 |
<div class="titlepage">
|
|
|
1580 |
<div>
|
|
|
1581 |
<div>
|
|
|
1582 |
<h4 class="title"><a name="idp5791744" id=
|
|
|
1583 |
"idp5791744"></a>4.3.2.6. graphicbuttons</h4>
|
|
|
1584 |
</div>
|
|
|
1585 |
</div>
|
|
|
1586 |
</div>
|
|
|
1587 |
|
|
|
1588 |
<p>If this is set, and the <span class=
|
|
|
1589 |
"application">Tix</span> package is available, the
|
|
|
1590 |
buttons will be created with icons instead of textual
|
|
|
1591 |
labels.</p>
|
|
|
1592 |
</div>
|
|
|
1593 |
</div>
|
|
|
1594 |
|
|
|
1595 |
<div class="sect2" title="4.3.3. Field attributes">
|
|
|
1596 |
<div class="titlepage">
|
|
|
1597 |
<div>
|
|
|
1598 |
<div>
|
|
|
1599 |
<h3 class="title"><a name=
|
|
|
1600 |
"PROGRAMMING.ARRAYENTRIES.FATTRIBUTES" id=
|
|
|
1601 |
"PROGRAMMING.ARRAYENTRIES.FATTRIBUTES"></a>4.3.3. Field
|
|
|
1602 |
attributes</h3>
|
|
|
1603 |
</div>
|
|
|
1604 |
</div>
|
|
|
1605 |
</div>
|
|
|
1606 |
|
|
|
1607 |
<div class="sect3" title=
|
|
|
1608 |
"4.3.3.1. Column type and length">
|
|
|
1609 |
<div class="titlepage">
|
|
|
1610 |
<div>
|
|
|
1611 |
<div>
|
|
|
1612 |
<h4 class="title"><a name="idp5794656" id=
|
|
|
1613 |
"idp5794656"></a>4.3.3.1. Column type and
|
|
|
1614 |
length</h4>
|
|
|
1615 |
</div>
|
|
|
1616 |
</div>
|
|
|
1617 |
</div>
|
|
|
1618 |
|
|
|
1619 |
<p>The <code class="literal">sqlsc_<em class=
|
|
|
1620 |
"replaceable"><code>colname</code></em>_len</code> and
|
|
|
1621 |
<code class="literal">sqlsc_<em class=
|
|
|
1622 |
"replaceable"><code>colname</code></em>_type</code>
|
|
|
1623 |
entries are normally created by the package, you do not
|
|
|
1624 |
need to set them. For character columns, you can set
|
|
|
1625 |
<code class="literal">sqlsc_<em class=
|
|
|
1626 |
"replaceable"><code>colname</code></em>_len</code> if
|
|
|
1627 |
you want the entry field to be of a size different from
|
|
|
1628 |
the column width (for example if the column is very
|
|
|
1629 |
wide). Example:</p>
|
|
|
1630 |
<pre class="programlisting">
|
|
|
1631 |
set customer(sqlsc_custname_len) 20
|
|
|
1632 |
|
|
|
1633 |
</pre>
|
|
|
1634 |
|
|
|
1635 |
<p>would create a 20 characters field even if custname
|
|
|
1636 |
is actually 100 characters wide. This does not
|
|
|
1637 |
constrain what you can enter because TK fields can
|
|
|
1638 |
scroll.</p>
|
|
|
1639 |
|
|
|
1640 |
<p>In any case, <span class=
|
|
|
1641 |
"application">SQLScreens</span> checks that the input
|
|
|
1642 |
can fit in the database column and will not allow
|
|
|
1643 |
entering more data in a field (except for the special
|
|
|
1644 |
'text' fields described further).</p>
|
|
|
1645 |
</div>
|
|
|
1646 |
|
|
|
1647 |
<div class="sect3" title="4.3.3.2. autopercent">
|
|
|
1648 |
<div class="titlepage">
|
|
|
1649 |
<div>
|
|
|
1650 |
<div>
|
|
|
1651 |
<h4 class="title"><a name="idp5801584" id=
|
|
|
1652 |
"idp5801584"></a>4.3.3.2. autopercent</h4>
|
|
|
1653 |
</div>
|
|
|
1654 |
</div>
|
|
|
1655 |
</div>
|
|
|
1656 |
|
|
|
1657 |
<p>The <code class="literal">autopercentboth</code>,
|
|
|
1658 |
<code class="literal">autopercentleft</code>,
|
|
|
1659 |
<code class="literal">autopercentright</code> lists can
|
|
|
1660 |
be set for character columns where you want '%' to be
|
|
|
1661 |
automatically added before a query (all char field
|
|
|
1662 |
queries are done with the LIKE operator). Example:</p>
|
|
|
1663 |
<pre class="programlisting">
|
|
|
1664 |
set customer(autopercentright) {custname}
|
|
|
1665 |
|
|
|
1666 |
</pre>
|
|
|
1667 |
|
|
|
1668 |
<p>would let you query by entering just the beginning
|
|
|
1669 |
of the name, without having to reach for the shift key
|
|
|
1670 |
to type '%'.</p>
|
|
|
1671 |
</div>
|
|
|
1672 |
|
|
|
1673 |
<div class="sect3" title="4.3.3.3. texts">
|
|
|
1674 |
<div class="titlepage">
|
|
|
1675 |
<div>
|
|
|
1676 |
<div>
|
|
|
1677 |
<h4 class="title"><a name="idp5805984" id=
|
|
|
1678 |
"idp5805984"></a>4.3.3.3. texts</h4>
|
|
|
1679 |
</div>
|
|
|
1680 |
</div>
|
|
|
1681 |
</div>
|
|
|
1682 |
|
|
|
1683 |
<p>This is a list of columns (typically text blobs)
|
|
|
1684 |
that should be displayed in multiline text widgets.
|
|
|
1685 |
Each entry is a triplet or quadruplet listing the
|
|
|
1686 |
column name, the width and height of the text widget,
|
|
|
1687 |
and a possible option field. Ex:</p>
|
|
|
1688 |
<pre class="programlisting">
|
|
|
1689 |
set product(texts) {{description 20 70} {notice 10 70 t}}
|
|
|
1690 |
|
|
|
1691 |
</pre>
|
|
|
1692 |
|
|
|
1693 |
<p>If the option field is present, it should be a
|
|
|
1694 |
string where each character will select an option.
|
|
|
1695 |
There are currently 2 possible (and mutually exclusive)
|
|
|
1696 |
options:</p>
|
|
|
1697 |
|
|
|
1698 |
<div class="variablelist">
|
|
|
1699 |
<dl>
|
|
|
1700 |
<dt><span class="term">t</span></dt>
|
|
|
1701 |
|
|
|
1702 |
<dd>
|
|
|
1703 |
<p>will display a label (column name) above the
|
|
|
1704 |
text area</p>
|
|
|
1705 |
</dd>
|
|
|
1706 |
|
|
|
1707 |
<dt><span class="term">l</span></dt>
|
|
|
1708 |
|
|
|
1709 |
<dd>
|
|
|
1710 |
<p>will display a label on the left of the text
|
|
|
1711 |
area</p>
|
|
|
1712 |
</dd>
|
|
|
1713 |
</dl>
|
|
|
1714 |
</div>
|
|
|
1715 |
|
|
|
1716 |
<p>By default, no label will be displayed for text
|
|
|
1717 |
fields.</p>
|
|
|
1718 |
|
|
|
1719 |
<p><span class="application">SQLScreens</span> will
|
|
|
1720 |
handle quoting and unquoting the blob contents.</p>
|
|
|
1721 |
|
|
|
1722 |
<p>Text entries will NOT be validated for maximum
|
|
|
1723 |
length against the database field width.</p>
|
|
|
1724 |
|
|
|
1725 |
<p>There is an exemple of texts use in the wines.tcl
|
|
|
1726 |
sample application.</p>
|
|
|
1727 |
</div>
|
|
|
1728 |
|
|
|
1729 |
<div class="sect3" title="4.3.3.4. choices">
|
|
|
1730 |
<div class="titlepage">
|
|
|
1731 |
<div>
|
|
|
1732 |
<div>
|
|
|
1733 |
<h4 class="title"><a name="idp5814720" id=
|
|
|
1734 |
"idp5814720"></a>4.3.3.4. choices</h4>
|
|
|
1735 |
</div>
|
|
|
1736 |
</div>
|
|
|
1737 |
</div>
|
|
|
1738 |
|
|
|
1739 |
<p>This list defines columns where entries should come
|
|
|
1740 |
from a menu instead of being free form. It is very
|
|
|
1741 |
useful, but the interface could be nicer.</p>
|
|
|
1742 |
|
|
|
1743 |
<p>The choices entry is a list. There are two list
|
|
|
1744 |
elements for every column. The first element is the
|
|
|
1745 |
column name, the second element the name for the list
|
|
|
1746 |
of possible values. For example:</p>
|
|
|
1747 |
<pre class="programlisting">
|
|
|
1748 |
set customer(choices) {
|
|
|
1749 |
custtype custtypelist
|
|
|
1750 |
custgender custgenderlist
|
|
|
1751 |
}
|
|
|
1752 |
|
|
|
1753 |
</pre>
|
|
|
1754 |
|
|
|
1755 |
<p>Would specify that the <code class=
|
|
|
1756 |
"literal">custtype</code> and <code class=
|
|
|
1757 |
"literal">custgender</code> columns will have values
|
|
|
1758 |
coming from custtypelist and custgenderlist. These
|
|
|
1759 |
lists would typically have been created beforehand
|
|
|
1760 |
(possibly by querying another table). The list of
|
|
|
1761 |
values can in turn be of two types: either a simple
|
|
|
1762 |
list or a list of pairs.</p>
|
|
|
1763 |
|
|
|
1764 |
<p>A simple list lists the possible values (would you
|
|
|
1765 |
believe this ?). Ex:</p>
|
|
|
1766 |
<pre class="programlisting">
|
|
|
1767 |
set custtypelist {normal distributor internal}
|
|
|
1768 |
|
|
|
1769 |
</pre>
|
|
|
1770 |
|
|
|
1771 |
<p><code class="literal">normal</code>, <code class=
|
|
|
1772 |
"literal">distributor</code> and <code class=
|
|
|
1773 |
"literal">internal</code> will be both displayed on the
|
|
|
1774 |
screen and used for querying or updating the
|
|
|
1775 |
database.</p>
|
|
|
1776 |
|
|
|
1777 |
<p>In a list of pairs, each pair defines the value that
|
|
|
1778 |
should be shown and the value that should be entered in
|
|
|
1779 |
the database. Example:</p>
|
|
|
1780 |
<pre class="programlisting">
|
|
|
1781 |
set custgenderlist {{unknown 0} {female 1} {male 2}}
|
|
|
1782 |
|
|
|
1783 |
</pre>
|
|
|
1784 |
|
|
|
1785 |
<p>With this list, the menu would display <code class=
|
|
|
1786 |
"literal">unknown</code>, <code class=
|
|
|
1787 |
"literal">male</code>, <code class=
|
|
|
1788 |
"literal">female</code>, but the values used for the
|
|
|
1789 |
database would be 0, 1, 2.</p>
|
|
|
1790 |
|
|
|
1791 |
<p>Note that when using <span class=
|
|
|
1792 |
"application">MySQL</span>, a choice menu will be
|
|
|
1793 |
automatically generated for 'enum' columns. You can
|
|
|
1794 |
still set your own list, which will override the
|
|
|
1795 |
automatically built one. This can be useful if the
|
|
|
1796 |
displayed values are different from the stored
|
|
|
1797 |
ones.</p>
|
|
|
1798 |
|
|
|
1799 |
<p>There are exemples of use (both automatic and
|
|
|
1800 |
explicit) in the wines.tcl sample program.</p>
|
|
|
1801 |
</div>
|
|
|
1802 |
|
|
|
1803 |
<div class="sect3" title="4.3.3.5. ordercols">
|
|
|
1804 |
<div class="titlepage">
|
|
|
1805 |
<div>
|
|
|
1806 |
<div>
|
|
|
1807 |
<h4 class="title"><a name="idp5828160" id=
|
|
|
1808 |
"idp5828160"></a>4.3.3.5. ordercols</h4>
|
|
|
1809 |
</div>
|
|
|
1810 |
</div>
|
|
|
1811 |
</div>
|
|
|
1812 |
|
|
|
1813 |
<p>This list defines column names that will be added in
|
|
|
1814 |
an ORDER BY clause each time a query is run. It has the
|
|
|
1815 |
format of a normal ORDER BY column list. Ex:</p>
|
|
|
1816 |
<pre class="programlisting">
|
|
|
1817 |
set customer(ordercols) "custid desc, custname"
|
|
|
1818 |
|
|
|
1819 |
</pre>
|
|
|
1820 |
</div>
|
|
|
1821 |
|
|
|
1822 |
<div class="sect3" title="4.3.3.6. updateindex">
|
|
|
1823 |
<div class="titlepage">
|
|
|
1824 |
<div>
|
|
|
1825 |
<div>
|
|
|
1826 |
<h4 class="title"><a name=
|
|
|
1827 |
"PROGRAMMING.ARRAYENTRIES.FATTRIBUTES.UPDATEINDEX"
|
|
|
1828 |
id=
|
|
|
1829 |
"PROGRAMMING.ARRAYENTRIES.FATTRIBUTES.UPDATEINDEX">
|
|
|
1830 |
</a>4.3.3.6. updateindex</h4>
|
|
|
1831 |
</div>
|
|
|
1832 |
</div>
|
|
|
1833 |
</div>
|
|
|
1834 |
|
|
|
1835 |
<p>This defines a column name (or a list of column
|
|
|
1836 |
names) that will be used in the WHERE clause of an
|
|
|
1837 |
UPDATE statement. It should provide a way to uniquely
|
|
|
1838 |
identify a row.</p>
|
|
|
1839 |
|
|
|
1840 |
<p>If neither <code class="literal">columns</code> nor
|
|
|
1841 |
<code class="literal">updateindex</code> are set before
|
|
|
1842 |
calling <code class="function">sqlscreen</code>,
|
|
|
1843 |
<span class="application">SQLScreens</span> will try to
|
|
|
1844 |
make up an <code class="literal">updateindex</code> by
|
|
|
1845 |
using a serial column or primary key if one is
|
|
|
1846 |
found.</p>
|
|
|
1847 |
|
|
|
1848 |
<p>If <code class="literal">columns</code> is set, and
|
|
|
1849 |
not <code class="literal">updateindex</code>, the
|
|
|
1850 |
latter is automatically generated only if the primary
|
|
|
1851 |
key is completely included in the column list.</p>
|
|
|
1852 |
|
|
|
1853 |
<p>If <code class="literal">updateindex</code> is set
|
|
|
1854 |
to an empty list by the caller, it is expanded to
|
|
|
1855 |
include all the screen's columns (no checks against a
|
|
|
1856 |
possible primary key in this case).</p>
|
|
|
1857 |
|
|
|
1858 |
<p>If no <code class="literal">updateindex</code> list
|
|
|
1859 |
finally exists, the screen will have no <span class=
|
|
|
1860 |
"guilabel">Update</span> button.</p>
|
|
|
1861 |
|
|
|
1862 |
<p>See <a class="link" href="#SQLGENERATION" title=
|
|
|
1863 |
"Chapter 5. SQL generation">Sql
|
|
|
1864 |
generation</a> for a more complete discussion of update
|
|
|
1865 |
row selection.</p>
|
|
|
1866 |
</div>
|
|
|
1867 |
|
|
|
1868 |
<div class="sect3" title="4.3.3.7. upshiftcols">
|
|
|
1869 |
<div class="titlepage">
|
|
|
1870 |
<div>
|
|
|
1871 |
<div>
|
|
|
1872 |
<h4 class="title"><a name="idp5841168" id=
|
|
|
1873 |
"idp5841168"></a>4.3.3.7. upshiftcols</h4>
|
|
|
1874 |
</div>
|
|
|
1875 |
</div>
|
|
|
1876 |
</div>
|
|
|
1877 |
|
|
|
1878 |
<p>This is a list of fields for which values should be
|
|
|
1879 |
automatically changed to upper case before inserting or
|
|
|
1880 |
querying. This is very useful with INFORMIX which is
|
|
|
1881 |
case-sensitive, not very useful with <span class=
|
|
|
1882 |
"application">MySQL</span>. Example:</p>
|
|
|
1883 |
<pre class="programlisting">
|
|
|
1884 |
set customer(upshiftcols) {custname custfirstname}
|
|
|
1885 |
|
|
|
1886 |
</pre>
|
|
|
1887 |
</div>
|
|
|
1888 |
|
|
|
1889 |
<div class="sect3" title="4.3.3.8. noentry">
|
|
|
1890 |
<div class="titlepage">
|
|
|
1891 |
<div>
|
|
|
1892 |
<div>
|
|
|
1893 |
<h4 class="title"><a name="idp5843888" id=
|
|
|
1894 |
"idp5843888"></a>4.3.3.8. noentry</h4>
|
|
|
1895 |
</div>
|
|
|
1896 |
</div>
|
|
|
1897 |
</div>
|
|
|
1898 |
|
|
|
1899 |
<p>This is a list of columns for which data entry is
|
|
|
1900 |
forbidden. They are displayed differently, and will not
|
|
|
1901 |
allow typing. This is sometimes useful for fields that
|
|
|
1902 |
should only be updated by the program or on which
|
|
|
1903 |
searching is forbidden.</p>
|
|
|
1904 |
</div>
|
|
|
1905 |
|
|
|
1906 |
<div class="sect3" title="4.3.3.9. nodisplay">
|
|
|
1907 |
<div class="titlepage">
|
|
|
1908 |
<div>
|
|
|
1909 |
<div>
|
|
|
1910 |
<h4 class="title"><a name="idp5845328" id=
|
|
|
1911 |
"idp5845328"></a>4.3.3.9. nodisplay</h4>
|
|
|
1912 |
</div>
|
|
|
1913 |
</div>
|
|
|
1914 |
</div>
|
|
|
1915 |
|
|
|
1916 |
<p>This is a list of columns for which no fields will
|
|
|
1917 |
be shown. The corresponding values are present in the
|
|
|
1918 |
array. This is used for fields which link several
|
|
|
1919 |
screens, or which the application wants to use, but
|
|
|
1920 |
which don't need to be displayed.</p>
|
|
|
1921 |
</div>
|
|
|
1922 |
</div>
|
|
|
1923 |
|
|
|
1924 |
<div class="sect2" title=
|
|
|
1925 |
"4.3.4. Auxiliary list window">
|
|
|
1926 |
<div class="titlepage">
|
|
|
1927 |
<div>
|
|
|
1928 |
<div>
|
|
|
1929 |
<h3 class="title"><a name="idp5846912" id=
|
|
|
1930 |
"idp5846912"></a>4.3.4. Auxiliary list
|
|
|
1931 |
window</h3>
|
|
|
1932 |
</div>
|
|
|
1933 |
</div>
|
|
|
1934 |
</div>
|
|
|
1935 |
|
|
|
1936 |
<div class="sect3" title=
|
|
|
1937 |
"4.3.4.1. list_columns, list_window">
|
|
|
1938 |
<div class="titlepage">
|
|
|
1939 |
<div>
|
|
|
1940 |
<div>
|
|
|
1941 |
<h4 class="title"><a name="idp5847552" id=
|
|
|
1942 |
"idp5847552"></a>4.3.4.1. list_columns,
|
|
|
1943 |
list_window</h4>
|
|
|
1944 |
</div>
|
|
|
1945 |
</div>
|
|
|
1946 |
</div>
|
|
|
1947 |
|
|
|
1948 |
<p>If <code class="literal">list_columns</code> and
|
|
|
1949 |
<code class="literal">list_window</code> are set,
|
|
|
1950 |
<code class="function">sqlscreen</code> will create an
|
|
|
1951 |
auxiliary list for the screen, in the specified window.
|
|
|
1952 |
The list screen will display one line for each result
|
|
|
1953 |
row, the data displayed will be taken from the
|
|
|
1954 |
<code class="literal">list_columns</code> columns. This
|
|
|
1955 |
is useful to get a compact display of a query's
|
|
|
1956 |
results.</p>
|
|
|
1957 |
|
|
|
1958 |
<p>Clicking on a line in the list with mouse button 1
|
|
|
1959 |
will display the corresponding row in the main
|
|
|
1960 |
screen.</p>
|
|
|
1961 |
|
|
|
1962 |
<p>This capability will only be available if an
|
|
|
1963 |
<code class="literal">updateindex</code> list has been
|
|
|
1964 |
defined for the screen (either implicitely or
|
|
|
1965 |
explicitely), see <a class="link" href=
|
|
|
1966 |
"#PROGRAMMING.ARRAYENTRIES.FATTRIBUTES.UPDATEINDEX"
|
|
|
1967 |
title="4.3.3.6. updateindex">the updateindex
|
|
|
1968 |
section</a>. The <code class=
|
|
|
1969 |
"literal">updateindex</code> columns must be part of
|
|
|
1970 |
<code class="literal">list_columns</code>, so that we
|
|
|
1971 |
can uniquely link back from the list to the detail
|
|
|
1972 |
screen.</p>
|
|
|
1973 |
|
|
|
1974 |
<p>Example:</p>
|
|
|
1975 |
<pre class="programlisting">
|
|
|
1976 |
set customers(list_columns) {custname custid}
|
|
|
1977 |
set customers(updateindex) custid
|
|
|
1978 |
toplevel .custlist
|
|
|
1979 |
set customers(list_window) .custlist
|
|
|
1980 |
|
|
|
1981 |
</pre>
|
|
|
1982 |
</div>
|
|
|
1983 |
|
|
|
1984 |
<div class="sect3" title="4.3.4.2. list_colwidths:">
|
|
|
1985 |
<div class="titlepage">
|
|
|
1986 |
<div>
|
|
|
1987 |
<div>
|
|
|
1988 |
<h4 class="title"><a name="idp5856288" id=
|
|
|
1989 |
"idp5856288"></a>4.3.4.2. list_colwidths:</h4>
|
|
|
1990 |
</div>
|
|
|
1991 |
</div>
|
|
|
1992 |
</div>
|
|
|
1993 |
|
|
|
1994 |
<p><span class="application">SQLScreens</span> will try
|
|
|
1995 |
its best to compute appropriate column widths for the
|
|
|
1996 |
list and to align the columns. You may force specific
|
|
|
1997 |
values for the column widths by specifying the
|
|
|
1998 |
<code class="literal">list_colwidths</code> entry, as a
|
|
|
1999 |
list specifying the width in characters for each
|
|
|
2000 |
column. Example:</p>
|
|
|
2001 |
<pre class="programlisting">
|
|
|
2002 |
set customers(list_colwidths) {40 5}
|
|
|
2003 |
|
|
|
2004 |
</pre>
|
|
|
2005 |
|
|
|
2006 |
<p>The widths must be specified in the same order as
|
|
|
2007 |
the columns in <code class=
|
|
|
2008 |
"literal">list_columns</code>.</p>
|
|
|
2009 |
</div>
|
|
|
2010 |
|
|
|
2011 |
<div class="sect3" title="4.3.4.3. list_lineproc:">
|
|
|
2012 |
<div class="titlepage">
|
|
|
2013 |
<div>
|
|
|
2014 |
<div>
|
|
|
2015 |
<h4 class="title"><a name="idp5860608" id=
|
|
|
2016 |
"idp5860608"></a>4.3.4.3. list_lineproc:</h4>
|
|
|
2017 |
</div>
|
|
|
2018 |
</div>
|
|
|
2019 |
</div>
|
|
|
2020 |
|
|
|
2021 |
<p>When displaying the list, <span class=
|
|
|
2022 |
"application">SQLScreens</span> will alternate the
|
|
|
2023 |
line's background between white and light grey to
|
|
|
2024 |
facilitate reading. If defined, the <code class=
|
|
|
2025 |
"literal">list_lineproc</code> procedure will be called
|
|
|
2026 |
for each displayed line, with parameters allowing it to
|
|
|
2027 |
change the line's display (for exemple, this would
|
|
|
2028 |
allow showing special rows needing attention in red).
|
|
|
2029 |
Example:</p>
|
|
|
2030 |
<pre class="programlisting">
|
|
|
2031 |
set customers(list_lineproc) custlineproc
|
|
|
2032 |
|
|
|
2033 |
</pre>
|
|
|
2034 |
|
|
|
2035 |
<p><code class="literal">list_lineproc</code> will then
|
|
|
2036 |
be called for each line with 3 parameters:</p>
|
|
|
2037 |
|
|
|
2038 |
<div class="orderedlist">
|
|
|
2039 |
<ol class="orderedlist" type="1">
|
|
|
2040 |
<li class="listitem">
|
|
|
2041 |
<p>The name of the TK text window where the line
|
|
|
2042 |
is displayed.</p>
|
|
|
2043 |
</li>
|
|
|
2044 |
|
|
|
2045 |
<li class="listitem">
|
|
|
2046 |
<p>The TK text tag name for the area associated
|
|
|
2047 |
with the line.</p>
|
|
|
2048 |
</li>
|
|
|
2049 |
|
|
|
2050 |
<li class="listitem">
|
|
|
2051 |
<p>The list of column values for this line.</p>
|
|
|
2052 |
</li>
|
|
|
2053 |
</ol>
|
|
|
2054 |
</div>
|
|
|
2055 |
|
|
|
2056 |
<p><code class="literal">list_lineproc</code> can then
|
|
|
2057 |
test one or several entries in the value list, and use
|
|
|
2058 |
the window and tag names to set attributes. The
|
|
|
2059 |
following exemple sets the ugly colors in the wine list
|
|
|
2060 |
according to the bottle count (from wines.tcl):</p>
|
|
|
2061 |
<pre class="programlisting">
|
|
|
2062 |
proc setlinecolor {w tag res} {
|
|
|
2063 |
# Get the bottle count from the value list
|
|
|
2064 |
set botcnt [lindex $res 0]
|
|
|
2065 |
# Set the background color accordingly
|
|
|
2066 |
switch $botcnt {
|
|
|
2067 |
1 {$w tag configure $tag -background red}
|
|
|
2068 |
2 {$w tag configure $tag -background orange}
|
|
|
2069 |
3 {$w tag configure $tag -background yellow}
|
|
|
2070 |
default {$w tag configure $tag -background green}
|
|
|
2071 |
}
|
|
|
2072 |
}
|
|
|
2073 |
|
|
|
2074 |
</pre>
|
|
|
2075 |
</div>
|
|
|
2076 |
</div>
|
|
|
2077 |
|
|
|
2078 |
<div class="sect2" title=
|
|
|
2079 |
"4.3.5. Miscellaneous array entries">
|
|
|
2080 |
<div class="titlepage">
|
|
|
2081 |
<div>
|
|
|
2082 |
<div>
|
|
|
2083 |
<h3 class="title"><a name="idp5870688" id=
|
|
|
2084 |
"idp5870688"></a>4.3.5. Miscellaneous array
|
|
|
2085 |
entries</h3>
|
|
|
2086 |
</div>
|
|
|
2087 |
</div>
|
|
|
2088 |
</div>
|
|
|
2089 |
|
|
|
2090 |
<div class="sect3" title="4.3.5.1. hdl">
|
|
|
2091 |
<div class="titlepage">
|
|
|
2092 |
<div>
|
|
|
2093 |
<div>
|
|
|
2094 |
<h4 class="title"><a name="idp5871328" id=
|
|
|
2095 |
"idp5871328"></a>4.3.5.1. hdl</h4>
|
|
|
2096 |
</div>
|
|
|
2097 |
</div>
|
|
|
2098 |
</div>
|
|
|
2099 |
|
|
|
2100 |
<p>The package uses this entry to store the database
|
|
|
2101 |
handle.</p>
|
|
|
2102 |
</div>
|
|
|
2103 |
|
|
|
2104 |
<div class="sect3" title="4.3.5.2. initfocus">
|
|
|
2105 |
<div class="titlepage">
|
|
|
2106 |
<div>
|
|
|
2107 |
<div>
|
|
|
2108 |
<h4 class="title"><a name="idp5872560" id=
|
|
|
2109 |
"idp5872560"></a>4.3.5.2. initfocus</h4>
|
|
|
2110 |
</div>
|
|
|
2111 |
</div>
|
|
|
2112 |
</div>
|
|
|
2113 |
|
|
|
2114 |
<p>This is the name of the window where the focus
|
|
|
2115 |
should go when the screen is reset. This can be useful
|
|
|
2116 |
for repetitive entry when you don't want to use the
|
|
|
2117 |
mouse.</p>
|
|
|
2118 |
</div>
|
|
|
2119 |
|
|
|
2120 |
<div class="sect3" title="4.3.5.3. tabcolserial">
|
|
|
2121 |
<div class="titlepage">
|
|
|
2122 |
<div>
|
|
|
2123 |
<div>
|
|
|
2124 |
<h4 class="title"><a name="idp5873904" id=
|
|
|
2125 |
"idp5873904"></a>4.3.5.3. tabcolserial</h4>
|
|
|
2126 |
</div>
|
|
|
2127 |
</div>
|
|
|
2128 |
</div>
|
|
|
2129 |
|
|
|
2130 |
<p>If there is a serial or auto_increment column,
|
|
|
2131 |
sqlscreen sets its name in there.</p>
|
|
|
2132 |
|
|
|
2133 |
<p>If the screen allows insertion, but this field is
|
|
|
2134 |
either not displayed or not modifiable (noentry), the
|
|
|
2135 |
value will be automatically reset to null before
|
|
|
2136 |
performing an insert, which will allow inserting a
|
|
|
2137 |
record by first querying for (and probably modifying)
|
|
|
2138 |
another one.</p>
|
|
|
2139 |
|
|
|
2140 |
<p>If the field is modifiable by the user, no special
|
|
|
2141 |
action will be taken.</p>
|
|
|
2142 |
|
|
|
2143 |
<p>If the beforeinsert procedure is defined for the
|
|
|
2144 |
screen, any modification is performed before calling
|
|
|
2145 |
it, to allow for a local value allocation scheme.</p>
|
|
|
2146 |
</div>
|
|
|
2147 |
|
|
|
2148 |
<div class="sect3" title="4.3.5.4. querynum">
|
|
|
2149 |
<div class="titlepage">
|
|
|
2150 |
<div>
|
|
|
2151 |
<div>
|
|
|
2152 |
<h4 class="title"><a name="idp5876928" id=
|
|
|
2153 |
"idp5876928"></a>4.3.5.4. querynum</h4>
|
|
|
2154 |
</div>
|
|
|
2155 |
</div>
|
|
|
2156 |
</div>
|
|
|
2157 |
|
|
|
2158 |
<p>This is the select result handle.</p>
|
|
|
2159 |
</div>
|
|
|
2160 |
|
|
|
2161 |
<div class="sect3" title=
|
|
|
2162 |
"4.3.5.5. sqlsc_colname_valsaved">
|
|
|
2163 |
<div class="titlepage">
|
|
|
2164 |
<div>
|
|
|
2165 |
<div>
|
|
|
2166 |
<h4 class="title"><a name="idp5878080" id=
|
|
|
2167 |
"idp5878080"></a>4.3.5.5. sqlsc_colname_valsaved</h4>
|
|
|
2168 |
</div>
|
|
|
2169 |
</div>
|
|
|
2170 |
</div>
|
|
|
2171 |
|
|
|
2172 |
<p>The package uses these entries to save the database
|
|
|
2173 |
values when a query is performed. This is used to
|
|
|
2174 |
compute the UPDATE statements (See the <a class="link"
|
|
|
2175 |
href="#SQLGENERATION" title=
|
|
|
2176 |
"Chapter 5. SQL generation">Sql
|
|
|
2177 |
generation</a> section).</p>
|
|
|
2178 |
</div>
|
|
|
2179 |
</div>
|
|
|
2180 |
</div>
|
|
|
2181 |
|
|
|
2182 |
<div class="sect1" title=
|
|
|
2183 |
"4.4. Global customization variables">
|
|
|
2184 |
<div class="titlepage">
|
|
|
2185 |
<div>
|
|
|
2186 |
<div>
|
|
|
2187 |
<h2 class="title" style="clear: both"><a name=
|
|
|
2188 |
"idp5880304" id="idp5880304"></a>4.4. Global
|
|
|
2189 |
customization variables</h2>
|
|
|
2190 |
</div>
|
|
|
2191 |
</div>
|
|
|
2192 |
</div>
|
|
|
2193 |
|
|
|
2194 |
<div class="sect2" title="4.4.1. sqlscshowstmts">
|
|
|
2195 |
<div class="titlepage">
|
|
|
2196 |
<div>
|
|
|
2197 |
<div>
|
|
|
2198 |
<h3 class="title"><a name="idp5880944" id=
|
|
|
2199 |
"idp5880944"></a>4.4.1. sqlscshowstmts</h3>
|
|
|
2200 |
</div>
|
|
|
2201 |
</div>
|
|
|
2202 |
</div>
|
|
|
2203 |
|
|
|
2204 |
<p>You can set this variable to 1 or 0 to print the SQL
|
|
|
2205 |
statements to stdout (or SQLSCLOG) when they are
|
|
|
2206 |
executed. This is not an array element but a global
|
|
|
2207 |
variable.</p>
|
|
|
2208 |
</div>
|
|
|
2209 |
|
|
|
2210 |
<div class="sect2" title="4.4.2. sqlscnobell">
|
|
|
2211 |
<div class="titlepage">
|
|
|
2212 |
<div>
|
|
|
2213 |
<div>
|
|
|
2214 |
<h3 class="title"><a name="idp5882288" id=
|
|
|
2215 |
"idp5882288"></a>4.4.2. sqlscnobell</h3>
|
|
|
2216 |
</div>
|
|
|
2217 |
</div>
|
|
|
2218 |
</div>
|
|
|
2219 |
|
|
|
2220 |
<p>You can set this to 1 to prevent use of the bell
|
|
|
2221 |
function when the end of a query is reached. The sound
|
|
|
2222 |
can become quite ennoying...</p>
|
|
|
2223 |
</div>
|
|
|
2224 |
</div>
|
|
|
2225 |
|
|
|
2226 |
<div class="sect1" title="4.5. Linking screens">
|
|
|
2227 |
<div class="titlepage">
|
|
|
2228 |
<div>
|
|
|
2229 |
<div>
|
|
|
2230 |
<h2 class="title" style="clear: both"><a name=
|
|
|
2231 |
"PROGRAMMING.LINKING" id=
|
|
|
2232 |
"PROGRAMMING.LINKING"></a>4.5. Linking
|
|
|
2233 |
screens</h2>
|
|
|
2234 |
</div>
|
|
|
2235 |
</div>
|
|
|
2236 |
</div>
|
|
|
2237 |
|
|
|
2238 |
<p>It is possible to link two screens so that a change in
|
|
|
2239 |
one screen will update the other one. This can be done in
|
|
|
2240 |
two ways.</p>
|
|
|
2241 |
|
|
|
2242 |
<div class="sect2" title="4.5.1. sqlmasterslave">
|
|
|
2243 |
<div class="titlepage">
|
|
|
2244 |
<div>
|
|
|
2245 |
<div>
|
|
|
2246 |
<h3 class="title"><a name="idp5885136" id=
|
|
|
2247 |
"idp5885136"></a>4.5.1. sqlmasterslave</h3>
|
|
|
2248 |
</div>
|
|
|
2249 |
</div>
|
|
|
2250 |
</div>
|
|
|
2251 |
|
|
|
2252 |
<p>This links the first screen to the second one so that
|
|
|
2253 |
a query in the first will run a query in the second.
|
|
|
2254 |
Example:</p>
|
|
|
2255 |
<pre class="programlisting">
|
|
|
2256 |
sqlmasterslave customer custid order ordercust
|
|
|
2257 |
|
|
|
2258 |
</pre>
|
|
|
2259 |
|
|
|
2260 |
<p>would link the customer and order screens so that the
|
|
|
2261 |
order screen is reset, the ordercust field is set to the
|
|
|
2262 |
value of the custid field and a query is run every time a
|
|
|
2263 |
query is run in customer.</p>
|
|
|
2264 |
|
|
|
2265 |
<p>Things are set up so that it is possible to have
|
|
|
2266 |
reciprocal links without creating an infinite loop.
|
|
|
2267 |
Example:</p>
|
|
|
2268 |
<pre class="programlisting">
|
|
|
2269 |
sqlmasterslave customer custid order ordercust
|
|
|
2270 |
sqlmasterslave order ordercust customer custid
|
|
|
2271 |
|
|
|
2272 |
</pre>
|
|
|
2273 |
|
|
|
2274 |
<p>is ok and would both show a customer's orders after
|
|
|
2275 |
querying in the customer screen and an order's customer
|
|
|
2276 |
after querying in the orders screen.</p>
|
|
|
2277 |
</div>
|
|
|
2278 |
|
|
|
2279 |
<div class="sect2" title="4.5.2. sqlslavemaster">
|
|
|
2280 |
<div class="titlepage">
|
|
|
2281 |
<div>
|
|
|
2282 |
<div>
|
|
|
2283 |
<h3 class="title"><a name="idp5889584" id=
|
|
|
2284 |
"idp5889584"></a>4.5.2. sqlslavemaster</h3>
|
|
|
2285 |
</div>
|
|
|
2286 |
</div>
|
|
|
2287 |
</div>
|
|
|
2288 |
|
|
|
2289 |
<p>This second type of link is used to just update a
|
|
|
2290 |
column in the target screen, without running a query
|
|
|
2291 |
there. It is useful to set the join column values.
|
|
|
2292 |
Example:</p>
|
|
|
2293 |
<pre class="programlisting">
|
|
|
2294 |
sqlslavemaster customer custid order ordercust
|
|
|
2295 |
|
|
|
2296 |
</pre>
|
|
|
2297 |
|
|
|
2298 |
<p>could be used to set the ordercust field by querying
|
|
|
2299 |
customer, typically while entering orders.</p>
|
|
|
2300 |
</div>
|
|
|
2301 |
</div>
|
|
|
2302 |
|
|
|
2303 |
<div class="sect1" title=
|
|
|
2304 |
"4.6. Controlling the number of button sets">
|
|
|
2305 |
<div class="titlepage">
|
|
|
2306 |
<div>
|
|
|
2307 |
<div>
|
|
|
2308 |
<h2 class="title" style="clear: both"><a name=
|
|
|
2309 |
"idp5892256" id=
|
|
|
2310 |
"idp5892256"></a>4.6. Controlling the number of
|
|
|
2311 |
button sets</h2>
|
|
|
2312 |
</div>
|
|
|
2313 |
</div>
|
|
|
2314 |
</div>
|
|
|
2315 |
|
|
|
2316 |
<p>Each screen in an application normally has a set of
|
|
|
2317 |
control buttons. It is sometimes useful to use only one set
|
|
|
2318 |
of buttons for several screens. This is done with the
|
|
|
2319 |
sqcommonbuttons routine. sqcommonbutons will create a TK
|
|
|
2320 |
frame with a set of control buttons inside. This set of
|
|
|
2321 |
buttons will not be linked to a particular screen, but will
|
|
|
2322 |
apply to the screen which has the current keyboard focus.
|
|
|
2323 |
Example:</p>
|
|
|
2324 |
<pre class="programlisting">
|
|
|
2325 |
sqcommonbuttons .f1.buttons
|
|
|
2326 |
|
|
|
2327 |
</pre>
|
|
|
2328 |
|
|
|
2329 |
<p>Will create the .f1.buttons frame and buttons inside
|
|
|
2330 |
there.</p>
|
|
|
2331 |
|
|
|
2332 |
<p>It is possible to create several sets of buttons (useful
|
|
|
2333 |
when there are several top level frames in the application)
|
|
|
2334 |
by calling sqcommonbuttons several times. Any of these sets
|
|
|
2335 |
will control the screen which currently has the keyboard
|
|
|
2336 |
focus.</p>
|
|
|
2337 |
|
|
|
2338 |
<p>In practice, this facility has not proved very useful
|
|
|
2339 |
because it is to easy to make mistake about where the
|
|
|
2340 |
current keyboard focus actually is.</p>
|
|
|
2341 |
|
|
|
2342 |
<p>It would be quite easy to use completely custom buttons
|
|
|
2343 |
for an application by setting the
|
|
|
2344 |
“usecommonbuttons” variable, and creating
|
|
|
2345 |
custom buttons with appropriate callbacks (look at the
|
|
|
2346 |
sqcommonbuttons code in sqlscreens.tcl).</p>
|
|
|
2347 |
</div>
|
|
|
2348 |
|
|
|
2349 |
<div class="sect1" title="4.7. Callback routines:">
|
|
|
2350 |
<div class="titlepage">
|
|
|
2351 |
<div>
|
|
|
2352 |
<div>
|
|
|
2353 |
<h2 class="title" style="clear: both"><a name=
|
|
|
2354 |
"idp5896944" id="idp5896944"></a>4.7. Callback
|
|
|
2355 |
routines:</h2>
|
|
|
2356 |
</div>
|
|
|
2357 |
</div>
|
|
|
2358 |
</div>
|
|
|
2359 |
|
|
|
2360 |
<p>The following callback routine names can be defined in
|
|
|
2361 |
the array:</p>
|
|
|
2362 |
|
|
|
2363 |
<div class="itemizedlist">
|
|
|
2364 |
<ul class="itemizedlist" type="disc">
|
|
|
2365 |
<li class="listitem">
|
|
|
2366 |
<p>afterinsert</p>
|
|
|
2367 |
</li>
|
|
|
2368 |
|
|
|
2369 |
<li class="listitem">
|
|
|
2370 |
<p>afterquery</p>
|
|
|
2371 |
</li>
|
|
|
2372 |
|
|
|
2373 |
<li class="listitem">
|
|
|
2374 |
<p>afterupdate</p>
|
|
|
2375 |
</li>
|
|
|
2376 |
|
|
|
2377 |
<li class="listitem">
|
|
|
2378 |
<p>afterdelete</p>
|
|
|
2379 |
</li>
|
|
|
2380 |
|
|
|
2381 |
<li class="listitem">
|
|
|
2382 |
<p>beforeinsert</p>
|
|
|
2383 |
</li>
|
|
|
2384 |
|
|
|
2385 |
<li class="listitem">
|
|
|
2386 |
<p>beforequery</p>
|
|
|
2387 |
</li>
|
|
|
2388 |
|
|
|
2389 |
<li class="listitem">
|
|
|
2390 |
<p>beforeupdate</p>
|
|
|
2391 |
</li>
|
|
|
2392 |
|
|
|
2393 |
<li class="listitem">
|
|
|
2394 |
<p>beforedelete</p>
|
|
|
2395 |
</li>
|
|
|
2396 |
</ul>
|
|
|
2397 |
</div>
|
|
|
2398 |
|
|
|
2399 |
<p>Example:</p>
|
|
|
2400 |
<pre class="programlisting">
|
|
|
2401 |
set customer(beforeinsert) checkcustfields
|
|
|
2402 |
|
|
|
2403 |
</pre>
|
|
|
2404 |
|
|
|
2405 |
<p>The different routines will be invoked in the following
|
|
|
2406 |
manner:</p>
|
|
|
2407 |
|
|
|
2408 |
<p>For beforexxx routines:</p>
|
|
|
2409 |
<pre class="programlisting">
|
|
|
2410 |
routinename optype arrayname
|
|
|
2411 |
|
|
|
2412 |
</pre>
|
|
|
2413 |
|
|
|
2414 |
<p>For afterxxx routines:</p>
|
|
|
2415 |
<pre class="programlisting">
|
|
|
2416 |
routinename optype txt arrayname
|
|
|
2417 |
|
|
|
2418 |
</pre>
|
|
|
2419 |
|
|
|
2420 |
<p>Where optype defines what's happening (like
|
|
|
2421 |
beforeinsert, afternext, etc...), arrayname is the affected
|
|
|
2422 |
screen's array name, and txt is the SQL text for afterxxx
|
|
|
2423 |
routines. We can't pass the text to the beforexxx routines,
|
|
|
2424 |
because they may be responsible to modify some field values
|
|
|
2425 |
that will affect the statement !</p>
|
|
|
2426 |
|
|
|
2427 |
<p>beforeinsert, afterinsert, beforeupdate, afterupdate,
|
|
|
2428 |
beforedelete and afterdelete will be called before and
|
|
|
2429 |
after inserting or updating data.</p>
|
|
|
2430 |
|
|
|
2431 |
<p>beforequery and afterquery will be called before and
|
|
|
2432 |
after doing a select, and afterquery will also be called
|
|
|
2433 |
after the user fetches the next record, rewinds the query,
|
|
|
2434 |
or resets the screen.</p>
|
|
|
2435 |
|
|
|
2436 |
<p>If one of the beforexxx routines returns anything but 0
|
|
|
2437 |
, the operation will be canceled (not run).</p>
|
|
|
2438 |
</div>
|
|
|
2439 |
|
|
|
2440 |
<div class="sect1" title=
|
|
|
2441 |
"4.8. Visible internal interfaces">
|
|
|
2442 |
<div class="titlepage">
|
|
|
2443 |
<div>
|
|
|
2444 |
<div>
|
|
|
2445 |
<h2 class="title" style="clear: both"><a name=
|
|
|
2446 |
"idp5910496" id="idp5910496"></a>4.8. Visible
|
|
|
2447 |
internal interfaces</h2>
|
|
|
2448 |
</div>
|
|
|
2449 |
</div>
|
|
|
2450 |
</div>
|
|
|
2451 |
|
|
|
2452 |
<p>In some cases it may be useful to start a database
|
|
|
2453 |
operation by a program call (as opposed to a button press
|
|
|
2454 |
by the user). This is easily feasible by calling the
|
|
|
2455 |
following routines. They all take the array name as sole
|
|
|
2456 |
argument, and use the values that are currently
|
|
|
2457 |
stored/displayed in the screen.</p>
|
|
|
2458 |
|
|
|
2459 |
<div class="itemizedlist">
|
|
|
2460 |
<ul class="itemizedlist" type="disc">
|
|
|
2461 |
<li class="listitem">
|
|
|
2462 |
<p>sqlscinsert: Generate and run an INSERT
|
|
|
2463 |
statement.</p>
|
|
|
2464 |
</li>
|
|
|
2465 |
|
|
|
2466 |
<li class="listitem">
|
|
|
2467 |
<p>sqlscupd: Generate and run an UPDATE
|
|
|
2468 |
statement.</p>
|
|
|
2469 |
</li>
|
|
|
2470 |
|
|
|
2471 |
<li class="listitem">
|
|
|
2472 |
<p>sqlscquery: Generate and run a SELECT
|
|
|
2473 |
statement.</p>
|
|
|
2474 |
</li>
|
|
|
2475 |
|
|
|
2476 |
<li class="listitem">
|
|
|
2477 |
<p>sqlscdelete: Generate and run a DELETE
|
|
|
2478 |
statement.</p>
|
|
|
2479 |
</li>
|
|
|
2480 |
|
|
|
2481 |
<li class="listitem">
|
|
|
2482 |
<p>sqlscnext: Fetch the next row in the current
|
|
|
2483 |
query.</p>
|
|
|
2484 |
</li>
|
|
|
2485 |
|
|
|
2486 |
<li class="listitem">
|
|
|
2487 |
<p>sqlscreopen: Rewind the current query.</p>
|
|
|
2488 |
</li>
|
|
|
2489 |
|
|
|
2490 |
<li class="listitem">
|
|
|
2491 |
<p>sqlscreset: Reset all data values for the
|
|
|
2492 |
screen.</p>
|
|
|
2493 |
</li>
|
|
|
2494 |
</ul>
|
|
|
2495 |
</div>
|
|
|
2496 |
|
|
|
2497 |
<p>In all cases, the effect will be exactly the same as the
|
|
|
2498 |
corresponding button press.</p>
|
|
|
2499 |
</div>
|
|
|
2500 |
|
|
|
2501 |
<div class="sect1" title="4.9. Small utility routines">
|
|
|
2502 |
<div class="titlepage">
|
|
|
2503 |
<div>
|
|
|
2504 |
<div>
|
|
|
2505 |
<h2 class="title" style="clear: both"><a name=
|
|
|
2506 |
"idp5918240" id="idp5918240"></a>4.9. Small
|
|
|
2507 |
utility routines</h2>
|
|
|
2508 |
</div>
|
|
|
2509 |
</div>
|
|
|
2510 |
</div>
|
|
|
2511 |
|
|
|
2512 |
<div class="itemizedlist">
|
|
|
2513 |
<ul class="itemizedlist" type="disc">
|
|
|
2514 |
<li class="listitem">
|
|
|
2515 |
<p>sqlsc_entrywidget arnm colname Return
|
|
|
2516 |
the name for the entry widget for arnm and
|
|
|
2517 |
colname.</p>
|
|
|
2518 |
</li>
|
|
|
2519 |
|
|
|
2520 |
<li class="listitem">
|
|
|
2521 |
<p>sqlsc_labelwidget arnm colname Return
|
|
|
2522 |
the name for the label widget for arnm and
|
|
|
2523 |
colname.</p>
|
|
|
2524 |
</li>
|
|
|
2525 |
</ul>
|
|
|
2526 |
</div>
|
|
|
2527 |
</div>
|
|
|
2528 |
</div>
|
|
|
2529 |
|
|
|
2530 |
<div class="chapter" title=
|
|
|
2531 |
"Chapter 5. SQL generation">
|
|
|
2532 |
<div class="titlepage">
|
|
|
2533 |
<div>
|
|
|
2534 |
<div>
|
|
|
2535 |
<h2 class="title"><a name="SQLGENERATION" id=
|
|
|
2536 |
"SQLGENERATION"></a>Chapter 5. SQL
|
|
|
2537 |
generation</h2>
|
|
|
2538 |
</div>
|
|
|
2539 |
</div>
|
|
|
2540 |
</div>
|
|
|
2541 |
|
|
|
2542 |
<p>Every time the user presses a button like query or update,
|
|
|
2543 |
the program will generate a SQL statement to perform the
|
|
|
2544 |
appropriate operation on the database. The following
|
|
|
2545 |
paragraphs describe how the statement is generated.</p>
|
|
|
2546 |
|
|
|
2547 |
<div class="sect1" title="5.1. Query">
|
|
|
2548 |
<div class="titlepage">
|
|
|
2549 |
<div>
|
|
|
2550 |
<div>
|
|
|
2551 |
<h2 class="title" style="clear: both"><a name=
|
|
|
2552 |
"idp5922880" id="idp5922880"></a>5.1. Query</h2>
|
|
|
2553 |
</div>
|
|
|
2554 |
</div>
|
|
|
2555 |
</div>
|
|
|
2556 |
|
|
|
2557 |
<p>The Query button generate a SELECT statement. The list
|
|
|
2558 |
of columns comes from the columns entry in the input array
|
|
|
2559 |
(all the columns by default).</p>
|
|
|
2560 |
|
|
|
2561 |
<p>The WHERE clause is built from all the fields that hold
|
|
|
2562 |
data (including the hidden ones if there are any).</p>
|
|
|
2563 |
|
|
|
2564 |
<p>For non character columns, the comparison operator used
|
|
|
2565 |
is =. For character columns, it is LIKE.</p>
|
|
|
2566 |
|
|
|
2567 |
<p>Example: for a screen with custid, custname,
|
|
|
2568 |
custfirstname, custsomenumber fields, where data was
|
|
|
2569 |
entered in custname (xxx) and custsomenumber (yyy), the
|
|
|
2570 |
statement would be:</p>
|
|
|
2571 |
|
|
|
2572 |
<p>SELECT custid,custname,custfirstname,custsomenumber from
|
|
|
2573 |
customers WHERE custname LIKE 'xxx' AND custsomenumber =
|
|
|
2574 |
yyy</p>
|
|
|
2575 |
|
|
|
2576 |
<p>If a numeric field begins with '<' or '>',
|
|
|
2577 |
whatever is entered in the field will be used as a
|
|
|
2578 |
condition in the WHERE clause, and AND'ed with the rest.
|
|
|
2579 |
(Ex: you could enter “>10” or
|
|
|
2580 |
“<>1234” , or “>10 and
|
|
|
2581 |
custsomenumber<20).</p>
|
|
|
2582 |
</div>
|
|
|
2583 |
|
|
|
2584 |
<div class="sect1" title="5.2. Add">
|
|
|
2585 |
<div class="titlepage">
|
|
|
2586 |
<div>
|
|
|
2587 |
<div>
|
|
|
2588 |
<h2 class="title" style="clear: both"><a name=
|
|
|
2589 |
"idp5926928" id="idp5926928"></a>5.2. Add</h2>
|
|
|
2590 |
</div>
|
|
|
2591 |
</div>
|
|
|
2592 |
</div>
|
|
|
2593 |
|
|
|
2594 |
<p>The Add button generates an INSERT statement. All fields
|
|
|
2595 |
which hold data are used for the values, the others are not
|
|
|
2596 |
listed. Char fields are suitably quoted. With the same
|
|
|
2597 |
example as above, the SQL statement would be:</p>
|
|
|
2598 |
|
|
|
2599 |
<p>INSERT INTO customers(custname,custsomenumber)
|
|
|
2600 |
VALUES('xxx',yyy)</p>
|
|
|
2601 |
|
|
|
2602 |
<p>There is no explicitely provided way to insert a NULL
|
|
|
2603 |
value (and certainly none for a char field).</p>
|
|
|
2604 |
|
|
|
2605 |
<p>If the table's primary key is a serial or auto_increment
|
|
|
2606 |
field, and the corresponding field is set as
|
|
|
2607 |
“noentry”, the value for the field is reset to
|
|
|
2608 |
“” before inserting to let auto_increment do
|
|
|
2609 |
its job.</p>
|
|
|
2610 |
|
|
|
2611 |
<p>There seems to be no way to retrieve the auto_increment
|
|
|
2612 |
attribute from a mysql client program, so that, when using
|
|
|
2613 |
<span class="application">MySQL</span>, we make the
|
|
|
2614 |
assumption that if an integer field is a primary key, it
|
|
|
2615 |
also has the auto_increment attribute. If the field is also
|
|
|
2616 |
set as noentry, it will be reset before inserting.</p>
|
|
|
2617 |
</div>
|
|
|
2618 |
|
|
|
2619 |
<div class="sect1" title="5.3. Update">
|
|
|
2620 |
<div class="titlepage">
|
|
|
2621 |
<div>
|
|
|
2622 |
<div>
|
|
|
2623 |
<h2 class="title" style="clear: both"><a name=
|
|
|
2624 |
"idp5931936" id=
|
|
|
2625 |
"idp5931936"></a>5.3. Update</h2>
|
|
|
2626 |
</div>
|
|
|
2627 |
</div>
|
|
|
2628 |
</div>
|
|
|
2629 |
|
|
|
2630 |
<p>The Update button generates an UPDATE statement. There
|
|
|
2631 |
are two issues: the WHERE clause and the values.</p>
|
|
|
2632 |
|
|
|
2633 |
<p>The WHERE clause is built from the columns that were
|
|
|
2634 |
designated in the updateindex list (if no such list was
|
|
|
2635 |
explicitely indicated, <span class=
|
|
|
2636 |
"application">SQLScreens</span> tries to use the primary
|
|
|
2637 |
index columns for the table. If there is no primary index,
|
|
|
2638 |
no updateindex list is built, and no Update button is
|
|
|
2639 |
created, neither can you run an update by typing Esc
|
|
|
2640 |
u).</p>
|
|
|
2641 |
|
|
|
2642 |
<p>The values in the WHERE clause are taken from those that
|
|
|
2643 |
were saved when the last Query (or Next, Rewind, Reset) was
|
|
|
2644 |
performed, which means that it is possible to update the
|
|
|
2645 |
columns in the primary index. If you try an update without
|
|
|
2646 |
having performed some query before, you will get strange
|
|
|
2647 |
error messages about missing array entries.</p>
|
|
|
2648 |
|
|
|
2649 |
<p>The values for the update are taken from the screen
|
|
|
2650 |
fields (including the possible hidden ones). All fields
|
|
|
2651 |
whose value is different from the saved value are used. If
|
|
|
2652 |
no value changed, no update is performed (and an error
|
|
|
2653 |
dialog is shown).</p>
|
|
|
2654 |
|
|
|
2655 |
<p>As opposed to what happens for SELECT and INSERT, even
|
|
|
2656 |
the fields with no data are used. For char fields, the
|
|
|
2657 |
columns are set to '', for other types, they are set to
|
|
|
2658 |
NULL. This is somewhat arbitrary, but we like it like
|
|
|
2659 |
this.</p>
|
|
|
2660 |
</div>
|
|
|
2661 |
|
|
|
2662 |
<div class="sect1" title="5.4. Delete">
|
|
|
2663 |
<div class="titlepage">
|
|
|
2664 |
<div>
|
|
|
2665 |
<div>
|
|
|
2666 |
<h2 class="title" style="clear: both"><a name=
|
|
|
2667 |
"idp5936832" id=
|
|
|
2668 |
"idp5936832"></a>5.4. Delete</h2>
|
|
|
2669 |
</div>
|
|
|
2670 |
</div>
|
|
|
2671 |
</div>
|
|
|
2672 |
|
|
|
2673 |
<p>The Delete button generates a DELETE statement. The
|
|
|
2674 |
WHERE clause is built like the SELECTs, except that no LIKE
|
|
|
2675 |
operators are used.</p>
|
|
|
2676 |
|
|
|
2677 |
<p>If some columns have NULL or zero-length string values,
|
|
|
2678 |
they will not be used in the WHERE clause. This means that
|
|
|
2679 |
more rows than expected could sometimes be affected by the
|
|
|
2680 |
statement.</p>
|
|
|
2681 |
|
|
|
2682 |
<p>For this reason, the program will create a dialog screen
|
|
|
2683 |
and ask for confirmation if more than one row would be
|
|
|
2684 |
affected by a DELETE statement.</p>
|
|
|
2685 |
</div>
|
|
|
2686 |
|
|
|
2687 |
<div class="sect1" title="5.5. Update issues">
|
|
|
2688 |
<div class="titlepage">
|
|
|
2689 |
<div>
|
|
|
2690 |
<div>
|
|
|
2691 |
<h2 class="title" style="clear: both"><a name=
|
|
|
2692 |
"idp5939296" id="idp5939296"></a>5.5. Update
|
|
|
2693 |
issues</h2>
|
|
|
2694 |
</div>
|
|
|
2695 |
</div>
|
|
|
2696 |
</div>
|
|
|
2697 |
|
|
|
2698 |
<p>Any application that displays database values and allows
|
|
|
2699 |
the user to update them has two problems:</p>
|
|
|
2700 |
|
|
|
2701 |
<div class="orderedlist">
|
|
|
2702 |
<ol class="orderedlist" type="1">
|
|
|
2703 |
<li class="listitem">
|
|
|
2704 |
<p>It must ensure that the generated UPDATE statement
|
|
|
2705 |
will really update the row that was displayed and not
|
|
|
2706 |
many other rows in the database.</p>
|
|
|
2707 |
</li>
|
|
|
2708 |
|
|
|
2709 |
<li class="listitem">
|
|
|
2710 |
<p>It must ensure that the affected row has not
|
|
|
2711 |
changed since it was displayed.</p>
|
|
|
2712 |
</li>
|
|
|
2713 |
</ol>
|
|
|
2714 |
</div>
|
|
|
2715 |
|
|
|
2716 |
<p>The first issue can be solved by certifying that the
|
|
|
2717 |
values initially retrieved uniquely define the row (for
|
|
|
2718 |
example, this would be the case if a complete primary key
|
|
|
2719 |
is included in the retrieved fields). That is why
|
|
|
2720 |
<span class="application">SQLScreens</span> insists on
|
|
|
2721 |
having an updateindex field list. This will be automaticaly
|
|
|
2722 |
generated from the primary key in some cases, or specified
|
|
|
2723 |
by the application in other cases. There are also other
|
|
|
2724 |
ways, such as using rowids or server-side cursors, but they
|
|
|
2725 |
are database-dependant.</p>
|
|
|
2726 |
|
|
|
2727 |
<p>For the second issue, <span class=
|
|
|
2728 |
"application">SQLScreens</span> takes the approach of
|
|
|
2729 |
including all the screen's fields in the WHERE clause, not
|
|
|
2730 |
only the updateindex fields. This guarantees that the
|
|
|
2731 |
UPDATE will fail if one of the fields changed in the
|
|
|
2732 |
database. It might still be possible that another field in
|
|
|
2733 |
the record (a field not used by the screen) would have
|
|
|
2734 |
changed since the query, but this change will not be
|
|
|
2735 |
affected by the new update. If this is still undesirable,
|
|
|
2736 |
you just need to include all the appropriate fields in the
|
|
|
2737 |
screen (possibly with the “nodisplay”
|
|
|
2738 |
attribute).</p>
|
|
|
2739 |
</div>
|
|
|
2740 |
</div>
|
|
|
2741 |
|
|
|
2742 |
<div class="chapter" title=
|
|
|
2743 |
"Chapter 6. The tcsq low level database access layer">
|
|
|
2744 |
<div class="titlepage">
|
|
|
2745 |
<div>
|
|
|
2746 |
<div>
|
|
|
2747 |
<h2 class="title"><a name="idp5945728" id=
|
|
|
2748 |
"idp5945728"></a>Chapter 6. The tcsq low
|
|
|
2749 |
level database access layer</h2>
|
|
|
2750 |
</div>
|
|
|
2751 |
</div>
|
|
|
2752 |
</div>
|
|
|
2753 |
|
|
|
2754 |
<p><span class="application">SQLScreens</span> uses an
|
|
|
2755 |
intermediate code layer to access the different databases in
|
|
|
2756 |
a consistent fashion. This intermediate layer is called
|
|
|
2757 |
tcsq.</p>
|
|
|
2758 |
|
|
|
2759 |
<p>The tcsq calls which are documented here may be useful as
|
|
|
2760 |
a database access layer for non-<span class=
|
|
|
2761 |
"application">SQLScreens</span> applications (for any TCL
|
|
|
2762 |
script accessing the supported databases), or in auxiliary
|
|
|
2763 |
routines inside an <span class=
|
|
|
2764 |
"application">SQLScreens</span> application (for example, for
|
|
|
2765 |
building lists of values by querying a table).</p>
|
|
|
2766 |
|
|
|
2767 |
<div class="sect1" title="6.1. Environment variables">
|
|
|
2768 |
<div class="titlepage">
|
|
|
2769 |
<div>
|
|
|
2770 |
<div>
|
|
|
2771 |
<h2 class="title" style="clear: both"><a name=
|
|
|
2772 |
"idp5950048" id=
|
|
|
2773 |
"idp5950048"></a>6.1. Environment variables</h2>
|
|
|
2774 |
</div>
|
|
|
2775 |
</div>
|
|
|
2776 |
</div>
|
|
|
2777 |
|
|
|
2778 |
<p>tcsq uses the same SQLDBTYPE environment variable as the
|
|
|
2779 |
<span class="application">SQLScreens</span> layer to define
|
|
|
2780 |
the database type.</p>
|
|
|
2781 |
</div>
|
|
|
2782 |
|
|
|
2783 |
<div class="sect1" title="6.2. API calls">
|
|
|
2784 |
<div class="titlepage">
|
|
|
2785 |
<div>
|
|
|
2786 |
<div>
|
|
|
2787 |
<h2 class="title" style="clear: both"><a name=
|
|
|
2788 |
"idp5951904" id="idp5951904"></a>6.2. API
|
|
|
2789 |
calls</h2>
|
|
|
2790 |
</div>
|
|
|
2791 |
</div>
|
|
|
2792 |
</div>
|
|
|
2793 |
|
|
|
2794 |
<div class="sect2" title="6.2.1. tcsqconnect">
|
|
|
2795 |
<div class="titlepage">
|
|
|
2796 |
<div>
|
|
|
2797 |
<div>
|
|
|
2798 |
<h3 class="title"><a name="idp5952544" id=
|
|
|
2799 |
"idp5952544"></a>6.2.1. tcsqconnect</h3>
|
|
|
2800 |
</div>
|
|
|
2801 |
</div>
|
|
|
2802 |
</div>
|
|
|
2803 |
<pre class="programlisting">
|
|
|
2804 |
tcsqconnect [host [user [passwd]]
|
|
|
2805 |
|
|
|
2806 |
</pre>
|
|
|
2807 |
|
|
|
2808 |
<p>Returns a server connection handle ( <span class=
|
|
|
2809 |
"emphasis"><em>hdl</em></span> in the following).
|
|
|
2810 |
Depending on the database type, it may actually connect
|
|
|
2811 |
to a server, or do nothing (Ex: informix).</p>
|
|
|
2812 |
</div>
|
|
|
2813 |
|
|
|
2814 |
<div class="sect2" title="6.2.2. tcsquse">
|
|
|
2815 |
<div class="titlepage">
|
|
|
2816 |
<div>
|
|
|
2817 |
<div>
|
|
|
2818 |
<h3 class="title"><a name="idp5954848" id=
|
|
|
2819 |
"idp5954848"></a>6.2.2. tcsquse</h3>
|
|
|
2820 |
</div>
|
|
|
2821 |
</div>
|
|
|
2822 |
</div>
|
|
|
2823 |
<pre class="programlisting">
|
|
|
2824 |
tcsquse hdl dbname
|
|
|
2825 |
|
|
|
2826 |
</pre>
|
|
|
2827 |
|
|
|
2828 |
<p>Associates the connection handle <span class=
|
|
|
2829 |
"emphasis"><em>hdl</em></span> with database <span class=
|
|
|
2830 |
"emphasis"><em>dbname</em></span> .</p>
|
|
|
2831 |
</div>
|
|
|
2832 |
|
|
|
2833 |
<div class="sect2" title="6.2.3. tcsqconuse">
|
|
|
2834 |
<div class="titlepage">
|
|
|
2835 |
<div>
|
|
|
2836 |
<div>
|
|
|
2837 |
<h3 class="title"><a name="idp5957392" id=
|
|
|
2838 |
"idp5957392"></a>6.2.3. tcsqconuse</h3>
|
|
|
2839 |
</div>
|
|
|
2840 |
</div>
|
|
|
2841 |
</div>
|
|
|
2842 |
<pre class="programlisting">
|
|
|
2843 |
tcsqconuse database [host]
|
|
|
2844 |
|
|
|
2845 |
</pre>
|
|
|
2846 |
|
|
|
2847 |
<p>Utility function: connect and use.</p>
|
|
|
2848 |
</div>
|
|
|
2849 |
|
|
|
2850 |
<div class="sect2" title="6.2.4. tcsqopensel">
|
|
|
2851 |
<div class="titlepage">
|
|
|
2852 |
<div>
|
|
|
2853 |
<div>
|
|
|
2854 |
<h3 class="title"><a name="idp5959168" id=
|
|
|
2855 |
"idp5959168"></a>6.2.4. tcsqopensel</h3>
|
|
|
2856 |
</div>
|
|
|
2857 |
</div>
|
|
|
2858 |
</div>
|
|
|
2859 |
<pre class="programlisting">
|
|
|
2860 |
tcsqopensel hdl stmt
|
|
|
2861 |
|
|
|
2862 |
</pre>
|
|
|
2863 |
|
|
|
2864 |
<p>Opens a query operation. <span class=
|
|
|
2865 |
"emphasis"><em>stmt</em></span> is a string holding an
|
|
|
2866 |
SQL SELECT statement. Returns a select handle (
|
|
|
2867 |
<span class="emphasis"><em>selhdl</em></span> in the
|
|
|
2868 |
following).</p>
|
|
|
2869 |
</div>
|
|
|
2870 |
|
|
|
2871 |
<div class="sect2" title="6.2.5. tcsqrew">
|
|
|
2872 |
<div class="titlepage">
|
|
|
2873 |
<div>
|
|
|
2874 |
<div>
|
|
|
2875 |
<h3 class="title"><a name="idp5961824" id=
|
|
|
2876 |
"idp5961824"></a>6.2.5. tcsqrew</h3>
|
|
|
2877 |
</div>
|
|
|
2878 |
</div>
|
|
|
2879 |
</div>
|
|
|
2880 |
<pre class="programlisting">
|
|
|
2881 |
tcsqrew selhdl
|
|
|
2882 |
|
|
|
2883 |
</pre>
|
|
|
2884 |
|
|
|
2885 |
<p>Rewinds the query associated with <span class=
|
|
|
2886 |
"emphasis"><em>selhdl</em></span> . This may actually
|
|
|
2887 |
rerun the query (INFORMIX) or be purely local
|
|
|
2888 |
(MYSQL).</p>
|
|
|
2889 |
</div>
|
|
|
2890 |
|
|
|
2891 |
<div class="sect2" title="6.2.6. tcsqclosel">
|
|
|
2892 |
<div class="titlepage">
|
|
|
2893 |
<div>
|
|
|
2894 |
<div>
|
|
|
2895 |
<h3 class="title"><a name="idp5964096" id=
|
|
|
2896 |
"idp5964096"></a>6.2.6. tcsqclosel</h3>
|
|
|
2897 |
</div>
|
|
|
2898 |
</div>
|
|
|
2899 |
</div>
|
|
|
2900 |
<pre class="programlisting">
|
|
|
2901 |
tcsqclosel selhdl
|
|
|
2902 |
|
|
|
2903 |
</pre>
|
|
|
2904 |
|
|
|
2905 |
<p>Closes a query, and frees the associated
|
|
|
2906 |
resources.</p>
|
|
|
2907 |
</div>
|
|
|
2908 |
|
|
|
2909 |
<div class="sect2" title="6.2.7. tcsqnext">
|
|
|
2910 |
<div class="titlepage">
|
|
|
2911 |
<div>
|
|
|
2912 |
<div>
|
|
|
2913 |
<h3 class="title"><a name="idp5965872" id=
|
|
|
2914 |
"idp5965872"></a>6.2.7. tcsqnext</h3>
|
|
|
2915 |
</div>
|
|
|
2916 |
</div>
|
|
|
2917 |
</div>
|
|
|
2918 |
<pre class="programlisting">
|
|
|
2919 |
tcsqnext selhdl
|
|
|
2920 |
|
|
|
2921 |
</pre>
|
|
|
2922 |
|
|
|
2923 |
<p>Returns the next row for the query, as a list of
|
|
|
2924 |
values, in the order of the columns in the SELECT
|
|
|
2925 |
statement. The last fetch returns an empty list.</p>
|
|
|
2926 |
</div>
|
|
|
2927 |
|
|
|
2928 |
<div class="sect2" title="6.2.8. tcsqexec">
|
|
|
2929 |
<div class="titlepage">
|
|
|
2930 |
<div>
|
|
|
2931 |
<div>
|
|
|
2932 |
<h3 class="title"><a name="idp5967824" id=
|
|
|
2933 |
"idp5967824"></a>6.2.8. tcsqexec</h3>
|
|
|
2934 |
</div>
|
|
|
2935 |
</div>
|
|
|
2936 |
</div>
|
|
|
2937 |
<pre class="programlisting">
|
|
|
2938 |
tcsqexec hdl stmt
|
|
|
2939 |
|
|
|
2940 |
</pre>
|
|
|
2941 |
|
|
|
2942 |
<p>Executes a non-SELECT SQL statement (Ie, INSERT,
|
|
|
2943 |
DELETE, etc...)</p>
|
|
|
2944 |
</div>
|
|
|
2945 |
|
|
|
2946 |
<div class="sect2" title="6.2.9. tcsqdiscon">
|
|
|
2947 |
<div class="titlepage">
|
|
|
2948 |
<div>
|
|
|
2949 |
<div>
|
|
|
2950 |
<h3 class="title"><a name="idp5969696" id=
|
|
|
2951 |
"idp5969696"></a>6.2.9. tcsqdiscon</h3>
|
|
|
2952 |
</div>
|
|
|
2953 |
</div>
|
|
|
2954 |
</div>
|
|
|
2955 |
<pre class="programlisting">
|
|
|
2956 |
tcsqdiscon hdl
|
|
|
2957 |
|
|
|
2958 |
</pre>
|
|
|
2959 |
|
|
|
2960 |
<p>Disconnects and frees resources associated with
|
|
|
2961 |
<span class="emphasis"><em>hdl</em></span> .</p>
|
|
|
2962 |
</div>
|
|
|
2963 |
|
|
|
2964 |
<div class="sect2" title="6.2.10. tcsqtabinfo">
|
|
|
2965 |
<div class="titlepage">
|
|
|
2966 |
<div>
|
|
|
2967 |
<div>
|
|
|
2968 |
<h3 class="title"><a name="idp5971936" id=
|
|
|
2969 |
"idp5971936"></a>6.2.10. tcsqtabinfo</h3>
|
|
|
2970 |
</div>
|
|
|
2971 |
</div>
|
|
|
2972 |
</div>
|
|
|
2973 |
<pre class="programlisting">
|
|
|
2974 |
tcsqtabinfo hdl
|
|
|
2975 |
|
|
|
2976 |
</pre>
|
|
|
2977 |
|
|
|
2978 |
<p>Returns a list of the user tables in the database
|
|
|
2979 |
referenced by hdl.</p>
|
|
|
2980 |
</div>
|
|
|
2981 |
|
|
|
2982 |
<div class="sect2" title="6.2.11. tcsqcolinfo">
|
|
|
2983 |
<div class="titlepage">
|
|
|
2984 |
<div>
|
|
|
2985 |
<div>
|
|
|
2986 |
<h3 class="title"><a name="idp5973808" id=
|
|
|
2987 |
"idp5973808"></a>6.2.11. tcsqcolinfo</h3>
|
|
|
2988 |
</div>
|
|
|
2989 |
</div>
|
|
|
2990 |
</div>
|
|
|
2991 |
<pre class="programlisting">
|
|
|
2992 |
tcsqcolinfo hdl tbl arnm
|
|
|
2993 |
|
|
|
2994 |
</pre>
|
|
|
2995 |
|
|
|
2996 |
<p>Returns information about table <span class=
|
|
|
2997 |
"emphasis"><em>tbl</em></span> into the array the name of
|
|
|
2998 |
which is specified by <span class=
|
|
|
2999 |
"emphasis"><em>arnm</em></span> .</p>
|
|
|
3000 |
</div>
|
|
|
3001 |
|
|
|
3002 |
<div class="sect2" title="6.2.12. tcsqinsertid">
|
|
|
3003 |
<div class="titlepage">
|
|
|
3004 |
<div>
|
|
|
3005 |
<div>
|
|
|
3006 |
<h3 class="title"><a name="idp5976432" id=
|
|
|
3007 |
"idp5976432"></a>6.2.12. tcsqinsertid</h3>
|
|
|
3008 |
</div>
|
|
|
3009 |
</div>
|
|
|
3010 |
</div>
|
|
|
3011 |
|
|
|
3012 |
<p>tcsqinsertid hdl</p>
|
|
|
3013 |
|
|
|
3014 |
<p>Returns the auto_increment value for the last inserted
|
|
|
3015 |
row.</p>
|
|
|
3016 |
</div>
|
|
|
3017 |
|
|
|
3018 |
<div class="sect2" title="6.2.13. tcsqquotequote">
|
|
|
3019 |
<div class="titlepage">
|
|
|
3020 |
<div>
|
|
|
3021 |
<div>
|
|
|
3022 |
<h3 class="title"><a name="idp5978048" id=
|
|
|
3023 |
"idp5978048"></a>6.2.13. tcsqquotequote</h3>
|
|
|
3024 |
</div>
|
|
|
3025 |
</div>
|
|
|
3026 |
</div>
|
|
|
3027 |
<pre class="programlisting">
|
|
|
3028 |
tcsqquotequote s
|
|
|
3029 |
|
|
|
3030 |
</pre>
|
|
|
3031 |
|
|
|
3032 |
<p>Returns a suitably escaped string, for use in sql
|
|
|
3033 |
statements.</p>
|
|
|
3034 |
</div>
|
|
|
3035 |
|
|
|
3036 |
<div class="sect2" title="6.2.14. tcsqquoteblob">
|
|
|
3037 |
<div class="titlepage">
|
|
|
3038 |
<div>
|
|
|
3039 |
<div>
|
|
|
3040 |
<h3 class="title"><a name="idp5979904" id=
|
|
|
3041 |
"idp5979904"></a>6.2.14. tcsqquoteblob</h3>
|
|
|
3042 |
</div>
|
|
|
3043 |
</div>
|
|
|
3044 |
</div>
|
|
|
3045 |
<pre class="programlisting">
|
|
|
3046 |
tcsqquoteblob s
|
|
|
3047 |
|
|
|
3048 |
</pre>
|
|
|
3049 |
|
|
|
3050 |
<p>Same for blobs.</p>
|
|
|
3051 |
</div>
|
|
|
3052 |
</div>
|
|
|
3053 |
|
|
|
3054 |
<div class="sect1" title="6.3. Programming example">
|
|
|
3055 |
<div class="titlepage">
|
|
|
3056 |
<div>
|
|
|
3057 |
<div>
|
|
|
3058 |
<h2 class="title" style="clear: both"><a name=
|
|
|
3059 |
"idp5981808" id=
|
|
|
3060 |
"idp5981808"></a>6.3. Programming example</h2>
|
|
|
3061 |
</div>
|
|
|
3062 |
</div>
|
|
|
3063 |
</div>
|
|
|
3064 |
|
|
|
3065 |
<p>The following shows a small program to search for a name
|
|
|
3066 |
in a MYSQL 'user' table. It is not supposed to be useful
|
|
|
3067 |
for any purpose except as an example.</p>
|
|
|
3068 |
<pre class="programlisting">
|
|
|
3069 |
#!/usr/local/cdkit/isqltcl
|
|
|
3070 |
package require tcsq
|
|
|
3071 |
set env(SQLDBTYPE) MYSQL
|
|
|
3072 |
|
|
|
3073 |
set hdl [tcsqconuse mysql localhost]
|
|
|
3074 |
set uname [tcsqquotequote "John O'Connell"]
|
|
|
3075 |
set qry [tcsqopensel $hdl "SELECT host,user \
|
|
|
3076 |
FROM user WHERE user LIKE '$uname'"]
|
|
|
3077 |
|
|
|
3078 |
while {[set res [tcsqnext $qry] != {}} {
|
|
|
3079 |
set host [lindex $res 0]
|
|
|
3080 |
set user [lindex $res 1]
|
|
|
3081 |
puts "Host: $host User: $user"
|
|
|
3082 |
}
|
|
|
3083 |
|
|
|
3084 |
tcsqclosel $qry
|
|
|
3085 |
tcsqdiscon $hdl
|
|
|
3086 |
exit 0
|
|
|
3087 |
|
|
|
3088 |
</pre>
|
|
|
3089 |
</div>
|
|
|
3090 |
</div>
|
|
|
3091 |
|
|
|
3092 |
<div class="chapter" title=
|
|
|
3093 |
"Chapter 7. Sample scripts">
|
|
|
3094 |
<div class="titlepage">
|
|
|
3095 |
<div>
|
|
|
3096 |
<div>
|
|
|
3097 |
<h2 class="title"><a name="SAMPLES" id=
|
|
|
3098 |
"SAMPLES"></a>Chapter 7. Sample scripts</h2>
|
|
|
3099 |
</div>
|
|
|
3100 |
</div>
|
|
|
3101 |
</div>
|
|
|
3102 |
|
|
|
3103 |
<p>Three sample scripts are provided with the package (in the
|
|
|
3104 |
samples directory). These are not real applications. For
|
|
|
3105 |
example you have to set the password in the environment or
|
|
|
3106 |
edit the scripts to change it. The goal was to keep things as
|
|
|
3107 |
simple as possible.</p>
|
|
|
3108 |
|
|
|
3109 |
<div class="variablelist">
|
|
|
3110 |
<dl>
|
|
|
3111 |
<dt><span class="term">tablescreen.tcl</span></dt>
|
|
|
3112 |
|
|
|
3113 |
<dd>
|
|
|
3114 |
<p>is a minimal application using all the automatic
|
|
|
3115 |
defaults to create a screen with all the columns in a
|
|
|
3116 |
given table. This can be a good skeleton for trying
|
|
|
3117 |
things.</p>
|
|
|
3118 |
</dd>
|
|
|
3119 |
|
|
|
3120 |
<dt><span class="term">mysqldb.tcl</span></dt>
|
|
|
3121 |
|
|
|
3122 |
<dd>
|
|
|
3123 |
<p>creates a screen to access the <span class=
|
|
|
3124 |
"application">MySQL</span> “db” table. The
|
|
|
3125 |
main goal is to show how you can arrange the fields in
|
|
|
3126 |
row-column. Otherwise, it does nothing more than
|
|
|
3127 |
tablescreen.tcl.</p>
|
|
|
3128 |
</dd>
|
|
|
3129 |
|
|
|
3130 |
<dt><span class="term">wines.tcl</span></dt>
|
|
|
3131 |
|
|
|
3132 |
<dd>
|
|
|
3133 |
<p>is something I use to manage my wine cellar. It
|
|
|
3134 |
demonstrates most of sqlscreen's features (and provides
|
|
|
3135 |
most of the incentives for new gadgets, by the
|
|
|
3136 |
way).</p>
|
|
|
3137 |
</dd>
|
|
|
3138 |
</dl>
|
|
|
3139 |
</div>
|
|
|
3140 |
|
|
|
3141 |
<p>To try wines.tcl, you will have to create tables and load
|
|
|
3142 |
the sample data in a database named “wines”. The
|
|
|
3143 |
“createloadwines.sh” shell-script will do this
|
|
|
3144 |
for you.</p>
|
|
|
3145 |
|
|
|
3146 |
<p>wines.tcl is not the perfect cellar management
|
|
|
3147 |
application, but it is quite useful right now. It is already
|
|
|
3148 |
better than my old Excel spreadsheet, and I don't need to
|
|
|
3149 |
reboot my PC under some strange Operating System to use
|
|
|
3150 |
it.</p>
|
|
|
3151 |
</div>
|
|
|
3152 |
</div>
|
|
|
3153 |
</body>
|
|
|
3154 |
</html>
|