1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
|
<html>
<head>
<!-- This file has been generated by unroff 1.0, 09/17/01 08:34:38. -->
<!-- Do not edit! -->
<STYLE TYPE="text/css">
<!--
A:link{text-decoration:none}
A:visited{text-decoration:none}
A:active{text-decoration:none}
-->
</STYLE>
<title>ploticus: sql</title>
<body bgcolor=D0D0EE vlink=0000FF>
<br>
<br>
<center>
<table cellpadding=2 bgcolor=FFFFFF width=550 ><tr>
<td>
<table cellpadding=2 width=550><tr>
<td><br><h2>SQL</h2></td>
<td align=right>
<small>
<a href="../doc/Welcome.html"><img src="../doc/ploticus.gif" border=0></a><br>
<a href="../doc/Welcome.html">Welcome</a>
<a href="../gallery/index.html">Gallery</a>
<a href="../doc/Contents.html">Handbook</a>
<td></tr></table>
</td></tr>
<td>
<br>
<br>
<title>Manual page for SQL(PL)</title>
</head>
<body>
<h2>
<tt>#sql [</tt><i>option</i><tt>] </tt><i>sql command</i><tt> [#endsql]</tt>
<br><br><br>
<hr></h2>
<h2>DESCRIPTION</h2>
This directive allows SQL code to be embedded in scripts, and
submits the embedded SQL code to your database.<tt> </tt>
<p>
For SQL SELECT commands, an <i>option</i> may be given; available
options (described below) are <tt>#load</tt>, <tt>#processrows</tt>, and several variations of
<tt>#dump</tt>.
If no <i>option</i> is given, <tt>#processrows</tt> is the default.<tt> </tt>
No option should be used with SQL INSERT, UPDATE, or DELETE.<tt> </tt>
<p>
<i>sql command</i> may use a single line construct or a
multi-line construct formatted as in examples 2 or 3 below.
<tt>#endsql</tt> must be used to terminate a multi-line construct.<tt> </tt>
<p>
For all SQL commands except SELECT,
the status of the command execution will be available in
the @_STATUS script variable (0 indicates success).<tt> </tt>
<p>
Two separate SQL database connections are available. For connection 1,
use <b>#sql</b>; for connection 2, use <b>#sql2</b> instead of <b>#sql</b>
(all other syntax is the same). When using the SQL functions
the connection may be specified using the <tt>dbc</tt> parameter (see below).<tt> </tt>
<p>
<a href="plshellsql.html">
Using #sql in ploticus scripts
</a>
<br><br><br>
<h2>EXAMPLES</h2>
Example 1:
<dl>
<dt><dd><p>
<tt>#sql insert into transactions values ( "TR6824", 155.43, "K" )</tt>
</dl>
<p>
Example 2 shows the proper way to set up a multi-line construct:
<dl>
<dt><dd><p>
<pre>
#sql
update transactions
set amount = 158.23, type = "P"
where tracknum = "@TNUM"
#endsql
</pre>
</dl>
<p>
Example 3 will load all retrieved fields as script variables.<tt> </tt>
It uses the 2nd database connection.<tt> </tt>
Query should retrieve one row.<tt> </tt>
<dl>
<dt><dd><p>
<pre>
#sql2 #load
select * from people where people_id = @people_id
#endsql
</pre>
</dl>
<p>
Example 4 is like above except written as a single line:
<dl>
<dt><dd><p>
<tt>#sql2 #load select name as venue from venues where venue_id = @v</tt>
</dl>
<p>
<br><br><br>
<h2>OPTIONS</h2>
For SQL SELECT commands that produce results, an <i>option</i>
may be specified to specify how the results will be displayed
or captured. If no <i>option</i> is given <tt>#processrows</tt>
is performed.<tt> </tt>
The <i>option</i>s all begin with pound sign (#) to distinguish them
from SQL command content.
<p>
<b>#load</b>
<dl>
<dt><dd><p>
The retrieved fields will be loaded as script variables.<tt> </tt>
Query should retrieve one row; if it retrieves multiple rows the first one
will be used.<tt> </tt>
If the query got no results, the variables will be set to a zero length string ("").<tt> </tt>
The following example would set script variables username and pw to the
retrieved values:
<pre>
#sql #load
select username, password as pw
from users
where session_id = "@SESS_ID"
#endsql
</pre>
</dl>
<p>
<b>#dumptab</b>
<dl>
<dt><dd><p>
All rows of results will be streamed in tab-delimited format.
Example:
<pre>
#sql #dumptab select prog_id, prog_name from proglist
</pre>
</dl>
<p>
<b>#dumphtml</b>
<dl>
<dt><dd><p>
All rows of results will be streamed in HTML table format.
Example:
<pre>
#sql #dumphtml select * from venues
</pre>
</dl>
<p>
<b>#dump</b>
<dl>
<dt><dd><p>
All rows of results will be streamed with no automatic field separation.<tt> </tt>
The SQL SELECT command must supply any necessary field separation.<tt> </tt>
Example:
<pre>
#sql #dump select id, " ", lastname, ", ", firstname from people
</pre>
</dl>
<p>
<b>#dumpsilent</b>
<dl>
<dt><dd><p>
All rows of results will be retrieved then discarded. Used to
get a count of the number of rows.<tt> </tt>
Example:
<pre>
#sql #dumpsilent select * from venues
#set N = $sqlrowcount()
</pre>
</dl>
<p>
<b>#processrows</b>
<dl>
<dt><dd><p>
Indicates that results will be retrieved and processed later one row at a time
using the <tt>$sqlrow()</tt> function (see below).<tt> </tt>
When processing rows, retrieved fields are available as script variables
(if a name has an embedded periods (.) these are converted to underscores
since script variable names cannot contain periods).<tt> </tt>
Example:
<dt><dd><p>
<pre>
#sql #processrows
select meascode, measdesc, value from mstats
where project_id = @PROJID
#endsql
// get the result rows..
#while $sqlrow = 0
// access the fields as variables..
#if @meascode like A*
... etc...
#endloop
</pre>
<br><br><br>
</dl>
<h2>SQL-RELATED FUNCTIONS</h2>
There are several SQL-related functions.<tt> </tt>
All of them take <tt>dbc</tt> as an optional
first argument. <tt>dbc</tt> specifies the database connection.<tt> </tt>
Two database connections are available. They are identified as
<tt>1</tt> and <tt>2</tt>. If <tt>dbc</tt> is omitted, connection# <tt>1</tt>
is assumed.<tt> </tt>
<p>
<b>$sqlrow( dbc )</b>
<dl>
<dt><dd><p>
Retrieve next row of results. After this is called, result fields
will be accessible by @name.
NULL fields will be converted to "".<tt> </tt>
Returns 0 to indicate success, 1 to
indicate no row retrieved and no more rows, or an error code.<tt> </tt>
<br>
Example: <tt>#call $sqlrow()</tt>
<br>
Example: <tt>#call $sqlrow( 2 )</tt>
</dl>
<p>
<b>$sqlrowcount( dbc )</b>
<dl>
<dt><dd><p>
Return number of rows retrieved/affected by most recently executed
sql command. The exact behavior of row count may depend on
the type of database being used.
<dt><dd><p>
With
<a href="pocketsql.html">
pocketSQL
</a>
, the row count is available immediately after an
<a href="sql.html">
#sql
</a>
executes. This row count is exact except when SELECT is used with
DISTINCT or LIMIT and result rows are retrieved individually
using $sqlrow() in which case the count will be exact after all rows have been retrieved.<tt> </tt>
<dt><dd><p>
Example: <tt>#if $sqlrowcount( ) == 0</tt>
<br>
Example: <tt>#if $sqlrowcount( 2 ) == 0</tt>
</dl>
<p>
<b>$sqlgetnames( dbc, outmode )</b>
<dl>
<dt><dd><p>
Using the most recently submitted sql SELECT command,
write result field names to standard output.<tt> </tt>
<i>outmode</i> may be <tt>dumptab</tt> for
tab delimited field names, or <tt>dumphtml</tt> for an html table row.<tt> </tt>
Returns 0 to indicate success, or a non-zero error code.<tt> </tt>
<br>
Example: <tt>#call $sqlgetnames( dumptab )</tt>
</dl>
<p>
<b>$sqlprefix( dbc, prefix )</b>
<dl>
<dt><dd><p>
Set a prefix to be prepended to all result field names retrieved
by the next SELECT command. This may be used to prevent result field names from
colliding with existing script variable names.<tt> </tt>
Must be used after the
<a href="sql.html">
#sql
</a>
statement and before <tt>$sqlrow()</tt>.<tt> </tt>
The prefix remains in effect for the current query only.<tt> </tt>
<br>
Example: <tt>#call $sqlprefix( "s" )</tt>
</dl>
<p>
<b>$sqlstripprefix( dbc, prefix )</b>
<dl>
<dt><dd><p>
Indicates that <i>prefix</i> should be removed from
the beginning of any result field name where it appears, for example to
remove table name prefix from join results.<tt> </tt>
Must be used after the
<a href="sql.html">
#sql
</a>
statement, and before <tt>$sqlrow()</tt>.<tt> </tt>
Remains in effect for the current query only.<tt> </tt>
<br>
Example: <tt>#call $sqlstripprefix( "people." )</tt>
<br><br><br>
</dl>
<h2>NOTES</h2>
<p>
Interfaces to databases are implemented in <tt>dbinterface.c</tt>
<p>
<b>#sql</b> cannot be intermingled with <b>#shell</b>.<tt> </tt>
<br>
<br>
</td></tr>
<td align=right>
<a href="Welcome.html">
<img src="../doc/ploticus.gif" border=0></a><br><small>data display engine <br>
<a href="../doc/Copyright.html">Copyright Steve Grubb</a>
<br>
<br>
<center>
<img src="../gallery/all.gif">
</center>
</td></tr>
</table>
<p><hr>
Markup created by <em>unroff</em> 1.0, <tt> </tt> <tt> </tt>September 17, 2001.
</body>
</html>
|
