1 /*
2 * $Id: mysql.c,v 1.10 2000/07/22 06:13:16 danny Exp $
3 *
4 * This file is part of Oleo, the GNU spreadsheet.
5 *
6 * Copyright � 1999-2000 by the Free Software Foundation, Inc.
7 * Written by Danny Backx <danny@gnu.org>.
8 *
9 * This program is free software; you can redistribute it and/or modify
10 * it under the terms of the GNU General Public License as published by
11 * the Free Software Foundation; either version 2 of the License, or
12 * (at your option) any later version.
13 *
14 * This program is distributed in the hope that it will be useful,
15 * but WITHOUT ANY WARRANTY; without even the implied warranty of
16 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
17 * GNU General Public License for more details.
18 *
19 * You should have received a copy of the GNU General Public License
20 * along with this program; if not, write to the Free Software
21 * Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
22 */
23
24 static char rcsid[] = "$Id: mysql.c,v 1.10 2000/07/22 06:13:16 danny Exp $";
25
26 #ifdef HAVE_CONFIG_H
27 #include "config.h"
28 #endif
29
30 #ifdef WITH_DMALLOC
31 #include <dmalloc.h>
32 #endif
33
34 #include "global.h"
35 #include "oleosql.h"
36 #include "cmd.h"
37 #include "io-term.h"
38 #include "mysql.h"
39
40 #ifdef HAVE_LIBMYSQLCLIENT
41
42 #include <stdio.h>
43 #include <stdlib.h>
44 #include <mysql/mysql.h>
45
46 /*
47 * Define some MySQL access functions for Oleo
48 */
49
50 #include "funcdef.h"
51 #include "sysdef.h"
52
53 #include "global.h"
54 #include "cell.h"
55 #include "eval.h"
56 #include "errors.h"
57
58 /*
59 * Declarations necessary for defining an Oleo function
60 */
61 struct value
62 {
63 int type;
64 union vals x;
65 };
66
67 #define Float x.c_d
68 #define String x.c_s
69 #define Int x.c_l
70 #define Value x.c_i
71 #define Rng x.c_r
72
73 /*
74 * Forward declarations
75 */
76 static void do_mysql_query(struct value *);
77 static void do_mysql_read(struct value *);
78 static void do_mysql_write(struct value *);
79
80 /*
81 * Define Oleo functions
82 */
83 struct function mysql_functions[] = {
84 { C_FN1, X_A1, "S", do_mysql_query, "mysql_query" },
85 { C_FN1, X_A1, "S", do_mysql_read, "mysql_read" },
86 { C_FN1, X_A1, "S", do_mysql_write, "mysql_write" },
87 { 0, 0, "", 0, 0 }
88 };
89
init_mysql_function_count(void)90 int init_mysql_function_count(void)
91 {
92 return sizeof(mysql_functions) / sizeof(struct function) - 1;
93 }
94
95 /*
96 * This function should just retrieve a single value from the dbms.
97 */
98 static void
do_mysql_query(struct value * p)99 do_mysql_query(struct value *p)
100 {
101 char *sql = p[0].String;
102 MYSQL db;
103 MYSQL_RES *res;
104 MYSQL_ROW row;
105 MYSQL_FIELD *field;
106 int r, i, j, nrows;
107 char *result;
108 double d;
109
110 AllocateDatabaseGlobal();
111
112 if (Global->DatabaseGlobal == NULL) {
113 io_error_msg("Need to initialize database");
114 return;
115 }
116 if (Global->DatabaseGlobal->name == 0 || Global->DatabaseGlobal->user == 0) {
117 io_error_msg("Database Access requires db name and user name");
118 return;
119 }
120
121 if (mysql_connect(&db, Global->DatabaseGlobal->host, Global->DatabaseGlobal->user, "")
122 == NULL) {
123 io_error_msg("MySQL error '%s'\n", mysql_error(&db));
124 return; /* FIX ME */
125 }
126
127 r = mysql_select_db(&db, Global->DatabaseGlobal->name);
128
129 if (r != 0) {
130 io_error_msg("MySQL error '%s'\n", mysql_error(&db));
131 return; /* FIX ME */
132 }
133
134 r = mysql_query(&db, sql);
135
136 if (r != 0) {
137 io_error_msg("MySQL error '%s'\n", mysql_error(&db));
138 return; /* FIX ME */
139 }
140
141 res = mysql_store_result(&db);
142
143 if (mysql_num_fields(res) != 1) {
144 /* There's more than one, or less than one, result. */
145 io_error_msg("MySQLQuery: number of results (%d) should be one\n", nrows);
146 return; /* FIX ME */
147 }
148
149 field = mysql_fetch_field(res);
150
151 nrows = mysql_num_rows(res);
152 if (nrows != 1) {
153 /* There's more than one, or less than one, result. */
154 io_error_msg("MySQLQuery: number of results (%d) should be one\n", nrows);
155 return; /* FIX ME */
156 }
157
158 row = mysql_fetch_row(res);
159
160 switch (field->type) {
161 case FIELD_TYPE_CHAR:
162 case FIELD_TYPE_NULL:
163 case FIELD_TYPE_TIMESTAMP:
164 case FIELD_TYPE_DATE:
165 case FIELD_TYPE_TIME:
166 case FIELD_TYPE_DATETIME:
167 case FIELD_TYPE_VAR_STRING:
168 case FIELD_TYPE_STRING:
169 p->type = TYP_STR;
170 p->String = strdup(row[0]);
171 break;
172 case FIELD_TYPE_DECIMAL:
173 case FIELD_TYPE_FLOAT:
174 case FIELD_TYPE_DOUBLE:
175 case FIELD_TYPE_LONGLONG:
176 case FIELD_TYPE_INT24:
177 default:
178 p->type = TYP_FLT;
179 d = atof(row[0]);
180 p->Float = d;
181 break;
182 case FIELD_TYPE_SHORT:
183 case FIELD_TYPE_LONG:
184 p->type = TYP_INT;
185 sscanf(row[0], "%d", &i);
186 p->Int = i;
187 break;
188 case FIELD_TYPE_TINY_BLOB:
189 case FIELD_TYPE_MEDIUM_BLOB:
190 case FIELD_TYPE_LONG_BLOB:
191 case FIELD_TYPE_BLOB:
192 break;
193 }
194
195 mysql_free_result(res);
196
197 mysql_close(&db);
198
199 return;
200 }
201
202 static void
do_mysql_read(struct value * p)203 do_mysql_read(struct value *p)
204 {
205 /*
206 * This doesn't do a thing.
207 *
208 * A database query is only triggered on request from the user.
209 */
210 }
211
212 static void
do_mysql_write(struct value * p)213 do_mysql_write(struct value *p)
214 {
215 /*
216 * This doesn't do a thing.
217 *
218 * A database query is only triggered on request from the user.
219 */
220 }
221
222 /*
223 * This function reads a block of data from an SQL statement
224 * into the spreadsheet.
225 */
226 void
MySQLRead(void)227 MySQLRead(void)
228 {
229 MYSQL db;
230 MYSQL_RES *res;
231 MYSQL_ROW row;
232 MYSQL_FIELD *field;
233 int r, i, j, nrows;
234 enum enum_field_types *types_list;
235
236 char *sql = "select * from koers";
237 int in_row = curow, in_col = cucol;
238
239 if (mysql_connect(&db, "localhost", "danny", "") == NULL) {
240 fprintf(stderr, "MySQL error '%s'\n", mysql_error(&db));
241 return;
242 }
243
244 r = mysql_select_db(&db, "test");
245
246 if (r != 0) {
247 fprintf(stderr, "MySQL error '%s'\n", mysql_error(&db));
248 return;
249 }
250
251 r = mysql_query(&db, sql);
252
253 if (r != 0) {
254 fprintf(stderr, "MySQL error '%s'\n", mysql_error(&db));
255 return;
256 }
257
258 res = mysql_store_result(&db);
259
260 types_list = malloc(mysql_num_fields(res));
261
262 for (j=0; j<mysql_num_fields(res); j++) {
263 field = mysql_fetch_field(res);
264
265 (void) quote_new_value(in_row, j+in_col, field->name);
266 types_list[j] = field->type;
267 }
268
269 nrows = mysql_num_rows(res);
270 for (i=0; i<mysql_num_rows(res); i++) {
271 row = mysql_fetch_row(res);
272 for (j=0; j<mysql_num_fields(res); j++) {
273 switch (types_list[j]) {
274 case FIELD_TYPE_CHAR:
275 case FIELD_TYPE_NULL:
276 case FIELD_TYPE_TIMESTAMP:
277 case FIELD_TYPE_DATE:
278 case FIELD_TYPE_TIME:
279 case FIELD_TYPE_DATETIME:
280 case FIELD_TYPE_VAR_STRING:
281 case FIELD_TYPE_STRING:
282 #ifdef VERBOSE
283 fprintf(stderr, "Cell[%d,%d] = '%s'\n", i+1+in_row, j+in_col, row[j]);
284 #endif
285 (void)quote_new_value(i+1+in_row, j+in_col, row[j]);
286 break;
287 case FIELD_TYPE_DECIMAL:
288 case FIELD_TYPE_SHORT:
289 case FIELD_TYPE_LONG:
290 case FIELD_TYPE_FLOAT:
291 case FIELD_TYPE_DOUBLE:
292 case FIELD_TYPE_LONGLONG:
293 case FIELD_TYPE_INT24:
294 default:
295 #ifdef VERBOSE
296 fprintf(stderr, "Cell[%d,%d] = %s\n", i+1+in_row, j+in_col, row[j]);
297 #endif
298 (void)new_value(i+1+in_row, j+in_col, row[j]);
299 break;
300 case FIELD_TYPE_TINY_BLOB:
301 case FIELD_TYPE_MEDIUM_BLOB:
302 case FIELD_TYPE_LONG_BLOB:
303 case FIELD_TYPE_BLOB:
304 break;
305 }
306 }
307 }
308
309 mysql_free_result(res);
310
311 mysql_close(&db);
312 free(types_list);
313
314 Global->modified = 1;
315 recalculate(1);
316
317 return;
318 }
319 #endif /* HAVE_LIBMYSQLCLIENT */
320
321 /*
322 * The functions below need to exist even if we don't have MySQL
323 */
AllocateDatabaseGlobal(void)324 void AllocateDatabaseGlobal(void)
325 {
326 if (Global->DatabaseGlobal == NULL) {
327 Global->DatabaseGlobal = (struct DatabaseGlobalType *)
328 malloc(sizeof(struct DatabaseGlobalType));
329 memset(Global->DatabaseGlobal, 0, sizeof(struct DatabaseGlobalType));
330 }
331 }
332
DatabaseSetName(const char * name)333 void DatabaseSetName(const char *name)
334 {
335 if (Global->DatabaseGlobal->name)
336 free(Global->DatabaseGlobal->name);
337 Global->DatabaseGlobal->name = strdup(name);
338 }
339
DatabaseSetHost(const char * host)340 void DatabaseSetHost(const char *host)
341 {
342 if (Global->DatabaseGlobal->host)
343 free(Global->DatabaseGlobal->host);
344 Global->DatabaseGlobal->host = strdup(host);
345 }
346
DatabaseSetUser(const char * user)347 void DatabaseSetUser(const char *user)
348 {
349 if (Global->DatabaseGlobal->user)
350 free(Global->DatabaseGlobal->user);
351 Global->DatabaseGlobal->user = strdup(user);
352 }
353
354 int
DatabaseInitialised(void)355 DatabaseInitialised(void)
356 {
357 if (Global->DatabaseGlobal)
358 return 1;
359 return 0;
360 }
361
362 char *
DatabaseGetName(void)363 DatabaseGetName(void)
364 {
365 if (Global->DatabaseGlobal)
366 return Global->DatabaseGlobal->name;
367 return NULL;
368 }
369
370 char *
DatabaseGetHost(void)371 DatabaseGetHost(void)
372 {
373 if (Global->DatabaseGlobal)
374 return Global->DatabaseGlobal->host;
375 return NULL;
376 }
377
378 char *
DatabaseGetUser(void)379 DatabaseGetUser(void)
380 {
381 if (Global->DatabaseGlobal)
382 return Global->DatabaseGlobal->user;
383 return NULL;
384 }
385
386 #ifndef HAVE_LIBMYSQLCLIENT
387
388 #include "eval.h"
389
390 /*
391 * Define Oleo functions
392 */
393 struct function mysql_functions[] = {
394 { 0, 0, "", 0, 0 }
395 };
396
init_mysql_function_count(void)397 int init_mysql_function_count(void)
398 {
399 return sizeof(mysql_functions) / sizeof(struct function) - 1;
400 }
401 #endif /* HAVE_LIBMYSQLCLIENT */
402