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
|
<title>Generic Tcl Database Interface API</title>
<BODY BGCOLOR="#ffffff" LINK="#0000ff" ALINK="#ff0000" TEXT="#000000">
<center>
<h2>Generic Tcl Database Interface API</h2>
<font size=-1>$Id: api.html,v 1.6 1998/07/23 01:54:36 tdarugar Exp $</font>
</center>
<p>
Your code must load the sql package using the normal method:
<code><font color="#000099">package require sql</font></code> .<br>
Alternatively you can simply load the shared object using something like:
<code><font color="#000099">load ./sql.so</font></code>
<p>
The sql package adds only a single command to tcl:
<code><font color="#000099">sql</font></code> . This command
takes several subcommands, as listed below. The subcommands
are always the first argument to the
<code><font color="#000099">sql</font></code> command. The second
argument, if necessary, is the handle for a given connection.
<p>
Normal usage would be to create a connection via
<code><font color="#000099">connect</font></code>, select a
database via <code><font color="#000099">selectdb</font></code>,
query or execute sql commands via
<code><font color="#000099">query</font></code> and
<code><font color="#000099">exec</font></code>, and
close the connection using
<code><font color="#000099">disconnect</font></code> .
<dl>
<dt><b>connect</b> <i><host></i> <i><userid> <password></i>
<dd>Connect to the database. Returns the handle from that connection. <br>
Takes two optional arguments, the userid and password.<br>
<code><font color="#000099">set conn [sql connect]</font></code><br>
<code><font color="#000099">set conn2 [sql connect host someuser somepassword]</font></code>
<dt><b>disconnect</b> <i>handle</i>
<dd>Disconnect the given connection.<br>
<code><font color="#000099">sql disconnect $conn</font></code>
<dt><b>selectdb</b> <i>handle dbname</i>
<dd>Select the <i>dbname</i> database to be used on the given handle.
After doing a <i>connect</i> you must <i>selectdb</i>. <br>
<code><font color="#000099">sql selectdb $conn mydatabase</font></code>
<dt><b>exec</b> <i>handle statement</i>
<dd>Execute a sql statement.<br>
<code><font color="#000099">sql exec $conn "create table sample (x integer)"</font></code>
<dt><b>query</b> <i>handle statement</i>
<dd>Query the database for some results (generally a select
statement). Use with <code><font color="#000099">fetchrow</font></code> and <code><font color="#000099">endquery</font></code>. <br>
<code><font color="#000099">sql query $conn "select * from sample where x > 3"</font></code>
<dt><b>fetchrow</b> <i>handle</i>
<dd>Fetch the next row of results. Must be executed after a
<code><font color="#000099">query</font></code> statement. The row is returned as a tcl
list. If there are no more rows available an empty string
is returned.<br>
<code><font color="#000099">while {[set row [sql fetchrow $conn]] != ""} { puts $row }</font></code>
</dl>
<p>
<hr width=50%>
<a name=sample><h3>Sample Usage:</h3></a>
Also see
<a href="sample.simple.txt">sample.simple.txt</a> and
<a href="sample.full.txt">sample.full.txt</a> .
<pre>
# Load the sql package:
package require sql
# Connect to the database
set conn [sql connect]
# select the database 'test' to be used.
sql selectdb $conn test
# Create a table and put some data in it:
sql exec $conn "create table junk (i integer, r real, s char(10))"
# Put some dummy data in:
for {set i 0} {$i < 10} {incr i} {
sql exec $conn "insert into junk values ($i, $i.01, 'xx $i xx')"
}
# Do a select and display the results
sql query $conn "select * from junk where i > 3"
while {[set row [sql fetchrow $conn]] != ""} {
puts "row = $row"
}
sql endquery $conn
sql exec $conn "drop table junk"
sql disconnect $conn
</pre>
<b>Results:</b>
<pre>
row = 4 4.0100 {xx 4 xx}
row = 5 5.0100 {xx 5 xx}
row = 6 6.0100 {xx 6 xx}
row = 7 7.0100 {xx 7 xx}
row = 8 8.0100 {xx 8 xx}
row = 9 9.0100 {xx 9 xx}
</pre>
|
