~ubuntu-branches/ubuntu/natty/python-kinterbasdb/natty-updates : revision 1

1

<html>

2

<head>

3

4

<title>KInterbasDB Usage Guide</title>

5

6

7

8

</head>

9

10

<body>

11

12

<a href="index.html">Table of Contents</a>

13

14

<hr>

15

16

<h1 class="compactHeading">KInterbasDB Usage Guide</h1>

17

18

<h3>Contents</h3>

19

<ul>

20

<li><a href="#db_api_compliance">Python Database API 2.0 Compliance</a>

21

<ul>

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>

26

</ul>

27

28

</li>

29

30

<li><a href="#unsupported_database_engine_features">Unsupported Database Engine Features</a>

31

32

</li>

33

34

<li><a href="#tutorial">Tutorial</a>

35

<ul>

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>

41

</ul>

42

</li>

43

</ul>

44

45

<hr>

46

47

<a name="db_api_compliance"><h2>Python Database API 2.0 Compliance</h2></a>

48

49

<a name="incompatibilities"><h3>Incompatibilities</h3></a>

50

51

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).

58

59

60

<a name="optional_unsupported"><h3>Unsupported Optional Features</h3></a>

61

<ul>

62

<li><code>Cursor</code> class

63

<ul>

64

<li><code>nextset</code> method

65

66

This method is not implemented because the database engine does

67

not support opening multiple result sets simultaneously

68

with a single cursor.

69

70

71

</li>

72

<li><code>rowcount</code> attribute

73

74

The value of this attribute is initially <code>-1</code>,

75

and it never changes,

76

because the Interbase/Firebird C API does not support the

77

determination of

78

the number of rows affected by an executed statement.

79

80

</li>

81

</ul>

82

</li>

83

</ul>

84

85

<a name="optional_nominally_supported"><h3>Nominally Supported Optional Features</h3></a>

86

<ul>

87

<li><code>Cursor</code> class

88

<ul>

89

<li><code>arraysize</code> attribute

90

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.

96

97

98

</li>

99

100

<li><code>setinputsizes</code> method

101

102

Although this method is present, it does nothing, as allowed

103

by the spec.

104

105

106

</li>

107

<li><code>setoutputsize</code> method

108

109

Although this method is present, it does nothing, as allowed

110

by the spec.

111

112

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.

117

118

</li>

119

</ul>

120

</li>

121

</ul>

122

123

<a name="extensions_and_caveats"><h3>Extensions and Caveats</h3></a>

124

<ul>

125

<li><code>connect</code> function

126

127

This function supports the following optional arguments in addition

128

to those required by the spec:

129

<ul>

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

135

Example:

136

137

138

kinterbasdb.connect(dsn="host:/path/database.gdb", user="limited_user",

139

password="pass", role="MORE_POWERFUL_ROLE")

140

</pre>

141

</li>

142

<li><code>charset</code> -

143

for explicitly specifying the character set of the connection.

144

145

Example:

146

147

148

kinterbasdb.connect(dsn="host:/path/database.gdb", user="sysdba",

149

password="masterkey", charset="UNICODE_FSS")

150

</pre>

151

</li>

152

<li><code>dialect</code> -

153

for explicitly specifying the SQL dialect of the connection.

154

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+

159

and Firebird).

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>.

162

163

164

Example:

165

166

167

kinterbasdb.connect(dsn="host:/path/database.gdb", user="sysdba",

168

password="masterkey", dialect=1)

169

</pre>

170

</ul>

171

172

</li>

173

<li><code>create_database</code> function

174

175

Creates a database according to the supplied

176

<code>CREATE DATABASE</code>

177

SQL statement.

178

Returns an open connection to the newly created database.

179

180

181

182

Arguments:

183

184

<ul>

185

<li>

186

<code>sql</code> -

187

string containing the <code>CREATE DATABASE</code> statement.

188

189

Note that you may

190

need to specify a username and password as part of this statement

191

(see the Interbase/Firebird SQL Reference for syntax).

192

193

</li>

194

<li><code>dialect</code> (optional) -

195

the SQL dialect under which to execute the statement

196

(defaults to <code>3</code>).

197

</li>

198

</ul>

199

200

</li>

201

<li><code>Connection</code> class

202

<ul>

203

<li><code>drop_database</code> method

204

205

