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
|
<?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" name="Database service"><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>
<comment>TODO: Add reference to the future help page SFDocuments.SF_Form service in the paragraph below</comment>
<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. This service does not provide access to forms or reports in the Base document.</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 can use optional square brackets to enclose tables, queries and fields instead of using other enclosing characters that may be exclusive to certain Relational Database Management Systems (RDBMS).</tip>
<h2 id="hd_id91587913266988" xml-lang="en-US">Service invocation</h2>
<h3 id="hd_id151585843652710" localize="false"><embedvar href="text/sbasic/shared/00000003.xhp#functsyntax"/></h3>
<paragraph role="paragraph" id="par_id541599408159668" xml-lang="en-US">The code snippet below shows how to access any database with the <literal>Database</literal> service.</paragraph>
<bascode>
<paragraph role="bascode" localize="false" id="bas_id121599408183587">Dim myDatabase As Object</paragraph>
<paragraph role="bascode" localize="false" id="bas_id551599408191327">Set myDatabase = CreateScriptService("SFDatabases.Database", [FileName], [RegistrationName], [ReadOnly], [User, [Password]])</paragraph>
<paragraph role="bascode" id="bas_id471599408248873">' ... Run queries, SQL statements, ...</paragraph>
<paragraph role="bascode" localize="false" id="bas_id581599408256629">myDatabase.CloseDatabase()</paragraph>
</bascode>
<h3 id="hd_id21585843652323" localize="false"><embedvar href="text/sbasic/shared/00000003.xhp#functparameters"/></h3>
<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 a file name 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#Python_Support"/>
<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" name="UI Service"><literal>ScriptForge.UI</literal></link> service, as shown in the example below:</paragraph>
<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("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_id251599408799447">myDoc.CloseDocument()</paragraph>
</bascode>
<tip id="par_id361619188184750">The <link href="text/sbasic/shared/03/sf_database.xhp#GetDatabase" name="GetDatabase method">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>
<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" name="XConnection API">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" name="XDatabaseMetaData API">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>
<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" name="CloseDatabase">CloseDatabase</link><br/>
<link href="text/sbasic/shared/03/sf_database.xhp#DFunctions" name="DAvg">DAvg</link><br/>
<link href="text/sbasic/shared/03/sf_database.xhp#DFunctions" name="DCount">DCount</link>
</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id611614360519104" role="tablecontent" localize="false">
<link href="text/sbasic/shared/03/sf_database.xhp#DFunctions" name="DMin">DMin</link><br/>
<link href="text/sbasic/shared/03/sf_database.xhp#DFunctions" name="DMax">DMax</link><br/>
<link href="text/sbasic/shared/03/sf_database.xhp#DFunctions" name="DSum">DSum</link>
</paragraph>
</tablecell>
<tablecell>
<paragraph id="par_id611614360518452" role="tablecontent" localize="false">
<link href="text/sbasic/shared/03/sf_database.xhp#DLookup" name="DLookup">DLookup</link><br/>
<link href="text/sbasic/shared/03/sf_database.xhp#GetRows" name="GetRows">GetRows</link><br/>
<link href="text/sbasic/shared/03/sf_database.xhp#RunSql" name="RunSql">RunSql</link>
</paragraph>
</tablecell>
</tablerow>
</table>
<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>
<h3 id="hd_id591587913266515" localize="false"><embedvar href="text/sbasic/shared/00000003.xhp#functsyntax"/></h3>
<bascode>
<paragraph role="bascode" localize="false" id="bas_id29158791326686">myDatabase.CloseDatabase()</paragraph>
</bascode>
</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>
<h3 id="hd_id461596554849371" localize="false"><embedvar href="text/sbasic/shared/00000003.xhp#functsyntax"/></h3>
<bascode>
<paragraph role="bascode" localize="false" id="bas_id441596554849435">myDatabase.DAvg(Expression As String, TableName As String, [Criteria As String]) As Variant</paragraph>
</bascode>
<h3 id="hd_id201596554849717" localize="false"><embedvar href="text/sbasic/shared/00000003.xhp#functparameters"/></h3>
<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>
<h3 id="hd_id841596554849466" localize="false"><embedvar href="text/sbasic/shared/00000003.xhp#functexample"/></h3>
<paragraph role="paragraph" id="par_id781615150306678">The example below assumes the file <literal>Employees.odb</literal> has a table named <literal>EmployeeData</literal>.</paragraph>
<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", "~/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>
</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>
<h3 id="hd_id991599488113830" localize="false"><embedvar href="text/sbasic/shared/00000003.xhp#functsyntax"/></h3>
<bascode>
<paragraph role="bascode" localize="false" id="bas_id351599488113828">myDatabase.DLookup(Expression As String, TableName As String, [Criteria As String], [OrderClause As String]) As Variant</paragraph>
</bascode>
<h3 id="hd_id681599488113330" localize="false"><embedvar href="text/sbasic/shared/00000003.xhp#functparameters"/></h3>
<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>
<h3 id="hd_id41599488113501" localize="false"><embedvar href="text/sbasic/shared/00000003.xhp#functexample"/></h3>
<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>
</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>
<h3 id="hd_id99159948927831" localize="false"><embedvar href="text/sbasic/shared/00000003.xhp#functsyntax"/></h3>
<bascode>
<paragraph role="bascode" localize="false" id="bas_id31599489278687">myDatabase.GetRows(SQLCommand As String, [DirectSQL As Boolean], [Header As Boolean], [MaxRows As Long]) As Variant</paragraph>
</bascode>
<h3 id="hd_id901599489278929" localize="false"><embedvar href="text/sbasic/shared/00000003.xhp#functparameters"/></h3>
<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>
<h3 id="hd_id531599489278812" localize="false"><embedvar href="text/sbasic/shared/00000003.xhp#functexample"/></h3>
<paragraph role="paragraph" id="par_id721615385125947">Below are a few examples of how the <literal>GetRows</literal> method can be used:</paragraph>
<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>
</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" xml-lang="en-US">The <literal>RunSql</literal> method is rejected with an error message in case the database was previously opened in read-only mode.</tip>
<h3 id="hd_id681599490609322" localize="false"><embedvar href="text/sbasic/shared/00000003.xhp#functsyntax"/></h3>
<bascode>
<paragraph role="bascode" localize="false" id="bas_id11599490609861">myDatabase.RunSql(SQLCommand As String, [DirectSQL As Boolean]) As Boolean</paragraph>
</bascode>
<h3 id="hd_id221599490609863" localize="false"><embedvar href="text/sbasic/shared/00000003.xhp#functparameters"/></h3>
<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" xml-lang="en-US"><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>
<h3 id="hd_id31599490609478" localize="false"><embedvar href="text/sbasic/shared/00000003.xhp#functexample"/></h3>
<bascode>
<paragraph role="bascode" localize="false" id="bas_id47159949060973">myDatabase.RunSql("INSERT INTO [EmployeeData] VALUES(25, 'Smith', 'John')", DirectSQL := True)</paragraph>
</bascode>
</section>
<embed href="text/sbasic/shared/03/lib_ScriptForge.xhp#SF_InternalUse"/>
<section id="relatedtopics">
<embed href="text/sbasic/shared/03/sf_document.xhp#DocumentService"/>
<embed href="text/sbasic/shared/03/sf_ui.xhp#UIService"/>
</section>
</body>
</helpdocument>
|
