1 /**********************************************************
2 * Version $Id: Get_Table.cpp 1925 2014-01-09 12:15:18Z oconrad $
3 *********************************************************/
4
5 ///////////////////////////////////////////////////////////
6 // //
7 // SAGA //
8 // //
9 // System for Automated Geoscientific Analyses //
10 // //
11 // Tool Library //
12 // Table_ODBC //
13 // //
14 //-------------------------------------------------------//
15 // //
16 // TLB_Interface.h //
17 // //
18 // Copyright (C) 2004 by //
19 // Olaf Conrad //
20 // //
21 //-------------------------------------------------------//
22 // //
23 // This file is part of 'SAGA - System for Automated //
24 // Geoscientific Analyses'. SAGA is free software; you //
25 // can redistribute it and/or modify it under the terms //
26 // of the GNU General Public License as published by the //
27 // Free Software Foundation; version 2 of the License. //
28 // //
29 // SAGA is distributed in the hope that it will be //
30 // useful, but WITHOUT ANY WARRANTY; without even the //
31 // implied warranty of MERCHANTABILITY or FITNESS FOR A //
32 // PARTICULAR PURPOSE. See the GNU General Public //
33 // License for more details. //
34 // //
35 // You should have received a copy of the GNU General //
36 // Public License along with this program; if not, //
37 // write to the Free Software Foundation, Inc., //
38 // 51 Franklin Street, 5th Floor, Boston, MA 02110-1301, //
39 // USA. //
40 // //
41 //-------------------------------------------------------//
42 // //
43 // e-mail: oconrad@saga-gis.org //
44 // //
45 // contact: Olaf Conrad //
46 // Institute of Geography //
47 // University of Goettingen //
48 // Goldschmidtstr. 5 //
49 // 37077 Goettingen //
50 // Germany //
51 // //
52 ///////////////////////////////////////////////////////////
53
54 //---------------------------------------------------------
55
56 ///////////////////////////////////////////////////////////
57 // //
58 // This code is something I did for SAGA GIS to //
59 // import data from MySQL Database. //
60 // //
61 // INPUT : Connection information and SQL statement //
62 // OUTPUT: Result data as Table //
63 // //
64 // RELEASE : v0.1b (Lot more to do) //
65 // //
66 // The SAGA structure code is grapped from //
67 // Olaf Conrad's ODBC example and database //
68 // communication part is replaced with MySQL //
69 // native library (libmysql.dll for win32) //
70 // //
71 // The code is compiled under //
72 // Visual C++ 2008 Express Edition //
73 //-------------------------------------------------------//
74 // //
75 // e-mail: sagamysql@ferhatbingol.com //
76 // //
77 // contact: Ferhat Bing�l //
78 // //
79 ///////////////////////////////////////////////////////////
80
81
82 #include <config-win.h> // Needed for Win32, change it for your system
83 #include <mysql.h> // Needed for Win32, change it (if needed) for your system
84 #include "Get_Table.h"
85
86
87
88
89
90 ///////////////////////////////////////////////////////////
91 // //
92 // //
93 // //
94 ///////////////////////////////////////////////////////////
95
96 //---------------------------------------------------------
CGet_Table(void)97 CGet_Table::CGet_Table(void)
98 {
99 Set_Name (_TL("Import DB Table from MySQL"));
100
101 Set_Author (SG_T("(c) 2009 by Ferhat Bing�l"));
102
103 Set_Description(
104 _TL("Get table from MySQL server.\n")
105 );
106
107
108 Parameters.Add_String(
109 NULL , "SERVER" , _TL("Server"),
110 _TL(""),
111 _TL("localhost")
112 );
113
114 Parameters.Add_String(
115 NULL , "PORT" , _TL("Port"),
116 _TL(""),
117 _TL("3306")
118 );
119
120 Parameters.Add_String(
121 NULL , "USERNAME" , _TL("Username"),
122 _TL(""),
123 _TL("root")
124 );
125
126 Parameters.Add_String(
127 NULL , "PASSWORD" , _TL("Password"),
128 _TL(""),
129 _TL("")
130 );
131
132 Parameters.Add_String(
133 NULL , "DATABASE" , _TL("Database"),
134 _TL(""),
135 _TL("")
136 );
137
138 Parameters.Add_String(
139 NULL , "SQLStatement" , _TL("SQL Statement"),
140 _TL(""),
141 _TL("Select * FROM test")
142 );
143
144
145 Parameters.Add_Table(
146 NULL , "SQLData" , _TL("MySQL Data"),
147 _TL(""),
148 PARAMETER_OUTPUT
149 );
150
151 }
152
153
~CGet_Table(void)154 CGet_Table::~CGet_Table(void)
155 {
156
157 }
158
159
160 //---------------------------------------------------------
On_Execute(void)161 bool CGet_Table::On_Execute(void)
162 {
163 // Input variables
164 CSG_String MyServer,MyUsername,MyPassword,MyDatabase,MySQLStatement,s,Types;
165 CSG_Table *MyTable;
166 int MyPort;
167
168 // MySQL Variables
169 char SQL[2000];
170 MYSQL *conn; // MySQL connection handle
171 MYSQL_RES *res; // MySQL Results
172 MYSQL_ROW row; // MySQL Rows
173 MYSQL_FIELD *fields; // Storage for the field names
174 int NR; // Number of returned raws
175 int NF; // Number of returned fields
176
177 // Variables used in transformation
178 CSG_String Name;
179 int iType,i,j;
180 CSG_Parameters P;
181 TSG_Table_Field_Type Type;
182
183
184
185
186 // Get the intput variables
187 MyServer = Parameters("SERVER") ->asString();
188 MyPort = Parameters("PORT") ->asInt();
189 MyUsername = Parameters("USERNAME") ->asString();
190 MyPassword = Parameters("PASSWORD") ->asString();
191 MyDatabase = Parameters("DATABASE") ->asString();
192 MySQLStatement = Parameters("SQLStatement") ->asString();
193
194 MyTable = Parameters("SQLData") ->asTable();
195
196
197 // print out what is going on to the execution screen
198 sprintf(SQL,"Server connection will be made to %s@%s:%i/%s", MyUsername,MyServer,MyPort,MyDatabase);
199 Message_Add(_TL(SQL));
200 sprintf(SQL,"Entered SQL statement -> %s)", MySQLStatement);
201 Message_Add(_TL(SQL));
202
203 // Init MySQL
204 conn = mysql_init(NULL);
205
206 // Connect
207 if (!mysql_real_connect(conn, MyServer,MyUsername, MyPassword, MyDatabase, MyPort, NULL, 0))
208 {
209 sprintf(SQL,"Cannot connect to the database server -> %s)", mysql_error(conn));
210 Message_Add(_TL(SQL));
211 return(false);
212 }
213 else
214 {
215 sprintf(SQL,"Connected to %s@%s:%i/%s",MyUsername,MyServer,MyPort,MyDatabase);
216 Message_Add(_TL(SQL));
217 }
218
219 // Send the SQL statement and get the data
220 if (mysql_query(conn, MySQLStatement)) {
221 sprintf(SQL,"Cannot get the data you asked with %s -> (%s)",MySQLStatement, mysql_error(conn));
222 Message_Add(_TL(SQL));
223 return(false);
224 }
225
226 // Store the results
227 res = mysql_store_result(conn);
228
229 // Get the number of rows and inform
230 NR=(int)mysql_num_rows(res);
231 sprintf(SQL,"%i number of rows are avaliable", NR);
232 Message_Add(_TL(SQL));
233
234 // Get the number of fields and inform
235 NF=mysql_num_fields(res);
236 sprintf(SQL,"%i number of fields are avaliable", NF);
237 Message_Add(_TL(SQL));
238
239 // Get the field types
240 fields = mysql_fetch_fields(res);
241
242 // Define the table field types and insert the names
243 for(i=0; i<NF; i++)
244 {
245
246 Name = fields[i].name;
247 iType = fields[i].type;
248 switch( iType )
249 {
250 default:
251 case 0: Type = TABLE_FIELDTYPE_None; break; // Not supported
252 case 1: Type = TABLE_FIELDTYPE_Int; break;
253 case 2: Type = TABLE_FIELDTYPE_Short; break;
254 case 3: Type = TABLE_FIELDTYPE_Long; break;
255 case 4: Type = TABLE_FIELDTYPE_Float; break;
256 case 5: Type = TABLE_FIELDTYPE_Double; break;
257 case 6: Type = TABLE_FIELDTYPE_None; break;
258 case 7: Type = TABLE_FIELDTYPE_String; break;
259 case 8: Type = TABLE_FIELDTYPE_Long; break;
260 case 9: Type = TABLE_FIELDTYPE_Long; break;
261 case 10:Type = TABLE_FIELDTYPE_String; break;
262 case 11:Type = TABLE_FIELDTYPE_String; break;
263 case 12:Type = TABLE_FIELDTYPE_String; break;
264 case 13:Type = TABLE_FIELDTYPE_String; break;
265 case 14:Type = TABLE_FIELDTYPE_String; break;
266 case 15:Type = TABLE_FIELDTYPE_String; break;
267 case 16:Type = TABLE_FIELDTYPE_None; break; // Not supported
268 case 246:Type = TABLE_FIELDTYPE_None; break; // Not supported
269 case 247:Type = TABLE_FIELDTYPE_None; break; // Not supported
270 case 248:Type = TABLE_FIELDTYPE_None; break; // Not supported
271 case 249:Type = TABLE_FIELDTYPE_None; break; // Not supported
272 case 250:Type = TABLE_FIELDTYPE_None; break; // Not supported
273 case 251:Type = TABLE_FIELDTYPE_None; break; // Not supported
274 case 252:Type = TABLE_FIELDTYPE_None; break; // Not supported
275 case 253:Type = TABLE_FIELDTYPE_String; break;
276 case 254:Type = TABLE_FIELDTYPE_String; break;
277 case 255:Type = TABLE_FIELDTYPE_None; break; // Not supported
278 }
279
280 MyTable->Add_Field(Name, Type);
281 }
282 // Insert data to the ready table
283 j=0;
284 while ((row = mysql_fetch_row(res)) != NULL)
285 {
286 MyTable->Add_Record();
287 for(i=0;i<NF;i++)
288 {
289 iType = fields[i].type;
290 switch( iType )
291 {
292 default:
293 case 0: MyTable->Set_Value(j,i,0.0); break; // Not supported
294 case 1: MyTable->Set_Value(j,i,atoi(row[i])); break;
295 case 2: MyTable->Set_Value(j,i,atoi(row[i])); break;
296 case 3: MyTable->Set_Value(j,i,atoi(row[i])); break;
297 case 4: MyTable->Set_Value(j,i,atof(row[i])); break;
298 case 5: MyTable->Set_Value(j,i,atof(row[i])); break;
299 case 6: MyTable->Set_Value(j,i,0.0); break;
300 case 7: MyTable->Set_Value(j,i,row[i]); break;
301 case 8: MyTable->Set_Value(j,i,atoi(row[i])); break;
302 case 9: MyTable->Set_Value(j,i,atoi(row[i])); break;
303 case 10: MyTable->Set_Value(j,i,row[i]); break;
304 case 11: MyTable->Set_Value(j,i,row[i]); break;
305 case 12: MyTable->Set_Value(j,i,row[i]); break;
306 case 13: MyTable->Set_Value(j,i,row[i]); break;
307 case 14: MyTable->Set_Value(j,i,row[i]); break;
308 case 15: MyTable->Set_Value(j,i,row[i]); break;
309 case 16: MyTable->Set_Value(j,i,0.0); break; // Not supported
310 case 246: MyTable->Set_Value(j,i,0.0); break; // Not supported
311 case 247: MyTable->Set_Value(j,i,0.0); break; // Not supported
312 case 248: MyTable->Set_Value(j,i,0.0); break; // Not supported
313 case 249: MyTable->Set_Value(j,i,0.0); break; // Not supported
314 case 250: MyTable->Set_Value(j,i,0.0); break; // Not supported
315 case 251: MyTable->Set_Value(j,i,0.0); break; // Not supported
316 case 252: MyTable->Set_Value(j,i,0.0); break; // Not supported
317 case 253: MyTable->Set_Value(j,i,row[i]); break;
318 case 254: MyTable->Set_Value(j,i,row[i]); break;
319 case 255: MyTable->Set_Value(j,i,0.0); break; // Not supported
320 }
321 }
322 j++;
323
324 }
325 mysql_close(conn);
326
327
328 return(true);
329 }