Deletes the database to which the connection is attached.

206

207

208

This method performs the database deletion in a responsible

209

fashion.

210

Specifically, it

211

<ul>

212

<li>raises an <code>OperationalError</code> instead of deleting

213

the database if there are other active connections to the

214

database</li>

215

<li>deletes supporting files and logs in addition to the

216

primary database file(s)</li>

217

</ul>

218

219

220

This method has no arguments.

221

222

223

</li>

224

225

<li><code>dialect</code> attribute

226

227

This integer attribute indicates which SQL dialect the connection

228

is using.

229

230

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.

235

236

237

For more information, see the documentation of the

238

<code>dialect</code> argument of the

239

<code>connect</code> function.

240

241

242

</li>

243

244

<li><code>precision_mode</code> attribute

245

246

Binary floating point representation of fractional numbers is

247

often slightly imprecise. For this reason, Interbase/Firebird

248

offers

249

floating point datatypes

250

to be used where a slight

251

lack of precision is tolerable

252

(<code>FLOAT</code> and <code>DOUBLE PRECISION</code>)

253

and

254

fixed point datatypes

255

to be used where perfect (though finite) precision is required

256

(<code>NUMERIC</code> and <code>DECIMAL</code>).

257

258

259

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).

265

266

267

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:

271

<ol>

272

<li>Represent fixed point values as Python floats

273

(convenient but potentially imprecise).

274

275

For example,

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

281

precision is lost.

282

283

</li>

284

<li>Represent fixed point values as scaled Python integers

285

(inconvenient but precise).

286

287

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

295

<code>cur.description[fieldPosition][kinterbasdb.DESCRIPTION_PRECISION]</code> .)

296

297

</li>

298

</ol>

299

300

301

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.

311

312

313

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.

323

324

325

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> .

329

330

331

332

</li>

333

334

<li><code>server_version</code> attribute (read-only)

335

336

The version string of the database server

337

to which this connection is connected.

338

339

340

For example, a connection to Firebird 1.0 on Windows has the

341

following <code>server_version</code>:

342

<code>WI-V6.2.794 Firebird 1.0</code>

343

344

345

</li>

346

347

<li><code>default_tpb</code> attribute

348

349

The transaction parameter buffer (TPB) that will

350

be used by default for new transactions opened in the context of this

351

connection.

352

353

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.

366

367

368

</li>

369

370

<li><code>execute_immediate</code> method

371

372

Executes a statement without caching its prepared form. The statement

373

must not be of a type that returns a result set.

374

375

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.

382

383

384

Arguments:

385

386

<ul>

387

<li>

388

<code>sql</code> -

389

string containing the SQL statement to execute.

390

</li>

391

</ul>

392

393

</li>

394

395

<li><code>database_info</code> method

396

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).

400

401

402

403

Note that this method is a very thin wrapper around

404

function <code>isc_database_info</code> .

405

This method does not attempt to interpret

406

its results except with regard to whether they are a string or an

407

integer.

408

409

410

411

For example, requesting <code>isc_info_user_names</code> with the

412

call

413

<code>con.database_info(kinterbasdb.isc_info_user_names, 's')</code>

414

will return a binary string

415

containing a raw 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.

419

420

421

422

Arguments:

423

424

<ul>

425

<li><code>request</code> - one of the

426

<code>kinterbasdb.isc_info_*</code> constants.

427

</li>

428

429

<li>

430

<code>result_type</code> -

431

must be either

432

<code>'s'</code> if you expect a string result, or

433

<code>'i'</code> if you expect an integer result.

434

</li>

435

</ul>

436

437

</li>

438

</ul>

439

</li>

440

441

<li><code>Cursor</code> class

442

<ul>

443

<li><code>description</code> attribute

444

445

KInterbasDB makes absolutely no guarantees about

446

<code>description</code> except

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 not rely on

451

<code>description</code> being an instance of a particular class or

452

type.

453

454

455

456

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

461

convenience.

462

463

464

<pre>DESCRIPTION_NAME

465

DESCRIPTION_TYPE_CODE

466

DESCRIPTION_DISPLAY_SIZE

467

DESCRIPTION_INTERNAL_SIZE

468

DESCRIPTION_PRECISION

469

DESCRIPTION_SCALE

470

DESCRIPTION_NULL_OK</pre>

471

472

473

