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
|
require 'libgda'
def list_datasources
Gda::DataSource.each do |ds|
puts "NAME: '#{ds.name}', PROVIDER: '#{ds.provider}', CNC: '#{ds.cnc_string}', " \
"USER: '#{ds.username}', PASSWORD: '#{ds.password}'."
end
end
def list_providers
Gda::Provider.each { |provider| puts "ID: '#{provider.prov_id}'." }
end
def save_ds
ds = Gda::DataSource.new("arrakis",
"MySQL",
"DATABASE=pinux;HOST=192.168.1.1",
"MySQL connection to arrakis for user pinux",
"pinux", "pinux@arrakis")
ds.save
end
def remove_ds
Gda::DataSource.find("arrakis").remove
end
def show_errors(conn)
# Obtains errors list and loop for getting error information.
conn.errors.each do |error|
puts "Error no: #{error.number.to_s}"
puts "Description: #{error.description}"
puts "Source: #{error.source}"
puts "SQL state: #{error.sqlstate}"
end
end
def show_table(dm)
dm.each_column { |title| puts title }
puts ""
dm.each_row { |row| row.each_value { |val| puts val.to_s } }
end
def create_command(text)
Gda::Command.new(text,
Gda::Command::TYPE_SQL,
Gda::Command::OPTION_STOP_ON_ERRORS)
end
def execute_sql_non_query(conn, text)
command = create_command(text)
conn.execute_non_query(command)
end
def execute_sql_command(conn, text)
command = create_command(text)
array = conn.execute_command(command)
array.each { |datamodel| show_table(datamodel) }
end
def execute_some_queries(conn)
execute_sql_non_query(conn, "DELETE FROM customers")
execute_sql_non_query(conn, "INSERT INTO customers (ref, name, surname, phone) " \
"VALUES (1, 'Linus', 'Torvalds', '264-85529-23');" \
"INSERT INTO customers (ref, name, surname, phone) " \
"VALUES (2, 'Alan', 'Cox', '05-564791235416');")
execute_sql_non_query(conn, "DELETE FROM accounts;" \
"INSERT INTO accounts (ref_customer, balance) " \
"VALUES (1, 1000);" \
"INSERT INTO accounts (ref_customer, balance) " \
"VALUES (2, 5000);")
execute_sql_command(conn, "SELECT * FROM customers;" \
"SELECT * FROM accounts")
end
def process_accounts(conn)
# Creates first transaction.
transaction_one = Gda::Transaction.new("accounts1")
# Changes the isolation level.
transaction_one.isolation_level = Gda::Transaction::ISOLATION_SERIALIZABLE
# Links it to connection.
conn.begin_transaction(transaction_one)
command = Gda::Command.new("UPDATE accounts SET balance=balance+50 WHERE ref_customer=1",
Gda::Command::TYPE_SQL,
Gda::Command::OPTION_STOP_ON_ERRORS)
# Links command to transaction.
command.transaction = transaction_one
conn.execute_non_query(command)
command = Gda::Command.new("UPDATE accounts SET balance=balance-50 WHERE ref_customer=2",
Gda::Command::TYPE_SQL,
Gda::Command::OPTION_STOP_ON_ERRORS)
command.transaction = transaction_one
conn.execute_non_query(command)
# Makes commit on transaction.
conn.commit_transaction(transaction_one)
transaction_two = Gda::Transaction.new("accounts2")
transaction_two.isolation_level = Gda::Transaction::ISOLATION_SERIALIZABLE
conn.begin_transaction(transaction_two)
command = Gda::Command.new("UPDATE accounts SET balance=balance+400 WHERE ref_customer=1",
Gda::Command::TYPE_SQL,
Gda::Command::OPTION_STOP_ON_ERRORS)
command.transaction = transaction_two
conn.execute_non_query(command)
command = Gda::Command.new("UPDATE accounts SET balance=balance-400 WHERE ref_customer=2",
Gda::Command::TYPE_SQL,
Gda::Command::OPTION_STOP_ON_ERRORS)
command.transaction = transaction_two
conn.execute_non_query(command)
# Makes rollback on second transaction.
conn.rollback_transaction(transaction_two)
execute_sql_command(conn, "SELECT * FROM accounts")
end
Gda.init("TestGDA", "0.1")
save_ds
Gda.main do
list_providers
list_datasources
client = Gda::Client.new
client.open_connection("arrakis", nil, nil) do |conn|
conn.signal_connect('error') { show_errors(conn) }
execute_some_queries(conn)
process_accounts(conn)
end
remove_ds
end
|
