4
<title>KInterbasDB Usage Guide</title>
6
<link rel="stylesheet" href="global.css" type="text/css">
12
<a href="index.html">Table of Contents</a>
16
<h1 class="compactHeading">KInterbasDB Usage Guide</h1>
20
<li><a href="#db_api_compliance">Python Database API 2.0 Compliance</a>
22
<li><a href="#incompatibilities">Incompatibilities</a></li>
23
<li><a href="#optional_unsupported">Unsupported Optional Features</a></li>
24
<li><a href="#optional_nominally_supported">Nominally Supported Optional Features</a></li>
25
<li><a href="#extensions_and_caveats">Extensions and Caveats</a></li>
30
<li><a href="#unsupported_database_engine_features">Unsupported Database Engine Features</a>
34
<li><a href="#tutorial">Tutorial</a>
36
<li><a href="#tutorial_connect">Connecting to a Database</a></li>
37
<li><a href="#tutorial_execute_sql">Executing SQL Statements (and Retrieving Results)</a></li>
38
<li><a href="#tutorial_stored_procedures">Calling Stored Procedures</a></li>
39
<li><a href="#tutorial_control_transactions">Controlling Transactions</a></li>
40
<li><a href="#tutorial_database_info_function">Using the <code>database_info</code> Function</a></li>
47
<a name="db_api_compliance"><h2>Python Database API 2.0 Compliance</h2></a>
49
<a name="incompatibilities"><h3>Incompatibilities</h3></a>
51
<p class="textParagraph">
52
All known outright incompatibilities between
53
KInterbasDB and the Python Database API Specification 2.0
54
have been fixed in KInterbasDB 3.0.
55
However, some optional features are not implemented (or are only nominally
56
implemented) due to limitations in
57
the Interbase/Firebird database engine (see below).
60
<a name="optional_unsupported"><h3>Unsupported Optional Features</h3></a>
62
<li><code>Cursor</code> class
64
<li><code>nextset</code> method<br>
65
<p class="textParagraph">
66
This method is not implemented because the database engine does
67
not support opening multiple result sets simultaneously
72
<li><code>rowcount</code> attribute<br>
73
<p class="textParagraph">
74
The value of this attribute is initially <code>-1</code>,
76
because the Interbase/Firebird C API does not support the
78
the number of rows affected by an executed statement.
85
<a name="optional_nominally_supported"><h3>Nominally Supported Optional Features</h3></a>
87
<li><code>Cursor</code> class
89
<li><code>arraysize</code> attribute<br>
90
<p class="textParagraph">
91
As required by the spec, the value of this attribute
92
is observed with respect to the <code>fetchmany</code>
93
method. However, changing the value of this attribute does
94
not make any difference in fetch efficiency because the
95
database engine only supports fetching a single row at a time.
100
<li><code>setinputsizes</code> method<br>
101
<p class="textParagraph">
102
Although this method is present, it does nothing, as allowed
107
<li><code>setoutputsize</code> method<br>
108
<p class="textParagraph">
109
Although this method is present, it does nothing, as allowed
112
<p class="textParagraph">
113
Note that the performance of BLOB retrieval has been greatly
114
enhanced in KInterbasDB 3.0; the implementation now behaves
115
in such a way that calling this method would almost never
116
offer any advantage even if it were implemented.
123
<a name="extensions_and_caveats"><h3>Extensions and Caveats</h3></a>
125
<li><code>connect</code> function
126
<p class="textParagraph">
127
This function supports the following optional arguments in addition
128
to those required by the spec:
130
<li><code>role</code> -
131
for connecting to a database with a specific SQL role
132
(see page 92 of the Interbase 6 Operations Guide for a
133
discussion of Interbase roles).
134
<p class="textParagraph" style="margin-bottom: 0px;">
137
<pre style="margin-top: 0px;">
138
kinterbasdb.connect(dsn="host:/path/database.gdb", user="limited_user",
139
password="pass", <strong>role</strong>="MORE_POWERFUL_ROLE")
142
<li><code>charset</code> -
143
for explicitly specifying the character set of the connection.
144
<p class="textParagraph" style="margin-bottom: 0px;">
147
<pre style="margin-top: 0px;">
148
kinterbasdb.connect(dsn="host:/path/database.gdb", user="sysdba",
149
password="masterkey", <strong>charset</strong>="UNICODE_FSS")
152
<li><code>dialect</code> -
153
for explicitly specifying the SQL dialect of the connection.
154
<p class="textParagraph">
155
In KInterbasDB 2.x, the default dialect was <code>1</code>
156
(the compatibility dialect for Interbase 5.5 and earlier).
157
In KInterbasDB 3.0, the default dialect is <code>3</code>
158
(the most featureful dialect, ideal for Interbase 6.0+
160
If you want to connect to Interbase 5.5 or earlier, you must
161
explicitly set this argument's value to <code>1</code>.
163
<p class="textParagraph" style="margin-bottom: 0px;">
166
<pre style="margin-top: 0px;">
167
kinterbasdb.connect(dsn="host:/path/database.gdb", user="sysdba",
168
password="masterkey", <strong>dialect</strong>=1)
173
<li><code>create_database</code> function<br>
174
<p class="textParagraph">
175
Creates a database according to the supplied
176
<code>CREATE DATABASE</code>
178
Returns an open connection to the newly created database.
181
<p style="margin-bottom: 0px;">
187
string containing the <code>CREATE DATABASE</code> statement.<br>
188
<p class="textParagraph">
190
need to specify a username and password as part of this statement
191
(see the Interbase/Firebird SQL Reference for syntax).
194
<li><code>dialect</code> <em>(optional)</em> -
195
the SQL dialect under which to execute the statement
196
(defaults to <code>3</code>).
201
<li><code>Connection</code> class
203
<li><code>drop_database</code> method<br>
204
<p class="textParagraph">
205
Deletes the database to which the connection is attached.
207
<p class="textParagraph">
208
This method performs the database deletion in a responsible
212
<li>raises an <code>OperationalError</code> instead of deleting
213
the database if there are other active connections to the
215
<li>deletes supporting files and logs in addition to the
216
primary database file(s)</li>
219
<p class="textParagraph">
220
This method has no arguments.
225
<li><code>dialect</code> attribute<br>
226
<p class="textParagraph">
227
This integer attribute indicates which SQL dialect the connection
230
<p class="textParagraph">
231
The value of this attribute can be changed dynamically, but that
232
is not a recommended programming practice. Instead, you should
233
specify the optional <code>dialect</code> argument of the
234
<code>connect</code> function when you first create the connection.
236
<p class="textParagraph">
237
For more information, see the documentation of the
238
<code>dialect</code> argument of the
239
<code>connect</code> function.
244
<li><code>precision_mode</code> attribute<br>
245
<p class="textParagraph">
246
Binary floating point representation of fractional numbers is
247
often slightly imprecise. For this reason, Interbase/Firebird
249
<em>floating point</em> datatypes
250
to be used where a slight
251
lack of precision is tolerable
252
(<code>FLOAT</code> and <code>DOUBLE PRECISION</code>)
254
<em>fixed point</em> datatypes
255
to be used where perfect (though finite) precision is required
256
(<code>NUMERIC</code> and <code>DECIMAL</code>).
259
<p class="textParagraph">
260
The representation of currency values is a typical situation
261
in which perfect precision is demanded.
262
For the gory details of the fixed point datatypes, see the
263
section of the Interbase 6 Data Definition Guide entitled
264
"Fixed-decimal datatypes" (page 64).
267
<p class="textParagraph">
268
Unfortunately, Python currently lacks a built-in fixed point
269
type. Those who wish to convert between the database engine's
270
fixed point types and native Python types face a choice:
272
<li>Represent fixed point values as Python floats
273
(convenient but potentially imprecise).
274
<p class="textParagraph">
276
on a typical 32-bit platform, the fractional number
277
<code>12.345</code> stored in the database in a
278
<code>NUMERIC(5,3)</code> field becomes the Python float
279
<code>12.345000000000001</code> . The Python programmer
280
can then use this value in a natural way, but some
284
<li>Represent fixed point values as scaled Python integers
285
(inconvenient but precise).
287
<p class="textParagraph">
288
For example, the fractional number
289
<code>12.345</code> stored in the database in a
290
<code>NUMERIC(5,3)</code> field becomes the Python
291
integer <code>12345</code> . The Python programmer
292
must take into account the fact that this value is scaled
293
by three decimal places. (Note that the scale factor can
294
be determined programmatically by examining<br>
295
<code>cur.description[<em>fieldPosition</em>][kinterbasdb.DESCRIPTION_PRECISION]</code> .)
301
<p class="textParagraph">
302
KInterbasDB 2.x and earlier always represented the database's
303
fixed point values as scaled Python integers. Via the
304
<code>Connection.precision_mode</code> attribute,
305
KInterbasDB 3.0 offers the Python programmer a choice between the
306
two representation schemes--between convenience and precision.
307
When a connection's <code>precision_mode</code> is <code>0</code>
308
(the default), approach #1 is used;
309
when <code>precision_mode</code> is
310
<code>1</code>, approach #2 is used.
313
<p class="textParagraph">
314
Keep in mind that the <code>precision_mode</code> affects input
315
as well as output. In <code>precision_mode</code>
316
<code>0</code>, placing the Python integer <code>12</code>
317
in a <code>NUMERIC(5,3)</code> field will result in a database
318
value of <code>12.000</code> . In <code>precision_mode</code>
319
<code>1</code>, placing the same Python integer in the same
320
field will result in a database value of <code>0.012</code> ,
321
because <code>precision_mode</code> <code>1</code> places the
322
burden of scaling on the client programmer.
325
<p class="textParagraph">
326
Note that in order to function correctly, code written for
327
KInterbasDB 2.x will require that each connection's
328
<code>precision_mode</code> be explicitly set to <code>1</code> .
334
<li><code>server_version</code> attribute <em>(read-only)</em><br>
335
<p class="textParagraph">
336
The version string of the database server
337
to which this connection is connected.
339
<p class="textParagraph">
340
For example, a connection to Firebird 1.0 on Windows has the
341
following <code>server_version</code>:<br>
342
<code>WI-V6.2.794 Firebird 1.0</code>
347
<li><code>default_tpb</code> attribute<br>
348
<p class="textParagraph">
349
The transaction parameter buffer (TPB) that will
350
be used by default for new transactions opened in the context of this
353
<p class="textParagraph">
354
TPBs are constructed by adding together
355
<code>kinterbasdb.isc_tpb_*</code>
356
bitmask constants (see the
357
Interbase API Guide for a definition of these constants' meanings).
358
By default, a connection's <code>default_tpb</code> attribute is
359
equal to <code>kinterbasdb.default_tpb</code> .
360
Changing a connection's <code>default_tpb</code> will
361
affect all transactions subsequently started on that connection,
362
unless the programmer overrides the
363
<code>default_tpb</code> by providing a
364
TPB to the connection's <code>begin</code> method when he starts
365
a transaction explicitly.
370
<li><code>execute_immediate</code> method<br>
371
<p class="textParagraph">
372
Executes a statement without caching its prepared form. The statement
373
must <em>not</em> be of a type that returns a result set.
375
<p class="textParagraph">
376
Before this method is called, a transaction must have been explicitly
377
started with the connection's <code>begin</code> method. In most cases
378
(especially cases in which the same statement--perhaps a parameterized
379
statement--is executed repeatedly), it is better to create a cursor
380
using the connection's <code>cursor</code> method, then execute the statement
381
using one of the cursor's execute methods.
383
<p style="margin-bottom: 0px;">
389
string containing the SQL statement to execute.<br>
395
<li><code>database_info</code> method<br>
396
<p class="textParagraph">
397
Wraps the Interbase C API function <code>isc_database_info</code> .
398
For documentation, see the Interbase 6 API Guide section entitled
399
"Requesting information about an attachment" (page 51).
402
<p class="textParagraph">
403
Note that this method is a <em>very thin</em> wrapper around
404
function <code>isc_database_info</code> .
405
This method does <em>not</em> attempt to interpret
406
its results except with regard to whether they are a string or an
410
<p class="textParagraph">
411
For example, requesting <code>isc_info_user_names</code> with the
413
<code>con.database_info(kinterbasdb.isc_info_user_names, 's')</code><br>
414
will return a binary string
415
containing a <em>raw</em> succession of length-name pairs. A thicker wrapper
416
might interpret those raw results and return a Python tuple, but it
417
would need to handle a multitude of special cases in order to cover
418
all possible <code>isc_info_*</code> items.
421
<p style="margin-bottom: 0px;">
425
<li><code>request</code> - one of the
426
<code>kinterbasdb.isc_info_*</code> constants.
430
<code>result_type</code> -
432
<code>'s'</code> if you expect a string result, or
433
<code>'i'</code> if you expect an integer result.
441
<li><code>Cursor</code> class
443
<li><code>description</code> attribute<br>
444
<p class="textParagraph">
445
KInterbasDB makes <strong>absolutely no guarantees</strong> about
446
<code>description</code> <strong>except</strong>
447
those required by the Python Database API Specification 2.0 (that
448
is, <code>description</code> is
449
either <code>None</code> or a sequence of 7-item sequences).
450
Therefore, client programmers should <em>not</em> rely on
451
<code>description</code> being an instance of a particular class or
456
<p class="textParagraph">
457
KInterbasDB provides several named positional constants to be
458
used as indices into a given element of <code>description</code> .
459
The contents of a <code>description</code> element are defined by
460
the DB API spec; these constants are provided merely for
464
<pre>DESCRIPTION_NAME
465
DESCRIPTION_TYPE_CODE
466
DESCRIPTION_DISPLAY_SIZE
467
DESCRIPTION_INTERNAL_SIZE
468
DESCRIPTION_PRECISION
470
DESCRIPTION_NULL_OK</pre>
472
<p class="textParagraph">
473
Here is an example of accessing the <em>name</em> of the first
474
field in the <code>description</code> of cursor <code>cur</code>:
476
<pre class="codeBlock">nameOfFirstField = cur.description[0][kinterbasdb.DESCRIPTION_NAME]</pre>
481
<li><code>fetch*</code> methods<br>
482
<p class="textParagraph">
483
KInterbasDB makes <strong>absolutely no guarantees</strong>
484
about the return value of the
485
<code>fetchone</code> / <code>fetchmany</code> / <code>fetchall</code>
486
methods <strong>except</strong> that it is a sequence indexed by
489
<p class="textParagraph">
490
KInterbasDB makes <strong>absolutely no guarantees</strong>
491
about the return value of the
492
<code>fetchonemap</code> / <code>fetchmanymap</code> / <code>fetchallmap</code>
493
methods (documented below)
494
<strong>except</strong> that it is a mapping of field name to field
497
<p class="textParagraph">
498
Therefore, client programmers should <em>not</em> rely on the return value being
499
an instance of a particular class or type.
504
<li><code>fetchonemap</code> method<br>
505
<p class="textParagraph">
506
This method is just like <code>fetchone</code>, except that it returns a mapping
507
of field name to field value, rather than a sequence.
512
<li><code>fetchmanymap</code> method<br>
513
<p class="textParagraph">
514
This method is just like <code>fetchmany</code>, except that it returns a sequence
515
of mappings of field name to field value, rather than a sequence of
521
<li><code>fetchallmap</code> method<br>
522
<p class="textParagraph">
523
This method is just like <code>fetchall</code>, except that it returns a sequence
524
of mappings of field name to field value, rather than a sequence of
535
<a name="unsupported_database_engine_features"><h2>Unsupported Database Engine Features</h2></a>
536
<p class="textParagraph">
537
KInterbasDB does not support the following features of the Interbase/Firebird
540
<li><code>ARRAY</code> datatype
541
<p class="textParagraph">
542
Although KInterbasDB 3.0 lacks direct support for the Interbase/Firebird
543
<code>ARRAY</code> datatype, it is still possible to create array
544
values by manually constructing their SQL string representations,
545
and to retrieve data stored in an array field by refering to specific
546
elements of the array using SQL.
548
<p class="textParagraph">
549
Suppose, for example, that table <code>tbl</code> has a 2-element array
550
field named <code>array_field</code>. Instead of:
552
<code>select array_field from tbl</code>
553
<p class="textParagraph">
556
<code>select array_field[1] as element1, array_field[2] as element2 from tbl</code>
558
<p class="textParagraph">
559
Direct support for arrays is planned for KInterbasDB 3.1.
561
<p class="textParagraph">
562
For more information about the database <code>ARRAY</code> datatype, see
563
page 80 of the Interbase 6 Data Definition Guide or page 149 of the
564
Interbase 6 API Guide.
569
<p class="textParagraph">
570
KInterbasDB 3.0 does not provide any way for the Python programmer to
571
listen for database events (which can be raised in stored procedures or
572
triggers with the <code>POST_EVENT</code> statement). KInterbasDB 3.1
573
will definitely support events; in fact, a prototype is already
574
written (as of 2002.06.23).
576
<p class="textParagraph">
577
For more information about database events, see page 187 of the
578
Interbase 6 API Guide.
583
<p class="textParagraph">
584
KInterbasDB 3.0 does not wrap the Interbase Services API, but support
585
is planned for KInterbasDB 3.1.
587
<p class="textParagraph">
588
For more information about the Services API, see page 199 of the
589
Interbase 6 API Guide.
597
<a name="tutorial"><h2>Tutorial</h2></a>
599
<p class="textParagraph">
600
This is not a comprehensive Python Database API tutorial, nor is it
601
comprehensive in its coverage of anything else.
602
It merely aims to demonstrate common
603
KInterbasDB usage patterns, and to illustrate useful features of
604
KInterbasDB that the Python Database API specification does not address.
607
<a name="tutorial_connect"><h3>Connecting to a Database</h3></a>
611
<p class="textParagraph">
612
A database connection is typically established with code such as this:
614
<pre class="codeBlock"><span style="color: #1111CC"># The server is named 'stalin'; the database file is at</span>
615
<span style="color: #1111CC"># 'd:/code/projects/kinterbasdb/ibtest.db'.</span>
617
<span style="color: #3333CC"><b>import</b></span> kinterbasdb
619
con = kinterbasdb.connect(
620
dsn=<span style="color: #115511">"stalin:d:/code/projects/kinterbasdb/ibtest.db"</span>,
621
user=<span style="color: #115511">"sysdba"</span>,
622
password=<span style="color: #115511">"pass"</span>
628
<p class="textParagraph">
629
Suppose we want to connect to an Interbase 5.5 server, specifying UNICODE_FSS
630
as the character set of the connection:
631
<pre class="codeBlock"><span style="color: #3333CC"><b>import</b></span> kinterbasdb
633
con = kinterbasdb.connect(
634
dsn=<span style="color: #115511">"stalin:d:/code/projects/kinterbasdb/ibtest.db"</span>,
635
user=<span style="color: #115511">"sysdba"</span>,
636
password=<span style="color: #115511">"pass"</span>,
637
dialect=1, <span style="color: #1111CC"># necessary for Interbase < 6.0</span>
638
charset=<span style="color: #115511">"UNICODE_FSS"</span> <span style="color: #1111CC"># specify a character set for the connection</span>
644
<a name="tutorial_execute_sql"><h3>Executing SQL Statements</h3></a>
646
<p class="textParagraph">
647
For this section, suppose we have a table defined and populated by the
651
<pre class="codeBlock">create table people
653
name_last varchar(20),
657
insert into people (name_last, age) values ('Yeltsin', 69);
658
insert into people (name_last, age) values ('Gorbachev', 72);
663
<p class="textParagraph">
664
This example shows the <em>simplest</em> way to
665
print the entire contents of the <code>people</code> table:
667
<pre class="codeBlock"><span style="color: #3333CC"><b>import</b></span> kinterbasdb
669
con = kinterbasdb.connect(
670
dsn=<span style="color: #115511">"stalin:d:/code/projects/kinterbasdb/ibtest.db"</span>,
671
user=<span style="color: #115511">"sysdba"</span>,
672
password=<span style="color: #115511">"pass"</span>
675
cur = con.cursor() <span style="color: #1111CC"># Get a Cursor object that operates in the context of</span>
676
<span style="color: #1111CC"># Connection con.</span>
678
cur.execute(<span style="color: #115511">"select * from people"</span>) <span style="color: #1111CC"># Execute the SELECT statement.</span>
680
<span style="color: #3333CC"><b>print</b></span> cur.fetchall() <span style="color: #1111CC"># Retrieve all rows as a sequence, then print that sequence.</span>
683
<p class="textParagraph">
686
<pre class="programOutputBlock">
687
[('Yeltsin', 69), ('Gorbachev', 72)]
693
<p class="textParagraph">
694
Let's try a more meaningful example.
695
Suppose we want to print the contents of the
696
<code>name_last</code> field for every row in the <code>people</code> table:
697
<pre class="codeBlock"><span style="color: #3333CC"><b>import</b></span> kinterbasdb
699
con = kinterbasdb.connect(
700
dsn=<span style="color: #115511">"stalin:d:/code/projects/kinterbasdb/ibtest.db"</span>,
701
user=<span style="color: #115511">"sysdba"</span>,
702
password=<span style="color: #115511">"pass"</span>
705
cur = con.cursor() <span style="color: #1111CC"># Get a Cursor object that operates in the context of</span>
706
<span style="color: #1111CC"># Connection con.</span>
708
cur.execute(<span style="color: #115511">"select name_last from people"</span>) <span style="color: #1111CC"># Execute the SELECT statement.</span>
710
<span style="color: #3333CC"><b>print</b></span> <span style="color: #115511">'NAME_LAST'</span>
711
<span style="color: #3333CC"><b>print</b></span> <span style="color: #115511">'---------'</span>
713
<span style="color: #3333CC"><b>while</b></span> 1: <span style="color: #1111CC"># Loop until instructed otherwise.</span>
714
row = cur.fetchonemap() <span style="color: #1111CC"># Get a mapping of field name to field value</span>
715
<span style="color: #1111CC"># for the next available row.</span>
716
<span style="color: #3333CC"><b>if</b></span> row <span style="color: #3333CC"><b>is</b></span> None: <span style="color: #1111CC"># If we've reached the end of the record set, end the loop.</span>
717
<span style="color: #3333CC"><b>break</b></span>
719
<span style="color: #3333CC"><b>print</b></span> row[<span style="color: #115511">'name_last'</span>] <span style="color: #1111CC"># Print the value of the name_last field in the</span>
720
<span style="color: #1111CC"># current row.</span>
724
<p class="textParagraph">
727
<pre class="programOutputBlock">
737
<p class="textParagraph">
738
The following program is a generic table printer
739
(applied in this example to <code>people</code>):
741
<pre class="codeBlock"><span style="color: #3333CC"><b>import</b></span> kinterbasdb, string
743
TABLE_NAME = <span style="color: #115511">'people'</span>
745
con = kinterbasdb.connect(
746
dsn=<span style="color: #115511">"stalin:d:/code/projects/kinterbasdb/ibtest.db"</span>,
747
user=<span style="color: #115511">"sysdba"</span>,
748
password=<span style="color: #115511">"pass"</span>
753
cur.execute(<span style="color: #115511">"select * from %s"</span> % TABLE_NAME)
755
<span style="color: #1111CC"># Print a header.</span>
756
<span style="color: #3333CC"><b>for</b></span> d <span style="color: #3333CC"><b>in</b></span> cur.description:
757
<span style="color: #3333CC"><b>print</b></span> (
759
d[kinterbasdb.DESCRIPTION_NAME],
760
d[kinterbasdb.DESCRIPTION_DISPLAY_SIZE]
763
<span style="color: #3333CC"><b>print</b></span> <span style="color: #1111CC"># Finish the header row with a newline.</span>
764
<span style="color: #3333CC"><b>print</b></span> <span style="color: #115511">'-'</span> * 78
766
<span style="color: #1111CC"># Print each row.</span>
767
<span style="color: #3333CC"><b>while</b></span> 1:
769
<span style="color: #3333CC"><b>if</b></span> row <span style="color: #3333CC"><b>is</b></span> None:
770
<span style="color: #3333CC"><b>break</b></span>
772
<span style="color: #1111CC"># Print the value of each field left-justified within the maximum </span>
773
<span style="color: #1111CC"># possible width of that field. </span>
774
<span style="color: #3333CC"><b>for</b></span> fieldPos <span style="color: #3333CC"><b>in</b></span> range(len(row)):
775
<span style="color: #3333CC"><b>print</b></span> (
778
cur.description[fieldPos][kinterbasdb.DESCRIPTION_DISPLAY_SIZE]
781
<span style="color: #3333CC"><b>print</b></span> <span style="color: #1111CC"># Finish the row with a newline.</span>
784
<p class="textParagraph">
787
<pre class="programOutputBlock">
789
------------------------------------------------------------------------------
797
<p class="textParagraph">
798
Let's insert more people into the <code>people</code> table:
800
<pre class="codeBlock"><span style="color: #3333CC"><b>import</b></span> kinterbasdb, string
802
con = kinterbasdb.connect(
803
dsn=<span style="color: #115511">"stalin:d:/code/projects/kinterbasdb/ibtest.db"</span>,
804
user=<span style="color: #115511">"sysdba"</span>,
805
password=<span style="color: #115511">"pass"</span>
811
(<span style="color: #115511">'Lebed'</span> , 56),
812
(<span style="color: #115511">'Zhirinovsky'</span> , 49)
815
<span style="color: #3333CC"><b>for</b></span> newPerson <span style="color: #3333CC"><b>in</b></span> newPeople:
816
cur.execute(<span style="color: #115511">"insert into people (name_last, age) values (?, ?)"</span>, newPerson)
818
con.commit() <span style="color: #1111CC"># The changes will not be saved unless the transaction is </span>
819
<span style="color: #1111CC"># committed explicitly.</span>
822
<p class="textParagraph">
823
Note the use of a <em>parameterized</em> SQL statement above. When dealing
824
with repetitive statements, this is faster and less error-prone than
825
assembling each SQL statement manually.
828
<p class="textParagraph">
829
After running Example 4, the table printer from Example 3
832
<pre class="programOutputBlock">
834
------------------------------------------------------------------------------
844
<a name="tutorial_stored_procedures"><h3>Calling Stored Procedures</h3></a>
846
<p class="textParagraph">
847
Interbase and Firebird support stored procedures written in a propriety
848
procedural SQL language.
849
IB/FB stored procedures can have <em>input</em> parameters and/or
850
<em>output</em> parameters, but not combined
851
<em>input/output</em> parameters.
854
<p class="textParagraph">
855
It is important to distinguish between procedures that return a
856
result set and procedures that populate and return their
857
output parameters exactly once (conceptually, these latter "return their
859
IB/FB's server-side procedural SQL syntax
860
makes no such distinction, but client programmers must do so in
862
A result set is retrieved from a stored procedure by
863
<code>SELECT</code>ing from the procedure, whereas output
864
parameters are retrieved with an <code>EXECUTE PROCEDURE</code>
869
<p class="textParagraph">
870
To retrieve a result set from a stored procedure with KInterbasDB,
871
use code such as this:
874
<pre class="codeBlock">cur.execute(<span style="color: #115511">"select output1, output2 from the_proc(?, ?)"</span>, (input1, input2))
876
<span style="color: #1111CC"># Ordinary fetch code here, such as:</span>
877
result_set = cur.fetchall()
881
<p class="textParagraph">
882
To call a stored procedure and access its output parameters
883
with KInterbasDB, use code such as this:
886
<pre class="codeBlock">cur.callproc(<span style="color: #115511">"the_proc"</span>, (input1, input2))
888
<span style="color: #1111CC"># If there are output parameters, retrieve them as though they were the</span>
889
<span style="color: #1111CC"># first row of a result set. For example:</span>
890
output_params = cur.fetchone()
895
<a name="tutorial_control_transactions"><h3>Controlling Transactions</h3></a>
897
<p class="textParagraph">
898
The Interbase engine requires that virtually every database operation
899
take place in the context of a transaction.
900
Even a typical <code>SELECT</code> statement has an associated transaction.
903
<p class="textParagraph">
904
For the sake of simplicity, KInterbasDB lets the Python programmer
905
ignore transaction management to the greatest extent allowed by the
906
Python Database API Specification 2.0. The specification says,
907
"if the database supports an auto-commit feature, this must be
908
initially off". At a minimum, therefore, it is necessary to call the
909
<code>commit</code> method of the connection in order to persist any
910
changes made to the database. Transactions left uncommitted by the
911
programmer will be <code>rollback</code>ed when the connection is
915
<p class="textParagraph">
916
KInterbasDB also supports explicit transaction management.
917
Connections have a <code>default_tpb</code> (default transaction parameter
918
buffer) attribute that can be used to specify the
919
characteristics of all transactions subsequently started on the connection.
920
The programmer can also start a transaction explicitly using the
922
<code>begin</code> method, which may optionally be passed a transaction
923
parameter buffer for that single transaction. If <code>begin</code> indeed
924
receives a transaction parameter buffer, it will be used for that
925
particular transaction, in place of the
926
connection's <code>default_tpb</code>.
929
<p class="textParagraph">
930
For more information, see
931
the documentation of <code>Connection.default_tpb</code> elsewhere in this
932
document or Chapter 5 of the Interbase 6 API Guide.
936
<p class="textParagraph">
937
The following example establishes an unobtrusive transaction to be used
938
for read-only access to the database:
940
<pre class="codeBlock"><span style="color: #3333CC"><b>import</b></span> kinterbasdb
942
con = kinterbasdb.connect(
943
<span style="color: #115511">'stalin:d:/code/projects/kinterbasdb/ibtest.db'</span>,
944
<span style="color: #115511">'sysdba'</span>, <span style="color: #115511">'pass'</span>)
946
tpb = kinterbasdb.isc_tpb_read \
947
+ kinterbasdb.isc_tpb_read_committed \
948
+ kinterbasdb.isc_tpb_rec_version
952
<span style="color: #1111CC"># Now read some data using a cursor...</span>
958
<a name="tutorial_database_info_function"><h3>Using the <code>database_info</code> Function</h3></a>
960
<p class="textParagraph">
961
The <code>kinterbasdb.database_info</code> function is a very thin wrapper
962
around the Interbase/Firebird C API function <code>isc_database_info</code>
963
(documented in the Interbase 6 API Guide section entitled
964
"Requesting information about an attachment" that begins on page 51).
967
<p class="textParagraph">
968
Reference documentation for <code>kinterbasdb.database_info</code> is
969
already provided in the
970
<a href="#extensions_and_caveats">Extensions and Caveats</a> section of
971
this document, so this tutorial only supplies an example program:
973
<pre class="codeBlock"><span style="color: #3333CC"><b>import</b></span> kinterbasdb
975
con = kinterbasdb.connect(
976
<span style="color: #115511">'stalin:d:/code/projects/kinterbasdb/ibtest.db'</span>,
977
<span style="color: #115511">'sysdba'</span>, <span style="color: #115511">'pass'</span>)
979
<span style="color: #1111CC"># Retrieving an integer info item is quite simple.</span>
980
bytesInUse = con.database_info(kinterbasdb.isc_info_current_memory, <span style="color: #115511">'i'</span>)
982
<span style="color: #3333CC"><b>print</b></span> <span style="color: #115511">'The server is currently using %d bytes of memory.'</span> % bytesInUse
984
<span style="color: #1111CC"># Retrieving a string info item is somewhat more involved, because</span>
985
<span style="color: #1111CC"># the information is returned in a raw binary buffer that must</span>
986
<span style="color: #1111CC"># be parsed according to the rules defined in the Interbase 6 API</span>
987
<span style="color: #1111CC"># Guide section entitled "Requesting buffer items and result buffer</span>
988
<span style="color: #1111CC"># values" (page 51).</span>
989
<span style="color: #1111CC"># Often, the buffer contains a succession of length-string pairs</span>
990
<span style="color: #1111CC"># (one byte telling the length of s, followed by s itself).</span>
991
<span style="color: #1111CC"># Function kinterbasdb.raw_byte_to_int is provided to convert a raw</span>
992
<span style="color: #1111CC"># byte to a Python integer (see examples below).</span>
993
buf = con.database_info(kinterbasdb.isc_info_db_id, <span style="color: #115511">'s'</span>)
995
<span style="color: #1111CC"># Parse the filename from the buffer.</span>
996
beginningOfFilename = 2
997
<span style="color: #1111CC"># The second byte in the buffer contains the size of the database filename</span>
998
<span style="color: #1111CC"># in bytes.</span>
999
lengthOfFilename = kinterbasdb.raw_byte_to_int(buf[1])
1000
filename = buf[beginningOfFilename:beginningOfFilename + lengthOfFilename]
1002
<span style="color: #1111CC"># Parse the host name from the buffer.</span>
1003
beginningOfHostName = (beginningOfFilename + lengthOfFilename) + 1
1004
<span style="color: #1111CC"># The first byte after the end of the database filename contains the size</span>
1005
<span style="color: #1111CC"># of the host name in bytes.</span>
1006
lengthOfHostName = kinterbasdb.raw_byte_to_int(buf[beginningOfHostName - 1])
1007
host = buf[beginningOfHostName:beginningOfHostName + lengthOfHostName]
1009
<span style="color: #3333CC"><b>print</b></span> (<span style="color: #115511">'We are connected to the database at %s on host %s.'</span> % (filename, host))
1012
<p class="textParagraph">
1015
<pre class="programOutputBlock">
1016
The server is currently using 17614848 bytes of memory.
1017
We are connected to the database at D:\CODE\PROJECTS\KINTER~1\IBTEST.DB on host STALIN.
1025
<a href="index.html">Table of Contents</a>
b'\\ No newline at end of file'