Skip to content
This repository
Newer
Older
100644 324 lines (275 sloc) 9.718 kb
c379285b » Martin Berends
2010-03-03 [examples/mysqlclient] add CREATE TABLE and INSERT, but not yet SELECT
1 # mysqlclient.p6
2
c18fbb26 » Martin Berends
2010-05-31 improve the MySQL client example with column names
3 # Requirements:
4 # 1. MySQL client lib (eg on Debian, sudo apt-get install mysqlclient-dev
5 # 2. An account and a database on a MySQL server, for this example:
6 # username = testuser
7 # password = testpass
8 # database = zavolaj
9 # 3. Permissions, eg "grant all privileges on zavolaj.* to testuser@localhost"
10
0e78155f » Martin Berends
2010-03-09 [examples/mysqlclient.p6] added several API functions and some docs
11 # Fortunately made possible by explicitly by hardcoded support in
c379285b » Martin Berends
2010-03-03 [examples/mysqlclient] add CREATE TABLE and INSERT, but not yet SELECT
12 # parrot/src/nci/extra_thunks.nci.
0e78155f » Martin Berends
2010-03-09 [examples/mysqlclient.p6] added several API functions and some docs
13 # See /usr/include/mysql.h for what should be callable, or browse
14 # http://dev.mysql.com/doc/refman/5.1/en/c-api-function-overview.html
c379285b » Martin Berends
2010-03-03 [examples/mysqlclient] add CREATE TABLE and INSERT, but not yet SELECT
15
16 # Status:
0e78155f » Martin Berends
2010-03-09 [examples/mysqlclient.p6] added several API functions and some docs
17 # Works: affected_rows close connect error fetch_field fetch_row
18 # field_count free_result get_client_info init num_rows query
19 # real_connect stat store_result use_result
20 # Fails: create_db library_end library_init
21 # The reason for most failures is that a mapping is not available
22 # between the native data types and the Perl 6 data types that must be
23 # used in the foreign function definitions below.
24 # As NativeCall.pm, Rakudo and Parrot continue to evolve, more of the
25 # functions that have not worked may become usable.
26 # Volunteers, please test from time to time and give feedback in #perl6.
c379285b » Martin Berends
2010-03-03 [examples/mysqlclient] add CREATE TABLE and INSERT, but not yet SELECT
27
28 use NativeCall;
29
0e78155f » Martin Berends
2010-03-09 [examples/mysqlclient.p6] added several API functions and some docs
30 # -------- foreign function definitions in alphabetical order ----------
c379285b » Martin Berends
2010-03-03 [examples/mysqlclient] add CREATE TABLE and INSERT, but not yet SELECT
31
0e78155f » Martin Berends
2010-03-09 [examples/mysqlclient.p6] added several API functions and some docs
32 sub mysql_affected_rows( OpaquePointer $mysql_client )
33 returns Int
34 is native('libmysqlclient')
35 { ... }
36
37 sub mysql_close( OpaquePointer $mysql_client )
a223fb92 » Martin Berends
2010-03-03 interim save, field [0] from mysql_fetch_row returns ok but higher on…
38 returns OpaquePointer
c379285b » Martin Berends
2010-03-03 [examples/mysqlclient] add CREATE TABLE and INSERT, but not yet SELECT
39 is native('libmysqlclient')
40 { ... }
41
0e78155f » Martin Berends
2010-03-09 [examples/mysqlclient.p6] added several API functions and some docs
42 sub mysql_data_seek( OpaquePointer $result_set, Int $row_number )
a223fb92 » Martin Berends
2010-03-03 interim save, field [0] from mysql_fetch_row returns ok but higher on…
43 returns OpaquePointer
c379285b » Martin Berends
2010-03-03 [examples/mysqlclient] add CREATE TABLE and INSERT, but not yet SELECT
44 is native('libmysqlclient')
45 { ... }
46
a223fb92 » Martin Berends
2010-03-03 interim save, field [0] from mysql_fetch_row returns ok but higher on…
47 sub mysql_error( OpaquePointer $mysql_client)
c379285b » Martin Berends
2010-03-03 [examples/mysqlclient] add CREATE TABLE and INSERT, but not yet SELECT
48 returns Str
49 is native('libmysqlclient')
50 { ... }
51
0e78155f » Martin Berends
2010-03-09 [examples/mysqlclient.p6] added several API functions and some docs
52 sub mysql_fetch_field( OpaquePointer $result_set )
c18fbb26 » Martin Berends
2010-05-31 improve the MySQL client example with column names
53 returns Positional of Str
ee1bc1c6 » Martin Berends
2010-03-03 [examples/mysqlclient.p6] renamed from connect.p6, and shinier
54 is native('libmysqlclient')
55 { ... }
56
0e78155f » Martin Berends
2010-03-09 [examples/mysqlclient.p6] added several API functions and some docs
57 sub mysql_fetch_lengths( OpaquePointer $result_set )
58 returns Positional of Int
ee1bc1c6 » Martin Berends
2010-03-03 [examples/mysqlclient.p6] renamed from connect.p6, and shinier
59 is native('libmysqlclient')
60 { ... }
61
0e78155f » Martin Berends
2010-03-09 [examples/mysqlclient.p6] added several API functions and some docs
62 sub mysql_fetch_row( OpaquePointer $result_set )
63 returns Positional of Str
64 is native('libmysqlclient')
65 { ... }
66
67 sub mysql_field_count( OpaquePointer $mysql_client )
c379285b » Martin Berends
2010-03-03 [examples/mysqlclient] add CREATE TABLE and INSERT, but not yet SELECT
68 returns Int
69 is native('libmysqlclient')
70 { ... }
71
0e78155f » Martin Berends
2010-03-09 [examples/mysqlclient.p6] added several API functions and some docs
72 sub mysql_free_result( OpaquePointer $result_set )
f2fccb06 » Martin Berends
2010-05-26 add a (commented out) diagnostic to trait_mod:<is>, tweak the mysql e…
73 # returns OpaquePointer
c379285b » Martin Berends
2010-03-03 [examples/mysqlclient] add CREATE TABLE and INSERT, but not yet SELECT
74 is native('libmysqlclient')
75 { ... }
76
0e78155f » Martin Berends
2010-03-09 [examples/mysqlclient.p6] added several API functions and some docs
77 sub mysql_get_client_info( OpaquePointer $mysql_client)
78 returns Str
79 is native('libmysqlclient')
80 { ... }
81
82 sub mysql_init( OpaquePointer $mysql_client )
a223fb92 » Martin Berends
2010-03-03 interim save, field [0] from mysql_fetch_row returns ok but higher on…
83 returns OpaquePointer
c379285b » Martin Berends
2010-03-03 [examples/mysqlclient] add CREATE TABLE and INSERT, but not yet SELECT
84 is native('libmysqlclient')
85 { ... }
86
0e78155f » Martin Berends
2010-03-09 [examples/mysqlclient.p6] added several API functions and some docs
87 sub mysql_library_init( Int $argc, OpaquePointer $argv,
88 OpaquePointer $group )
c379285b » Martin Berends
2010-03-03 [examples/mysqlclient] add CREATE TABLE and INSERT, but not yet SELECT
89 returns Int
90 is native('libmysqlclient')
91 { ... }
92
0e78155f » Martin Berends
2010-03-09 [examples/mysqlclient.p6] added several API functions and some docs
93 sub mysql_library_end()
f2fccb06 » Martin Berends
2010-05-26 add a (commented out) diagnostic to trait_mod:<is>, tweak the mysql e…
94 # returns OpaquePointer # currently not working, should be void
c379285b » Martin Berends
2010-03-03 [examples/mysqlclient] add CREATE TABLE and INSERT, but not yet SELECT
95 is native('libmysqlclient')
96 { ... }
97
a223fb92 » Martin Berends
2010-03-03 interim save, field [0] from mysql_fetch_row returns ok but higher on…
98 sub mysql_num_rows( OpaquePointer $result_set )
c379285b » Martin Berends
2010-03-03 [examples/mysqlclient] add CREATE TABLE and INSERT, but not yet SELECT
99 returns Int
100 is native('libmysqlclient')
101 { ... }
102
0e78155f » Martin Berends
2010-03-09 [examples/mysqlclient.p6] added several API functions and some docs
103 sub mysql_query( OpaquePointer $mysql_client, Str $sql_command )
104 returns Int
105 is native('libmysqlclient')
106 { ... }
107
108 sub mysql_real_connect( OpaquePointer $mysql_client, Str $host, Str $user,
109 Str $password, Str $database, Int $port, Str $socket, Int $flag )
a223fb92 » Martin Berends
2010-03-03 interim save, field [0] from mysql_fetch_row returns ok but higher on…
110 returns OpaquePointer
ee1bc1c6 » Martin Berends
2010-03-03 [examples/mysqlclient.p6] renamed from connect.p6, and shinier
111 is native('libmysqlclient')
112 { ... }
113
0e78155f » Martin Berends
2010-03-09 [examples/mysqlclient.p6] added several API functions and some docs
114 sub mysql_stat( OpaquePointer $mysql_client)
115 returns Str
116 is native('libmysqlclient')
117 { ... }
118
119 sub mysql_store_result( OpaquePointer $mysql_client )
120 returns OpaquePointer
121 is native('libmysqlclient')
122 { ... }
123
124 sub mysql_use_result( OpaquePointer $mysql_client )
a223fb92 » Martin Berends
2010-03-03 interim save, field [0] from mysql_fetch_row returns ok but higher on…
125 returns OpaquePointer
ee1bc1c6 » Martin Berends
2010-03-03 [examples/mysqlclient.p6] renamed from connect.p6, and shinier
126 is native('libmysqlclient')
127 { ... }
128
c379285b » Martin Berends
2010-03-03 [examples/mysqlclient] add CREATE TABLE and INSERT, but not yet SELECT
129 # ----------------------- main example program -------------------------
130
0e78155f » Martin Berends
2010-03-09 [examples/mysqlclient.p6] added several API functions and some docs
131 # Structure as recommended in the MySQL 5.1 Reference Manual, C API.
132 # Follow the 5 step general outline after the function summary in
133 # http://dev.mysql.com/doc/refman/5.1/en/c-api-function-overview.html
134
135 # strictly necessary only in multithreaded programs
136 print "library_init [currently not working] ";
137 #my $library_init_result = mysql_library_init( 0, pir::null__P(),
138 # pir::null__P() );
139 #if $library_init_result != 0 {
140 # die "could not initialize MySQL library, returned $library_init_result";
141 #}
142 say "";
143
c379285b » Martin Berends
2010-03-03 [examples/mysqlclient] add CREATE TABLE and INSERT, but not yet SELECT
144 say "init";
145 my $client = mysql_init( pir::null__P() );
146 print mysql_error($client);
147
0e78155f » Martin Berends
2010-03-09 [examples/mysqlclient.p6] added several API functions and some docs
148 print "get_client_info: ";
149 say mysql_get_client_info($client);
150
ee1bc1c6 » Martin Berends
2010-03-03 [examples/mysqlclient.p6] renamed from connect.p6, and shinier
151 say "real_connect";
a223fb92 » Martin Berends
2010-03-03 interim save, field [0] from mysql_fetch_row returns ok but higher on…
152 mysql_real_connect( $client, 'localhost', 'testuser', 'testpass',
153 'mysql', 0, pir::null__P(), 0 );
c379285b » Martin Berends
2010-03-03 [examples/mysqlclient] add CREATE TABLE and INSERT, but not yet SELECT
154 print mysql_error($client);
155
0e78155f » Martin Berends
2010-03-09 [examples/mysqlclient.p6] added several API functions and some docs
156 print "stat: ";
157 say mysql_stat($client);
158
c379285b » Martin Berends
2010-03-03 [examples/mysqlclient] add CREATE TABLE and INSERT, but not yet SELECT
159 say "DROP DATABASE zavolaj";
a223fb92 » Martin Berends
2010-03-03 interim save, field [0] from mysql_fetch_row returns ok but higher on…
160 mysql_query( $client, "
c379285b » Martin Berends
2010-03-03 [examples/mysqlclient] add CREATE TABLE and INSERT, but not yet SELECT
161 DROP DATABASE zavolaj
162 ");
163 print mysql_error($client);
164
165 say "CREATE DATABASE zavolaj";
a223fb92 » Martin Berends
2010-03-03 interim save, field [0] from mysql_fetch_row returns ok but higher on…
166 mysql_query( $client, "
c379285b » Martin Berends
2010-03-03 [examples/mysqlclient] add CREATE TABLE and INSERT, but not yet SELECT
167 CREATE DATABASE zavolaj
168 ");
169 print mysql_error($client);
170
171 say "USE zavolaj";
a223fb92 » Martin Berends
2010-03-03 interim save, field [0] from mysql_fetch_row returns ok but higher on…
172 mysql_query( $client, "
c379285b » Martin Berends
2010-03-03 [examples/mysqlclient] add CREATE TABLE and INSERT, but not yet SELECT
173 USE zavolaj
174 ");
175 print mysql_error($client);
176
177 say "CREATE TABLE nom";
a223fb92 » Martin Berends
2010-03-03 interim save, field [0] from mysql_fetch_row returns ok but higher on…
178 mysql_query( $client,"
c379285b » Martin Berends
2010-03-03 [examples/mysqlclient] add CREATE TABLE and INSERT, but not yet SELECT
179 CREATE TABLE nom (
180 name char(4),
181 description char(30),
182 quantity int,
183 price numeric(5,2)
184 )
185 ");
186 print mysql_error($client);
187
188 say "INSERT nom";
a223fb92 » Martin Berends
2010-03-03 interim save, field [0] from mysql_fetch_row returns ok but higher on…
189 mysql_query( $client, "
ee1bc1c6 » Martin Berends
2010-03-03 [examples/mysqlclient.p6] renamed from connect.p6, and shinier
190 INSERT nom (name, description, quantity, price)
c379285b » Martin Berends
2010-03-03 [examples/mysqlclient] add CREATE TABLE and INSERT, but not yet SELECT
191 VALUES ( 'BUBH', 'Hot beef burrito', 1, 4.95 ),
192 ( 'TAFM', 'Mild fish taco', 1, 4.85 ),
193 ( 'BEOM', 'Medium size orange juice', 2, 1.20 )
194 ");
195 print mysql_error($client);
196
0e78155f » Martin Berends
2010-03-09 [examples/mysqlclient.p6] added several API functions and some docs
197 print "affected rows ";
198 my $affected_rows = mysql_affected_rows( $client );
199 print mysql_error($client);
200 say $affected_rows;
201
ee1bc1c6 » Martin Berends
2010-03-03 [examples/mysqlclient.p6] renamed from connect.p6, and shinier
202 say "SELECT *, quantity*price AS amount FROM nom";
a223fb92 » Martin Berends
2010-03-03 interim save, field [0] from mysql_fetch_row returns ok but higher on…
203 mysql_query( $client, "
ee1bc1c6 » Martin Berends
2010-03-03 [examples/mysqlclient.p6] renamed from connect.p6, and shinier
204 SELECT *, quantity*price AS amount FROM nom
c379285b » Martin Berends
2010-03-03 [examples/mysqlclient] add CREATE TABLE and INSERT, but not yet SELECT
205 ");
206 print mysql_error($client);
207
208 print "field_count ";
209 my $field_count = mysql_field_count($client);
210 print mysql_error($client);
ee1bc1c6 » Martin Berends
2010-03-03 [examples/mysqlclient.p6] renamed from connect.p6, and shinier
211 say $field_count;
c379285b » Martin Berends
2010-03-03 [examples/mysqlclient] add CREATE TABLE and INSERT, but not yet SELECT
212
d27d5a89 » Martin Berends
2010-03-09 [examples/mysqlclient.p6] SELECT works in batch mode, not in per-row …
213 my @rows;
214 my $row_count;
215 my @width = 0 xx $field_count;
c379285b » Martin Berends
2010-03-03 [examples/mysqlclient] add CREATE TABLE and INSERT, but not yet SELECT
216 # There are two ways to retrieve result sets: all at once in a single
217 # batch, or one row at a time. Choose according to the amount of data
218 # and the overhead on the server and the client.
65006905 » Martin Berends
2010-03-07 interim commit, caveat emptor
219 my $batch-mode;
220 $batch-mode = (True,False).pick; # aha, you came looking for this line :-)
59e240f8 » Martin Berends
2010-05-24 add Bool to NativeArray, False means the pointer is null, True means …
221 #$batch-mode = False;
c379285b » Martin Berends
2010-03-03 [examples/mysqlclient] add CREATE TABLE and INSERT, but not yet SELECT
222 if $batch-mode {
223 # Retrieve all the rows in a single batch operation
224 say "store_result";
225 my $result_set = mysql_store_result($client);
226 print mysql_error($client);
227
c18fbb26 » Martin Berends
2010-05-31 improve the MySQL client example with column names
228 say "Columns:";
229 loop ( my $column_number=0; $column_number<$field_count; $column_number++ ) {
230 my $field_info = mysql_fetch_field($result_set);
231 my $column_name = $field_info[0];
232 say " $column_name";
233 }
234
d27d5a89 » Martin Berends
2010-03-09 [examples/mysqlclient.p6] SELECT works in batch mode, not in per-row …
235 print "row_count ";
236 $row_count = mysql_num_rows($result_set);
c379285b » Martin Berends
2010-03-03 [examples/mysqlclient] add CREATE TABLE and INSERT, but not yet SELECT
237 print mysql_error($client);
d27d5a89 » Martin Berends
2010-03-09 [examples/mysqlclient.p6] SELECT works in batch mode, not in per-row …
238 say $row_count;
c379285b » Martin Berends
2010-03-03 [examples/mysqlclient] add CREATE TABLE and INSERT, but not yet SELECT
239
65006905 » Martin Berends
2010-03-07 interim commit, caveat emptor
240 # Since mysql_fetch_fields() is not usable yet, derive the
241 # column widths from the maximum widths of the data in each
d27d5a89 » Martin Berends
2010-03-09 [examples/mysqlclient.p6] SELECT works in batch mode, not in per-row …
242 # column.
0e78155f » Martin Berends
2010-03-09 [examples/mysqlclient.p6] added several API functions and some docs
243 say "fetch_row, fetch_lengths and fetch_field";
d27d5a89 » Martin Berends
2010-03-09 [examples/mysqlclient.p6] SELECT works in batch mode, not in per-row …
244 loop ( my $row_number=0; $row_number<$row_count; $row_number++ ) {
ee1bc1c6 » Martin Berends
2010-03-03 [examples/mysqlclient.p6] renamed from connect.p6, and shinier
245 my $row_data = mysql_fetch_row( $result_set );
0e78155f » Martin Berends
2010-03-09 [examples/mysqlclient.p6] added several API functions and some docs
246 my $field_length_array = mysql_fetch_lengths( $result_set );
65006905 » Martin Berends
2010-03-07 interim commit, caveat emptor
247
ee1bc1c6 » Martin Berends
2010-03-03 [examples/mysqlclient.p6] renamed from connect.p6, and shinier
248 # It would be better to be able to call mysql_fetch_fields().
65006905 » Martin Berends
2010-03-07 interim commit, caveat emptor
249 # my @row = mysql_fetch_fields($result_set);
250 # But that cannot be implmented yet in Rakudo because the
251 # returned result is a packed binary record of character
252 # pointers, unsigned longs and unsigned ints. See mysql.h
d27d5a89 » Martin Berends
2010-03-09 [examples/mysqlclient.p6] SELECT works in batch mode, not in per-row …
253 my @row = ();
ee1bc1c6 » Martin Berends
2010-03-03 [examples/mysqlclient.p6] renamed from connect.p6, and shinier
254 loop ( my $field_number=0; $field_number<$field_count; $field_number++ ) {
d27d5a89 » Martin Berends
2010-03-09 [examples/mysqlclient.p6] SELECT works in batch mode, not in per-row …
255 my $field = $row_data[$field_number];
0e78155f » Martin Berends
2010-03-09 [examples/mysqlclient.p6] added several API functions and some docs
256 # array of unsigned long segfaults
257 # my $chars = $field_length_array[$field_number];
258 my $chars = $field.chars;
259 @width[$field_number] = max @width[$field_number], $chars;
d27d5a89 » Martin Berends
2010-03-09 [examples/mysqlclient.p6] SELECT works in batch mode, not in per-row …
260 push @row, $field;
ee1bc1c6 » Martin Berends
2010-03-03 [examples/mysqlclient.p6] renamed from connect.p6, and shinier
261 }
d27d5a89 » Martin Berends
2010-03-09 [examples/mysqlclient.p6] SELECT works in batch mode, not in per-row …
262 push @rows, [@row];
c379285b » Martin Berends
2010-03-03 [examples/mysqlclient] add CREATE TABLE and INSERT, but not yet SELECT
263 }
ee1bc1c6 » Martin Berends
2010-03-03 [examples/mysqlclient.p6] renamed from connect.p6, and shinier
264 say "free_result";
265 mysql_free_result($result_set);
266 print mysql_error($client);
d27d5a89 » Martin Berends
2010-03-09 [examples/mysqlclient.p6] SELECT works in batch mode, not in per-row …
267 # Having determined the column widths by measuring every field,
268 # it is finally possible to pretty print the table.
269 loop ( my $j=0; $j<$field_count; $j++ ) {
270 print "+--";
271 print '-' x @width[$j];
272 }
273 say '+';
274 loop ( my $i=0; $i<$row_count; $i++ ) {
275 my @row = @rows[$i];
276 loop ( my $j=0; $j<$field_count; $j++ ) {
277 my $field = @row[0][$j];
278 print "| $field ";
279 print ' ' x ( @width[$j] - $field.chars );
280 }
281 say '|';
282 }
283 loop ( my $k=0; $k<$field_count; $k++ ) {
284 print "+--";
285 print '-' x @width[$k];
286 }
287 say '+';
c379285b » Martin Berends
2010-03-03 [examples/mysqlclient] add CREATE TABLE and INSERT, but not yet SELECT
288 }
289 else {
290 # Retrieve rows one at a time from the server
291 say "use_result";
292 my $result_set = mysql_use_result($client);
293 print mysql_error($client);
294
c18fbb26 » Martin Berends
2010-05-31 improve the MySQL client example with column names
295 say "Columns:";
296 loop ( my $column_number=0; $column_number<$field_count; $column_number++ ) {
297 my $field_info = mysql_fetch_field($result_set);
298 my $column_name = $field_info[0];
299 say " $column_name";
300 }
301
59e240f8 » Martin Berends
2010-05-24 add Bool to NativeArray, False means the pointer is null, True means …
302 while my $row_data = mysql_fetch_row($result_set) {
303 my @row;
d27d5a89 » Martin Berends
2010-03-09 [examples/mysqlclient.p6] SELECT works in batch mode, not in per-row …
304 loop ( my $field_number=0; $field_number<$field_count; $field_number++ ) {
305 my $field = $row_data[$field_number];
306 @width[$field_number] = max @width[$field_number], $field.chars;
307 push @row, $field;
308 }
59e240f8 » Martin Berends
2010-05-24 add Bool to NativeArray, False means the pointer is null, True means …
309 @row.join(', ').say;
310 # no fancy boxes this time because the width of later fields is unknown
c379285b » Martin Berends
2010-03-03 [examples/mysqlclient] add CREATE TABLE and INSERT, but not yet SELECT
311 }
d27d5a89 » Martin Berends
2010-03-09 [examples/mysqlclient.p6] SELECT works in batch mode, not in per-row …
312 say "free_result";
313 mysql_free_result($result_set);
314 print mysql_error($client);
c379285b » Martin Berends
2010-03-03 [examples/mysqlclient] add CREATE TABLE and INSERT, but not yet SELECT
315 }
316
0e78155f » Martin Berends
2010-03-09 [examples/mysqlclient.p6] added several API functions and some docs
317 say "close";
318 mysql_close($client);
319 print mysql_error($client);
320
321 say "library_end [currently not working]";
322 #mysql_library_end();
d27d5a89 » Martin Berends
2010-03-09 [examples/mysqlclient.p6] SELECT works in batch mode, not in per-row …
323
c379285b » Martin Berends
2010-03-03 [examples/mysqlclient] add CREATE TABLE and INSERT, but not yet SELECT
324 say "mysqlclient.p6 done";
Something went wrong with that request. Please try again.