Here is an example of accessing the name of the first

474

field in the <code>description</code> of cursor <code>cur</code>:

475

476

<pre class="codeBlock">nameOfFirstField = cur.description[0][kinterbasdb.DESCRIPTION_NAME]</pre>

477

478

479

</li>

480

481

<li><code>fetch*</code> methods

482

483

KInterbasDB makes absolutely no guarantees

484

about the return value of the

485

<code>fetchone</code> / <code>fetchmany</code> / <code>fetchall</code>

486

methods except that it is a sequence indexed by

487

field position.

488

489

490

KInterbasDB makes absolutely no guarantees

491

about the return value of the

492

<code>fetchonemap</code> / <code>fetchmanymap</code> / <code>fetchallmap</code>

493

methods (documented below)

494

except that it is a mapping of field name to field

495

value.

496

497

498

Therefore, client programmers should not rely on the return value being

499

an instance of a particular class or type.

500

501

502

</li>

503

504

<li><code>fetchonemap</code> method

505

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.

508

509

510

</li>

511

512

<li><code>fetchmanymap</code> method

513

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

516

sequences.

517

518

519

</li>

520

521

<li><code>fetchallmap</code> method

522

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

525

sequences.

526

527

528

</li>

529

</ul>

530

</li>

531

</ul>

532

533

<hr>

534

535

<a name="unsupported_database_engine_features"><h2>Unsupported Database Engine Features</h2></a>

536

537

KInterbasDB does not support the following features of the Interbase/Firebird

538

database engine:

539

<ul>

540

<li><code>ARRAY</code> datatype

541

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.

547

548

549

Suppose, for example, that table <code>tbl</code> has a 2-element array

550

field named <code>array_field</code>. Instead of:

551

552

<code>select array_field from tbl</code>

553

554

one could use

555

556

<code>select array_field[1] as element1, array_field[2] as element2 from tbl</code>

557

558

559

Direct support for arrays is planned for KInterbasDB 3.1.

560

561

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.

565

566

567

</li>

568

<li>database events

569

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).

575

576

577

For more information about database events, see page 187 of the

578

Interbase 6 API Guide.

579

580

581

</li>

582

<li>Services API

583

584

KInterbasDB 3.0 does not wrap the Interbase Services API, but support

585

is planned for KInterbasDB 3.1.

586

587

588

For more information about the Services API, see page 199 of the

589

Interbase 6 API Guide.

590

591

</li>

592

</ul>

593

594

595

<hr>

596

597

<a name="tutorial"><h2>Tutorial</h2></a>

598

599

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.

605

606

607

<a name="tutorial_connect"><h3>Connecting to a Database</h3></a>

608

609

<h4>Example 1</h4>

610

611

612

A database connection is typically established with code such as this:

613

614

<pre class="codeBlock"># The server is named 'stalin'; the database file is at

615

# 'd:/code/projects/kinterbasdb/ibtest.db'.

616

617

import kinterbasdb

618

619

con = kinterbasdb.connect(

620

dsn="stalin:d:/code/projects/kinterbasdb/ibtest.db",

621

user="sysdba",

622

password="pass"

623

)

624

</pre>

625

626

<h4>Example 2</h4>

627

628

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">import kinterbasdb

632

633

con = kinterbasdb.connect(

634

dsn="stalin:d:/code/projects/kinterbasdb/ibtest.db",

635

user="sysdba",

636

password="pass",

637

dialect=1, # necessary for Interbase < 6.0

638

charset="UNICODE_FSS" # specify a character set for the connection

639

)

640

</pre>

641

642

643

644

<a name="tutorial_execute_sql"><h3>Executing SQL Statements</h3></a>

645

646

647

For this section, suppose we have a table defined and populated by the

648

following SQL code:

649

650

651

<pre class="codeBlock">create table people

652

(

653

name_last varchar(20),

654

age integer

655

);

656

657

insert into people (name_last, age) values ('Yeltsin', 69);

658

insert into people (name_last, age) values ('Gorbachev', 72);

659

</pre>

660

661

<h4>Example 1</h4>

662

663

664

This example shows the simplest way to

665

print the entire contents of the <code>people</code> table:

666

667

<pre class="codeBlock">import kinterbasdb

668

669

