Project

General

Profile

Bug #3786 » MySQL.C

Drus Kor, 02/17/2015 05:53 PM

 
1
/*
2
 * Copyright (C) 2008 Emweb bvba, Kessel-Lo, Belgium.
3
 *
4
 * See the LICENSE file for terms of use.
5
 *
6
 * Contributed by: Paul Harrison
7
 */
8
#include "Wt/Dbo/backend/MySQL"
9
#include "Wt/Dbo/Exception"
10

    
11
#include <boost/lexical_cast.hpp>
12
#include <iostream>
13
#include <vector>
14
#include <sstream>
15

    
16
#include <boost/date_time/posix_time/posix_time.hpp>
17

    
18
#ifdef WT_WIN32
19
#define snprintf _snprintf
20
#include <winsock2.h>
21
#endif
22
#include <mysql.h>
23

    
24
#define BYTEAOID 17
25

    
26
#define DEBUG(x)
27
//#define DEBUG(x) x
28

    
29
namespace Wt {
30
  namespace Dbo {
31
    namespace backend {
32

    
33
class MySQLException : public Exception
34
{
35
  public:
36
  MySQLException(const std::string& msg)
37
  : Exception(msg)
38
  { }
39
};
40

    
41
class MySQL_impl{
42
public:
43
  MYSQL *mysql;
44

    
45
  MySQL_impl():
46
    mysql(NULL)
47
  {}
48

    
49
  MySQL_impl(MYSQL *newmysql):
50
    mysql(newmysql)
51
  {}
52

    
53
  MYSQL *ptr()
54
  {
55
    return mysql;
56
  }
57

    
58
};
59

    
60
namespace {
61
  struct LibraryInitializer {
62
    LibraryInitializer() {
63
#if !defined(MYSQL_NO_LIBRARY_INIT)
64
      mysql_library_init(0, NULL, NULL);
65
#endif
66
    }
67
  } libraryInitializer;
68
}
69

    
70
/*!
71
 * \brief MySQL prepared statement.
72
 * @todo should the getResult requests all be type checked...
73
 */
74
class MySQLStatement : public SqlStatement
75
{
76
  public:
77
    MySQLStatement(MySQL& conn, const std::string& sql)
78
    : conn_(conn),
79
      sql_(sql)
80
    {
81
      lastId_ = -1;
82
      row_ = affectedRows_ = 0;
83
      result_ = 0;
84
      out_pars_ = 0;
85
      errors_ = 0;
86
      lastOutCount_ = 0;
87

    
88
      stmt_ =  mysql_stmt_init(conn_.connection()->mysql);
89
      mysql_stmt_attr_set(stmt_, STMT_ATTR_UPDATE_MAX_LENGTH, &mysqltrue_);
90
      if(mysql_stmt_prepare(stmt_, sql_.c_str(), sql_.length()) != 0) {
91
        throw MySQLException("error creating prepared statement: '"
92
			      + sql + "': " + mysql_stmt_error(stmt_));
93
      }
94
      if(mysql_stmt_param_count(stmt_)> 0)
95
      {
96
          in_pars_ = (MYSQL_BIND *)malloc(
97
                sizeof(struct st_mysql_bind) * mysql_stmt_param_count(stmt_));
98
          memset(in_pars_, 0,
99
                 sizeof(struct st_mysql_bind)*mysql_stmt_param_count(stmt_));
100
      }
101
      else{
102
        in_pars_ = 0;
103
      }
104
//      snprintf(name_, 64, "SQL%p%08X", this, rand());
105

    
106

    
107
      DEBUG(std::cerr <<  " new SQLStatement for: " << sql_ << std::endl);
108

    
109
      state_ = Done;
110
    }
111

    
112
    virtual ~MySQLStatement()
113
    {
114
      DEBUG(std::cerr << "closing prepared stmt " << sql_ << std::endl);
115
      for(unsigned int i = 0;   i < mysql_stmt_param_count(stmt_) ; ++i)
116
          freeColumn(i);
117
      if (in_pars_) free(in_pars_);
118

    
119
      if(out_pars_) free_outpars();
120

    
121
      if (errors_) delete[] errors_;
122

    
123
      if(result_) {
124
        mysql_free_result(result_);
125
      }
126

    
127
      mysql_stmt_close(stmt_);
128
      stmt_ = 0;
129
    }
130

    
131
    virtual void reset()
132
    {
133
      state_ = Done;
134
      has_truncation_ = false;
135
    }
136

    
137
    virtual void bind(int column, const std::string& value)
138
    {
139
      if (in_pars_ == 0)
140
        throw MySQLException(std::string("Try to bind too much?"));
141

    
142
      DEBUG(std::cerr << this << " bind " << column << " "
143
            << value << std::endl);
144

    
145
      unsigned long * len = (unsigned long *)malloc(sizeof(unsigned long));
146

    
147
      char * data;
148
      //memset(&in_pars_[column], 0, sizeofin_pars_[column]);// Check
149
      in_pars_[column].buffer_type = MYSQL_TYPE_STRING;
150

    
151
      unsigned long bufLen = value.length() + 1;
152
      *len = value.length();
153
      data = (char *)malloc(bufLen);
154
      memcpy(data, value.c_str(), value.length());
155
      freeColumn(column);
156
      in_pars_[column].buffer = data;
157
      in_pars_[column].buffer_length = bufLen;
158
      in_pars_[column].length = len;
159
      in_pars_[column].is_null = 0;
160
    }
161

    
162
    virtual void bind(int column, short value)
163
    {
164
      if (in_pars_ == 0)
165
        throw MySQLException(std::string("Try to bind too much?"));
166

    
167
      DEBUG(std::cerr << this << " bind " << column << " "
168
            << value << std::endl);
169
      short * data = (short *)malloc(sizeof(short));
170
      *data = value;
171
      freeColumn(column);
172
      in_pars_[column].buffer_type = MYSQL_TYPE_SHORT;
173
      in_pars_[column].buffer = data;
174
      in_pars_[column].length = 0;
175
      in_pars_[column].is_null = 0;
176

    
177
    }
178

    
179
    virtual void bind(int column, int value)
180
    {
181
      if (in_pars_ == 0)
182
        throw MySQLException(std::string("Try to bind too much?"));
183

    
184
      DEBUG(std::cerr << this << " bind " << column << " "
185
            << value << std::endl);
186
      int * data = (int *)malloc(sizeof(int));
187
      *data = value;
188
      freeColumn(column);
189
      in_pars_[column].buffer_type = MYSQL_TYPE_LONG;
190
      in_pars_[column].buffer = data;
191
      in_pars_[column].length = 0;
192
      in_pars_[column].is_null = 0;
193
    }
194

    
195
    virtual void bind(int column, long long value)
196
    {
197
      if (in_pars_ == 0)
198
        throw MySQLException(std::string("Try to bind too much?"));
199

    
200
      DEBUG(std::cerr << this << " bind " << column << " "
201
            << value << std::endl);
202
      long long * data = (long long *)malloc(sizeof(long long));
203
      *data = value;
204
      freeColumn(column);
205
      in_pars_[column].buffer_type = MYSQL_TYPE_LONGLONG;
206
      in_pars_[column].buffer = data;
207
      in_pars_[column].length = 0;
208
      in_pars_[column].is_null = 0;
209
    }
210

    
211
    virtual void bind(int column, float value)
212
    {
213
      DEBUG(std::cerr << this << " bind " << column << " "
214
            << value << std::endl);
215
      float * data = (float *) malloc(sizeof(float));
216
      *data = value;
217
      freeColumn(column);
218
      in_pars_[column].buffer_type = MYSQL_TYPE_FLOAT;
219
      in_pars_[column].buffer = data;
220
      in_pars_[column].length = 0;
221
      in_pars_[column].is_null = 0;
222
   }
223

    
224
    virtual void bind(int column, double value)
225
    {
226
      if (in_pars_ == 0)
227
        throw MySQLException(std::string("Try to bind too much?"));
228

    
229
      DEBUG(std::cerr << this << " bind " << column << " "
230
            << value << std::endl);
231
      double * data = (double *)malloc(sizeof(double));
232
      *data = value;
233
      freeColumn(column);
234
      in_pars_[column].buffer_type = MYSQL_TYPE_DOUBLE;
235
      in_pars_[column].buffer = data;
236
      in_pars_[column].length = 0;
237
      in_pars_[column].is_null = 0;
238
    }
239

    
240
    virtual void bind(int column, const boost::posix_time::ptime& value,
241
                      SqlDateTimeType type)
242
    {
243
      if (in_pars_ == 0)
244
        throw MySQLException(std::string("Try to bind too much?"));
245

    
246
      DEBUG(std::cerr << this << " bind " << column << " "
247
            << boost::posix_time::to_simple_string(value) << std::endl);
248

    
249
      MYSQL_TIME*  ts = (MYSQL_TIME*)malloc(sizeof(struct st_mysql_time));
250

    
251
      boost::posix_time::ptime::time_duration_type  tim = value.time_of_day();
252
      boost::posix_time::ptime::date_type dd = value.date();
253
      ts->year = dd.year();
254
      ts->month = dd.month();
255
      ts->day = dd.day();
256
      ts->neg = 0;
257

    
258
      if (type == SqlDate){
259
        in_pars_[column].buffer_type = MYSQL_TYPE_DATE;
260
           ts->hour = 0;
261
           ts->minute = 0;
262
           ts->second = 0;
263
           ts->second_part = 0;
264

    
265
      }
266
      else {
267
        in_pars_[column].buffer_type = MYSQL_TYPE_DATETIME;
268
        ts->hour = tim.hours();
269
        ts->minute = tim.minutes();
270
        ts->second = tim.seconds();
271
        if (conn_.getFractionalSecondsPart() > 0)
272
          ts->second_part = (unsigned long)tim.fractional_seconds();
273
        else
274
          ts->second_part = 0;
275
      }
276
      freeColumn(column);
277
      in_pars_[column].buffer = ts;
278
      in_pars_[column].length = 0;
279
      in_pars_[column].is_null = 0;
280

    
281
     }
282

    
283
    virtual void bind(int column, const boost::posix_time::time_duration& value)
284
    {
285
      if (in_pars_ == 0)
286
        throw MySQLException(std::string("Try to bind too much?"));
287

    
288
      DEBUG(std::cerr << this << " bind " << column << " "
289
            << boost::posix_time::to_simple_string(value) << std::endl);
290

    
291
      MYSQL_TIME* ts  = (MYSQL_TIME *)malloc(sizeof(struct st_mysql_time));
292

    
293
      //IMPL note that there is not really a "duration" type in mysql...
294
      //mapping to a datetime
295
      in_pars_[column].buffer_type = MYSQL_TYPE_TIME;//MYSQL_TYPE_DATETIME;
296

    
297
      ts->year = 0;
298
      ts->month = 0;
299
      ts->day = 0;
300
      ts->neg = 0;
301
      ts->hour = value.hours();
302
      ts->minute = value.minutes();
303
      ts->second = value.seconds();
304
      if(conn_.getFractionalSecondsPart() > 0)
305
        ts->second_part = (unsigned long)value.fractional_seconds();
306
      else
307
        ts->second_part = 0;
308
      freeColumn(column);
309
      in_pars_[column].buffer = ts;
310
      in_pars_[column].length = 0;
311
      in_pars_[column].is_null = 0;
312
    }
313

    
314
    virtual void bind(int column, const std::vector<unsigned char>& value)
315
    {
316
      if (in_pars_ == 0)
317
        throw MySQLException(std::string("Try to bind too much?"));
318

    
319
      DEBUG(std::cerr << this << " bind " << column << " (blob, size=" <<
320
            value.size() << ")" << std::endl);
321

    
322
      unsigned long * len = (unsigned long *)malloc(sizeof(unsigned long));
323

    
324
      char * data;
325
      in_pars_[column].buffer_type = MYSQL_TYPE_BLOB;
326

    
327
      *len = value.size();
328
      data = (char *)malloc(*len);
329
      if (value.size() > 0) // must not dereference begin() for empty vectors
330
	memcpy(data, &(*value.begin()), *len);
331

    
332
      freeColumn(column);
333
      in_pars_[column].buffer = data;
334
      in_pars_[column].buffer_length = *len;
335
      in_pars_[column].length = len;
336
      in_pars_[column].is_null = 0;
337

    
338
      // FIXME if first null was bound, check here and invalidate the prepared
339
      // statement if necessary because the type changes
340
    }
341

    
342
    virtual void bindNull(int column)
343
    {
344
      if (in_pars_ == 0)
345
        throw MySQLException(std::string("Try to bind too much?"));
346

    
347
      DEBUG(std::cerr << this << " bind " << column << " null" << std::endl);
348

    
349
      freeColumn(column);
350
      in_pars_[column].buffer_type = MYSQL_TYPE_NULL;
351
      in_pars_[column].is_null = const_cast<my_bool*>(&mysqltrue_);
352
      unsigned long * len = (unsigned long *)malloc(sizeof(unsigned long));
353
      in_pars_[column].buffer = 0;
354
      in_pars_[column].buffer_length = 0;
355
      in_pars_[column].length = len;
356
    }
357

    
358
    virtual void execute()
359
    {
360
      if (conn_.showQueries())
361
        std::cerr << sql_ << std::endl;
362

    
363

    
364
      if(mysql_stmt_bind_param(stmt_, &in_pars_[0]) == 0){
365
        if (mysql_stmt_execute(stmt_) == 0) {
366
          if(mysql_stmt_field_count(stmt_) == 0) { // assume not select
367
            affectedRows_ = mysql_stmt_affected_rows(stmt_);
368
           state_ = NoFirstRow;
369
            if (affectedRows_ == 1 ) {
370
              lastId_ = mysql_stmt_insert_id(stmt_);
371
            }
372
          }
373
          else {
374
            row_ = 0;
375

    
376
            if(result_){
377
              mysql_free_result(result_);
378
            }
379

    
380
            result_ = mysql_stmt_result_metadata(stmt_);
381
            mysql_stmt_store_result(stmt_); //possibly not efficient,
382
            //but suffer from "commands out of sync" errors with the usage
383
            //patterns that Wt::Dbo uses if not called.
384
            if( result_ ) {
385
              if(mysql_num_fields(result_) > 0){
386
                state_ = NextRow;
387
              }
388
              else {
389
                state_ = NoFirstRow; // not sure how/if this can happen
390
              }
391
            }
392
            else {
393
              throw MySQLException(std::string("error getting result metadata ")
394
                                   + mysql_stmt_error(stmt_));
395
            }
396
          }
397
        }
398
        else {
399
          throw MySQLException(
400
                std::string("error executing prepared statement ")+
401
                mysql_stmt_error(stmt_));
402
        }
403
      }
404
      else {
405
        throw MySQLException(std::string("error binding parameters")+
406
                             mysql_stmt_error(stmt_));
407
      }
408
    }
409

    
410
    virtual long long insertedId()
411
    {
412
      return lastId_;
413
    }
414

    
415
    virtual int affectedRowCount()
416
    {
417
      return (int)affectedRows_;
418
    }
419

    
420
    virtual bool nextRow()
421
    {
422
      int status = 0;
423
      switch (state_) {
424
        case NoFirstRow:
425
          state_ = Done;
426
          return false;
427
        case NextRow:
428
          //bind the output..
429
          bind_output();
430
          if ((status = mysql_stmt_fetch(stmt_)) == 0 ||
431
	      status == MYSQL_DATA_TRUNCATED) {
432
	    if (status == MYSQL_DATA_TRUNCATED)
433
	      has_truncation_ = true;
434
	    else
435
	      has_truncation_ = false;
436
            row_++;
437
            return true;
438
          } else {
439
            if(status == MYSQL_NO_DATA ) {
440
              lastOutCount_ = mysql_num_fields(result_);
441
              mysql_free_result(result_);
442
              mysql_stmt_free_result(stmt_);
443
              result_ = 0;
444
              state_ = Done;
445
              return false;
446
            } else {
447
              throw MySQLException(std::string("MySQL: row fetch failure: ") +
448
                                   mysql_stmt_error(stmt_));
449
            }
450
          }
451
        break;
452
      case Done:
453
        throw MySQLException("MySQL: nextRow(): statement already "
454
                             "finished");
455
      }
456

    
457
      return false;
458
    }
459

    
460
    virtual bool getResult(int column, std::string *value, int size)
461
    {
462
      if (*(out_pars_[column].is_null) == 1)
463
        return false;
464

    
465
      if(*(out_pars_[column].length) > 0){
466
        char * str;
467
	if (*(out_pars_[column].length) + 1 > out_pars_[column].buffer_length) {
468
	  free(out_pars_[column].buffer);
469
	  out_pars_[column].buffer = malloc(*(out_pars_[column].length)+1);
470
	  out_pars_[column].buffer_length = *(out_pars_[column].length)+1;
471
	}
472
        mysql_stmt_fetch_column(stmt_,  &out_pars_[column], column, 0);
473

    
474
        if (has_truncation_ && *out_pars_[column].error)
475
	  throw MySQLException("MySQL: getResult(): truncated result for column "
476
	    + boost::lexical_cast<int>(column));
477

    
478

    
479
	str = static_cast<char*>( out_pars_[column].buffer);
480
        *value = std::string(str, *out_pars_[column].length);
481

    
482
        DEBUG(std::cerr << this
483
              << " result string " << column << " " << *value << std::endl);
484

    
485
        return true;
486
      }
487
      else
488
        return false;
489
    }
490

    
491
    virtual bool getResult(int column, short *value)
492
    {
493
      if (has_truncation_ && *out_pars_[column].error)
494
	throw MySQLException("MySQL: getResult(): truncated result for column "
495
	  + boost::lexical_cast<int>(column));
496

    
497
      if (*(out_pars_[column].is_null) == 1)
498
         return false;
499

    
500
      *value = *static_cast<short*>(out_pars_[column].buffer);
501

    
502
      return true;
503
    }
504

    
505
    virtual bool getResult(int column, int *value)
506
    {
507

    
508
      if (*(out_pars_[column].is_null) == 1)
509
        return false;
510
      switch (out_pars_[column].buffer_type ){
511
      case MYSQL_TYPE_TINY:
512
        if (has_truncation_ && *out_pars_[column].error)
513
	  throw MySQLException("MySQL: getResult(): truncated result for column "
514
	    + boost::lexical_cast<int>(column));
515
        *value = (int)*static_cast<bool*>(out_pars_[column].buffer);
516
        break;
517
      case MYSQL_TYPE_LONG:
518
        if (has_truncation_ && *out_pars_[column].error)
519
	  throw MySQLException("MySQL: getResult(): truncated result for column "
520
	    + boost::lexical_cast<int>(column));
521
        *value = *static_cast<int*>(out_pars_[column].buffer);
522
        break;
523

    
524
      case MYSQL_TYPE_LONGLONG:
525
        if (has_truncation_ && *out_pars_[column].error)
526
	  throw MySQLException("MySQL: getResult(): truncated result for column "
527
	    + boost::lexical_cast<int>(column));
528
        *value = (int)*static_cast<long long*>(out_pars_[column].buffer);
529
        break;
530

    
531
      case MYSQL_TYPE_NEWDECIMAL:
532
	{
533
	  std::string strValue;
534
	  if (!getResult(column, &strValue, 0))
535
	    return false;
536

    
537
	  try{
538
	    *value = boost::lexical_cast<int>(strValue);
539
	  } catch( boost::bad_lexical_cast const& ) {
540
	    try{
541
	      *value = (int)boost::lexical_cast<double>(strValue);
542
	    } catch( boost::bad_lexical_cast const& ) {
543
	      std::cout << "Error: MYSQL_TYPE_NEWDECIMAL " << strValue
544
		<< "could not be casted to int" << std::endl;
545
	      return false;
546
	    }
547
	  }
548
	}
549
        break;
550
      default:
551
	return false;
552
      }
553

    
554
      DEBUG(std::cerr << this
555
            << " result  int " << column << " " << *value << std::endl);
556

    
557
      return true;
558
    }
559

    
560
    virtual bool getResult(int column, long long *value)
561
    {
562
      if (has_truncation_ && *out_pars_[column].error)
563
	throw MySQLException("MySQL: getResult(): truncated result for column "
564
	  + boost::lexical_cast<int>(column));
565

    
566
      if (*(out_pars_[column].is_null) == 1)
567
        return false;
568
      switch (out_pars_[column].buffer_type ){
569
        case MYSQL_TYPE_LONG:
570

    
571
          *value = *static_cast<int*>(out_pars_[column].buffer);
572
          break;
573

    
574
        case MYSQL_TYPE_LONGLONG:
575

    
576
          *value = *static_cast<long long*>(out_pars_[column].buffer);
577
          break;
578

    
579
        default:
580

    
581
	  throw MySQLException("MySQL: getResult(long long): unknown type: "
582
			       + boost::lexical_cast<int>
583
			       (out_pars_[column].buffer_type ));
584
	  break;
585
      }
586

    
587
      DEBUG(std::cerr << this
588
            << " result long long " << column << " " << *value << std::endl);
589

    
590
      return true;
591
    }
592

    
593
    virtual bool getResult(int column, float *value)
594
    {
595
      if (has_truncation_ && *out_pars_[column].error)
596
	throw MySQLException("MySQL: getResult(): truncated result for column "
597
	  + boost::lexical_cast<int>(column));
598

    
599
      if (*(out_pars_[column].is_null) == 1)
600
         return false;
601

    
602
       *value = *static_cast<float*>(out_pars_[column].buffer);
603

    
604
      DEBUG(std::cerr << this
605
            << " result float " << column << " " << *value << std::endl);
606

    
607
      return true;
608
    }
609

    
610
    virtual bool getResult(int column, double *value)
611
    {
612

    
613
      if (*(out_pars_[column].is_null) == 1)
614
         return false;
615
      switch (out_pars_[column].buffer_type ){
616
      case MYSQL_TYPE_DOUBLE:
617
        if (has_truncation_ && *out_pars_[column].error)
618
	  throw MySQLException("MySQL: getResult(): truncated result for column "
619
	    + boost::lexical_cast<int>(column));
620
        *value = *static_cast<double*>(out_pars_[column].buffer);
621
        break;
622
      case MYSQL_TYPE_FLOAT:
623
        if (has_truncation_ && *out_pars_[column].error)
624
	  throw MySQLException("MySQL: getResult(): truncated result for column "
625
	    + boost::lexical_cast<int>(column));
626
        *value = *static_cast<float*>(out_pars_[column].buffer);
627
        break;
628
      case MYSQL_TYPE_NEWDECIMAL:
629
	{
630
	  std::string strValue;
631
	  if (!getResult(column, &strValue, 0))
632
	    return false;
633

    
634
	  try {
635
	    *value = boost::lexical_cast<double>(strValue);
636
	  } catch( boost::bad_lexical_cast const& ) {
637
	    std::cout << "Error: MYSQL_TYPE_NEWDECIMAL " << strValue
638
	      << "could not be casted to double" << std::endl;
639
	    return false;
640
	  }
641
	}
642
        break;
643
      default:
644
	return false;
645
      }
646

    
647
      DEBUG(std::cerr << this
648
            << " result double " << column << " " << *value << std::endl);
649

    
650
      return true;
651
    }
652

    
653
    virtual bool getResult(int column, boost::posix_time::ptime *value,
654
                           SqlDateTimeType type)
655
    {
656
      if (has_truncation_ && *out_pars_[column].error)
657
	throw MySQLException("MySQL: getResult(): truncated result for column "
658
	  + boost::lexical_cast<int>(column));
659

    
660
      if (*(out_pars_[column].is_null) == 1)
661
         return false;
662

    
663
      MYSQL_TIME* ts = static_cast<MYSQL_TIME*>(out_pars_[column].buffer);
664

    
665
      if (type == SqlDate){
666
        *value = boost::posix_time::ptime(
667
              boost::gregorian::date(ts->year, ts->month, ts->day),
668
                                          boost::posix_time::hours(0));
669
      }
670
      else
671
        *value = boost::posix_time::ptime(
672
            boost::gregorian::date(ts->year, ts->month, ts->day),
673
            boost::posix_time::time_duration(ts->hour, ts->minute, ts->second)
674
            + boost::posix_time::microseconds(ts->second_part));
675

    
676
      DEBUG(std::cerr << this
677
            << " result time " << column << " " << *value << std::endl);
678

    
679
      return true;
680
    }
681

    
682
    virtual bool getResult(int column, boost::posix_time::time_duration* value)
683
    {
684
      if (has_truncation_ && *out_pars_[column].error)
685
	throw MySQLException("MySQL: getResult(): truncated result for column "
686
	  + boost::lexical_cast<int>(column));
687

    
688
      if (*(out_pars_[column].is_null) == 1)
689
         return false;
690

    
691
       MYSQL_TIME* ts = static_cast<MYSQL_TIME*>(out_pars_[column].buffer);
692
       *value = boost::posix_time::time_duration(
693
             ts->hour, ts->minute, ts->second, ts->second_part);
694

    
695
       DEBUG(std::cerr << this
696
             << " result time " << column << " " << *value << std::endl);
697

    
698
       return true;
699
    }
700

    
701
    virtual bool getResult(int column, std::vector<unsigned char> *value,
702
                           int size)
703
    {
704
      if (*(out_pars_[column].is_null) == 1)
705
        return false;
706

    
707
      if(*(out_pars_[column].length) > 0){
708
	if (*(out_pars_[column].length) > out_pars_[column].buffer_length) {
709
	  free(out_pars_[column].buffer);
710
	  out_pars_[column].buffer = malloc(*(out_pars_[column].length));
711
	  out_pars_[column].buffer_length = *(out_pars_[column].length);
712
	}
713
        mysql_stmt_fetch_column(stmt_,  &out_pars_[column], column, 0);
714

    
715
      if (*out_pars_[column].error)
716
	throw MySQLException("MySQL: getResult(): truncated result for column "
717
	  + boost::lexical_cast<int>(column));
718

    
719

    
720
	std::size_t vlength = *(out_pars_[column].length);
721
        unsigned char *v =
722
            static_cast<unsigned char*>(out_pars_[column].buffer);
723

    
724
        value->resize(vlength);
725
        std::copy(v, v + vlength, value->begin());
726

    
727
        DEBUG(std::cerr << this
728
              << " result blob " << column << " (blob, size = "
729
              << vlength << ")"<< std::endl);
730

    
731
        return true;
732
      }
733
      else
734
        return false;
735
    }
736

    
737
    virtual std::string sql() const {
738
      return sql_;
739
    }
740

    
741
  private:
742

    
743
    MySQL& conn_;
744
    std::string sql_;
745
    char name_[64];
746
    bool has_truncation_;
747
    MYSQL_RES *result_;
748
    MYSQL_STMT* stmt_;
749
    MYSQL_BIND* in_pars_;
750
    MYSQL_BIND* out_pars_;
751
    my_bool* errors_;
752
    unsigned int lastOutCount_;
753
    // true value to use because mysql specifies that pointer to the boolean
754
    // is passed in many cases....
755
    static const my_bool mysqltrue_;
756
    enum { NoFirstRow, NextRow, Done } state_;
757
    long long lastId_, row_, affectedRows_;
758

    
759
    void bind_output() {
760
      if (!out_pars_) {
761
	out_pars_ =(MYSQL_BIND *)malloc(
762
	      mysql_num_fields(result_) * sizeof(struct st_mysql_bind));
763
	memset(out_pars_, 0,
764
		mysql_num_fields(result_) * sizeof(struct st_mysql_bind));
765
	errors_ = new my_bool[mysql_num_fields(result_)];
766
	for(unsigned int i = 0; i < mysql_num_fields(result_); ++i){
767
	  MYSQL_FIELD* field = mysql_fetch_field_direct(result_, i);
768
	  out_pars_[i].buffer_type = field->type;
769
	  out_pars_[i].error = &errors_[i];
770
	  switch(field->type){
771
	  case MYSQL_TYPE_TINY:
772
	    out_pars_[i].buffer = malloc(1);
773
	    out_pars_[i].buffer_length = 1;
774
	    break;
775

    
776
	  case MYSQL_TYPE_SHORT:
777
	    out_pars_[i].buffer = malloc(sizeof(short));
778
	    out_pars_[i].buffer_length = sizeof(short);
779
	    break;
780

    
781
	  case MYSQL_TYPE_LONG:
782
	    out_pars_[i].buffer = malloc(sizeof(int));
783
	    out_pars_[i].buffer_length = sizeof(int);
784
	    break;
785
	  case MYSQL_TYPE_FLOAT:
786
	    out_pars_[i].buffer = malloc(sizeof(float));
787
	    out_pars_[i].buffer_length = sizeof(float);
788
	    break;
789

    
790
	  case MYSQL_TYPE_LONGLONG:
791
	    out_pars_[i].buffer = malloc(sizeof(long long));
792
	    out_pars_[i].buffer_length = sizeof(long long);
793
	    break;
794
	  case MYSQL_TYPE_DOUBLE:
795
	    out_pars_[i].buffer = malloc(sizeof(double));
796
	    out_pars_[i].buffer_length = sizeof(double);
797
	    break;
798

    
799
	  case MYSQL_TYPE_TIME:
800
	  case MYSQL_TYPE_DATE:
801
	  case MYSQL_TYPE_DATETIME:
802
	  case MYSQL_TYPE_TIMESTAMP:
803
	    out_pars_[i].buffer = malloc(sizeof(struct st_mysql_time));
804
	    out_pars_[i].buffer_length = sizeof(struct st_mysql_time);
805
	    break;
806

    
807
	  case MYSQL_TYPE_NEWDECIMAL: // newdecimal is stored as string.
808
	  case MYSQL_TYPE_STRING:
809
	  case MYSQL_TYPE_VAR_STRING:
810
	  case MYSQL_TYPE_BLOB:
811
	    out_pars_[i].buffer = malloc(256);
812
	    out_pars_[i].buffer_length = 256; // Reserve 256 bytes, if the content is longer, it will be reallocated later
813
	    //http://dev.mysql.com/doc/refman/5.0/en/mysql-stmt-fetch.html
814
	    break;
815
	  default:
816
	    std::cerr << "MySQL Backend Programming Error: unknown type "
817
		      << field->type << std::endl;
818
	  }
819
	  out_pars_[i].buffer_type = field->type;
820
	  out_pars_[i].is_null = (my_bool *)malloc(sizeof(char));
821
	  out_pars_[i].length =
822
	      (unsigned long *) malloc(sizeof(unsigned long));
823
	  out_pars_[i].error = (my_bool *)malloc(sizeof(char));
824
	}
825
      }
826
      mysql_stmt_bind_result(stmt_, out_pars_);
827
    }
828

    
829
    void freeColumn(int column)
830
    {
831
      if(in_pars_[column].length != 0 ) {
832
        free(in_pars_[column].length);
833
        in_pars_[column].length = 0;
834
      }
835

    
836
      if(in_pars_[column].buffer != 0 ) {
837
        free(in_pars_[column].buffer);
838
        in_pars_[column].buffer = 0;
839
      }
840
    }
841

    
842
    void free_outpars(){
843

    
844
      unsigned int count;
845
      if(!result_){
846
          count = lastOutCount_;
847
      }else
848
        count = mysql_num_fields(result_);
849

    
850
      for (unsigned int i = 0; i < count; ++i){
851
       if(out_pars_[i].buffer != 0)free(out_pars_[i].buffer);
852
       if(out_pars_[i].is_null != 0)free(out_pars_[i].is_null) ;
853
       if(out_pars_[i].length != 0)free(out_pars_[i].length);
854
       if(out_pars_[i].error != 0)free(out_pars_[i].error);
855

    
856
      }
857
      free(out_pars_);
858
      out_pars_ = 0;
859
    }
860

    
861
};
862

    
863
const my_bool MySQLStatement::mysqltrue_ = 1;
864

    
865
MySQL::MySQL(const std::string &db,  const std::string &dbuser,
866
             const std::string &dbpasswd, const std::string dbhost,
867
             unsigned int dbport, const std::string &dbsocket,
868
             int fractionalSecondsPart)
869
: impl_(new MySQL_impl())
870
{
871
  setFractionalSecondsPart(fractionalSecondsPart);
872

    
873
  try {
874
    connect(db, dbuser, dbpasswd, dbhost, dbport, dbsocket);
875
  } catch (...) {
876
    delete impl_;
877
    throw;
878
  }
879
}
880

    
881
MySQL::MySQL(const MySQL& other)
882
  : SqlConnection(other),
883
    impl_(new MySQL_impl())
884
{
885
  setFractionalSecondsPart(other.fractionalSecondsPart_);
886

    
887
  try {
888
    if (!other.dbname_.empty())
889
      connect(other.dbname_, other.dbuser_, other.dbpasswd_, other.dbhost_, other.dbport_, other.dbsocket_);
890
  } catch (...) {
891
    delete impl_;
892
    throw;
893
  }
894
}
895

    
896
MySQL::~MySQL()
897
{
898
  clearStatementCache();
899
  if (impl_ && impl_->mysql)
900
    mysql_close(impl_->mysql);
901

    
902
  if (impl_){
903
    delete impl_;
904
    impl_ = 0;
905
  }
906
}
907

    
908
MySQL *MySQL::clone() const
909
{
910
  return new MySQL(*this);
911
}
912

    
913
bool MySQL::connect(const std::string &db,  const std::string &dbuser,
914
             const std::string &dbpasswd, const std::string &dbhost,
915
             unsigned int dbport, const std::string &dbsocket)
916
{
917
  if (impl_->mysql)
918
    throw MySQLException("MySQL : Already connected, disconnect first");
919

    
920
  if((impl_->mysql = mysql_init(NULL))){
921
//    my_bool reconnect = true;
922
//    if(!mysql_options(impl_->mysql, MYSQL_OPT_RECONNECT, &reconnect)) {
923
      if(mysql_real_connect(impl_->mysql, dbhost.c_str(), dbuser.c_str(),
924
	dbpasswd.empty() ? 0 : dbpasswd.c_str(),
925
	db.c_str(), dbport,
926
	dbsocket.c_str(),
927
	CLIENT_FOUND_ROWS) != impl_->mysql) {
928
	  std::string errtext = mysql_error(impl_->mysql);
929
	  mysql_close(impl_->mysql);
930
	  impl_->mysql = 0;
931
	  throw MySQLException(
932
	    std::string("MySQL : Failed to connect to database server: ")
933
	    + errtext);
934
      } else {
935
	// success!
936
	dbname_ = db;
937
	dbuser_ = dbuser;
938
	dbpasswd_ = dbpasswd;
939
	dbhost_ = dbhost;
940
	dbsocket_ = dbsocket;
941
	dbport_ = dbport;
942
      }
943
//    } else {
944
//      mysql_close(impl_->mysql);
945
//      impl_->mysql = 0;
946
//      throw MySQLException("MySQL : Failed to set the reconnect option");
947
//    }
948
  } else {
949
    throw MySQLException(
950
      std::string("MySQL : Failed to initialize database: ") + dbname_);
951
  }
952

    
953
  init();
954

    
955
  return true;
956
}
957

    
958
void MySQL::init()
959
{
960
  executeSql("SET sql_mode='ANSI_QUOTES,REAL_AS_FLOAT'");
961
  executeSql("SET storage_engine=INNODB;");
962
  executeSql("SET NAMES 'utf8';");
963
}
964

    
965
SqlStatement *MySQL::prepareStatement(const std::string& sql)
966
{
967
  return new MySQLStatement(*this, sql);
968
}
969

    
970
void MySQL::executeSql(const std::string &sql)
971
{
972
  if( !impl_->mysql ) return;
973
  if (showQueries())
974
    std::cerr << sql << std::endl;
975

    
976
  if( mysql_query(impl_->mysql, sql.c_str()) != 0 ){
977
    throw MySQLException("MySQL error performing query: '" +
978
  		 sql + "': " + mysql_error(impl_->mysql));
979
  }
980
  //use any results up
981
  MYSQL_RES* res = mysql_store_result(impl_->mysql);
982
  if(res) mysql_free_result(res);
983

    
984
}
985

    
986
std::string MySQL::autoincrementType() const
987
{
988
  return "BIGINT";
989
}
990

    
991
std::string MySQL::autoincrementSql() const
992
{
993
  return "AUTO_INCREMENT";
994
}
995

    
996
std::string MySQL::autoincrementInsertSuffix(const std::string &id) const
997
{
998
  return std::string();
999
}
1000

    
1001
std::vector<std::string>
1002
MySQL::autoincrementCreateSequenceSql(const std::string &table,
1003
                                      const std::string &id) const{
1004
  return std::vector<std::string>();
1005
}
1006

    
1007
std::vector<std::string>
1008
MySQL::autoincrementDropSequenceSql(const std::string &table,
1009
                             const std::string &id) const{
1010

    
1011
  return std::vector<std::string>();
1012
}
1013

    
1014
const char *MySQL::dateTimeType(SqlDateTimeType type) const
1015
{
1016
  switch (type) {
1017
  case SqlDate:
1018
    return "date";
1019
  case SqlDateTime:
1020
    return dateType_.c_str();
1021
  case SqlTime:
1022
    return timeType_.c_str();
1023
  }
1024
  std::stringstream ss;
1025
  ss << __FILE__ << ":" << __LINE__ << ": implementation error";
1026
  throw MySQLException(ss.str());
1027
}
1028

    
1029
const char *MySQL::blobType() const
1030
{
1031
  return "blob";
1032
}
1033

    
1034
bool MySQL::supportAlterTable() const
1035
{
1036
  return true;
1037
}
1038

    
1039
const char *MySQL::alterTableConstraintString() const
1040
{
1041
  return "foreign key";
1042
}
1043

    
1044
int MySQL::getFractionalSecondsPart() const
1045
{
1046
  return fractionalSecondsPart_;
1047
}
1048

    
1049
void MySQL::setFractionalSecondsPart(int fractionalSecondsPart)
1050
{
1051
  fractionalSecondsPart_ = fractionalSecondsPart;
1052

    
1053
  if (fractionalSecondsPart_ != -1) {
1054
    dateType_ = "datetime(";
1055
    dateType_ += boost::lexical_cast<std::string>(fractionalSecondsPart_);
1056
    dateType_ += ")";
1057
  } else
1058
    dateType_ = "datetime";
1059

    
1060

    
1061
  //IMPL note that there is not really a "duration" type in mysql...
1062
  if (fractionalSecondsPart_ != -1) {
1063
    timeType_ = "time(";
1064
    timeType_ += boost::lexical_cast<std::string>(fractionalSecondsPart_);
1065
    timeType_ += ")";
1066
  } else
1067
    timeType_ = "time";
1068
}
1069

    
1070
void MySQL::reconnect()
1071
{
1072
  this->clearStatementCache();
1073
  try {
1074
    connect(dbname_, dbuser_, dbpasswd_, dbhost_, dbport_, dbsocket_);
1075
  } catch (...) {
1076
    throw;
1077
  }    
1078
}
1079

    
1080
void MySQL::startTransaction()
1081
{
1082
  if( ! impl_->mysql ) reconnect();
1083
  if (showQueries())
1084
     std::cerr << "start transaction" << std::endl;
1085
  if( mysql_query(impl_->mysql, "start transaction") != 0 ){
1086
    if( impl_->mysql )
1087
    {
1088
      mysql_close(impl_->mysql);
1089
      impl_->mysql = NULL;      
1090
    }
1091
    reconnect();
1092
    if( mysql_query(impl_->mysql, "start transaction") != 0 ){
1093
        throw MySQLException(std::string("MySQL error starting transaction: ") +
1094
                             mysql_error(impl_->mysql));
1095
    }
1096
  }
1097
  //use any results up
1098
  MYSQL_RES* res = mysql_store_result(impl_->mysql);
1099
  if(res) mysql_free_result(res);
1100
}
1101

    
1102
void MySQL::commitTransaction()
1103
{
1104
  if( !impl_->mysql ) return;
1105
  my_bool status;
1106
  if (showQueries())
1107
     std::cerr << "commit transaction" << std::endl;
1108
  if( (status = mysql_commit(impl_->mysql)) != 0 ){
1109
    std::cerr << "error committing transaction: "
1110
              << mysql_error(impl_->mysql) << std::endl;
1111
    throw MySQLException(std::string("MySQL error committing transaction: ") +
1112
                         mysql_error(impl_->mysql));
1113
  }
1114
  //use any results up
1115
  MYSQL_RES* res = mysql_store_result(impl_->mysql);
1116
  if(res) mysql_free_result(res);
1117
}
1118

    
1119
void MySQL::rollbackTransaction()
1120
{
1121
  if( !impl_->mysql ) return;
1122
  my_bool status;
1123
  if (showQueries())
1124
     std::cerr << "rollback" << std::endl;
1125
  if((status =  mysql_rollback(impl_->mysql)) != 0 ){
1126
    throw MySQLException(std::string("MySQL error rolling back transaction: ") +
1127
                         mysql_error(impl_->mysql));
1128
  }
1129
  //use any results up
1130
  MYSQL_RES* res = mysql_store_result(impl_->mysql);
1131
  if(res) mysql_free_result(res);
1132
}
1133

    
1134
}
1135
}
1136
}
(2-2/2)