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
|
<?xml version="1.0" encoding="UTF-8"?>
<helpdocument version="1.0">
<!--
* This file is part of the LibreOffice project.
*
* This Source Code Form is subject to the terms of the Mozilla Public
* License, v. 2.0. If a copy of the MPL was not distributed with this
* file, You can obtain one at http://mozilla.org/MPL/2.0/.
*
-->
<meta>
<topic id="SF_Database" indexer="include" status="PUBLISH">
<title id="tit" xml-lang="en-US">SFDatabases.Database service</title>
<filename>/text/sbasic/shared/03/sf_database.xhp</filename>
</topic>
</meta>
<body>
<section id="ScriptForge-sf_database">
<bookmark xml-lang="en-US" localize="false" branch="index" id="bm_id151587913266190">
<bookmark_value>Database service</bookmark_value>
</bookmark>
</section>
<section id="abstract">
<h1 id="hd_id371587913266310" xml-lang="en-US"><variable id="DatabaseService"><link href="text/sbasic/shared/03/sf_database.xhp"><literal>SFDatabases</literal>.<literal>Database</literal> service</link></variable></h1>
<paragraph role="paragraph" id="par_id891599407198144" xml-lang="en-US">The <literal>Database</literal> service provides access to databases either embedded or described in Base documents. This service provides methods to:</paragraph>
<list type="unordered">
<listitem>
<paragraph id="par_id551615386924481" role="listitem">Get access to data in database tables.</paragraph>
</listitem>
<listitem>
<paragraph id="par_id551615386924285" role="listitem">Run <literal>SELECT</literal> queries and perform aggregate functions.</paragraph>
</listitem>
<listitem>
<paragraph id="par_id551615386924111" role="listitem">Run SQL action statements such as <literal>INSERT</literal>, <literal>UPDATE</literal>, <literal>DELETE</literal>, etc.</paragraph>
</listitem>
</list>
</section>
<paragraph role="paragraph" id="par_id811599407236602" xml-lang="en-US">Each instance of the <literal>Database</literal> service represents a single database and gives access to its tables, queries and data.</paragraph>
<paragraph role="paragraph" id="par_id111625692871642">This service does not provide access to forms or reports in the Base document that contains the database. To access forms in a Base document, refer to the method <link href="text/sbasic/shared/03/sf_base.xhp#FormDocuments"><literal>FormDocuments</literal></link> of the <literal>Base</literal> service.</paragraph>
<note id="par_id231615386789950">All exchanges between this service and the database are done using SQL only.</note>
<paragraph role="paragraph" id="par_id121599407322804" xml-lang="en-US">SQL statements may be run in <emph>direct</emph> or <emph>indirect</emph> mode. In direct mode the statement is transferred to the database engine without any syntax checking or review.</paragraph>
<paragraph role="paragraph" id="par_id681599407189019" xml-lang="en-US">The provided interfaces include simple tables and queries lists, as well as access to database data.</paragraph>
<tip id="par_id891599407280007" xml-lang="en-US">To make SQL statements more readable, you may use square brackets "[ ]" to enclose names of tables, queries and fields instead of using other enclosing characters that may be exclusive to certain Relational Database Management Systems (RDBMS). But beware that enclosing characters are mandatory in this context.</tip>
<section id="Transaction">
<h2 id="hd_id461701357494930">Transaction handling</h2>
<bookmark branch="index" id="bm_id121701365380522">
<bookmark_value>Database service; Transaction handling</bookmark_value>
</bookmark>
<paragraph role="paragraph" id="par_id71701357507703">By default the database handles transactions in auto-commit mode, meaning that a commit is done after every SQL statement.</paragraph>
<paragraph role="paragraph" id="par_id321701357603871">Use the <literal>SetTransactionMode</literal> method to change the default behavior, which allows for manual commits and rollbacks.</paragraph>
<paragraph role="paragraph" id="par_id431701443412927">The methods <literal>Commit</literal> and <literal>Rollback</literal> are used to delimit transactions.</paragraph>
<paragraph role="paragraph" id="par_id531701365474516">In %PRODUCTNAME, there are five types of transaction isolation modes, as defined in the <link href="https://api.libreoffice.org/docs/idl/ref/namespacecom_1_1sun_1_1star_1_1sdbc_1_1TransactionIsolation.html">com.sun.star.sdbc.TransactionIsolation</link> constant group:</paragraph>
</section>
<table id="tab_id221701441607725">
<tablerow>
<tablecell>
<paragraph id="par_id631701441607725" role="tablehead">Constant</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id671701441607725" role="tablehead">Value</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id441701441607725" role="tablehead">Interpretation</paragraph>
</tablecell>
</tablerow>
<tablerow>
<tablecell>
<paragraph id="par_id681701441607725" localize="false" role="tablecontent">NONE</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id831701441607725" localize="false" role="tablecontent">0</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id601701441607725" role="tablecontent">Transaction handling is disabled and the database is set to the default auto-commit mode.</paragraph>
</tablecell>
</tablerow>
<tablerow>
<tablecell>
<paragraph id="par_id681701441607147" localize="false" role="tablecontent">READ_UNCOMMITTED</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id831701441607208" localize="false" role="tablecontent">1</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id601701441607099" role="tablecontent">Dirty reads, non-repeatable reads and phantom reads can occur.</paragraph>
<paragraph id="par_id601701441603205" role="tablecontent">If a row is changed by a transaction, another transaction will be able to read these changes even if they have not been committed.</paragraph>
</tablecell>
</tablerow>
<tablerow>
<tablecell>
<paragraph id="par_id681701441607317" localize="false" role="tablecontent">READ_COMMITTED</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id831701441607714" localize="false" role="tablecontent">2</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id601701441607012" role="tablecontent">Dirty reads are prevented, however non-repeatable reads and phantom reads can occur.</paragraph>
<paragraph id="par_id601701441608244" role="tablecontent">This level prevents that rows with uncommitted changes are read.</paragraph>
</tablecell>
</tablerow>
<tablerow>
<tablecell>
<paragraph id="par_id681701441607209" localize="false" role="tablecontent">REPEATABLE_READ</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id831701441607103" localize="false" role="tablecontent">4</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id601701441607300" role="tablecontent">Dirty reads and non-repeatable reads are prevented. However, phantom reads can occur.</paragraph>
<paragraph id="par_id601701441608161" role="tablecontent">Besides preventing uncommitted data from being read, it also prevents that two read operations in the same transaction return different results.</paragraph>
</tablecell>
</tablerow>
<tablerow>
<tablecell>
<paragraph id="par_id681701441607060" localize="false" role="tablecontent">SERIALIZABLE</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id831701441607688" localize="false" role="tablecontent">8</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id601701441607896" role="tablecontent">Dirty reads, non-repeatable reads and phantom reads are prevented.</paragraph>
<paragraph id="par_id601701441607117" role="tablecontent">In addition to the constraints of the previous level, it also ensures that the set of records that match a <literal>WHERE</literal> clause remains unchanged inside the same transaction.</paragraph>
</tablecell>
</tablerow>
</table>
<tip id="par_id991701357744355">Read the Wikipedia page on <link href="https://en.wikipedia.org/wiki/Isolation_(database_systems)">Isolation in Database Systems</link> to learn more about transaction integrity.</tip>
<h2 id="hd_id91587913266988" xml-lang="en-US">Service invocation</h2>
<paragraph role="paragraph" id="par_id141609955500101">Before using the <literal>Database</literal> service the <literal>ScriptForge</literal> library needs to be loaded or imported:</paragraph>
<embed href="text/sbasic/shared/03/lib_ScriptForge.xhp#importLibs"/>
<embed href="text/sbasic/shared/00000003.xhp#functsyntax"/>
<paragraph role="paragraph" id="par_id541599408159668" xml-lang="en-US">To create a instance of the <literal>Database</literal> service you can use the <literal>CreateScriptService</literal> method:</paragraph>
<paragraph role="paragraph" localize="false" id="par_id251625693098616">
<input>CreateScriptService("SFDatabases.Database", [filename: str], [registrationname], [readonly], [user, [password]]): svc</input>
</paragraph>
<note id="par_id551625693442959">In the syntax described above you can use either "SFDatabases.Database" or simply "Database" as the first argument of the <literal>CreateScriptService</literal> method.</note>
<embed href="text/sbasic/shared/00000003.xhp#functparameters"/>
<paragraph role="paragraph" id="par_id111615146818256"><emph>filename</emph>: The name of the Base file. Must be expressed using <literal>SF_FileSystem.FileNaming</literal> notation.</paragraph>
<paragraph role="paragraph" id="par_id771615146944307"><emph>registrationname</emph>: The name of a registered database. If <literal>filename</literal> is provided, this argument should not be used.</paragraph>
<paragraph role="paragraph" id="par_id491615147048748">Conversely, if a <literal>registrationname</literal> is specified, the <literal>filename</literal> parameter should not be defined.</paragraph>
<paragraph role="paragraph" id="par_id841615147168279"><emph>readonly</emph>: Determines if the database will be opened as readonly (Default = <literal>True</literal>).</paragraph>
<paragraph role="paragraph" id="par_id291615147236001"><emph>user, password</emph>: Additional connection parameters to the database server.</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functexample"/>
<embed href="text/sbasic/shared/00000003.xhp#In_Basic"/>
<bascode>
<paragraph role="bascode" localize="false" id="bas_id451625698094586">GlobalScope.BasicLibraries.LoadLibrary("ScriptForge")</paragraph>
<paragraph role="bascode" localize="false" id="bas_id191625698094873">Dim myDatabase as Object</paragraph>
<paragraph role="bascode" localize="false" id="bas_id181625698095184">Set myDatabase = CreateScriptService("Database", "/home/user/Documents/myDB.odb")</paragraph>
<paragraph role="bascode" id="bas_id871625698095504">' Run queries, SQL statements, ...</paragraph>
<paragraph role="bascode" localize="false" id="bas_id501625698095793">myDatabase.CloseDatabase()</paragraph>
</bascode>
<embed href="text/sbasic/shared/00000003.xhp#In_Python"/>
<pycode>
<paragraph role="pycode" localize="false" id="pyc_id291625698186091">from scriptforge import CreateScriptService</paragraph>
<paragraph role="pycode" localize="false" id="pyc_id141625698186499">myDatabase = CreateScriptService("Database", "/home/user/Documents/myDB.odb")</paragraph>
<paragraph role="pycode" id="pyc_id791625698186675"># Run queries, SQL statements, ...</paragraph>
<paragraph role="pycode" localize="false" id="pyc_id521625698186867">myDatabase.CloseDatabase()</paragraph>
</pycode>
<h2 id="hd_id771615147491563">Accessing Databases with the UI Service</h2>
<paragraph role="paragraph" id="par_id901599408410712" xml-lang="en-US">It is also possible to access the database associated with a Base document using the <link href="text/sbasic/shared/03/sf_ui.xhp"><literal>ScriptForge.UI</literal></link> service, as shown in the examples below:</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#In_Basic"/>
<bascode>
<paragraph role="bascode" localize="false" id="bas_id1001599408773502">Dim myDoc As Object, myDatabase As Object, ui As Object</paragraph>
<paragraph role="bascode" localize="false" id="bas_id631599408779968">Set ui = CreateScriptService("UI")</paragraph>
<paragraph role="bascode" localize="false" id="bas_id801599408784337">Set myDoc = ui.OpenBaseDocument("/home/user/Documents/myDB.odb")</paragraph>
<paragraph role="bascode" id="bas_id631615147843278">' User and password are supplied below, if needed</paragraph>
<paragraph role="bascode" localize="false" id="bas_id741599408788321">Set myDatabase = myDoc.GetDatabase()</paragraph>
<paragraph role="bascode" id="bas_id921599408791887">' Run queries, SQL statements, ...</paragraph>
<paragraph role="bascode" localize="false" id="bas_id971625699354855">myDatabase.CloseDatabase()</paragraph>
<paragraph role="bascode" localize="false" id="bas_id251599408799447">myDoc.CloseDocument()</paragraph>
</bascode>
<embed href="text/sbasic/shared/00000003.xhp#In_Python"/>
<pycode>
<paragraph role="pycode" localize="false" id="pyc_id881625699438103">ui = CreateScriptService("UI")</paragraph>
<paragraph role="pycode" localize="false" id="pyc_id111625699438354">doc = ui.OpenBaseDocument("/home/user/Documents/myDB.odb")</paragraph>
<paragraph role="pycode" id="pyc_id731625699527917"># User and password are supplied below, if needed</paragraph>
<paragraph role="pycode" localize="false" id="pyc_id741625699438527">myDatabase = doc.GetDatabase()</paragraph>
<paragraph role="pycode" id="pyc_id201625699438702"># Run queries, SQL statements, ...</paragraph>
<paragraph role="pycode" localize="false" id="pyc_id171625699438887">myDatabase.CloseDatabase()</paragraph>
<paragraph role="pycode" localize="false" id="pyc_id231625699570502">doc.CloseDocument()</paragraph>
</pycode>
<tip id="par_id361619188184750">The <link href="text/sbasic/shared/03/sf_base.xhp#GetDatabase">GetDatabase</link> method used in the example above is part of ScriptForge's <literal>Base</literal> service.</tip>
<bookmark xml-lang="en-US" branch="index" id="bm_id771615387442357" localize="false">
<bookmark_value>Database Service;Queries</bookmark_value>
<bookmark_value>Database Service;Tables</bookmark_value>
</bookmark>
<h2 id="hd_id841587913266618" xml-lang="en-US">Properties</h2>
<section id="properties_toc">
<table id="tab_id291587913266435">
<tablerow>
<tablecell>
<paragraph id="par_id521587913266568" role="tablehead" xml-lang="en-US">Name</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id421587913266368" role="tablehead" xml-lang="en-US">Readonly</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id631587914939732" role="tablehead" xml-lang="en-US">Type</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id951587913266220" role="tablehead" xml-lang="en-US">Description</paragraph>
</tablecell>
</tablerow>
<tablerow>
<tablecell>
<paragraph id="par_id161587913266162" role="tablecontent" xml-lang="en-US" localize="false">Queries</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id651587913266754" role="tablecontent" xml-lang="en-US">Yes</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id421587914989890" role="tablecontent" xml-lang="en-US">Array of strings</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id351587913266349" role="tablecontent" xml-lang="en-US">The list of stored queries.</paragraph>
</tablecell>
</tablerow>
<tablerow>
<tablecell>
<paragraph id="par_id851599409717318" role="tablecontent" xml-lang="en-US" localize="false">Tables</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id931599409717463" role="tablecontent" xml-lang="en-US">Yes</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id71599409717945" role="tablecontent" xml-lang="en-US">Array of strings</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id341599409717612" role="tablecontent" xml-lang="en-US">The list of stored tables.</paragraph>
</tablecell>
</tablerow>
<tablerow>
<tablecell>
<paragraph id="par_id561599409777730" role="tablecontent" xml-lang="en-US" localize="false">XConnection</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id741599409777967" role="tablecontent" xml-lang="en-US">Yes</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id101599409777906" role="tablecontent" xml-lang="en-US" localize="false"><link href="https://api.libreoffice.org/docs/idl/ref/interfacecom_1_1sun_1_1star_1_1sdbc_1_1XConnection.html">XConnection</link></paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id551599409777759" role="tablecontent" xml-lang="en-US">The UNO object representing the current database connection.</paragraph>
</tablecell>
</tablerow>
<tablerow>
<tablecell>
<paragraph id="par_id361599409887831" role="tablecontent" xml-lang="en-US" localize="false">XMetaData</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id271599409887585" role="tablecontent" xml-lang="en-US">Yes</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id701599409887579" role="tablecontent" xml-lang="en-US" localize="false"><link href="https://api.libreoffice.org/docs/idl/ref/interfacecom_1_1sun_1_1star_1_1sdbc_1_1XDatabaseMetaData.html">XDatabaseMetaData</link></paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id861599409887284" role="tablecontent" xml-lang="en-US">The UNO object representing the metadata describing the database system attributes.</paragraph>
</tablecell>
</tablerow>
</table>
</section>
<section id="methods_toc">
<table id="tab_id551614360519973">
<tablerow>
<tablecell colspan="3">
<paragraph id="par_id231614360519973" role="tablehead">List of Methods in the Database Service</paragraph>
</tablecell>
</tablerow>
<tablerow>
<tablecell>
<paragraph id="par_id611614360519255" role="tablecontent" localize="false">
<link href="text/sbasic/shared/03/sf_database.xhp#CloseDatabase">CloseDatabase</link><br/>
<link href="text/sbasic/shared/03/sf_database.xhp#Commit">Commit</link><br/>
<link href="text/sbasic/shared/03/sf_database.xhp#CreateDataset">CreateDataset</link><br/>
<link href="text/sbasic/shared/03/sf_database.xhp#DFunctions">DAvg</link><br/>
<link href="text/sbasic/shared/03/sf_database.xhp#DFunctions">DCount</link><br/>
<link href="text/sbasic/shared/03/sf_database.xhp#DFunctions">DMin</link><br/>
</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id611614360519104" role="tablecontent" localize="false">
<link href="text/sbasic/shared/03/sf_database.xhp#DFunctions">DMax</link><br/>
<link href="text/sbasic/shared/03/sf_database.xhp#DFunctions">DSum</link><br/>
<link href="text/sbasic/shared/03/sf_database.xhp#DLookup">DLookup</link><br/>
<link href="text/sbasic/shared/03/sf_database.xhp#GetRows">GetRows</link><br/>
<link href="text/sbasic/shared/03/sf_database.xhp#OpenFormDocument">OpenFormDocument</link><br/>
<link href="text/sbasic/shared/03/sf_database.xhp#OpenQuery">OpenQuery</link><br/>
</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id611614360518452" role="tablecontent" localize="false">
<link href="text/sbasic/shared/03/sf_database.xhp#OpenSql">OpenSql</link><br/>
<link href="text/sbasic/shared/03/sf_database.xhp#OpenTable">OpenTable</link><br/>
<link href="text/sbasic/shared/03/sf_database.xhp#Rollback">Rollback</link><br/>
<link href="text/sbasic/shared/03/sf_database.xhp#RunSql">RunSql</link><br/>
<link href="text/sbasic/shared/03/sf_database.xhp#SetTransactionMode">SetTransactionMode</link><br/><br/>
</paragraph>
</tablecell>
</tablerow>
</table>
</section>
<section id="CloseDatabase">
<comment> CloseDatabase ---------------------------------------------------------------------------------- </comment>
<bookmark xml-lang="en-US" localize="false" branch="index" id="bm_id991587913266189">
<bookmark_value>Database Service;CloseDatabase</bookmark_value>
</bookmark>
<h2 id="hd_id76158791326673" localize="false">CloseDatabase</h2>
<paragraph role="paragraph" id="par_id201587913266596">Closes the current database connection.</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functsyntax"/>
<paragraph role="paragraph" localize="false" id="par_id821625699786747">
<input>db.CloseDatabase()</input>
</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functexample"/>
<bascode>
<paragraph role="bascode" localize="false" id="bas_id191625699910447">myDatabase.CloseDatabase() ' Basic</paragraph>
</bascode>
<pycode>
<paragraph role="pycode" localize="false" id="pyc_id591625699936578">myDatabase.CloseDatabase() # Python</paragraph>
</pycode>
</section>
<section id="Commit">
<comment> Commit --------------------------------------------------------------------------------------------- </comment>
<bookmark xml-lang="en-US" localize="false" branch="index" id="bm_id991587913240529">
<bookmark_value>Database Service;Commit</bookmark_value>
</bookmark>
<h2 id="hd_id76158791326672" localize="false">Commit</h2>
<paragraph role="paragraph" id="par_id201587913264308">Commits all updates done since the previous <literal>Commit</literal> or <literal>Rollback</literal> call.</paragraph>
<note id="par_id391701355293522">This method is ignored if commits are done automatically after each SQL statement, i.e. the database is set to the default auto-commit mode.</note>
<embed href="text/sbasic/shared/00000003.xhp#functsyntax"/>
<paragraph role="paragraph" localize="false" id="par_id821625603786747">
<input>db.Commit()</input>
</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functexample"/>
<embed href="text/sbasic/shared/00000003.xhp#In_Basic"/>
<bascode>
<paragraph role="bascode" id="bas_id141701355821478">' Set the REPEATABLE_READ transaction level</paragraph>
<paragraph role="bascode" localize="false" id="bas_id191625699913187">myDB.SetTransactionMode(4)</paragraph>
<paragraph role="bascode" localize="false" id="bas_id791701355662193">myDB.RunSql("UPDATE ...")</paragraph>
<paragraph role="bascode" localize="false" id="bas_id517013556462633">myDB.Commit()</paragraph>
<paragraph role="bascode" localize="false" id="bas_id941701355662801">myDB.RunSql("DELETE ...")</paragraph>
<paragraph role="bascode" id="bas_id311701355662993">' Test some condition before committing</paragraph>
<paragraph role="bascode" localize="false" id="bas_id841701355961091">If bSomeCondition Then</paragraph>
<paragraph role="bascode" localize="false" id="bas_id491701355985754"> myDB.Commit()</paragraph>
<paragraph role="bascode" localize="false" id="bas_id117013559861863">Else</paragraph>
<paragraph role="bascode" localize="false" id="bas_id100170135598648"> myDB.Rollback()</paragraph>
<paragraph role="bascode" localize="false" id="bas_id291701356026092">End If</paragraph>
<paragraph role="bascode" id="bas_id711701355663208">' Restore auto-commit mode</paragraph>
<paragraph role="bascode" localize="false" id="bas_id441701355777989">myDB.SetTransactionMode()</paragraph>
</bascode>
<embed href="text/sbasic/shared/00000003.xhp#In_Python"/>
<pycode>
<paragraph role="pycode" localize="false" id="pyc_id591625699936776">myDB.SetTransactionMode(4)</paragraph>
<paragraph role="pycode" localize="false" id="pyc_id721701356152080">myDB.RunSql("UPDATE ...")</paragraph>
<paragraph role="pycode" localize="false" id="pyc_id531701356152407">myDB.Commit()</paragraph>
<paragraph role="pycode" localize="false" id="pyc_id841701356152599">myDB.RunSql("DELETE ...")</paragraph>
<paragraph role="pycode" localize="false" id="pyc_id861701356152807">if some_condition:</paragraph>
<paragraph role="pycode" localize="false" id="pyc_id991701356153032"> myDB.Commit()</paragraph>
<paragraph role="pycode" localize="false" id="pyc_id351701356153359">else:</paragraph>
<paragraph role="pycode" localize="false" id="pyc_id761701357236266"> myDB.Rollback()</paragraph>
<paragraph role="pycode" localize="false" id="pyc_id201701357236435">myDB.SetTransactionMode()</paragraph>
</pycode>
</section>
<section id="CreateDataset">
<comment> CreateDataset -------------------------------------------------------------------------------------- </comment>
<bookmark xml-lang="en-US" localize="false" branch="index" id="bm_id161599488115021">
<bookmark_value>Database Service;CreateDataset</bookmark_value>
</bookmark>
<h2 id="hd_id151599488110425" localize="false">CreateDataset</h2>
<paragraph role="paragraph" id="par_id41599488113502">Creates a <literal>Dataset</literal> service instance based on a table, query or SQL <literal>SELECT</literal> statement.</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functsyntax"/>
<paragraph role="paragraph" localize="false" id="par_id41625701240781">
<input>db.CreateDataset(sqlcommand: str, opt directsql: bool, opt filter: str, opt orderby: str): svc</input>
</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functparameters"/>
<paragraph role="paragraph" id="par_id591701267115505"><emph>sqlcommand</emph>: A table name, a query name or a valid SQL <literal>SELECT</literal> statement. Identifiers may be enclosed with square brackets. This argument is case-sensitive.</paragraph>
<paragraph role="paragraph" id="par_id841701267114060"><emph>directsql</emph>: Set this argument to <literal>True</literal> to send the statement directly to the database engine without preprocessing by %PRODUCTNAME (Default = <literal>False</literal>).</paragraph>
<paragraph role="paragraph" id="par_id241701267114527"><emph>filter</emph>: Specifies the condition that records must match to be included in the returned dataset. This argument is expressed as a SQL <literal>WHERE</literal> statement without the "WHERE" keyword.</paragraph>
<paragraph role="paragraph" id="par_id471701267115093"><emph>orderby</emph>: Specifies the ordering of the dataset as a SQL <literal>ORDER BY</literal> statement without the "ORDER BY" keyword.</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functexample"/>
<paragraph role="paragraph" id="par_id941701267748877">The following examples in Basic and Python return a dataset with the records of a table named "Customers".</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#In_Basic"/>
<bascode>
<paragraph role="bascode" id="bas_id241701267631273">oDataset = myDatabase.CreateDataset("Customers", Filter := "[Name] LIKE 'A'")</paragraph>
</bascode>
<embed href="text/sbasic/shared/00000003.xhp#In_Python"/>
<pycode>
<paragraph role="pycode" id="pyc_id931701267696812">dataset = myDatabase.CreateDataset("Customers", Filter = "[Name] LIKE 'A'")</paragraph>
</pycode>
</section>
<section id="DFunctions">
<comment> DFunctions ----------------------------------------------------------------------------------- </comment>
<bookmark xml-lang="en-US" localize="false" branch="index" id="bm_id861599488343584">
<bookmark_value>Database Service;DAvg</bookmark_value>
</bookmark>
<bookmark xml-lang="en-US" localize="false" branch="index" id="bm_id221599488343636">
<bookmark_value>Database Service;DCount</bookmark_value>
</bookmark>
<bookmark xml-lang="en-US" localize="false" branch="index" id="bm_id261599488343619">
<bookmark_value>Database Service;DMax</bookmark_value>
</bookmark>
<bookmark xml-lang="en-US" localize="false" branch="index" id="bm_id231599488343876">
<bookmark_value>Database Service;DMin</bookmark_value>
</bookmark>
<bookmark xml-lang="en-US" localize="false" branch="index" id="bm_id831599488343138">
<bookmark_value>Database Service;DSum</bookmark_value>
</bookmark>
<h2 id="hd_id281596554849363" localize="false">DAvg, DCount, DMin, DMax, DSum</h2>
<paragraph role="paragraph" id="par_id13159655484952">Computes the given aggregate function on a field or expression belonging to a table.</paragraph>
<paragraph role="paragraph" id="par_id101615148468548">Optionally, a SQL <literal>WHERE</literal> clause can be specified as a filter that will be applied prior to the aggregate function.</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functsyntax"/>
<paragraph role="paragraph" localize="false" id="par_id481625700732942">
<input>db.DAvg(expression: str, tablename: str, [criteria: str]): any</input>
</paragraph>
<paragraph role="paragraph" localize="false" id="par_id481625700732113">
<input>db.DCount(expression: str, tablename: str, [criteria: str]): any</input>
</paragraph>
<paragraph role="paragraph" localize="false" id="par_id481625700730298">
<input>db.DMin(expression: str, tablename: str, [criteria: str]): any</input>
</paragraph>
<paragraph role="paragraph" localize="false" id="par_id481625700720177">
<input>db.DMax(expression: str, tablename: str, [criteria: str]): any</input>
</paragraph>
<paragraph role="paragraph" localize="false" id="par_id481625700731179">
<input>db.DSum(expression: str, tablename: str, [criteria: str]): any</input>
</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functparameters"/>
<paragraph role="paragraph" id="par_id441596554849949"><emph>expression</emph>: A SQL expression in which the field names are surrounded with square brackets.</paragraph>
<paragraph role="paragraph" id="par_id381596554849698" xml-lang="en-US"><emph>tablename</emph>: A table name (without square brackets).</paragraph>
<paragraph role="paragraph" id="par_id521596554849185" xml-lang="en-US"><emph>criteria</emph>: A <literal>WHERE</literal> clause without the "WHERE" keyword, in which field names are surrounded with square brackets.</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functexample"/>
<paragraph role="paragraph" id="par_id781615150306678">The example below assumes the file <literal>Employees.odb</literal> has a table named <literal>EmployeeData</literal>.</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#In_Basic"/>
<bascode>
<paragraph role="bascode" localize="false" id="bas_id921596554849612">GlobalScope.BasicLibraries.LoadLibrary("ScriptForge")</paragraph>
<paragraph role="bascode" localize="false" id="bas_id561615149909875">Dim myDB as Variant</paragraph>
<paragraph role="bascode" localize="false" id="bas_id211615149928005">Set myDB = CreateScriptService("Database", "/home/user/Databases/Employees.odb")</paragraph>
<paragraph role="bascode" id="bas_id871615150277916">' Counts the number of employees in the table</paragraph>
<paragraph role="bascode" localize="false" id="bas_id31615149929509">MsgBox myDB.DCount("[ID]", "EmployeeData")</paragraph>
<paragraph role="bascode" id="bas_id291615150373387">' Returns the sum of all salaries in the table</paragraph>
<paragraph role="bascode" localize="false" id="bas_id881615150162528">MsgBox myDB.DSum("[Salary]", "EmployeeData")</paragraph>
<paragraph role="bascode" id="bas_id931615150423062">' Below are some examples of how tables can be filtered</paragraph>
<paragraph role="bascode" localize="false" id="bas_id241615149929870">MsgBox myDB.DCount("[ID]", "EmployeeData", "[Position] = 'Manager'")</paragraph>
<paragraph role="bascode" localize="false" id="bas_id241615149929145">MsgBox myDB.DCount("[ID]", "EmployeeData", "[Position] = 'Sales' AND [City] = 'Chicago'")</paragraph>
<paragraph role="bascode" localize="false" id="bas_id311615150740276">MsgBox myDB.DCount("[ID]", "EmployeeData", "[FirstName] LIKE 'Paul%'")</paragraph>
</bascode>
<embed href="text/sbasic/shared/00000003.xhp#In_Python"/>
<pycode>
<paragraph role="pycode" localize="false" id="pyc_id641625700473857">myDB = CreateScriptService("Database", "/home/user/Databases/Employees.odb")</paragraph>
<paragraph role="pycode" localize="false" id="pyc_id611625700474124">bas = CreateScriptService("Basic")</paragraph>
<paragraph role="pycode" localize="false" id="pyc_id991625700474287">bas.MsgBox(myDB.DCount("[ID]", "EmployeeData"))</paragraph>
<paragraph role="pycode" localize="false" id="pyc_id321625700474470">bas.MsgBox(myDB.DSum("[Salary]", "EmployeeData"))</paragraph>
<paragraph role="pycode" localize="false" id="pyc_id811625700474663">bas.MsgBox(myDB.DCount("[ID]", "EmployeeData", "[Position] = 'Manager'"))</paragraph>
<paragraph role="pycode" localize="false" id="pyc_id421625700474895">bas.MsgBox(myDB.DCount("[ID]", "EmployeeData", "[Position] = 'Sales' AND [City] = 'Chicago'"))</paragraph>
<paragraph role="pycode" localize="false" id="pyc_id441625700475239">bas.MsgBox(myDB.DCount("[ID]", "EmployeeData", "[FirstName] LIKE 'Paul%'"))</paragraph>
</pycode>
</section>
<section id="DLookup">
<comment> DLookup -------------------------------------------------------------------------------------------- </comment>
<bookmark xml-lang="en-US" localize="false" branch="index" id="bm_id161599488113736">
<bookmark_value>Database Service;DLookup</bookmark_value>
</bookmark>
<h2 id="hd_id151599488113565" localize="false">DLookup</h2>
<paragraph role="paragraph" id="par_id41599488113961">Computes a SQL expression on a single record returned by a <literal>WHERE</literal> clause defined by the <literal>Criteria</literal> parameter.</paragraph>
<paragraph role="paragraph" id="par_id601615381471954">If the query returns multiple records, only the first one is considered. Use the <literal>OrderClause</literal> parameter to determine how query results are sorted.</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functsyntax"/>
<paragraph role="paragraph" localize="false" id="par_id41625701240138">
<input>db.DLookup(expression: str, tablename: str, [criteria:str], [orderclause: str]): any</input>
</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functparameters"/>
<paragraph role="paragraph" id="par_id671599488113986"><emph>expression</emph>: A SQL expression in which the field names are surrounded with square brackets.</paragraph>
<paragraph role="paragraph" id="par_id441599488113247" xml-lang="en-US"><emph>tablename</emph>: A table name (without square brackets).</paragraph>
<paragraph role="paragraph" id="par_id40159948811316" xml-lang="en-US"><emph>criteria</emph>: A <literal>WHERE</literal> clause without the "WHERE" keyword, in which field names are surrounded with square brackets.</paragraph>
<paragraph role="paragraph" id="par_id71599488689029" xml-lang="en-US"><emph>orderclause</emph>: An <literal>ORDER BY</literal> clause without the "ORDER BY" keywords. Field names should be surrounded with square brackets.</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functexample"/>
<embed href="text/sbasic/shared/00000003.xhp#In_Basic"/>
<bascode>
<paragraph role="bascode" localize="false" id="bas_id311599488264597">MsgBox myDB.DLookup("[FirstName]", "EmployeeData", Criteria := "[LastName] LIKE 'Smith'", OrderClause := "[FirstName] DESC")</paragraph>
<paragraph role="bascode" localize="false" id="bas_id311599488113597">MsgBox myDB.DLookup("[Salary]", "EmployeeData", Criteria := "[ID] = '3'")</paragraph>
<paragraph role="bascode" localize="false" id="bas_id21599488808504">MsgBox myDB.DLookup("[Quantity] * [Value]", "Sales", Criteria := "[SaleID] = '5014'")</paragraph>
</bascode>
<embed href="text/sbasic/shared/00000003.xhp#In_Python"/>
<pycode>
<paragraph role="pycode" localize="false" id="pyc_id151625701420880">bas = CreateScriptService("Basic")</paragraph>
<paragraph role="pycode" localize="false" id="pyc_id181625701421328">bas.MsgBox(myDB.DLookup("[FirstName]", "EmployeeData", criteria = "[LastName] LIKE 'Smith'", orderclause = "[FirstName] DESC"))</paragraph>
<paragraph role="pycode" localize="false" id="pyc_id801625701421784">bas.MsgBox(myDB.DLookup("[Salary]", "EmployeeData", criteria = "[ID] = '3'"))</paragraph>
<paragraph role="pycode" localize="false" id="pyc_id551625701422176">bas.MsgBox(myDB.DLookup("[Quantity] * [Value]", "Sales", criteria = "[SaleID] = '5014'"))</paragraph>
</pycode>
</section>
<section id="GetRows">
<comment> GetRows ---------------------------------------------------------------------------------------- </comment>
<bookmark xml-lang="en-US" localize="false" branch="index" id="bm_id91599489278645">
<bookmark_value>Database Service;GetRows</bookmark_value>
</bookmark>
<h2 id="hd_id79159948927847" localize="false">GetRows</h2>
<paragraph role="paragraph" id="par_id481599489278579">Stores the contents of a table or the results of a <literal>SELECT</literal> query or of an SQL statement in a two-dimensional array. The first index in the array corresponds to the rows and the second index refers to the columns.</paragraph>
<paragraph role="paragraph" id="par_id821615384762425">An upper limit can be specified to the number of returned rows. Optionally column names may be inserted in the first row of the array.</paragraph>
<paragraph role="paragraph" id="par_id271599490209915" xml-lang="en-US">The returned array will be empty if no rows are returned and the column headers are not required.</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functsyntax"/>
<paragraph role="paragraph" localize="false" id="par_id121625701537873">
<input>db.GetRows(sqlcommand: str, directsql: bool = False, header: bool = False, maxrows: int = 0): any</input>
</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functparameters"/>
<paragraph role="paragraph" id="par_id451599489278429"><emph>sqlcommand</emph>: A table or query name (without square brackets) or a <literal>SELECT</literal> SQL statement.</paragraph>
<paragraph role="paragraph" id="par_id271599489278141" xml-lang="en-US"><emph>directsql</emph>: When <literal>True</literal>, the SQL command is sent to the database engine without pre-analysis. Default is <literal>False</literal>. This argument is ignored for tables. For queries, the applied option is the one set when the query was defined.</paragraph>
<paragraph role="paragraph" id="par_id941599489278747" xml-lang="en-US"><emph>header</emph>: When <literal>True</literal>, the first row of the returned array contains the column headers.</paragraph>
<paragraph role="paragraph" id="par_id591599489278926" xml-lang="en-US"><emph>maxrows</emph>: The maximum number of rows to return. The default is zero, meaning there is no limit to the number of returned rows.</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functexample"/>
<paragraph role="paragraph" id="par_id721615385125947">Below are a few examples of how the <literal>GetRows</literal> method can be used:</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#In_Basic"/>
<bascode>
<paragraph role="bascode" localize="false" id="bas_id821599489278193">Dim queryResults as Variant</paragraph>
<paragraph role="bascode" id="bas_id171615385196045">' Returns all rows in the table with column headers</paragraph>
<paragraph role="bascode" localize="false" id="bas_id261599489278488">queryResults = myDB.GetRows("EmployeeData", Header := True)</paragraph>
<paragraph role="bascode" id="bas_id371615385230721">' Returns the first 50 employee records ordered by the 'FirstName' field</paragraph>
<paragraph role="bascode" localize="false" id="bas_id871615385264078">queryResults = myDB.GetRows("SELECT * FROM EmployeeData ORDER BY [FirstName]", MaxRows := 50)</paragraph>
</bascode>
<embed href="text/sbasic/shared/00000003.xhp#In_Python"/>
<pycode>
<paragraph role="pycode" localize="false" id="pyc_id41625701996174">queryResults = myDB.GetRows("EmployeeData", header = True)</paragraph>
<paragraph role="pycode" localize="false" id="pyc_id871625701996504">queryResults = myDB.GetRows("SELECT * FROM EmployeeData ORDER BY [FirstName]", maxrows = 50)</paragraph>
</pycode>
</section>
<section id="OpenFormDocument">
<comment> OpenFormDocument ------------------------------------------------------------------------------------- </comment>
<bookmark xml-lang="en-US" branch="index" id="bm_id341619035528912" localize="false">
<bookmark_value>Database service;OpenFormDocument</bookmark_value>
</bookmark>
<h2 id="hd_id611619035632927" localize="false">OpenFormDocument</h2>
<paragraph role="paragraph" id="par_id801619099743199">Opens the specified form document in normal mode. This method returns a <literal>FormDocument</literal> service instance corresponding to the specified form document.</paragraph>
<paragraph role="paragraph" id="par_id451619100075330">If the form document is already open, the form document window is activated.</paragraph>
<paragraph role="paragraph" id="par_id651692554712005">If the specified form document does not exist, then <literal>Nothing</literal> is returned.</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functsyntax"/>
<paragraph role="paragraph" localize="false" id="par_id71623105516650">
<input>svc.OpenFormDocument(formdocument: str): svc</input>
</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functparameters"/>
<paragraph role="paragraph" id="par_id941619099797810"><emph>formdocument:</emph> The name of the <literal>FormDocument</literal> to be opened, as a case-sensitive string.</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functexample"/>
<embed href="text/sbasic/shared/00000003.xhp#In_Basic"/>
<paragraph role="paragraph" id="par_id251619100191992">Most form documents are stored in the root of the Base document and they can be opened simply using their names, as in the example below:</paragraph>
<bascode>
<paragraph role="bascode" localize="false" id="bas_id511692554784974">Dim oFormDoc As Object</paragraph>
<paragraph role="bascode" localize="false" id="bas_id461619100283210">oFormDoc = myDB.OpenFormDocument("myFormDocument")</paragraph>
</bascode>
<paragraph role="paragraph" id="par_id351619100327505">If form documents are organized in folders, it becomes necessary to include the folder name to specify the form document to be opened, as illustrated in the following example:</paragraph>
<bascode>
<paragraph role="bascode" localize="false" id="bas_id461619100283712">oFormDoc = myDB.OpenFormDocument("myFolder/myFormDocument")</paragraph>
</bascode>
<embed href="text/sbasic/shared/00000003.xhp#In_Python"/>
<pycode>
<paragraph role="pycode" localize="false" id="pyc_id361623105659717">formDoc = myDB.OpenFormDocument("myFormDocument")</paragraph>
</pycode>
<pycode>
<paragraph role="pycode" localize="false" id="pyc_id361623105659616">formDoc = myDB.OpenFormDocument("myFolder/myFormDocument")</paragraph>
</pycode>
</section>
<section id="OpenQuery">
<comment> OpenQuery ------------------------------------------------------------------------------------------- </comment>
<bookmark xml-lang="en-US" localize="false" branch="index" id="bm_id781599490609222">
<bookmark_value>Database Service;OpenQuery</bookmark_value>
</bookmark>
<h2 id="hd_id80159949060014" localize="false">OpenQuery</h2>
<paragraph role="paragraph" id="par_id31599490604484">Opens the Data View window of the specified query and returns an instance of the <literal>Datasheet</literal> service.</paragraph>
<paragraph role="paragraph" id="par_id801671111609985">If the query could not be opened, then <literal>Nothing</literal> is returned.</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functsyntax"/>
<paragraph role="paragraph" localize="false" id="par_id421625701780171">
<input>db.OpenQuery(queryname: str): obj</input>
</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functparameters"/>
<paragraph role="paragraph" id="par_id701599490609511"><emph>queryname</emph>: The name of an existing query as a case-sensitive String.</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functexample"/>
<embed href="text/sbasic/shared/00000003.xhp#In_Basic"/>
<bascode>
<paragraph role="bascode" localize="false" id="bas_id47159949060363">myDatabase.OpenQuery("MyQuery")</paragraph>
</bascode>
<embed href="text/sbasic/shared/00000003.xhp#In_Python"/>
<pycode>
<paragraph role="pycode" localize="false" id="pyc_id331625701876004">myDatabase.OpenQuery("MyQuery")</paragraph>
</pycode>
</section>
<section id="OpenSql">
<comment> OpenSql --------------------------------------------------------------------------------------------- </comment>
<bookmark xml-lang="en-US" localize="false" branch="index" id="bm_id781599490609302">
<bookmark_value>Database Service;OpenSql</bookmark_value>
</bookmark>
<h2 id="hd_id80159949060989" localize="false">OpenSql</h2>
<paragraph role="paragraph" id="par_id31599490601045">Runs a SQL <literal>SELECT</literal> command, opens a Data View window with the results and returns an instance of the <literal>Datasheet</literal> service.</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functsyntax"/>
<paragraph role="paragraph" localize="false" id="par_id421625701780011">
<input>db.OpenSql(sql: str, directsql: bool): obj</input>
</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functparameters"/>
<paragraph role="paragraph" id="par_id701599490604461"><emph>sql</emph>: A string containing a valid SQL SELECT statement. Identifiers may be enclosed by square brackets.</paragraph>
<paragraph role="paragraph" id="par_id51599490609622"><emph>directsql</emph>: When <literal>True</literal>, the SQL command is sent to the database engine without pre-analysis (Default = <literal>False</literal>).</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functexample"/>
<embed href="text/sbasic/shared/00000003.xhp#In_Basic"/>
<bascode>
<paragraph role="bascode" localize="false" id="bas_id47159949069513">myDatabase.OpenSql("SELECT * FROM [Customers] ORDER BY [CITY]")</paragraph>
</bascode>
<embed href="text/sbasic/shared/00000003.xhp#In_Python"/>
<pycode>
<paragraph role="pycode" localize="false" id="pyc_id331625701876324">myDatabase.OpenSql("SELECT * FROM [Customers] ORDER BY [CITY]")</paragraph>
</pycode>
</section>
<section id="OpenTable">
<comment> OpenTable ------------------------------------------------------------------------------------------- </comment>
<bookmark xml-lang="en-US" localize="false" branch="index" id="bm_id781599490609404">
<bookmark_value>Database Service;OpenTable</bookmark_value>
</bookmark>
<h2 id="hd_id80159949063914" localize="false">OpenTable</h2>
<paragraph role="paragraph" id="par_id31599490605021">Opens the Data View window of the specified table and returns an instance of the <literal>Datasheet</literal> service.</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functsyntax"/>
<paragraph role="paragraph" localize="false" id="par_id421625701750551">
<input>db.OpenTable(tablename: str): obj</input>
</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functparameters"/>
<paragraph role="paragraph" id="par_id701599490609021"><emph>tablename</emph>: The name of an existing table as a case-sensitive String.</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functexample"/>
<embed href="text/sbasic/shared/00000003.xhp#In_Basic"/>
<bascode>
<paragraph role="bascode" localize="false" id="bas_id47159949069553">myDatabase.OpenTable("MyTable")</paragraph>
</bascode>
<embed href="text/sbasic/shared/00000003.xhp#In_Python"/>
<pycode>
<paragraph role="pycode" localize="false" id="pyc_id331625701876314">myDatabase.OpenTable("MyTable")</paragraph>
</pycode>
</section>
<section id="Rollback">
<comment> Rollback ------------------------------------------------------------------------------------------- </comment>
<bookmark xml-lang="en-US" localize="false" branch="index" id="bm_id781599490603364">
<bookmark_value>Database Service;Rollback</bookmark_value>
</bookmark>
<h2 id="hd_id80159949010214" localize="false">Rollback</h2>
<paragraph role="paragraph" id="par_id31599490603311">Cancels all changes made to the database since the last <literal>Commit</literal> or <literal>Rollback</literal> call.</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functsyntax"/>
<paragraph role="paragraph" localize="false" id="par_id421625701753721">
<input>db.Rollback()</input>
</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functexample"/>
<embed href="text/sbasic/shared/00000003.xhp#In_Basic"/>
<bascode>
<paragraph role="bascode" localize="false" id="bas_id251701443147366">myDB.SetTransactionMode(1)</paragraph>
<paragraph role="bascode" localize="false" id="bas_id701701443119662">myDB.RunSql("UPDATE ...")</paragraph>
<paragraph role="bascode" localize="false" id="bas_id401701443301083">' ...</paragraph>
<paragraph role="bascode" localize="false" id="bas_id441701443140590">If bSomeCondition Then</paragraph>
<paragraph role="bascode" localize="false" id="bas_id471591949067683"> myDB.Rollback()</paragraph>
<paragraph role="bascode" localize="false" id="bas_id541701443221657">End If</paragraph>
</bascode>
<embed href="text/sbasic/shared/00000003.xhp#In_Python"/>
<pycode>
<paragraph role="pycode" localize="false" id="pyc_id171701443246729">myDB.SetTransactionMode(1)</paragraph>
<paragraph role="pycode" localize="false" id="pyc_id961701443248882">myDB.RunSql("UPDATE ...")</paragraph>
<paragraph role="pycode" localize="false" id="pyc_id861701443289938"># ...</paragraph>
<paragraph role="pycode" localize="false" id="pyc_id551701443249113">if bSomeCondition:</paragraph>
<paragraph role="pycode" localize="false" id="pyc_id331625701876109"> myDB.Rollback()</paragraph>
</pycode>
</section>
<section id="RunSql">
<comment> RunSql -------------------------------------------------------------------------------------------- </comment>
<bookmark xml-lang="en-US" localize="false" branch="index" id="bm_id781599490609499">
<bookmark_value>Database Service;RunSql</bookmark_value>
</bookmark>
<h2 id="hd_id80159949060917" localize="false">RunSql</h2>
<paragraph role="paragraph" id="par_id31599490609759">Executes an action query of an SQL statement such as creating a table, as well as inserting, updating and deleting records.</paragraph>
<paragraph role="paragraph" id="par_id331615385491925">The method returns <literal>True</literal> when successful.</paragraph>
<tip id="par_id21599490810021">The <literal>RunSql</literal> method is rejected with an error message in case the database was previously opened in read-only mode.</tip>
<embed href="text/sbasic/shared/00000003.xhp#functsyntax"/>
<paragraph role="paragraph" localize="false" id="par_id421625701780046">
<input>db.RunSql(sqlcommand: str, directsql: bool = False): bool</input>
</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functparameters"/>
<paragraph role="paragraph" id="par_id701599490609473"><emph>sqlcommand</emph>: A query name (without square brackets) or a SQL statement.</paragraph>
<paragraph role="paragraph" id="par_id51599490609377"><emph>directsql</emph>: When <literal>True</literal>, the SQL command is sent to the database engine without pre-analysis. (Default = <literal>False</literal>). For queries, the applied option is the one set when the query was defined.</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functexample"/>
<embed href="text/sbasic/shared/00000003.xhp#In_Basic"/>
<bascode>
<paragraph role="bascode" localize="false" id="bas_id47159949060973">myDatabase.RunSql("INSERT INTO [EmployeeData] VALUES(25, 'Smith', 'John')", DirectSQL := True)</paragraph>
</bascode>
<embed href="text/sbasic/shared/00000003.xhp#In_Python"/>
<pycode>
<paragraph role="pycode" localize="false" id="pyc_id331625701876367">myDatabase.RunSql("INSERT INTO [EmployeeData] VALUES(25, 'Smith', 'John')", directsql = True)</paragraph>
</pycode>
</section>
<section id="SetTransactionMode">
<comment> SetTransactionMode --------------------------------------------------------------------------------- </comment>
<bookmark xml-lang="en-US" localize="false" branch="index" id="bm_id781599490603230">
<bookmark_value>Database Service;SetTransactionMode</bookmark_value>
</bookmark>
<h2 id="hd_id80159949010722" localize="false">SetTransactionMode</h2>
<paragraph role="paragraph" id="par_id31599490604648">Defines the level of isolation in database transactions.</paragraph>
<paragraph role="paragraph" id="par_id281701459963822">By default databases manage transactions in auto-commit mode, which means that a <literal>Commit</literal> is automatically performed after every SQL statement.</paragraph>
<paragraph role="paragraph" id="par_id181701460140309">Use this method to manually determine the isolation level of transactions. When a transaction mode other than <literal>NONE</literal> is set, the script has to explicitly call the <literal>Commit</literal> method to apply the changes to the database.</paragraph>
<paragraph role="paragraph" id="par_id211701461020712">This method returns <literal>True</literal> when successful.</paragraph>
<warning id="par_id831701530239962">Changing the transaction mode closes all <literal>Dataset</literal> instances created from the current database.</warning>
<embed href="text/sbasic/shared/00000003.xhp#functsyntax"/>
<paragraph role="paragraph" localize="false" id="par_id421625701752132">
<input>db.SetTransactionMode(transactionmode: int = 0): bool</input>
</paragraph>
<embed href="text/sbasic/shared/00000003.xhp#functparameters"/>
<paragraph role="paragraph" id="par_id701599490609584"><emph>transactionmode</emph>: Specifies the transaction mode. This argument must be one of the constants defined in <link href="https://api.libreoffice.org/docs/idl/ref/namespacecom_1_1sun_1_1star_1_1sdbc_1_1TransactionIsolation.html">com.sun.star.sdbc.TransactionIsolation</link> (Default = <literal>NONE</literal>)</paragraph>
<note id="par_id301701459879269">Read the section <link href="text/sbasic/shared/03/sf_database.xhp#Transaction">Transaction handling</link> above to learn more about the transaction isolation levels used in %PRODUCTNAME.</note>
<embed href="text/sbasic/shared/00000003.xhp#functexample"/>
<embed href="text/sbasic/shared/00000003.xhp#In_Basic"/>
<bascode>
<paragraph role="bascode" localize="false" id="bas_id141701443608332">myDB.SetTransactionMode(com.sun.star.sdbc.TransactionIsolation.REPEATABLE_READ)</paragraph>
<paragraph role="bascode" localize="false" id="bas_id711701460522626">oDataset = myDB.CreateDataset("SELECT ...")</paragraph>
<paragraph role="bascode" localize="false" id="bas_id701701460619670">' ...</paragraph>
<paragraph role="bascode" id="bas_id951701460628717">' Reset the transaction mode to default</paragraph>
<paragraph role="bascode" localize="false" id="bas_id581701460647887">myDB.SetTransactionMode()</paragraph>
</bascode>
<embed href="text/sbasic/shared/00000003.xhp#In_Python"/>
<pycode>
<paragraph role="pycode" localize="false" id="pyc_id741701460782621">from com.sun.star.sdbc import TransactionIsolation</paragraph>
<paragraph role="pycode" localize="false" id="pyc_id101701443616246">myDB.SetTransactionMode(TransactionIsolation.REPEATABLE_READ)</paragraph>
<paragraph role="pycode" localize="false" id="pyc_id951701460806562">dataset = myDB.CreateDataset("SELECT ...")</paragraph>
<paragraph role="pycode" localize="false" id="pyc_id171701460807371"># ...</paragraph>
<paragraph role="pycode" localize="false" id="pyc_id981701460901220">myDB.SetTransactionMode()</paragraph>
</pycode>
</section>
<embed href="text/sbasic/shared/03/lib_ScriptForge.xhp#SF_InternalUse"/>
<section id="relatedtopics">
<embed href="text/sbasic/shared/03/sf_datasheet.xhp#DatasheetService"/>
<embed href="text/sbasic/shared/03/sf_dataset.xhp#DatasetService"/>
<embed href="text/sbasic/shared/03/sf_document.xhp#DocumentService"/>
<embed href="text/sbasic/shared/03/sf_form.xhp#FormService"/>
<embed href="text/sbasic/shared/03/sf_ui.xhp#UIService"/>
</section>
</body>
</helpdocument>
|