con = kinterbasdb.connect(

670

dsn="stalin:d:/code/projects/kinterbasdb/ibtest.db",

671

user="sysdba",

672

password="pass"

673

)

674

675

cur = con.cursor() # Get a Cursor object that operates in the context of

676

# Connection con.

677

678

cur.execute("select * from people") # Execute the SELECT statement.

679

680

print cur.fetchall() # Retrieve all rows as a sequence, then print that sequence.

681

</pre>

682

683

684

Sample output:

685

686

687

[('Yeltsin', 69), ('Gorbachev', 72)]

688

</pre>

689

690

691

<h4>Example 2</h4>

692

693

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">import kinterbasdb

698

699

con = kinterbasdb.connect(

700

dsn="stalin:d:/code/projects/kinterbasdb/ibtest.db",

701

user="sysdba",

702

password="pass"

703

)

704

705

cur = con.cursor() # Get a Cursor object that operates in the context of

706

# Connection con.

707

708

cur.execute("select name_last from people") # Execute the SELECT statement.

709

710

print 'NAME_LAST'

711

print '---------'

712

713

while 1: # Loop until instructed otherwise.

714

row = cur.fetchonemap() # Get a mapping of field name to field value

715

# for the next available row.

716

if row is None: # If we've reached the end of the record set, end the loop.

717

break

718

719

print row['name_last'] # Print the value of the name_last field in the

720

# current row.

721

</pre>

722

723

724

725

Sample output:

726

727

728

NAME_LAST

729

---------

730

Yeltsin

731

Gorbachev

732

</pre>

733

734

735

<h4>Example 3</h4>

736

737

738

The following program is a generic table printer

739

(applied in this example to <code>people</code>):

740

741

<pre class="codeBlock">import kinterbasdb, string

742

743

TABLE_NAME = 'people'

744

745

con = kinterbasdb.connect(

746

dsn="stalin:d:/code/projects/kinterbasdb/ibtest.db",

747

user="sysdba",

748

password="pass"

749

)

750

751

cur = con.cursor()

752

753

cur.execute("select * from %s" % TABLE_NAME)

754

755

# Print a header.

756

for d in cur.description:

757

print (

758

string.ljust(

759

d[kinterbasdb.DESCRIPTION_NAME],

760

d[kinterbasdb.DESCRIPTION_DISPLAY_SIZE]

761

)

762

),

763

print # Finish the header row with a newline.

764

print '-' * 78

765

766

# Print each row.

767

while 1:

768

row = cur.fetchone()

769

if row is None:

770

break

771

772

# Print the value of each field left-justified within the maximum

773

# possible width of that field.

774

for fieldPos in range(len(row)):

775

print (

776

string.ljust(

777

str(row[fieldPos]),

778

cur.description[fieldPos][kinterbasdb.DESCRIPTION_DISPLAY_SIZE]

779

)

780

),

781

print # Finish the row with a newline.

782

</pre>

783

784

785

Sample output:

786

787

788

NAME_LAST AGE

789

------------------------------------------------------------------------------

790

Yeltsin 69

791

Gorbachev 72

792

</pre>

793

794

795

<h4>Example 4</h4>

796

797

798

Let's insert more people into the <code>people</code> table:

799

800

<pre class="codeBlock">import kinterbasdb, string

801

802

con = kinterbasdb.connect(

803

dsn="stalin:d:/code/projects/kinterbasdb/ibtest.db",

804

user="sysdba",

805

password="pass"

806

)

807

808

cur = con.cursor()

809

810

newPeople = (

811

('Lebed' , 56),

812

('Zhirinovsky' , 49)

813

)

814

815

for newPerson in newPeople:

816

cur.execute("insert into people (name_last, age) values (?, ?)", newPerson)

817

818

con.commit() # The changes will not be saved unless the transaction is

819

# committed explicitly.

820

</pre>

821

822

823

Note the use of a parameterized SQL statement above. When dealing

824

with repetitive statements, this is faster and less error-prone than

825

assembling each SQL statement manually.

826

827

828

829

After running Example 4, the table printer from Example 3

830

would print:

831

832

833

NAME_LAST AGE

834

------------------------------------------------------------------------------

835

Yeltsin 69

836

Gorbachev 72

837

Lebed 56

838

Zhirinovsky 49

839

</pre>

840

841

842

843

844

<a name="tutorial_stored_procedures"><h3>Calling Stored Procedures</h3></a>

