[r8764]: incubator / ooSQLite / examples / insertIntoDatabase.rex Maximize Restore History

Download this file

insertIntoDatabase.rex    459 lines (343 with data), 15.5 kB

  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
#!/usr/bin/rexx
/*----------------------------------------------------------------------------*/
/* */
/* Copyright (c) 2012-2013 Rexx Language Association. All rights reserved. */
/* */
/* This program and the accompanying materials are made available under */
/* the terms of the Common Public License v1.0 which accompanies this */
/* distribution. A copy is also available at the following address: */
/* http://www.oorexx.org/license.html */
/* */
/* Redistribution and use in source and binary forms, with or */
/* without modification, are permitted provided that the following */
/* conditions are met: */
/* */
/* Redistributions of source code must retain the above copyright */
/* notice, this list of conditions and the following disclaimer. */
/* Redistributions in binary form must reproduce the above copyright */
/* notice, this list of conditions and the following disclaimer in */
/* the documentation and/or other materials provided with the distribution. */
/* */
/* Neither the name of Rexx Language Association nor the names */
/* of its contributors may be used to endorse or promote products */
/* derived from this software without specific prior written permission. */
/* */
/* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS */
/* "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT */
/* LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS */
/* FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT */
/* OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, */
/* SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED */
/* TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, */
/* OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY */
/* OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING */
/* NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS */
/* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. */
/* */
/*----------------------------------------------------------------------------*/
/**
* insertIntoDatabas.rex
*
* Uses the phone book database created in the createDatabase.rex example.
* Please execute createDatabase.rex first, then execute this example. The
* example will not work correctly if createDatabase.rex is not executed prior
* to executing this program.
*
* This example inserts data into the various tables in the phoneBook database.
* The various task involved in inserting the data are carried out by methods of
* the ProgramAssistant class. The ProgramAssistant class is not a part of
* the ooSQLite extension.
*
* Note that the author has little to no previous experience with SQL and
* relational databases. This example is not intended to show the "best" way
* to use SQL or to deal with a relational database in general. It is meant to
* help someone get started using ooSQLite.
*
* A note on SQL Injection Flaws
*
* SQL Injection flaws are introduced when software developers create dynamic
* database queries that include user supplied input.
*
* In this program dynamic SQL INSERT statements are constructed, however, the
* input to the construction does not come from user input. The input comes
* from within this program. The input should be trustworthy in this case. Do
* not blindly copy code from this example without giving some thought to SQL
* Injection flaws.
*/
dbFile = .File~new('phoneBook.rdbx')
if \ dbFile~exists then do
say 'This example program requires that the phoneBook.rdbx database'
say 'exists, and it can not be found.'
say
say 'First execute the "createDatabase.rex" example program. This'
say 'will create the required database. Then try this example.'
say
return 99
end
-- ProgramAssistant is not a part of ooSQLite. It is a private class defined
-- in this example. It is used to make the code more modular. The
-- ProgramAssistant has 6 main methods. 1 method to check that the database is
-- empty of records and 1 method to insert the records into each of the 5
-- tables of the database.
helper = .ProgramAssistant~new
db = .ooSQLiteConnection~new('phoneBook.rdbx')
if db~initCode <> 0 then return helper~openDbErr(db)
-- We set a busy handler of 3 seconds, which is more than enough for this
-- simple program. If we get busy timeouts with this value, it would indicate
-- that there was an error in this program.
db~busyTimeout(3000)
-- This checks that the database is empty of records.
if \ helper~okayToContinue(db) then do
db~close
return 99
end
if helper~addContacts(db) \== db~OK then return 99
if helper~addAddressTypes(db) \== db~OK then return 99
if helper~addStreetAddresses(db) \== db~OK then return 99
if helper~addPhones(db) \== db~OK then return 99
if helper~addInternet(db) \== db~OK then return 99
ret = db~close
if ret == db~BUSY then do
say 'Database busy return from close.'
end
say
say 'The phoneBook database is now populated with records.'
say
return 0
::requires 'ooSQLite.cls'
::class 'ProgramAssistant'
/* Simple method to print an error message if there is an error trying to open
* the database.
*/
::method openDbErr
use strict arg db
errRC = db~lastErrCode
errMsg = db~lastErrMsg
say 'ooSQLiteConnection initialization error:' db~initCode
say ' Error code:' errRC '('errMsg')'
db~close
return 99
/* Checks that the database is empty, that there are no records in it yet.
* Inserting records when they already exist causes an error. To run this
* program properly, createDatabase.rex needs to be run first.
*/
::method okayToContinue
use strict arg db
sql = 'select * from addr_type;'
stmt = .ooSQLiteStmt~new(db, sql)
if stmt~initCode <> 0 then do
stmt~finalize
return .false
end
-- If there are no records then the return should be db~DONE
ret = stmt~step
stmt~finalize
if ret <> db~DONE then do
say 'This example program requires the database to be created, but'
say 'empty of records.'
say
say 'Please execute "createDatabase.rex" first to create the empty'
say 'database, then run this example again.'
say
return .false
end
return .true
/* Adds the initial records to the contacts table */
::method addContacts
use strict arg db
contacts = self~getContacts
do c over contacts
sql = "INSERT INTO contacts (fName, lName, mName, nickname, title)" || -
" VALUES('"c[1]"', '"c[2]"', '"c[3]"', '"c[4]"', '"c[5]"');"
stmt = .ooSQLiteStmt~new(db, sql)
if stmt~initCode <> 0 then return self~stmtError(stmt, db, 'PREPARE', 'contacts')
ret = stmt~step
if ret <> db~DONE then return self~stmtError(stmt, db, 'INSERT', 'contacts')
stmt~finalize
end
say 'Added contact records'
return db~OK
/* Adds the initial records to the addr_type table */
::method addAddressTypes
use strict arg db
vals = .array~of('Home', 'Mobile', 'Work', 'Business', 'Office', 'Home e-mail', -
'Alternate e-mail', 'Work e-mail', 'Business e-mail', 'Office e-mail', -
'Business website', 'Personal website')
sql = "INSERT INTO addr_type (type) VALUES(?1);"
stmt = .ooSQLiteStmt~new(db, sql)
if stmt~initCode <> 0 then return self~stmtError(stmt, db, 'PREPARE', 'addr_type')
do i = 1 to vals~items
ret = stmt~bindText(1, vals[i])
if ret <> db~OK then return self~bindError(db, stmt, ret, vals[i])
ret = stmt~step
if ret <> db~DONE then return self~stmtError(stmt, db, 'INSERT', 'addr_type')
ret = stmt~reset
if ret <> db~OK then return self~resetError(db, stmt, ret, vals[i])
end
ret = stmt~finalize
if ret == db~BUSY then do
say 'Database busy after last statement finalize.' ret
db~close
end
say 'Added address type records'
return ret
/* Adds the initial records to the street_addr table */
::method addStreetAddresses
use strict arg db
sql = "INSERT INTO street_addr (street1, street2, city, state, zip, contact_id, type_id)" -
"VALUES(?1, ?2, ?3, ?4, ?5, ?6, ?7);"
stmt = .ooSQLiteStmt~new(db, sql)
if stmt~initCode <> 0 then return self~stmtError(stmt, db, 'PREPARE', 'street_addr')
-- Tom Sawyer
vals = .Array~of('61 Mud St', '', 'St. Petersburg', 'Missouri', '62264', 1, 1)
ret = self~addRecord(db, stmt, vals, 7, 'street_adr')
if ret == 99 then return ret
vals = .Array~of('raft', '', 'Mississippi River', 'Missouri', '62264', 1, 5)
ret = self~addRecord(db, stmt, vals, 7, 'street_adr')
if ret == 99 then return ret
-- Howard Martin
vals = .Array~of('505 Dellwood Ave', 'Apt. #23', 'Boulder', 'Colorado', '80301', 2, 1)
ret = self~addRecord(db, stmt, vals, 7, 'street_adr')
if ret == 99 then return ret
vals = .Array~of('928 Perl St', '', 'Boulder', 'Colorado', '80302', 2, 3)
ret = self~addRecord(db, stmt, vals, 7, 'street_adr')
if ret == 99 then return ret
-- Mary Lyle
vals = .Array~of('4325 NE Knott St', '', 'Portland', 'Oregon', '97266', 3, 1)
ret = self~addRecord(db, stmt, vals, 7, 'street_adr')
if ret == 99 then return ret
vals = .Array~of('5555 N Channel Ave', '#71', 'Portland', 'Oregon', '97603', 3, 4)
ret = self~addRecord(db, stmt, vals, 7, 'street_adr')
if ret == 99 then return ret
-- Frank Lyle
vals = .Array~of('7633 SE Garrett Dr', '', 'Portland', 'Oregon', '97222', 4, 1)
ret = self~addRecord(db, stmt, vals, 7, 'street_adr')
if ret == 99 then return ret
vals = .Array~of('3900 Northeast 158th Ave', '', 'Portland', 'Oregon', '97603', 4, 3)
ret = self~addRecord(db, stmt, vals, 7, 'street_adr')
if ret == 99 then return ret
stmt~finalize
say 'Added address records'
return 0
/* Adds the initial records to the phone_addr table */
::method addPhones
use strict arg db
sql = "INSERT INTO phone_addr (number, area_code, extension, contact_id, type_id)" -
"VALUES(?1, ?2, ?3, ?4, ?5);"
stmt = .ooSQLiteStmt~new(db, sql)
if stmt~initCode <> 0 then return self~stmtError(stmt, db, 'PREPARE', 'phone_addr')
-- Howard Martin
vals = .Array~of('543-3892', '303', '', 2, 1)
ret = self~addRecord(db, stmt, vals, 5, 'phone_adr')
if ret == 99 then return ret
vals = .Array~of('667-8392', '970', '', 2, 2)
ret = self~addRecord(db, stmt, vals, 5, 'phone_adr')
if ret == 99 then return ret
vals = .Array~of('935-6911', '720', '', 2, 3)
ret = self~addRecord(db, stmt, vals, 5, 'phone_adr')
if ret == 99 then return ret
-- Mary Lyle
vals = .Array~of('713-2398', '503', '', 3, 1)
ret = self~addRecord(db, stmt, vals, 5, 'phone_adr')
if ret == 99 then return ret
vals = .Array~of('771-6651', '503', '55', 3, 4)
ret = self~addRecord(db, stmt, vals, 5, 'phone_adr')
if ret == 99 then return ret
vals = .Array~of('833-0523', '360', '', 3, 3)
ret = self~addRecord(db, stmt, vals, 5, 'phone_adr')
if ret == 99 then return ret
-- Frank Lyle
vals = .Array~of('713-2398', '503', '', 4, 1)
ret = self~addRecord(db, stmt, vals, 5, 'phone_adr')
if ret == 99 then return ret
vals = .Array~of('887-4106', '360', '', 4, 2)
ret = self~addRecord(db, stmt, vals, 5, 'phone_adr')
if ret == 99 then return ret
stmt~finalize
say 'Added phone records'
return 0
/* Adds the initial records to the inet_addr table */
::method addInternet
use strict arg db
sql = "INSERT INTO inet_addr (inet_addr, contact_id, type_id)" -
"VALUES(?1, ?2, ?3);"
stmt = .ooSQLiteStmt~new(db, sql)
if stmt~initCode <> 0 then return self~stmtError(stmt, db, 'PREPARE', 'inet_addr')
-- Howard Martin
vals = .Array~of('hmartin@san.rr.com', 2, 6)
ret = self~addRecord(db, stmt, vals, 3, 'inet_adr')
if ret == 99 then return ret
vals = .Array~of('hmarabcd@manta.com', 2, 3)
ret = self~addRecord(db, stmt, vals, 3, 'inet_adr')
if ret == 99 then return ret
vals = .Array~of('http://www.manta.com/', 2, 11)
ret = self~addRecord(db, stmt, vals, 3, 'inet_adr')
if ret == 99 then return ret
vals = .Array~of('http://www.howard.martin.name/', 2, 12)
ret = self~addRecord(db, stmt, vals, 3, 'inet_adr')
if ret == 99 then return ret
-- Mary Lyle
vals = .Array~of('marylyle@gmail.com', 3, 6)
ret = self~addRecord(db, stmt, vals, 3, 'inet_adr')
if ret == 99 then return ret
-- Frank Lyle
vals = .Array~of('franklyle@gmail.com', 4, 6)
ret = self~addRecord(db, stmt, vals, 3, 'inet_adr')
if ret == 99 then return ret
stmt~finalize
say 'Added Internet records'
return 0
::method addRecord private
use strict arg db, stmt, vals, count, tableName
do i = 1 to count
ret = stmt~bindText(i, vals[i])
if ret <> db~OK then return self~bindError(db, stmt, ret, vals[i])
end
ret = stmt~step
if ret <> db~DONE then return self~stmtError(stmt, db, 'INSERT', tableName)
ret = stmt~reset
if ret <> db~OK then return self~resetError(db, stmt, ret, vals[i])
return 0
::method getContacts private
contacts = .array~new(10)
contacts[ 1] = .array~of('Tom', 'Sawyer', '', '', '' )
contacts[ 2] = .array~of('Howard', 'Martin', 'Pat', 'Howie', 'Mr.' )
contacts[ 3] = .array~of('Mary', 'Lyle', 'Beth', 'Mary Beth', 'Mrs.')
contacts[ 4] = .array~of('Frank', 'Lyle', 'Richard', '', 'Mr.' )
return contacts
::method bindError private
use strict arg db, stmt, ret, val
say 'Error binding "'val'". RC:' ret
say " database error:" db~lastErrCode "("db~lastErrMsg")"
stmt~finalize
db~close
return 99
::method resetError
use strict arg db, stmt, ret, val
say 'Error reseting statement after binding "'val'". RC:' ret
say " database error:" db~lastErrCode "("db~lastErrMsg")"
stmt~finalize
db~close
return 99
::method stmtError
use strict arg stmt, db, id, tableName = ''
if id == 'INIT' then do
say 'ooSQLiteStmt initialization error (create' tablenName 'table):'
say ' Error:' stmt~initCode '('stmt~lastErrMsg')'
end
else if id == 'TABLE' then do
say "Unexpected return from creating" tableName "table:"
say " database error:" db~lastErrCode "("db~lastErrMsg")"
end
else if id == 'PREPARE' then do
say 'ooSQLiteStmt initialization error (insert into' tablenName 'table):'
say ' Error:' stmt~initCode '('stmt~lastErrMsg')'
end
else if id == 'INSERT' then do
say 'Unexpected return from inserting record into' tablenName 'table):'
say ' Error:' stmt~initCode '('stmt~lastErrMsg')'
end
say "Aborting example program ..."
stmt~finalize
db~close
return 99