-
-
Notifications
You must be signed in to change notification settings - Fork 209
/
Using_OO_API.html
3834 lines (3632 loc) · 212 KB
/
Using_OO_API.html
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
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8"/>
<title></title>
<meta name="generator" content="LibreOffice 7.1.5.2 (Linux)"/>
<meta name="author" content="alex "/>
<meta name="created" content="2013-05-31T00:00:00.010003100"/>
<meta name="changed" content="2022-06-24T17:47:03.455745039"/>
<meta name="created" content="00:00:00">
<meta name="created" content="00:00:00">
<meta name="created" content="00:00:00">
<meta name="created" content="00:00:00">
<meta name="created" content="00:00:00">
<meta name="created" content="00:00:00">
<meta name="created" content="00:00:00">
<meta name="created" content="00:00:00">
<style type="text/css">
@page { margin: 2.01cm }
h1 { color: #000000 }
p { margin-bottom: 0.2cm; color: #000000 }
</style>
</head>
<body lang="en-US" text="#000000" dir="ltr"><p style="margin-top: 0.43cm; margin-bottom: 0.51cm; page-break-after: avoid">
<font face="Albany, sans-serif"><font size="5" style="font-size: 18pt">Firebird
interfaces.</font></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Firebird's
OO API is based on use of interfaces. That interfaces, though looking
in some aspects like OLE2 interfaces (some of them have addRef() and
release() methods) are non standard and have features, missing in
other widely used types of interfaces. First of all Firebird
interfaces are </font><font size="4" style="font-size: 14pt"><b>language
independent</b></font> – <font size="4" style="font-size: 14pt">that
means that to define/use them one need not use language specific
constructions like </font><font size="4" style="font-size: 14pt"><i>class</i></font>
<font size="4" style="font-size: 14pt">in C++, interface may be
defined using any language able to call functions using C calling
conventions and having concepts of array and pointer to
procedure/function. Next interfaces are </font><font size="4" style="font-size: 14pt"><b>versioned</b></font>
– <font size="4" style="font-size: 14pt">i.e. we support different
versions of same interface. Binary layout of interfaces is designed
to support that features very efficient (there is no need in
additional virtual calls like in OLE2/COM with it's </font><strong><font size="4" style="font-size: 14pt">QueryInterface)</font></strong><strong>
</strong><strong><font size="4" style="font-size: 14pt">but it's not
convenient for direct use from most languages. Therefore
language-specific wrappers should better be designed for different
languages making use of API easier. Currently we have wrappers for
C++ and Pascal, Java is coming soon. From end-user POV calls from C++
and Pascal have absolutely no difference, though some additional
language-specific features present in C++ (like ability to turn off
automatic status check after API calls) are missing in Pascal.</font></strong></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm; font-weight: normal"><font size="4" style="font-size: 14pt">Typically
database API is used to access data stored in database. Firebird OO
API certainly performs this task but in addition it supports writing
your own </font><font size="4" style="font-size: 14pt"><b>plugins</b></font>
– <font size="4" style="font-size: 14pt">modules, making it
possible to enhance Firebird capabilities according to your needs.
Therefore this document contains 2 big parts – accessing databases
and writing plugins. Certainly some interfaces (like status vector)
are used in both parts of API, they will be discussed in data access
part and freely referenced later when discussing plugins. Therefore
even if you plan to write some plugin you should better start with
the first part of this document. Moreover a lot of plugins need to
access databases themselves and data access API is typically needed
for it.</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><a name="result_box"></a><font size="4" style="font-size: 14pt">Firebird
installation package contains a number of live samples of use of OO
API – they are in examples/interfaces (database access) and
examples/dbcrypt (plugin performing fictitious database encryption)
directories. It's supposed that the reader is familiar with ISC API
used in Firebird since interbase times.</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">This
document does not pretend to be a full Firebird 3 documentation –
it just describes new object oriented API, and a reader should be
familiar with main Firebird concepts, knowledge about ISC API is also
much welcome. For example – when describing how to work with
services there is no explanation what is service and why is it
needed, only description of how to obtain <a href="#Service">IService</a>
interface and how to use it. Also pay attention that samples of code
do not use a lot of powerful features of C++. Not used reference
counted pointers, not used other RAII holders, not used templates
(except one present in firebird public headers), etc. Primary goal is
to make this text usable not only to C++ people because our API is
oriented to support not only C++ but other, more simple languages
too.</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-top: 0.43cm; margin-bottom: 0.51cm; page-break-after: avoid">
<font face="Albany, sans-serif"><font size="5" style="font-size: 18pt">Accessing
databases.</font></font></p>
<h1><font size="4" style="font-size: 14pt">Creating database and
attaching to existing database.</font></h1>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">First
of all we need to get access to <b>IMaster</b> interface. IMaster is
primary Firebird interface, required to access all the rest of
interfaces. Therefore there is a special way of accessing it – the
only one needed to use OO API plain function called
fb_get_master_interface(). This function has no parameters and always
succeeds. There is one and only one instance of IMaster per Firebird
client library, therefore one need not care about releasing memory,
used by master interface. A simplest way to access it from your
program is to have appropriate global or static variable:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>static
<a href="#Master">IMaster</a>* master = fb_get_master_interface();</i></font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">For
a lot of methods, used in Firebird API, first parameter is <b>IStatus</b>
interface. It's a logical replacement of ISC_STATUS_ARRAY, but works
separately with errors and warnings (not mixing them in same array),
can contain unlimited number of errors inside and (this will be
important if you plan to implement IStatus yourself) always keeps
strings, referenced by it, inside interface. Typically you need at
least one instance of IStatus to call other methods. You obtain it
from IMaster:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i><a href="#Status">IStatus</a>*
st = master->getStatus();</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">If
method getStatus() fails for some reason (OOM for example) it returns
NULL – obviously we can't use generic error reporting method which
is based on use of IStatus here.</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Now
we are going to deal with first interface, directly related to
database calls. This is <a href="#Provider">IProvider</a> –
interface called this way cause it's exactly that interface that must
be implemented by any provider in Firebird. Firebird client library
also has it's own implementation of IProvider, which must be used to
start any database activity. To obtain it we call IMaster's method:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>IProvider*
prov = master->getDispatcher();</i></font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">When
attaching to existing database or moreover creating new one it's
often necessary to pass a lot of additional parameters
(logon/password, page size for new database, etc.) to API call.
Having separate language-level parameters is close to unreal – we
will have to modify a call too often to add new parameters, and
number of them will be very big no matter of the fact that typically
one needs to pass not too much of them. Therefore to pass additional
parameters special in-memory data structure, called </font><font size="4" style="font-size: 14pt"><i>database
parameters block</i></font> <font size="4" style="font-size: 14pt">(DPB)
is used. Format of it is well defined, and it's possible to build DPB
byte after byte. But it's much easier to use special interface
</font><a href="#XpbBuilder"><font size="4" style="font-size: 14pt"><b>IXpbBuilder</b></font></a><font size="4" style="font-size: 14pt">,
which simplifies creation of various parameters blocks. To obtain an
instance of IXpbBuilder you must know one more generic-use interface
of firebird API – </font><a href="#Util"><font size="4" style="font-size: 14pt"><b>IUtil</b></font></a><font size="4" style="font-size: 14pt">.
It's a kind of placeholder for the calls that do not fit well in
other places. So we do</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>IUtil*
utl = master->getUtilInterface();</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>IXpbBuilder*
dpb = utl->getXpbBuilder(&status, IXpbBuilder::DPB, NULL, 0);</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">This
creates empty parameters' block builder of DPB type. Now adding
required parameter to it is trivial:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>dpb->insertInt(&status,
isc_dpb_page_size, 4 * 1024);</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">will
make firebird to create new database with pagesize equal to 4Kb and
meaning of</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>dpb->insertString(&status,
isc_dpb_user_name, “sysdba”);</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>dpb->insertString(&status,
isc_dpb_password, “masterkey”);</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">is
(I hope) obvious.</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><a name="Status Wrapper"></a><font size="4" style="font-size: 14pt"><u>The
following is C++ specific: </u>We are almost ready to call
createDatabase() method of IProvider, but before it a few words about
concept of <b>Status Wrapper</b> should be said. Status wrapper is
not an interface, it's very thin envelope for IStatus interface. It
helps to customize behavior of C++ API (change a way how errors,
returned in IStatus interface, are processed). For the first time we
recommend use of <b>ThrowStatusWrapper</b>, which raises C++
exception each time an error is returned in IStatus.</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>ThrowStatusWrapper
status(st);</i></font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Now
we may create new empty database:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i><a href="#Attachment">IAttachment</a>*
att = prov->createDatabase(&status, "fbtests.fdb",
dpb->getBufferLength(&status), dpb->getBuffer(&status));</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>printf("Database
fbtests.fdb created\n");</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Pay
attention that we do not check status after the call to
createDatabase(), because in case of error C++ or Pascal exception
will be raised (therefore it's very good idea to have
try/catch/except syntax in your program). We also use two new
functions from IXpbBuilder – getBufferLength() and getBuffer(),
which extract data from interface in native parameters block format.
As you can see there is no need to check explicitly for status of
functions, returning intermediate results.</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Detaching
from just created database is trivial:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>att->detach(&status);</i></font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Now
it remains to enclose all operators into <i>try</i> block and write a
handler in catch block. When using ThrowStatusWrapper you should
always catch defined in C++ API exception class FbException, in
Pascal you must also work with class FbException. Exception handler
block in simplest case may look this way:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>catch
(const FbException& error)</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>{</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>char
buf[256];</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>utl->formatStatus(buf,
sizeof(buf), error.getStatus());</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>fprintf(stderr,
"%s\n", buf);</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>}</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Pay
attention that here we use one more function from <a href="#Util">IUtil</a>
– formatStatus(). It returns in buffer text, describing an error
(warning), stored in IStatus parameter.</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">To
attach to existing database just use attachDatabase() method of
IProvider instead createDatabase(). All parameters are the same for
both methods.</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>att
= prov->attachDatabase(&status, "fbtests.fdb", 0,
NULL);</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">This
sample is using no additional DPB parameters. Take into account that
without logon/password any remote connection will fail if no trusted
authorization plugin is configured. Certainly login info may be also
provided in environment (in ISC_USER and ISC_PASSWORD variables) like
it was before.</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Our
examples contain complete samples, dedicated except others to
creating databases – 01.create.cpp and 01.create.pas. When samples
are present it will be very useful to build and try to run
appropriate samples when reading this document.</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<h1><font size="4" style="font-size: 14pt">Working with transactions.</font></h1>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Only
creating empty databases is definitely not enough to work with RDBMS.
We want to be able to create various objects (like tables and so on)
in database and insert data in that tables. Any operation within
database is performed by firebird under transaction control.
Therefore first of all we must learn to start transaction. Here we do
not discuss distributed transactions (supported by <a href="#Dtc">IDtc</a>
interface) to avoid unneeded to most users overcomplication. Starting
of non-distributed transaction is very simple and done via attachment
interface:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i><a href="#Transaction">ITransaction</a>*
tra = att->startTransaction(&status, 0, NULL);</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">In
this sample default transaction parameters are used – no TPB is
passed to startTransaction() method. If you need non-default
parameters you may create appropriate <a href="#XpbBuilder">IXpbBuilder</a>,
add required items to it:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i><a href="#XpbBuilder">IXpbBuilder</a>*
tpb = utl->getXpbBuilder(&status, IXpbBuilder::TPB, NULL, 0);</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>tpb->insertTag(&status,
isc_tpb_read_committed);</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">and
pass resulting TPB to startTransaction():</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>ITransaction*
tra = att->startTransaction(&status,
tpb->getBufferLength(&status), tpb->getBuffer(&status));</i></font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Transaction
interface is used as a parameter in a lot of other API calls but
itself it does not perform any actions except commit/rollback
transaction, may be retaining:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>tra->commit(&status);</i></font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">You
may take a look at how to start and commit transaction in examples
01.create.cpp and 01.create.pas.</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<h1><font size="4" style="font-size: 14pt">Executing SQL operator
without input parameters and returned rows.</font></h1>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">With
started transaction we are ready to execute our first SQL operators.
Used for it execute() method in <a href="#Attachment">IAttachment</a>
is rather universal and may be also used to execute SQL operators
with input and output parameters (which is typical for EXECUTE
PROCEDURE statement), but right now we will use the simple most form
of it. Both DDL and DML operators can be executed:</font></p>
<p style="margin-bottom: 0cm; font-weight: normal"><font size="4" style="font-size: 14pt"><i>att->execute(&status,
tra, 0, "create table dates_table (d1 date)",
SQL_DIALECT_V6, NULL, NULL, NULL, NULL);</i></font></p>
<p style="margin-bottom: 0cm; font-weight: normal"><font size="4" style="font-size: 14pt"><i>tra->commitRetaining(&status);</i></font></p>
<p style="margin-bottom: 0cm; font-weight: normal"><font size="4" style="font-size: 14pt"><i>att->execute(&status,
tra, 0, "insert into dates_table values (CURRENT_DATE)",
SQL_DIALECT_V6, NULL, NULL, NULL, NULL);</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">As
you can see transaction interface is a required parameter for
execute() method (must be NULL only if you execute START TRANSACTION
statement). Next follows length of SQL operator (may be zero causing
use of C rules to determine string length), text of operator and SQL
dialect that should be used for it. The following for NULLs stand for
metadata descriptions and buffers of input parameters and output
data. Complete description of this method is provided in <a href="#Attachment">IAttachment</a>
interface.</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">You
may take a look at how to start and commit transaction in examples
01.create.cpp and 01.create.pas.</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<h1><font size="4" style="font-size: 14pt">Executing SQL operator
with input parameters.</font></h1>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">There
are 2 ways to execute statement with input parameters. Choice of
correct method depends upon do you need to execute it more than once
and do you know in advance format of parameters. When that format is
known and statement is needed to be run only once single call to
IAttachment::execute() may be used. In other cases SQL statement
should be prepared first and after it executed, may be many times
with different parameters.</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">To
prepare SQL statement for execution use prepare() method of
<a href="#Attachment">IAttachment</a> interface:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i><a href="#Statement">IStatement</a>*
stmt = att->prepare(&status, tra, 0, “UPDATE department SET
budget = ? * budget + budget WHERE dept_no = ?”,</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>SQL_DIALECT_V6,
IStatement::PREPARE_PREFETCH_METADATA);</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">If
you are not going to use parameters description from firebird (i.e.
you can provide that information yourself) please use
IStatement::PREPARE_PREFETCH_NONE instead PREPARE_PREFETCH_METADATA –
this will save client/server traffic and server resources a bit.</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">In
ISC API XSQLDA is used to describe format of statement parameters.
New API does not use XSQLDA – instead interface IMessageMetadata is
used. A set of input parameters (and also a row fetched from cursor)
is described in firebird API in same way and later called message.
<a href="#MessageMetadata">IMessageMetadata</a> is passed as a
parameter to the methods performing message exchange between your
program and database engine. There are many ways to have an instance
of IMessageMetadata – one can:</font></p>
<ul>
<li><p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">get
it from <a href="#Statement">IStatement</a>,</font></p>
</ul>
<ul>
<li value="1"><p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">build
using <a href="#MetadataBuilder">IMetadataBuilder</a> interface,</font></p>
<li><p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">have
your own implementation of this interface.</font></p>
</ul>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Getting
metadata from prepared statement is very simple – method
getInputMetadata() return interface describing input message (i.e.
statement parameters), interface returned by getOutputMetadata()
describes output message (i.e. row in selected data or values
returned by procedure). In our case we can:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i><a href="#MessageMetadata">IMessageMetadata</a>*
meta = stmt->getInputMetadata(&status);</i></font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Or
we can build message metadata ourself. First of all we need builder
interface for it:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i><a href="#MetadataBuilder">IMetadataBuilder</a>*
builder = master->getMetadataBuilder(&status, 2);</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Second
parameter is expected number of fields in the message, it can be
changed later, i.e. that's just an optimization.</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Now
it's necessary to set individual fields characteristics in the
builder. An absolute minimum is field types and length for string
fields:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>builder->setType(&status,
0, SQL_DOUBLE + 1);</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>builder->setType(&status,
1, SQL_TEXT + 1);</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>builder->setLength(&status,
1, 3);</i></font></p>
<p style="margin-bottom: 0cm; font-variant: normal; font-style: normal">
<font size="4" style="font-size: 14pt">New API is using old constants
for SQL types, smallest bit as earlier stands for nullability. In
some case it may also make sense to set sub-type (for blobs),
character set (for text fields) or scale (for numeric fields). And
finally it's time to get an instance of IMessageMetadata:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i><a href="#MessageMetadata">IMessageMetadata</a>*
meta = builder->getMetadata(&status);</i></font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm; font-variant: normal; font-style: normal">
<font size="4" style="font-size: 14pt">Here we do not discuss in
details own implementation of IMessageMetadata. If one cares there is
a sample 05.user_metadata.cpp.</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">So
finally we have obtained (one or another way) an instance of metadata
description of input parameters. But to work with a message we also
need buffer for it. Buffer size is one of main message metadata
characteristics and it's returned by method in IMessageMetadata:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>char*
buffer = new char[meta->getMessageLength(&status)];</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">To
deal with individual values inside buffer offset to them should be
taken into an account. IMessageMetadata is aware of offsets for all
values in a message, using it we can create pointers to them:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>double*
percent_inc = (double*) &buffer[meta->getOffset(&status,
0)];</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>char*
dept_no = &buffer[meta->getOffset(&status, 1)];</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Also
let's do not forget to set to NOT NULL null flags:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>short*
flag = (short*)&buffer[meta->getNullOffset(&status, 0)];</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>*flag
= 0;</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>flag
= (short*) &buffer[meta->getNullOffset(&status, 1)];</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>*flag
= 0;</i></font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">After
finishing with offsets we are ready to execute statement with some
parameters values:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>getInputValues(dept_no,
percent_inc);</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">and
may execute prepared statement:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>stmt->execute(&status,
tra, meta, buffer, NULL, NULL);</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Two
more NULLs in the end of parameters stand for output message and is
used typically for EXECUTE PROCEDURE statement.</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">If
you do not need to get metadata from statement and plan to execute it
only once you may choose a simpler way – use method execute() in
<a href="#Attachment">IAttachment</a> interface:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>att->execute(&status,
tra, 0, "UPDATE department SET budget = ? * budget + budget
WHERE dept_no = ?", SQL_DIALECT_V6, meta, buffer, NULL, NULL);</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">In
that case you do not need to use <a href="#Statement">IStatement</a>
at all.</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">An
example how to execute UPDATE with parameters is present in
02.update.cpp, you will also see how raised in trigger/procedure
exception may be caught by C++ program.</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<h1><font size="4" style="font-size: 14pt">Opening cursor and
fetching data from it.</font></h1>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">The
only way to get rows of data, returned by SELECT operator, in OO API
is to use <a href="#ResultSet">IResultSet</a> interface. This
interface is returned by openCursor() method in both IAttachment and
IStatement. openCursor() is in most aspects alike execute() and a
choice how to open cursor (using prepared statement or directly from
attachment interface) is the same. In the samples 03.select.cpp and
04.print_table.cpp both methods are used. Let's pay attention at one
specific openCursor() feature compared with execute() - one does not
pass buffer for output message into openCursor(), it will be passed
later when data is fetched from cursor. This makes it possible to
open cursor with unknown format of output message (NULL is passed
instead output metadata). Firebird is using in this case default
message format which may be requested from IResultSet interface:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>const
char* sql = "select * from ..."; // some select statement</i></font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i><a href="#ResultSet">IResultSet</a>*
curs = att->openCursor(&status, tra, 0, sql, SQL_DIALECT_V6,
NULL, NULL, NULL, NULL, 0);</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i><a href="#MessageMetadata">IMessageMetadata</a>*
meta = curs->getMetadata(&status);</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Later
this metadata may be used to allocate buffer for data and parse
fetched rows.</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">As
an alternative one can first prepare statement, get metadata from
prepared statement and after it open cursor. This is preferred way if
cursor is likely to be opened >1 times.</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><a href="#Statement">IStatement</a>*
stmt = att->prepare(&status, tra, 0, sql, SQL_DIALECT_V6,
Istatement::PREPARE_PREFETCH_METADATA);</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><a href="#MessageMetadata">IMessageMetadata</a>*
meta = stmt->getOutputMetadata(&status);</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><a href="#ResultSet">IResultSet</a>*
curs = stmt->openCursor(&status, tra, NULL, NULL, NULL, 0);</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">We
have obtained (one or another way) an instance of metadata
description of output fields (a row in a set). To work with a message
we also need buffer for it:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>unsigned
char* buffer = new unsigned char[meta->getMessageLength(&status)];</i></font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">IResultSet
has a lot of various fetch methods but when cursor is not opened with
SCROLL option only fetchNext() works, i.e. one can navigate only
forward record by record. In addition to errors and warnings in
status fetchNext() returns completion code, which may have values
RESULT_OK (when buffer is filled with values for next row) or
RESULT_NO_DATA (when no more rows left in cursor). RESULT_NO_DATA is
not error state, it's normal state after completion of the method,
but we know that data in cursor is over. If status wrapper, not
throwing exception in case of error return is used, one more value –
RESULT_ERROR – may be returned, that means no data in buffer and
error vector in status. Method fetchNext() is usually called in a
cycle:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>while
(curs->fetchNext(&status, buffer) == IStatus::RESULT_OK)</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>{</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>//
row processing</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>}</i></font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">What
is done during row processing depends upon your needs. To access
particular field field's offset should be used:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>unsigned
char* field_N_ptr = buffer + meta->getOffset(&status, n);</i></font></p>
<p style="margin-bottom: 0cm; font-variant: normal; font-style: normal">
<font size="4" style="font-size: 14pt">where n is the number of a
field in a message. That pointer should be casted to appropriate
type, depending upon field type. For example, for a VARCHAR field
cast to struct vary should be used:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>vary*
v_ptr = (vary*) (buffer + meta->getOffset(&status, n));</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Now
we may print the value of a field:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>printf(“field
%s value is %*.*s\n”, meta->getField(&status, n),
v_ptr->vary_length, v_ptr->vary_length, v_ptr->vary_string);</i></font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">If
you need maximum performance it will be good idea to cache needed
metadata values like it's done in our samples 03.select.cpp and
04.print_table.cpp.</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<h1><font size="4" style="font-size: 14pt">Using FB_MESSAGE macro for
static messages.</font></h1>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Working
with data using offsets is rather efficient but requires a lot of
code to be written. In C++ this problem can be solved using
templates, but even compared with them the most convenient way to
work with the message is to represent it in native (for given
language) way – structure in C/C++, record in Pascal, etc.
Certainly this works only if format of a message is known in advance.
To help building such structures in C++ firebird contains special
macro FB_MESSAGE.</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">FB_MESSAGE
has 3 arguments – message (struct) name, type of status wrapper and
list of fields. Usage of first and second is obvious, list of fields
contains pairs (field_type, field_name), where field_type is one of
the following:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">FB_BIGINT</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">FB_BLOB</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">FB_BOOLEAN</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">FB_CHAR(len)</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">FB_DATE</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">FB_DECFIXED(scale)</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">FB_DECFLOAT16</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">FB_DECFLOAT34</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">FB_DOUBLE</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">FB_FLOAT</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">FB_INTEGER</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">FB_INTL_CHAR(len,
charSet)</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">FB_INTL_VARCHAR(len,
charSet)</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">FB_SCALED_BIGINT(scale)</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">FB_SCALED_INTEGER(scale)</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">FB_SCALED_SMALLINT(scale)</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">FB_SMALLINT</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">FB_TIME</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">FB_TIMESTAMP</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">FB_VARCHAR(len)</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">In
generated by preprocessor structure integer and float types are
matched with appropriate C types, date and time – with classes
<a href="#FbDate">FbDate</a> and <a href="#FbTime">FbTime</a> (all
mentioned here classes are in namespace Firebird), timestamp – with
class FbTimestamp, containing two public data members date and time
of appropriate class, char - with struct <a href="#FbChar">FbChar</a>
and varchar – with struct <a href="#FbVarChar">FbVarChar</a>. For
each field preprocessor creates two data members in the message –
</font><font size="4" style="font-size: 14pt"><i>name</i></font> <font size="4" style="font-size: 14pt">for
field/parameter value and </font><font size="4" style="font-size: 14pt"><i>nameNull</i></font>
<font size="4" style="font-size: 14pt">for NULL indicator. Message
constructor has 2 parameters – pointer to status wrapper and master
interface:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>FB_MESSAGE(Output,
ThrowStatusWrapper,</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>(FB_SMALLINT,
relationId)</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>(FB_CHAR(31),
relationName)</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>(FB_VARCHAR(100),
description)</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>)
output(&status, master);</i></font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">For
static messages use of FB_MESSAGE is sooner of all the best choice –
they can be at the same time easily passed to execute, openCursor and
fetch methods:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>rs
= att->openCursor(&status, tra, 0, sqlText,</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>SQL_DIALECT_V6,
NULL, NULL, output.getMetadata(), NULL, 0);</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">and
used to work with values of individual fields:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>while
(rs->fetchNext(&status, output.getData()) ==
IStatus::RESULT_OK)</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>{</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>printf("%4d
%31.31s %*.*s\n", output->relationId,
output->relationName.str,</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>output->descriptionNull
? 0 : output->description.length,</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>output->descriptionNull
? 0 : output->description.length, output->description.str);</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>}</i></font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">An
example of using macro FB_MESSAGE to work with messages is in the
sample 06.fb_message.cpp.</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<h1><font size="4" style="font-size: 14pt">Working with blobs.</font></h1>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">For
blobs in message buffer firebird stores blob identifier – an 8-byte
entity which should be aligned on 4-byte boundary. Identifier has
ISC_QUAD type. Interface <a href="#Attachment">IAttachment</a> has 2
methods to work with blobs – openBlob() and createBlob(), both
returning interface <a href="#Blob">IBlob</a> and having same set of
parameters, but performing somewhat contrary actions: openBlob()
takes blob identifier from the message and prepares blob for reading
but createBlob() creates new blob, puts it's identifier into message
and prepares blob for writing. </font>
</p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">To
work with blobs one must first of all include blob identifier into
the message. If you get metadata from firebird engine field of
appropriate type will be already present. You just use it's offset
(assuming variable blobFieldNumber contains number of blob field)
(and appropriate null offset to check for nulls or set null flag) to
obtain pointer into the message buffer:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>ISC_QUAD*
blobPtr = (ISC_QUAD*) &buffer[metadata->getOffset(&status,
blobFieldNumber)];</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>ISC_SHORT*
blobNullPtr = (ISC_SHORT*) &buffer[metadata->getNullOffset(&status,
blobFieldNumber)];</i></font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">If
you use static messages and FB_MESSAGE macro blob field is declared
as having FB_BLOB type:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>FB_MESSAGE(Msg,
ThrowStatusWrapper,</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>(FB_BLOB,
b)</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>)
message(&status, master);</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>ISC_QUAD*
blobPtr = &message->b;</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>ISC_SHORT*
blobNullPtr = &message->bNull;</i></font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">To
create new blob invoke createBlob() method:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i><a href="#Blob">IBlob</a>*
blob = att->createBlob(status, tra, blobPtr, 0, NULL);</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Last
two parameters are required only if you want to use blob filters or
use stream blob, that's out of scope here.</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Blob
interface is ready to accept data into blob. Use putSegment() method
to send data to engine:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>void*
segmentData;</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>unsigned
segmentLength;</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>while
(userFunctionProvidingBlobData(&segmentData, &segmentLength))</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>blob->putSegment(&status,
segmentLength, segmentData);</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">After
sending some data to blob do not forget to close blob interface:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>blob->close(&status);</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Make
sure that null flag is not set (not required if you nullified all
message buffer before creating blob):</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>*blobNullPtr
= 0;</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">and
message, containing blob, may be used in insert or update statement.
After execution of that statement new blob will be stored in
database.</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">To
read a blob begin with getting containing it's identifier message
from firebird engine. This may be done using fetch() or execute()
methods. After it use openBlob() attachment's method:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i><a href="#Blob">IBlob</a>*
blob = att->openBlob(status, tra, blobPtr, 0, NULL);</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Blob
interface is ready to provide blob data. Use getSegment() method to
receive data from engine:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>char
buffer[BUFSIZE];</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>unsigned
actualLength;</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>for(;;)</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>{</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>switch
(blob->getSegment(&status, sizeof(buffer), buffer,
&actualLength))</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>{</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>case
IStatus::RESULT_OK:</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>userFunctionAcceptingBlobData(buffer,
actualLength, true);</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>continue;</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>case
IStatus::RESULT_SEGMENT:</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>userFunctionAcceptingBlobData(buffer,
actualLength, false);</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>continue;</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>default:</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>break;</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>}</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>}</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Last
parameter in userFunctionAcceptingBlobData() is a flag that end of
segment is reached – when getSegment() returns RESULT_SEGMENT
completion code that function is notified (by passing false as last
parameter) that segment was not read completely and continuation is
expected at next call.</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">After
finishing with blob do not forget to close it:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>blob->close(&status);</i></font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<h1><a name="Modifying data in a batch"></a><font size="4" style="font-size: 14pt">Modifying
data in a batch.</font></h1>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Since
version 4 firebird supports batch execution of statements with input
parameters – that means sending more than single set of parameters
when executing statement. <a href="#Batch">Batch</a> interface is
designed (first of all) in order to satisfy JDBC requirements for
prepared statement’s batch processing but has some serious
differences:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">-
like all operations with data in firebird it’s oriented on
messages, not single field;</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">-
as an important extension out batch interface supports inline use of
blobs (specially efficient when working with small blobs);</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">-
execute() method returns not plain array of integers but special
<a href="#BatchCompletionState">BatchCompletionState</a> interface
which can (depending upon batch creation parameters) contain both
update records info and in addition to error flag detailed status
vectors for messages that caused execution errors.</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><a href="#Batch">Batch</a>
(exactly like <a href="#ResultSet">ResultSet</a>) may be created in 2
ways – using <a href="#Statement">Statement</a> or <a href="#Attachment">Attachment</a>
interface, in both cases createBatch() method of appropriate
interface is called. In second case text of SQL statement to be
executed in a batch is passed directly to createBatch(). Tuning of
batch operation is performed using <a href="#Batch_PB">Batch
parameters block</a> which has format more or less similar to DPB v.2
– tag in the beginning (IBatch::CURRENT_VERSION) followed by the
set of wide clumplets: 1-byte tag, 4-byte length, length-byte value.
Possible tags are <a href="#Batch_PB">described</a> in batch
interface. The simplest (and recommended) way to create parameters
block for batch creation is to use appropriate <a href="#XpbBuilder">XpbBuilder</a>
interface:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>IXpbBuilder*
pb = utl->getXpbBuilder(&status, IXpbBuilder::BATCH, NULL, 0);</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>pb->insertInt(&status,
IBatch::RECORD_COUNTS, 1);</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Use
of such parameters block directs batch to account number of updated
records on per-message basis.</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">To
create batch interface with desired parameters pass parameters block
to createBatch() call:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>IBatch*
batch = att->createBatch(&status, tra, 0, sqlStmtText,
SQL_DIALECT_V6, NULL,</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>pb->getBufferLength(&status),
pb->getBuffer(&status));</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">In
this sample batch interface is created with default format of
messages cause NULL is passed instead input metadata format.</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">In
order to proceed with created batch interface we need to know format
of messages in it. It can be obtained using getMetadata() method:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>IMessageMetadata*
meta = batch->getMetadata(&status);</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Certainly
if you have passed your own format of messages to the batch you may
simply use it.</font></p>
<p style="margin-bottom: 0cm; font-variant: normal; font-style: normal">
<font size="4" style="font-size: 14pt">In the former text I suppose
that some function fillNextMessage(unsigned char* data,
IMessageMetadata* metadata) is present and can fill buffer ‘data’
according to passed format ‘metadata’. In order to work with
messages we need a buffer for a data:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>unsigned
char* data = new unsigned char[meta->getMessageLength(&status)];</i></font></p>
<p style="margin-bottom: 0cm; font-variant: normal; font-style: normal">
<font size="4" style="font-size: 14pt">Now we can add some messages
full of data to the batch:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>fillNextMessage(data,
meta);</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>batch->add(&status,
1, data);</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>fillNextMessage(data,
meta);</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>batch->add(&status,
1, data);</i></font></p>
<p style="margin-bottom: 0cm; font-variant: normal; font-style: normal">
<font size="4" style="font-size: 14pt">An alternative way of working
with messages (using FB_MESSAGE macro) is present in the sample of
using batch interface 11.batch.cpp.</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm; font-variant: normal; font-style: normal">
<font size="4" style="font-size: 14pt">Finally batch should be
executed:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i><a href="#BatchCompletionState">IBatchCompletionState</a>*
cs = batch->execute(&status, tra);</i></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">We
requested accounting of the number of modified (inserted, updated or
deleted) records per message. To print it we must use
<a href="#BatchCompletionState">BatchCompletionState</a> interface.
Determine total number of messages processed by batch (it can be less
than the number of messages passed to the batch if error happened and
an option enabling multiple errors during batch processing was not
turned on):</font></p>
<p><font size="4" style="font-size: 14pt"><i>unsigned total =
cs->getSize(&status);</i></font></p>
<p><font size="4" style="font-size: 14pt">Now print the state of each
message:</font></p>
<p><font size="4" style="font-size: 14pt"><i>for (unsigned p = 0; p <
total; ++p) printf(“Msg %u state %d\n”, p, cs->getState(&status,
p));</i></font></p>
<p style="font-variant: normal; font-style: normal"><font size="4" style="font-size: 14pt">When
finished analyzing completion state don’t forget to dispose it:</font></p>
<p><font size="4" style="font-size: 14pt"><i>cs->dispose();</i></font></p>
<p><font color="#000000"><font size="4" style="font-size: 14pt">Full
sample of printing contents</font></font><font color="#000000"> of
</font><font color="#000000"><font size="4" style="font-size: 14pt"><a href="#BatchCompletionState">BatchCompletionState</a>
is in print_cs() function in sample 11.batch.cpp.</font></font></p>
<p style="font-variant: normal; font-style: normal"><font size="4" style="font-size: 14pt">If
for some reason you want to make batch buffers empty not executing it
(i.e. prepare for new portion of messages to process) use cancel()
method:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>batch->cancel(&status);</i></font></p>
<p style="margin-bottom: 0cm; font-variant: normal; font-style: normal">
<font size="4" style="font-size: 14pt">Like the rest of our data
access interfaces Batch has special method to close it:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>batch->close(&status);</i></font></p>
<p style="margin-bottom: 0cm; font-variant: normal; font-style: normal">
<font size="4" style="font-size: 14pt">Standard release() call may be
used instead if one does not care about errors:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><i>batch->release();</i></font></p>
<p style="margin-bottom: 0cm; font-variant: normal; font-style: normal">
<font size="4" style="font-size: 14pt">Described methods help to
implement all what one needs for JDBC-style prepared statement batch
operations. </font>
</p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Notice:
JDBC does not recommend to use too large batches, for example “<a href="https://docs.oracle.com/cd/E11882_01/java.112/e16548/oraperf.htm#JJDBC28753">Oracle
recommends you to keep the batch sizes in the general range of 50 to
100</a>”. Firebird supports bigger batches but it anyway has to
limit maximum batch size – see <a href="#TAG_BUFFER_BYTES_SIZE">TAG_BUFFER_BYTES_SIZE</a>.
If total size of messages exceeds that limit isc_batch_too_big error
is returned. Pay attention that due to deep buffering of batch data
when sending it over the wire you will not get this error at once but
only when buffers are flashed from client to server. This may happen
in both add() and execute() methods – execute() performs final
flash. If you still wish to execute the batch with the messages that
fit in a buffer you may do it (when error was returned by execute()
just repeat it). An actual number of processed messages will be
returned in </font><font color="#000000"><font size="4" style="font-size: 14pt"><a href="#BatchCompletionState">BatchCompletionState</a>.
An optimal batch size should be found for each particular case but
sooner of all having it >1000 hardly gives you serious performance
increase.</font></font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p><font size="4" style="font-size: 14pt">One can add more than
single message in one call to the batch. When doing it please