845

846

847

Interbase and Firebird support stored procedures written in a propriety

848

procedural SQL language.

849

IB/FB stored procedures can have input parameters and/or

850

output parameters, but not combined

851

input/output parameters.

852

853

854

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

858

output parameters").

859

IB/FB's server-side procedural SQL syntax

860

makes no such distinction, but client programmers must do so in

861

their SQL code.

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>

865

statement.

866

867

868

869

870

To retrieve a result set from a stored procedure with KInterbasDB,

871

use code such as this:

872

873

874

<pre class="codeBlock">cur.execute("select output1, output2 from the_proc(?, ?)", (input1, input2))

875

876

# Ordinary fetch code here, such as:

877

result_set = cur.fetchall()

878

</pre>

879

880

881

882

To call a stored procedure and access its output parameters

883

with KInterbasDB, use code such as this:

884

885

886

<pre class="codeBlock">cur.callproc("the_proc", (input1, input2))

887

888

# If there are output parameters, retrieve them as though they were the

889

# first row of a result set. For example:

890

output_params = cur.fetchone()

891

</pre>

892

893

894

895

<a name="tutorial_control_transactions"><h3>Controlling Transactions</h3></a>

896

897

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.

901

902

903

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

912

garbage collected.

913

914

915

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

921

connection's

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>.

927

928

929

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.

933

934

935

936

937

The following example establishes an unobtrusive transaction to be used

938

for read-only access to the database:

939

940

<pre class="codeBlock">import kinterbasdb

941

942

con = kinterbasdb.connect(

943

'stalin:d:/code/projects/kinterbasdb/ibtest.db',

944

'sysdba', 'pass')

945

946

tpb = kinterbasdb.isc_tpb_read \

947

+ kinterbasdb.isc_tpb_read_committed \

948

+ kinterbasdb.isc_tpb_rec_version

949

950

con.begin(tpb)

951

952

# Now read some data using a cursor...

953

</pre>

954

955

956

957

958

<a name="tutorial_database_info_function"><h3>Using the <code>database_info</code> Function</h3></a>

959

960

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).

965

966

967

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:

972

973

<pre class="codeBlock">import kinterbasdb

974

975

con = kinterbasdb.connect(

976

'stalin:d:/code/projects/kinterbasdb/ibtest.db',

977

'sysdba', 'pass')

978

979

# Retrieving an integer info item is quite simple.

980

bytesInUse = con.database_info(kinterbasdb.isc_info_current_memory, 'i')

981

982

print 'The server is currently using %d bytes of memory.' % bytesInUse

983

984

# Retrieving a string info item is somewhat more involved, because

985

# the information is returned in a raw binary buffer that must

986

# be parsed according to the rules defined in the Interbase 6 API

987

# Guide section entitled "Requesting buffer items and result buffer

988

# values" (page 51).

989

# Often, the buffer contains a succession of length-string pairs

990

# (one byte telling the length of s, followed by s itself).

991

# Function kinterbasdb.raw_byte_to_int is provided to convert a raw

992

# byte to a Python integer (see examples below).

993

buf = con.database_info(kinterbasdb.isc_info_db_id, 's')

994

995

# Parse the filename from the buffer.

996

beginningOfFilename = 2

997

# The second byte in the buffer contains the size of the database filename

998

# in bytes.

999

lengthOfFilename = kinterbasdb.raw_byte_to_int(buf[1])

1000

filename = buf[beginningOfFilename:beginningOfFilename + lengthOfFilename]

1001

1002

# Parse the host name from the buffer.

1003

beginningOfHostName = (beginningOfFilename + lengthOfFilename) + 1

1004

# The first byte after the end of the database filename contains the size

1005

# of the host name in bytes.

1006

lengthOfHostName = kinterbasdb.raw_byte_to_int(buf[beginningOfHostName - 1])

1007

host = buf[beginningOfHostName:beginningOfHostName + lengthOfHostName]

1008

1009

print ('We are connected to the database at %s on host %s.' % (filename, host))

1010

</pre>

1011

1012

1013

Sample output:

1014

1015

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.

1018

</pre>

1019

1020

1021

1022

1023

<hr>

1024

1025

<a href="index.html">Table of Contents</a>

1026

1027

</body>

1028

</html>

b'\\ No newline at end of file'