db_conn  v0.2.1-alpha
Database Connection API
sybase_example.cpp
1 #include "sybase_driver.hpp"
2 
3 #include <locale>
4 //#include <codecvt> // Standard code conversion facets - uncomment if available
5 #include <iomanip>
6 using namespace std;
7 using namespace vgi::dbconn::dbi;
8 using namespace vgi::dbconn::dbd;
9 
10 constexpr auto DBNAME = "DEV_SYB_DB";
11 constexpr auto DBUSER = "sa";
12 constexpr auto DBPASS = "password";
13 
14 int main(int argc, char** argv)
15 {
16  /************************
17  * simple usage
18  ************************/
19  try
20  {
21  cout.precision(12);
22  cout.setf(ios_base::fixed, ios::floatfield);
23 
24  cout << endl << "============= Simple example =============" << endl;
25  cout << "===== connecting to database server\n";
26  connection conn = driver<sybase::driver>::load().get_connection(DBNAME, DBUSER, DBPASS);
27  if (conn.connect())
28  {
29  cout << "===== done...\n\n";
30  statement stmt = conn.get_statement();
31 
32  cout << "===== switching database\n";
33  stmt.execute("use tempdb");
34  cout << "===== done...\n\n";
35 
36  cout << "===== drop table if it exists\n";
37  stmt.execute("if object_id('tempdb..test') is not null drop table test");
38  cout << "===== done...\n\n";
39 
40  cout << "===== creating table\n";
41  stmt.execute("create table test ( "
42  "id int not null, "
43  "txt1 char(25) not null, "
44  "txt2 varchar(10) not null, "
45 #ifndef CS_VERSION_150 // for using old sybase 12.5 client with sybase 12 or 15 server
46  "txt3 varchar(256) null, "
47  "txt4 varchar(256) null, "
48 #else
49  "txt3 text null, "
50  "txt4 text null, "
51 #endif
52  "bool bit not null, "
53  "flag1 char(1) not null, "
54  "flag2 varchar(1) not null, "
55  "short1 tinyint not null, "
56  "short2 smallint not null, "
57  "long1 decimal not null, "
58  "long2 numeric not null, "
59  "float1 real not null, "
60  "float2 float(15) not null, "
61  "double1 float(16) not null, "
62 #ifndef CS_VERSION_150
63  "double2 float(18) not null, "
64 #else
65  "double2 double precision not null, "
66 #endif
67  "double3 numeric(18, 8) not null, "
68  "date1 date not null, "
69  "date2 datetime not null, "
70  "date3 smalldatetime not null, "
71 #ifndef CS_VERSION_150
72  "date4 datetime not null, "
73 #else
74  "date4 bigdatetime not null, "
75 #endif
76  "datetime1 date not null, "
77  "datetime2 datetime not null, "
78  "datetime3 smalldatetime not null, "
79 #ifndef CS_VERSION_150
80  "datetime4 datetime not null, "
81 #else
82  "datetime4 bigdatetime not null, "
83 #endif
84  "time1 time not null, "
85 #ifndef CS_VERSION_150
86  "time2 time not null, "
87 #else
88  "time2 bigtime not null, "
89 #endif
90  "time3 datetime not null, "
91  "time4 smalldatetime not null, "
92 #ifndef CS_VERSION_150
93  "time5 datetime not null, "
94 #else
95  "time5 bigdatetime not null, "
96 #endif
97  "u16str1 univarchar(20) not null, "
98  "u16str2 unichar(20) not null, "
99  "u16str3 unitext not null, "
100  "u16char unichar(1) not null, "
101 
102  "nstr1 nchar(20) not null, "
103  "nstr2 nvarchar(20) not null, "
104  "nchar nchar(1) not null, "
105 #ifndef CS_VERSION_150
106  "img binary(250), "
107 #else
108  "img image, "
109 #endif
110  "bin1 binary(25), "
111  "bin2 varbinary(25), "
112  "mon1 smallmoney, "
113  "mon2 money, "
114  "primary key(id) ) ");
115  cout << "===== done...\n\n";
116 
117  cout << "===== inserting row into the table\n";
118  stmt.execute("insert into test values ( \
119  1, \
120  'text1', \
121  'text2', \
122  'text3', \
123  null, \
124  0, \
125  'Y', \
126  'N', \
127  1, \
128  2, \
129  167000000, \
130  167890000, \
131  1.45, \
132  2.56, \
133  12345.123456, \
134  12345.123456, \
135  12345.123456, \
136  getdate(), \
137  getdate(), \
138  getdate(), \
139  getdate(), \
140  getdate(), \
141  getdate(), \
142  getdate(), \
143  getdate(), \
144  getdate(), \
145  getdate(), \
146  getdate(), \
147  getdate(), \
148  getdate(), \
149  to_unichar(1055) + to_unichar(1089) + to_unichar(1080) + to_unichar(1093), \
150  to_unichar(1055) + to_unichar(1089) + to_unichar(1080) + to_unichar(1093), \
151  to_unichar(1055) + to_unichar(1089) + to_unichar(1080) + to_unichar(1093), \
152  to_unichar(1044), \
153  '\u041F\u0441\u0438\u0445', \
154  '\u041F\u0441\u0438\u0445', \
155  '\u0414', \
156  0x0000008300000000000100000000013c, \
157  0x01234, \
158  0x0123456789, \
159  214748.3647, \
160  122337203685477.58)");
161  cout << "===== done...\n\n";
162 
163  cout << "===== selecting data from the table\n";
164  result_set rs = stmt.execute("select * from test");
165 
166  int date;
167  time_t datetime;
168  double time;
169  std::u16string u16str;
170  char16_t char16;
171  char* c;
172  std::string nstr;
173  std::vector<uint8_t> imgvec;
174  std::vector<uint8_t> binvec1;
175  std::vector<uint8_t> binvec2;
176 
177  time_t tm;
178  while (rs.next())
179  {
180  cout << "-------------- data by index\n";
181  size_t i = 0;
182  cout << rs.column_name(i) << ": >" << rs.get_int(i) << "<\n"; ++i;
183  cout << rs.column_name(i) << ": >" << rs.get_string(i) << "<\n"; ++i;
184  cout << rs.column_name(i) << ": >" << rs.get_string(i) << "<\n"; ++i;
185  cout << rs.column_name(i) << ": >" << (rs.is_null(i) ? "NULL" : rs.get_string(i)) << "<\n"; ++i;
186  cout << rs.column_name(i) << ": >" << (rs.is_null(i) ? "NULL" : rs.get_string(i)) << "<\n"; ++i;
187  cout << rs.column_name(i) << ": >" << rs.get_bool(i) << "<\n"; ++i;
188  cout << rs.column_name(i) << ": >" << rs.get_char(i) << "<\n"; ++i;
189  cout << rs.column_name(i) << ": >" << rs.get_char(i) << "<\n"; ++i;
190  cout << rs.column_name(i) << ": >" << rs.get_short(i) << "<\n"; ++i;
191  cout << rs.column_name(i) << ": >" << rs.get_short(i) << "<\n"; ++i;
192  cout << rs.column_name(i) << ": >" << rs.get_long(i) << "<\n"; ++i;
193  cout << rs.column_name(i) << ": >" << rs.get_long(i) << "<\n"; ++i;
194  cout << rs.column_name(i) << ": >" << rs.get_float(i) << "<\n"; ++i;
195  cout << rs.column_name(i) << ": >" << rs.get_float(i) << "<\n"; ++i;
196  cout << rs.column_name(i) << ": >" << rs.get_double(i) << "<\n"; ++i;
197  cout << rs.column_name(i) << ": >" << rs.get_double(i) << "<\n"; ++i;
198  cout << rs.column_name(i) << ": >" << rs.get_double(i) << "<\n"; ++i;
199  cout << rs.column_name(i) << ": >" << rs.get_date(i) << "<\n"; date = rs.get_date(i); ++i;
200  cout << rs.column_name(i) << ": >" << rs.get_date(i) << "<\n"; ++i;
201  cout << rs.column_name(i) << ": >" << rs.get_date(i) << "<\n"; ++i;
202  cout << rs.column_name(i) << ": >" << rs.get_date(i) << "<\n"; ++i;
203  cout << rs.column_name(i) << ": >" << ctime(&(tm = rs.get_datetime(i))); ++i;
204  cout << rs.column_name(i) << ": >" << ctime(&(tm = rs.get_datetime(i))); datetime = rs.get_datetime(i); ++i;
205  cout << rs.column_name(i) << ": >" << ctime(&(tm = rs.get_datetime(i))); ++i;
206  cout << rs.column_name(i) << ": >" << ctime(&(tm = rs.get_datetime(i))); ++i;
207  cout << rs.column_name(i) << ": >" << rs.get_time(i) << "<\n"; time = rs.get_time(i); ++i;
208  cout << rs.column_name(i) << ": >" << rs.get_time(i) << "<\n"; ++i;
209  cout << rs.column_name(i) << ": >" << rs.get_time(i) << "<\n"; ++i;
210  cout << rs.column_name(i) << ": >" << rs.get_time(i) << "<\n"; ++i;
211  cout << rs.column_name(i) << ": >" << rs.get_time(i) << "<\n"; ++i;
212  // Standard code conversion facets
213  //wstring_convert<codecvt_utf8<char16_t>, char16_t> cv;
214  //cout << rs.column_name(i) << ": >" << cv.to_bytes(rs.get_u16string(i)) << "<\n"; ++i;
215  //cout << rs.column_name(i) << ": >" << cv.to_bytes(rs.get_u16string(i)) << "<\n"; ++i;
216  //cout << rs.column_name(i) << ": >" << cv.to_bytes(rs.get_u16string(i)) << "<\n"; ++i;
217  //cout << rs.column_name(i) << ": >" << cv.to_bytes(rs.get_u16char(i)) << "<\n"; ++i;
218  // Or just print out each char after manual conversion
219  cout.setf(ios_base::hex, ios::basefield);
220  cout << rs.column_name(i) << ": >";
221  u16str = rs.get_u16string(i); ++i;
222  for (auto chr16 : u16str)
223  {
224  // little endian
225  c = reinterpret_cast<char*>(&chr16);
226  cout << "\\u" << setfill('0') << setw(2) << uppercase << (int)(*(++c));
227  cout << setfill('0') << setw(2) << uppercase << (int)(*(--c));
228  }
229  cout << "<\n";
230  cout << rs.column_name(i) << ": >";
231  u16str = rs.get_u16string(i); ++i;
232  for (auto chr16 : u16str)
233  {
234  // little endian
235  c = reinterpret_cast<char*>(&chr16);
236  cout << "\\u" << setfill('0') << setw(2) << uppercase << (int)(*(++c));
237  cout << setfill('0') << setw(2) << uppercase << (int)(*(--c));
238  }
239  cout << "<\n";
240  cout << rs.column_name(i) << ": >";
241  u16str = rs.get_u16string(i); ++i;
242  for (auto chr16 : u16str)
243  {
244  // little endian
245  c = reinterpret_cast<char*>(&chr16);
246  cout << "\\u" << setfill('0') << setw(2) << uppercase << (int)(*(++c));
247  cout << setfill('0') << setw(2) << uppercase << (int)(*(--c));
248  }
249  cout << "<\n";
250  cout << rs.column_name(i) << ": >";
251  char16 = rs.get_u16char(i); ++i;
252  c = reinterpret_cast<char*>(&char16);
253  // little endian
254  cout << "\\u" << setfill('0') << setw(2) << uppercase << (int)(*(++c));
255  cout << setfill('0') << setw(2) << uppercase << (int)(*(--c));
256  cout << "<\n";
257  // End of code conversion facets
258 
259  cout.setf(ios_base::dec, ios::basefield);
260  nstr = rs.get_string(i);
261  cout << rs.column_name(i) << ": >" << rs.get_string(i) << "<\n"; ++i; // this is utf-8
262  cout << rs.column_name(i) << ": >" << rs.get_string(i) << "<\n"; ++i; // this is utf-8
263  cout << rs.column_name(i) << ": >" << rs.get_char(i) << "<\n"; ++i; // this is utf-8
264  auto idata1 = rs.get_binary(i);
265  imgvec.insert(imgvec.begin(), idata1.begin(), idata1.end());
266  cout << rs.column_name(i) << ": >"; ++i;
267  cout.setf(ios_base::hex, ios::basefield);
268  for (uint16_t k : idata1)
269  cout << setfill('0') << setw(2) << k;
270  cout << "<\n";
271  auto bdata1 = rs.get_binary(i);
272  binvec1.insert(binvec1.begin(), bdata1.begin(), bdata1.end());
273  cout << rs.column_name(i) << ": >"; ++i;
274  for (uint16_t k : bdata1)
275  cout << setfill('0') << setw(2) << k;
276  cout << "<\n";
277  auto bbdata1 = rs.get_binary(i);
278  binvec2.insert(binvec2.begin(), bbdata1.begin(), bbdata1.end());
279  cout << rs.column_name(i) << ": >"; ++i;
280  for (uint16_t k : bbdata1)
281  cout << setfill('0') << setw(2) << k;
282  cout.setf(ios_base::dec, ios::basefield);
283  cout << "<\n";
284  cout << rs.column_name(i) << ": >" << rs.get_double(i) << "<\n"; ++i;
285  cout << rs.column_name(i) << ": >" << rs.get_double(i) << "<\n"; ++i;
286  cout << "-------------- data by name\n";
287  cout << "column id: >" << rs.get_int("id") << "<\n";
288  cout << "column txt1: >" << rs.get_string("txt1") << "<\n";
289  cout << "column txt2: >" << rs.get_string("txt2") << "<\n";
290  cout << "column txt3: >" << (rs.is_null("txt3") ? "NULL" : rs.get_string("txt3")) << "<\n";
291  cout << "column txt4: >" << (rs.is_null("txt4") ? "NULL" : rs.get_string("txt4")) << "<\n";
292  cout << "column bool: >" << rs.get_bool("bool") << "<\n";
293  cout << "column flag1: >" << rs.get_char("flag1") << "<\n";
294  cout << "column flag2: >" << rs.get_char("flag2") << "<\n";
295  cout << "column short1: >" << rs.get_long("short1") << "<\n";
296  cout << "column short2: >" << rs.get_long("short2") << "<\n";
297  cout << "column long1: >" << rs.get_long("long1") << "<\n";
298  cout << "column long2: >" << rs.get_long("long2") << "<\n";
299  cout << "column float1: >" << rs.get_float("float1") << "<\n";
300  cout << "column float2: >" << rs.get_float("float2") << "<\n";
301  cout << "column double1: >" << rs.get_double("double1") << "<\n";
302  cout << "column double2: >" << rs.get_double("double2") << "<\n";
303  cout << "column double3: >" << rs.get_double("double3") << "<\n";
304  cout << "column date1: >" << rs.get_date("date1") << "<\n";
305  cout << "column date2: >" << rs.get_date("date2") << "<\n";
306  cout << "column date3: >" << rs.get_date("date3") << "<\n";
307  cout << "column date4: >" << rs.get_date("date4") << "<\n";
308  cout << "column datetime1: >" << ctime(&(tm = rs.get_datetime("datetime1")));
309  cout << "column datetime2: >" << ctime(&(tm = rs.get_datetime("datetime2")));
310  cout << "column datetime3: >" << ctime(&(tm = rs.get_datetime("datetime3")));
311  cout << "column datetime4: >" << ctime(&(tm = rs.get_datetime("datetime4")));
312  cout << "column time1: >" << rs.get_time("time1") << "<\n";
313  cout << "column time2: >" << rs.get_time("time2") << "<\n";
314  cout << "column time3: >" << rs.get_time("time3") << "<\n";
315  cout << "column time4: >" << rs.get_time("time4") << "<\n";
316  cout << "column time5: >" << rs.get_time("time5") << "<\n";
317  cout.setf(ios_base::hex, ios::basefield);
318  cout << "column u16str1: >";
319  u16str = rs.get_u16string("u16str1");
320  for (auto chr16 : u16str)
321  {
322  // little endian
323  c = reinterpret_cast<char*>(&chr16);
324  cout << "\\u" << setfill('0') << setw(2) << uppercase << (int)(*(++c));
325  cout << setfill('0') << setw(2) << uppercase << (int)(*(--c));
326  }
327  cout << "<\n";
328  cout << "column u16str2: >";
329  u16str = rs.get_u16string("u16str2");
330  for (auto chr16 : u16str)
331  {
332  // little endian
333  c = reinterpret_cast<char*>(&chr16);
334  cout << "\\u" << setfill('0') << setw(2) << uppercase << (int)(*(++c));
335  cout << setfill('0') << setw(2) << uppercase << (int)(*(--c));
336  }
337  cout << "<\n";
338  cout << "column u16str3: >";
339  u16str = rs.get_u16string("u16str3");
340  for (auto chr16 : u16str)
341  {
342  // little endian
343  c = reinterpret_cast<char*>(&chr16);
344  cout << "\\u" << setfill('0') << setw(2) << uppercase << (int)(*(++c));
345  cout << setfill('0') << setw(2) << uppercase << (int)(*(--c));
346  }
347  cout << "<\n";
348  cout << "column u16char: >";
349  char16 = rs.get_u16char("u16char"); ++i;
350  c = reinterpret_cast<char*>(&char16);
351  // little endian
352  cout << "\\u" << setfill('0') << setw(2) << uppercase << (int)(*(++c));
353  cout << setfill('0') << setw(2) << uppercase << (int)(*(--c));
354  cout << "<\n";
355  cout.setf(ios_base::dec, ios::basefield);
356  cout << "column nstr1: >" << rs.get_string("nstr1") << "<\n"; // this is utf-8
357  cout << "column nstr2: >" << rs.get_string("nstr2") << "<\n"; // this is utf-8
358  cout << "column nchar: >" << rs.get_char("nchar") << "<\n"; // this is utf-8
359  cout << setfill('0') << setw(2) << hex;
360  auto idata2 = rs.get_binary("img");
361  cout << "column img: >";
362  cout.setf(ios_base::hex, ios::basefield);
363  for (uint16_t i : idata2)
364  cout << setfill('0') << setw(2) << i;
365  cout << "<\n";
366  auto bdata2 = rs.get_binary("bin1");
367  cout << "column bin: >";
368  for (uint16_t i : bdata2)
369  cout << setfill('0') << setw(2) << i;
370  cout << "<\n";
371  auto bbdata2 = rs.get_binary("bin2");
372  cout << "column bin: >";
373  for (uint16_t i : bbdata2)
374  cout << setfill('0') << setw(2) << i;
375  cout.setf(ios_base::dec, ios::basefield);
376  cout << "<\n";
377  cout << "column mon1: >" << rs.get_double("mon1") << "<\n";
378  cout << "column mon2: >" << rs.get_double("mon2") << "<\n";
379  }
380  cout << "===== done...\n\n";
381 
382  cout << "===== testing prepared statement (stored proc would be the same) data types binding\n";
383  u16str = u"\u041F\u0441\u0438\u0445";
384  size_t i = 0;
385  stmt.prepare("insert into test values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
386  stmt.set_int(i++, 2);
387  stmt.set_string(i++, "text1");
388  stmt.set_string(i++, "text2");
389  stmt.set_string(i++, "text3");
390  stmt.set_null(i++);
391  stmt.set_bool(i++, false);
392  stmt.set_char(i++, 'Y');
393  stmt.set_char(i++, 'N');
394  stmt.set_short(i++, 1);
395  stmt.set_short(i++, 2);
396  stmt.set_long(i++, 167000000);
397  stmt.set_long(i++, 167890000);
398  stmt.set_float(i++, 1.45);
399  stmt.set_float(i++, 2.56);
400  stmt.set_double(i++, 12345.123456);
401  stmt.set_double(i++, 12345.123456);
402  stmt.set_double(i++, 12345.123456);
403  stmt.set_date(i++, date);
404  stmt.set_date(i++, date);
405  stmt.set_date(i++, date);
406  stmt.set_date(i++, date);
407  stmt.set_datetime(i++, datetime);
408  stmt.set_datetime(i++, datetime);
409  stmt.set_datetime(i++, datetime);
410  stmt.set_datetime(i++, datetime);
411  stmt.set_time(i++, time);
412  stmt.set_time(i++, time);
413  stmt.set_time(i++, time);
414  stmt.set_time(i++, time);
415  stmt.set_time(i++, time);
416  stmt.set_u16string(i++, u16str);
417  stmt.set_u16string(i++, u16str);
418  stmt.set_u16string(i++, u16str);
419  stmt.set_u16char(i++, u16str[0]);
420  stmt.set_string(i++, nstr);
421  stmt.set_string(i++, nstr);
422  stmt.set_char(i++, nstr[0]);
423  stmt.set_binary(i++, imgvec);
424  stmt.set_binary(i++, binvec1);
425  stmt.set_binary(i++, binvec2);
426  stmt.set_double(i++, 214748.3647);
427  stmt.set_double(i++, 122337203685477.58);
428  stmt.execute();
429  cout << "===== done...\n\n";
430 
431  cout << "===== updating row in the table\n";
432  stmt.execute("update test set txt4 = 'text4' where id = 1");
433  cout << "===== done...\n\n";
434 
435  cout << "===== deleting from the table\n";
436  stmt.execute("delete from test where id = 1");
437  cout << "===== done...\n\n";
438 
439  cout << "===== dropping the table\n";
440  stmt.execute("drop table test");
441  cout << "===== done...\n\n";
442  }
443  else
444  cout << "===== failed to connect!\n";
445  }
446  catch (const exception& e)
447  {
448  cout << "===== simple example exception: " << e.what() << endl;
449  }
450 
451  /************************
452  * advanced usage
453  ************************/
454  try
455  {
456  cout << endl << "============= Advanced example =============" << endl;
457 
458  array<char, 256> version_string = {'\0'};
459  long version = 0;
460  string verstr;
461  string my_context_info = "A: 1";
462  string my_conn_info = "B: 2";
463 
464  cout << "===== connecting to database server using custom settings\n";
465  /*
466  * Initialize Sybase driver, get driver information, and set custom properties
467  */
468  sybase::driver& sybdriver = driver<sybase::driver>::load().
469  // debug works only if compiled with debug version of sybase libraries
470 // debug(sybase::debug_flag::ERROR | sybase::debug_flag::NETWORK).
471 // debug_file("test.log").
472 // debug_protocol_file("proto.log").
473  // get version
474  version(version).
475  version_string(verstr).
476  // set application name
477  app_name("test.conn2").
478  // set context maximum connections
479  max_connections(1).
480  // set connection timeout - 60 seconds
481  timeout(60).
482  // set keepalive (default is true)
483  keepalive(true).
484  // set keepalive (default is true)
485  tcp_nodelay(true).
486  // set custom configuration file
487  config_file("/opt/sybase64/interfaces").
488  // set custom locale
489  locale("en_US").
490  // set custom user data struct pointer - can be used to set/get custom settings
491  userdata(my_context_info).
492  // set custom library callback
493  cs_msg_callback([](sybase::Context* context, sybase::ClientMessage* msg)
494  {
495  cout << __FUNCTION__ << ": CS Library message: Severity - " << CS_SEVERITY(msg->msgnumber) <<
496  " (" << sybase::driver::decode_severity(CS_SEVERITY(msg->msgnumber)) <<
497  "), layer - " << CS_LAYER(msg->msgnumber) << ", origin - " << CS_ORIGIN(msg->msgnumber) <<
498  ", number -" << CS_NUMBER(msg->msgnumber) << ", message - " << msg->msgstring << endl;
499  if (msg->osstringlen > 0)
500  cout << __FUNCTION__ << ": Operating System Message: " << msg->osstring << endl;
501  return CS_SUCCEED;
502  }).
503  // set custom client callback
504  ct_msg_callback([](sybase::Context* context, sybase::Connection* conn, sybase::ClientMessage* msg)
505  {
506  cout << __FUNCTION__ << ": Open Client Message: Severity - " << CS_SEVERITY(msg->msgnumber) <<
507  " (" << sybase::driver::decode_severity(CS_SEVERITY(msg->msgnumber)) <<
508  "), layer - " << CS_LAYER(msg->msgnumber) << ", origin - " << CS_ORIGIN(msg->msgnumber) <<
509  ", number - " << CS_NUMBER(msg->msgnumber) << ", message - " << msg->msgstring << endl;
510  if (msg->osstringlen > 0)
511  cout << __FUNCTION__ << ": Operating System Message: " << msg->osstring << endl;
512  if (nullptr != context)
513  {
514  string* my_context_info = nullptr;
515  if (CS_SUCCEED == cs_config(context, CS_GET, CS_USERDATA, (CS_VOID*)&my_context_info, sizeof(my_context_info), nullptr))
516  cout << "my_context_info: " << my_context_info << endl;
517  }
518  return CS_SUCCEED;
519  }).
520  // set custom server reply callback
521  srv_msg_callback([](sybase::Context* context, sybase::Connection* conn, sybase::ServerMessage* msg)
522  {
523  cout << __FUNCTION__ << ": Server message: " << (msg->svrnlen > 0 ? string("Server '").append(msg->svrname).append("': ") : "")
524  << (msg->proclen > 0 ? string("Procedure '").append(msg->proc).append("': ") : "") << "Severity - " << msg->severity
525  << ", state - " << msg->state << ", origin - " << msg->line << ", number - " << msg->msgnumber << ", message - " << msg->text << endl;
526  if (nullptr != conn)
527  {
528  string* my_conn_info = nullptr;
529  if (CS_SUCCEED == ct_con_props(conn, CS_GET, CS_USERDATA, (CS_VOID*)&my_conn_info, sizeof(my_conn_info), nullptr))
530  cout << "my_conn_info: " << *my_conn_info << endl;
531  }
532  return CS_SUCCEED;
533  }).
534  // use low level function to set/get any other desired configuration for cs/ct
535  // get Client-Library version string - same as what version_sring() would return
536  config(sybase::action::GET, sybase::cfg_type::CT_LIB, CS_VER_STRING, version_string.data(), version_string.size());
537 
538 
539  /*
540  * Get connection
541  */
542  connection conn = sybdriver.get_connection(DBNAME, DBUSER, DBPASS);
543 
544  /*
545  * Print information from the driver
546  */
547  cout << endl << "Client-Library version number: " << version;
548  cout << endl << "Client-Library version string: " << verstr;
549  cout << endl << "Client-Library version string via explicit request using config() call: " << version_string.data();
550  string* my_cust_context_data;
551  // get the context user data that was set before
552  sybdriver.userdata(my_cust_context_data);
553  cout << endl << "My context custom data structure: " << *my_cust_context_data;
554 
555 
556  /*
557  * Set custom connection properties
558  */
559  static_cast<sybase::connection&>(conn).
560  // set custom user data struct pointer - can be used to set/get custom settings
561  userdata(my_conn_info).
562  // use low level function to set/get any other desired configuration for cs/ct
563  props(sybase::action::SET, CS_DIAG_TIMEOUT, &sybase::TRUE);
564 
565 
566  /*
567  * Print information from the connection
568  */
569  string* my_cust_conn_data;
570  // get the connection user data that was set before
571  static_cast<sybase::connection&>(conn).userdata(my_cust_conn_data);
572  cout << endl << "My connection custom data structure: " << *my_cust_conn_data << endl;
573 
574 
575  /*
576  * Open connection and run some tests
577  */
578  cout << "===== connecting to database server\n";
579  if (conn.connect())
580  {
581  cout << "===== done...\n\n";
582 
583  statement stmt = conn.get_statement();
584 
585  cout << "===== switching database\n";
586  stmt.execute("use tempdb");
587  cout << "===== done...\n\n";
588 
589  cout << "===== creating table\n";
590  // By default, columns in Adaptive Server Enterprise default to NOT NULL, whereas in Sybase IQ the default setting is NULL
591  // To allow NULL values we explicitly state so for the column
592  result_set rs = stmt.execute("create table test ( id int, txt varchar(10) NULL, dt numeric(18, 8) NULL, primary key(id) )");
593  cout << "===== done...\n\n";
594 
595  /********************************************************************
596  * Use multiple insert statement to populate table.
597  * Some statements are invalid.
598  *
599  * Note that errors are handled differently depending on type of error
600  */
601 
602  cout << "===== using multiple statements to insert rows into the table - all will fail on invalid table name in one of the statements\n";
603  // If failed to prepare all statement - then whole chain fails
604  try
605  {
606  rs = stmt.execute( "insert into test (id, txt) values (1, 'hello1') \
607  insert into test (id, txt) values (1, 'hello1') \
608  insert into test (id, txt) values (2, 'hello2') \
609  insert into bogus (id, txt) values (7, 'hello2') \
610  insert into test (id, txt) values (3, 'hello2') \
611  insert into test (id, txt) values (4, 'hello3') ");
612  }
613  catch (const exception& e)
614  {
615  cout << e.what() << "\n";
616  }
617  cout << "rows affected = " << rs.rows_affected() << "\n";
618  cout << "===== done...\n\n";
619 
620 
621  cout << "===== using multiple statements to insert rows into the table - two will fail on broken unique index\n";
622  // If certain statements fail during runtime - then all the other statements continue to be executed
623  try
624  {
625  rs = stmt.execute( "insert into test (id, txt) values (1, 'hello1') \
626  insert into test (id, txt) values (1, 'hello1') \
627  insert into test (id, txt) values (2, 'hello2') \
628  insert into test (id, txt) values (3, 'hello2') \
629  insert into test (id, txt) values (4, 'hello3') \
630  insert into test (id, txt) values (4, 'hello3') \
631  insert into test (id, txt) values (5, 'hello5') ");
632  }
633  catch (const exception& e)
634  {
635  cout << e.what() << "\n";
636  }
637  cout << "rows affected = " << rs.rows_affected() << "\n";
638  cout << "===== done...\n\n";
639 
640 
641  /********************************************************************
642  * Use multiple update statement to populate table.
643  * Some statements are invalid.
644  */
645 
646  cout << "===== using multiple statements to update rows in the table - all will fail on invalid data type for the column in one of the statements\n";
647  // If failed to prepare all statement (invalid data type) - then whole chain fails
648  try
649  {
650  rs = stmt.execute( "update test set txt = 'boom' where id = 5 \
651  update test set txt = 'test2' where id = 6 \
652  update test set txt = 3 where id = 1 ");
653  }
654  catch (const exception& e)
655  {
656  cout << e.what() << "\n";
657  }
658  cout << "rows affected = " << rs.rows_affected() << "\n";
659  cout << "===== done...\n\n";
660 
661 
662  cout << "===== using multiple statements to update rows in the table - one will fail on invalid id\n";
663  // If certain statements fail during runtime - then all the other statements continue to be executed
664  try
665  {
666  rs = stmt.execute( "update test set txt = 'boom' where id = 5 \
667  update test set txt = 'test2' where id = 6 ");
668  }
669  catch (const exception& e)
670  {
671  cout << e.what() << "\n";
672  }
673  cout << "rows affected = " << rs.rows_affected() << "\n";
674  cout << "===== done...\n\n";
675 
676 
677  /********************************************************************
678  * Use multiple select statement to retrieve data from table.
679  * Some statements are invalid.
680  */
681 
682  cout << "===== using multiple statements to select data from the table - all will fail on invalid table name in one of the statements\n";
683  // If failed to prepare all statement (invalid table name) - then whole chain fails
684  try
685  {
686  rs = stmt.execute( "select id from test where txt = 'hello1' \
687  select id, txt from test where txt = 'hello2' \
688  select id from test where txt = 'hello4' \
689  select id, txt from bogus where txt = 'aa' \
690  select id, txt from test where txt = 'hello3' ");
691  }
692  catch (const exception& e)
693  {
694  cout << e.what() << "\n";
695  }
696  cout << "has data = " << rs.has_data() << "\n";
697  cout << "more results = " << rs.more_results() << "\n";
698  cout << "===== done...\n\n";
699 
700 
701  cout << "===== using multiple statements to select data from the table - one will return no data, one will fail on invalid data type for the column\n";
702  // If certain statements fail during runtime - then all the other statements continue to be executed
703  try
704  {
705  rs = stmt.execute( "select id from test where txt = 'hello1' \
706  select id, txt from test where txt = 'hello2' \
707  select id from test where txt = 'hello4' \
708  select id, txt from test where txt = 1 \
709  select id, txt from test where txt = 'hello3' ");
710  }
711  catch (const exception& e)
712  {
713  cout << e.what() << "\n";
714  }
715  for (int i = 1; rs.more_results(); ++i)
716  {
717  try
718  {
719  cout << "select " << i << ": data set (" << rs.column_count() << " columns):\n";
720  if (rs.has_data())
721  {
722  while (rs.next())
723  {
724  cout << "\trow " << rs.row_count() << ":\n";
725  cout << "\tcolumn 1: >" << (rs.is_null(0) ? -1 : rs.get_int(0)) << "<\n";
726  if (rs.column_count() > 1)
727  cout << "\tcolumn 2: >" << (rs.is_null(1) ? "NULL" : rs.get_string(1)) << "<\n";
728  }
729  }
730  cout << "rows affected = " << rs.rows_affected() << "\n";
731  cout << "---------\n";
732  }
733  catch (const exception& e)
734  {
735  cout << "select " << i << ": exception: " << e.what() << "\n";
736  cout << "---------\n";
737  }
738  }
739  cout << "===== done...\n\n";
740 
741 
742  cout << "===== using compute select statements\n";
743  /********************************************************************
744  * Using compute in select
745  */
746  try
747  {
748  rs = stmt.execute( "select id, txt, dt from test order by id, txt compute count(txt) by id, txt ");
749  }
750  catch (const exception& e)
751  {
752  cout << e.what() << "\n";
753  }
754  for (int i = 1; rs.more_results(); ++i)
755  {
756  try
757  {
758  cout << "Result " << i << ": data set (" << rs.column_count() << " columns):\n";
759  if (rs.has_data())
760  {
761  while (rs.next())
762  {
763  cout << "\trow " << rs.row_count() << ":\n";
764  cout << "\t" << rs.column_name(0) << ": >" << (rs.is_null(0) ? -1 : rs.get_int(0)) << "<\n";
765  if (rs.column_count() > 1)
766  cout << "\t" << rs.column_name(1) << ": >" << (rs.is_null(1) ? "NULL" : rs.get_string(1)) << "<\n";
767  }
768  }
769  cout << "---------\n";
770  }
771  catch (const exception& e)
772  {
773  cout << "select " << i << ": exception: " << e.what() << "\n";
774  cout << "---------\n";
775  }
776  }
777  cout << "rows affected = " << rs.rows_affected() << "\n";
778  cout << "===== done...\n\n";
779 
780 
781  /********************************************************************
782  * Use prepared SQL statements for faster execution of repeated statements
783  */
784 
785 
786  cout << "===== using prepared select statements - repeated execution setting new values\n";
787  /*
788  * Prepared SQL statements for select
789  */
790  stmt.prepare("select id, txt from test where id = ? or txt = ?");
791  for (int i = 0; i < 6; ++i)
792  {
793  cout << "--------------\n";
794  stmt.set_int(0, i);
795  stmt.set_string(1, "hello3");
796  rs = stmt.execute();
797  if (rs.has_data())
798  {
799  while (rs.next())
800  {
801  cout << "\trow " << rs.row_count() << ":\n";
802  cout << "\t" << rs.column_name(0) << ": >" << (rs.is_null(0) ? -1 : rs.get_int(0)) << "<\n";
803  cout << "\t" << rs.column_name(1) << ": >" << (rs.is_null(1) ? "NULL" : rs.get_string(1)) << "<\n";
804  }
805  cout << "done\n";
806  }
807  else
808  cout << "no data\n";
809  cout << "rows affected = " << rs.rows_affected() << "\n";
810  }
811  cout << "===== done...\n\n";
812 
813 
814  cout << "===== using prepared update statements - repeated execution setting new values (no affected row count is available)\n";
815  /*
816  * Prepared SQL statements for update
817  *
818  * NOTE!!! weirdly the Sybase does not return affected row count for prepared
819  * update SQL statements
820  */
821  stmt.prepare("update test set txt = ?, dt = ? where id = ?");
822  for (int i = 4; i < 6; ++i)
823  {
824  cout << "--------------\n";
825  stmt.set_null(0);
826  stmt.set_double(1, 12345.123456);
827  stmt.set_int(2, i);
828  rs = stmt.execute();
829  cout << "rows affected = " << rs.rows_affected() << "\n";
830  }
831  cout << "===== done...\n\n";
832 
833  cout << "===== using prepared delete statements - repeated execution setting new values (no affected row count is available)\n";
834  /*
835  * Prepared SQL statements for delete
836  *
837  * NOTE!!! weirdly the Sybase does not return row count for prepared
838  * delete SQL statements
839  */
840  stmt.prepare("delete from test where id = ?");
841  cout << "--------------\n";
842  stmt.set_int(0, 4);
843  rs = stmt.execute();
844  cout << "rows affected = " << rs.rows_affected() << "\n";
845  cout << "===== done...\n\n";
846 
847 
848  /********************************************************************
849  * Use SQL stored procedures
850  */
851  try
852  {
853  stmt.execute("drop procedure test_proc");
854  }
855  catch (const exception&)
856  {
857  // ignore exception if procedure doesn't exist
858  }
859  cout << "===== creating stored procedure\n";
860  stmt.execute("CREATE PROCEDURE test_proc @id INT, @error VARCHAR(128) output, @status VARCHAR(20) output AS \
861  BEGIN \
862  DECLARE @ret int \
863  SET @error = NULL \
864  SET @ret = 0 \
865  IF @id IS NULL \
866  BEGIN \
867  SET @error = 'You need to pass in an id number' \
868  SET @status = 'Ooops' \
869  SET @id = -2222222 \
870  SET @ret = 1 \
871  END \
872  ELSE IF @id = 5 \
873  BEGIN \
874  SET @error = 'ID 5 is not allowed' \
875  SET @status = 'Exception' \
876  SET @id = -2222222 \
877  SET @ret = 2 \
878  RAISERROR 90001 @error \
879  END \
880  ELSE \
881  SET @status = 'OK' \
882  PRINT @status \
883  SELECT txt FROM test WHERE id = @id \
884  RETURN @ret \
885  END");
886  cout << "===== done...\n\n";
887 
888 
889  cout << "===== using stored procedure\n";
890  stmt.call("test_proc");
891  stmt.set_int(0, 3);
892  cout << "--------------\n";
893  rs = stmt.execute();
894  while (rs.next())
895  {
896  cout << "\trow " << rs.row_count() << ":\n";
897  cout << "\t" << rs.column_name(0) << ": >" << (rs.is_null(0) ? "NULL" : rs.get_string(0)) << "<\n";
898  }
899  cout << "rows affected = " << rs.rows_affected() << "\n";
900  cout << "stored proc return = " << stmt.proc_retval() << "\n";
901  cout << "@error: >" << (rs.is_null(0) ? "NULL" : rs.get_string("@error")) << "<\n";
902  cout << "@status: >" << (rs.is_null(1) ? "NULL" : rs.get_string("@status")) << "<\n";
903 
904  stmt.set_null(0);
905  cout << "--------------\n";
906  rs = stmt.execute();
907  while (rs.next())
908  {
909  cout << "\trow " << rs.row_count() << ":\n";
910  cout << "\t" << rs.column_name(0) << ": >" << (rs.is_null(0) ? "NULL" : rs.get_string(0)) << "<\n";
911  }
912  cout << "rows affected = " << rs.rows_affected() << "\n";
913  cout << "stored proc return = " << stmt.proc_retval() << "\n";
914  cout << "@error: >" << (rs.is_null(0) ? "NULL" : rs.get_string("@error")) << "<\n";
915  cout << "@status: >" << (rs.is_null(1) ? "NULL" : rs.get_string("@status")) << "<\n";
916 
917  stmt.set_int(0, 5);
918  cout << "--------------\n";
919  rs = stmt.execute();
920  while (rs.next())
921  {
922  cout << "\trow " << rs.row_count() << ":\n";
923  cout << "\t" << rs.column_name(0) << ": >" << (rs.is_null(0) ? "NULL" : rs.get_string(0)) << "<\n";
924  }
925  cout << "rows affected = " << rs.rows_affected() << "\n";
926  cout << "stored proc return = " << stmt.proc_retval() << "\n";
927  cout << "@error: >" << (rs.is_null(0) ? "NULL" : rs.get_string("@error")) << "<\n";
928  cout << "@status: >" << (rs.is_null(1) ? "NULL" : rs.get_string("@status")) << "<\n";
929  cout << "===== done...\n\n";
930 
931 
932  cout << "===== using delete with number of affected rows\n";
933  /********************************************************************
934  * Use delete SQL statement.
935  */
936  rs = stmt.execute("delete from test where id = 1 or id = 2");
937  cout << "delete : rows affected = " << rs.rows_affected() << "\n";
938  cout << "===== done...\n\n";
939 
940 
941  cout << "===== using drop with number of affected rows\n";
942  /********************************************************************
943  * Use drop table SQL statement.
944  */
945  rs = stmt.execute("drop table test");
946  cout << "drop : rows affected = " << rs.rows_affected() << "\n";
947  cout << "===== done...\n\n";
948 
949 
950  cout << "===== using explicit commit\n";
951  /********************************************************************
952  * Use autocommit OFF mode
953  */
954  stmt.execute("create table test (id int, txt varchar(10) NULL, primary key(id) )");
955  conn.autocommit(false);
956  cout << "autocommit is OFF\n";
957  cout << "insert 1 row\n";
958  stmt.execute("insert into test values (1, 'test1')");
959  cout << "run commit\n";
960  conn.commit();
961  cout << "insert 2 rows\n";
962  stmt.execute("insert into test values (2, 'test2')");
963  stmt.execute("insert into test values (3, 'test3')");
964  cout << "run rollback\n";
965  conn.rollback();
966  conn.autocommit(true);
967  cout << "autocommit is ON\n";
968  cout << "check row count to make sure rollback worked\n";
969  rs = stmt.execute( "select count(*) from test");
970  rs.next();
971  cout << "\tTable has >" << rs.get_int(0) << "< rows\n";
972  cout << "===== done...\n\n";
973 
974 
975  /********************************************************************
976  * Use cursors for SELECT statements
977  */
978  stmt.execute("insert into test values (2, 'test2')");
979  stmt.execute("insert into test values (3, 'test3')");
980  stmt.execute("insert into test values (4, 'test4')");
981  stmt.execute("insert into test values (5, 'test5')");
982 
983  cout << "===== using cursor for select statements - read only cursor (note that rows affected is not returned by sybase)\n";
984  rs = stmt.execute("select id, txt from test", true);
985  while (rs.next())
986  {
987  cout << "\trow " << rs.row_count() << ":\n";
988  cout << "\t" << rs.column_name(0) << ": >" << (rs.is_null(0) ? -1 : rs.get_int(0)) << "<\n";
989  cout << "\t" << rs.column_name(1) << ": >" << (rs.is_null(1) ? "NULL" : rs.get_string(1)) << "<\n";
990  }
991  cout << "done\n";
992  cout << "rows affected = " << rs.rows_affected() << "\n";
993  cout << "===== done...\n\n";
994 
995  cout << "===== using cursor for select statements - scrollable cursor (note that rows affected is not returned by sybase)\n";
996  rs = stmt.execute("select id, txt from test", true, true);
997  while (rs.next())
998  {
999  cout << "row " << rs.row_count() << ":\n";
1000  cout << "\t" << rs.column_name(0) << ": >" << (rs.is_null(0) ? -1 : rs.get_int(0)) << "<\n";
1001  cout << "\t" << rs.column_name(1) << ": >" << (rs.is_null(1) ? "NULL" : rs.get_string(1)) << "<\n";
1002  }
1003 
1004  cout << "-------------- rewinding the cursor to the start\n";
1005  rs.first();
1006  do
1007  {
1008  cout << "row " << rs.row_count() << ":\n";
1009  cout << "\t" << rs.column_name(0) << ": >" << (rs.is_null(0) ? -1 : rs.get_int(0)) << "<\n";
1010  cout << "\t" << rs.column_name(1) << ": >" << (rs.is_null(1) ? "NULL" : rs.get_string(1)) << "<\n";
1011  }
1012  while (rs.next());
1013 
1014  cout << "-------------- iterating from end to start\n";
1015  while (rs.prev())
1016  {
1017  cout << "row " << rs.row_count() << ":\n";
1018  cout << "\t" << rs.column_name(0) << ": >" << (rs.is_null(0) ? -1 : rs.get_int(0)) << "<\n";
1019  cout << "\t" << rs.column_name(1) << ": >" << (rs.is_null(1) ? "NULL" : rs.get_string(1)) << "<\n";
1020  }
1021 
1022  cout << "-------------- rewinding the cursor to the end - resets row count and starts increasing the count\n";
1023  rs.last();
1024  do
1025  {
1026  cout << "row " << rs.row_count() << ":\n";
1027  cout << "\t" << rs.column_name(0) << ": >" << (rs.is_null(0) ? -1 : rs.get_int(0)) << "<\n";
1028  cout << "\t" << rs.column_name(1) << ": >" << (rs.is_null(1) ? "NULL" : rs.get_string(1)) << "<\n";
1029  }
1030  while (rs.prev());
1031  cout << "rows affected = " << rs.rows_affected() << "\n";
1032  cout << "===== done...\n\n";
1033 
1034  stmt.execute("drop table test");
1035  }
1036  else
1037  cout << "failed to connect!\n";
1038  }
1039  catch (const exception& e)
1040  {
1041  cout << "advanced example exception: " << e.what() << endl;
1042  }
1043 
1044  return 0;
1045 }
vgi::dbconn::dbd::sybase::driver
sybase ASE driver class based on ctlib sybase C library.
Definition: sybase_driver.hpp:984
vgi::dbconn::dbi::result_set::column_name
const std::string column_name(size_t col_idx)
Function returns column name by column index or throws an exception if index is invalid.
Definition: result_set.hpp:175
vgi::dbconn::dbi::statement
statement - is a class that manages native driver statement handle.
Definition: statement.hpp:72
vgi::dbconn::dbi::result_set::has_data
bool has_data()
Function checks if current result set contains data.
Definition: result_set.hpp:128
vgi::dbconn::dbd::driver
driver is a singleton template class which creates a concrete driver on load() and returns its instan...
Definition: connection.hpp:28
vgi::dbconn::dbi::result_set::more_results
bool more_results()
Function checks if there is another data set or status.
Definition: result_set.hpp:137
vgi::dbconn::dbi::connection::get_statement
statement get_statement()
Function returns statement object for the connection.
Definition: connection.hpp:157
std
STL namespace.
vgi::dbconn::dbi::result_set::rows_affected
size_t rows_affected() const
Function returns number of affected rows after execution of command.
Definition: result_set.hpp:155
vgi::dbconn::dbi::statement::call
void call(const std::string &proc)
Function prepares SQL stored procedure.
Definition: statement.hpp:108
vgi::dbconn::dbi::result_set::last
bool last()
Function moves iterator to the last row of the current result data set This function can only be used...
Definition: result_set.hpp:225
vgi::dbconn::dbi::result_set::first
bool first()
Function moves iterator to the first row of the current result data set This function can only be use...
Definition: result_set.hpp:215
vgi::dbconn::dbi::result_set::column_count
size_t column_count() const
Function returns number of columns of current result set or zero.
Definition: result_set.hpp:164
vgi::dbconn::dbd::sybase::connection
connection - is a class that implements dbi::iconnection interface and represents native database con...
Definition: sybase_driver.hpp:760
vgi::dbconn::dbi::result_set::is_null
bool is_null(size_t col_idx)
Function checks if cell data is NULL by column index or throws an exception if index is invalid...
Definition: result_set.hpp:236
vgi::dbconn::dbi::result_set::prev
bool prev()
Function moves iterator to the previous row of the current result data set This function can only be ...
Definition: result_set.hpp:205
vgi::dbconn::dbi::result_set::row_count
size_t row_count() const
Function returns current row count while iterating through the result set and total number of rows af...
Definition: result_set.hpp:146
vgi::dbconn::dbi::statement::execute
result_set execute()
Function runs last executed SQL statement.
Definition: statement.hpp:117
vgi::dbconn::dbi::result_set
result_set - is a class that manages native driver result_set handle.
Definition: result_set.hpp:78
vgi::dbconn::dbi::statement::proc_retval
int proc_retval()
Function returns stored procedure return value.
Definition: statement.hpp:149
vgi::dbconn::dbi::connection::connect
bool connect()
Function opens connection to the database.
Definition: connection.hpp:97
vgi::dbconn::dbi::statement::prepare
void prepare(const std::string &sql)
Function prepares dynamic SQL statement.
Definition: statement.hpp:99
vgi::dbconn::dbi::result_set::next
bool next()
Function moves iterator to the next row of the current result data set.
Definition: result_set.hpp:195
vgi::dbconn::dbi::connection
connection - is a class that manages native driver connection handle.
Definition: connection.hpp:62