-
Notifications
You must be signed in to change notification settings - Fork 17
/
t1.pas
151 lines (126 loc) · 2.99 KB
/
t1.pas
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
program t1;
{$mode objfpc}{$H+}
uses
heaptrc,
Classes, SysUtils,
// gh
ghSQL, ghSQLdbLib;
var
Co: TghSQLConnector;
User: TghSQLTable;
SQL: TghSQLClient;
procedure ShowUser;
var
lStr: string;
begin
lStr := Format('#%d %s: %s(%s)',
[User['id'].AsInteger, User['login'].AsString,
User['name'].AsString, User['passwd'].AsString]);
writeln(lStr);
end;
procedure ShowAll;
begin
writeln;
writeln('Show all:');
if not User.IsEmpty then
begin
User.First;
while not User.EOF do
begin
ShowUser;
User.Next;
end;
end
else
writeln('No records found.');
writeln;
end;
begin
// create the connector an set configurations using SQLite
Co := TghSQLConnector.Create(TghSQLite3Lib);
SQL := TghSQLClient.Create(Co);
try
// set params
Co.Database := 'DB.sqlite';
// execute the external script
SQL.Clear;
SQL.Script.LoadFromFile('script.sql');
SQL.IsBatch := True;
SQL.Execute;
// get the User table
// you do not need (but possible) to use Free method for these instances
User := Co.Tables['user'].Open;
// Adding Default constraints
User.Constraints.AddDefault('login', 'guest');
User.Constraints.AddDefault('passwd', '123');
User.Constraints.AddDefault('access_id', '2');
User.Append;
User['name'].AsString := 'Nick Bool';
User.Commit;
// see
writeln('New user: <see default values>');
ShowUser;
User.Close.Open;
// select (optional) and conditionals (optional)
writeln('Select one record:');
User.Select('*').Where('id = %d', [2]).Open;
writeln('User found: ' + User['name'].AsString);
// editing...
writeln('Editing...');
User.Edit;
User.Columns['name'].AsString := 'John Black';
User.Commit;
// show only one
ShowAll;
// get all records
User.Close.Open;
ShowAll;
// Adding a Unique constraint
// User.Constraints.AddUnique(['name']);
// Trying to insert admin, but he already exist!! (see script.sql)
User.Append;
User['name'].AsString := 'admin';
if User.Post.HasErrors then
begin
WriteLn('ERROR: ' + User.GetErrors.Text);
User.Cancel;
end
else
User.Commit;
// Adding a Check constraint
User.Constraints.AddCheck('login', ['g1', 'g2', 'eric']);
User.Append;
User['login'].AsString := 'g1';
User['name'].AsString := 'Jenny';
if User.Post.HasErrors then
begin
WriteLn('ERROR: ' + User.GetErrors.Text);
User.Cancel;
end
else
// OK, login "g1" is ok...
User.Commit;
// see
ShowAll;
// trying again...
User.Append;
User['login'].AsString := 'test';
User['name'].AsString := 'Martin';
// login "test" is Ok?
if User.Post.HasErrors then
begin
WriteLn('ERROR: ' + User.GetErrors.Text);
User.Cancel;
end
else
User.Commit;
// see
ShowAll;
finally
SQL.Free;
Co.Free;
end;
writeln;
writeln('Done.');
writeln;
end.