NCBI C++ ToolKit
sdbapi_advanced_features.cpp
Go to the documentation of this file.

Go to the SVN repository for this file.

1 /* $Id: sdbapi_advanced_features.cpp 90055 2020-05-06 18:48:03Z ivanov@NCBI.NLM.NIH.GOV $
2 * ===========================================================================
3 *
4 * PUBLIC DOMAIN NOTICE
5 * National Center for Biotechnology Information
6 *
7 * This software/database is a "United States Government Work" under the
8 * terms of the United States Copyright Act. It was written as part of
9 * the author's official duties as a United States Government employee and
10 * thus cannot be copyrighted. This software/database is freely available
11 * to the public for use. The National Library of Medicine and the U.S.
12 * Government have not placed any restriction on its use or reproduction.
13 *
14 * Although all reasonable efforts have been taken to ensure the accuracy
15 * and reliability of the software and data, the NLM and the U.S.
16 * Government do not and cannot warrant the performance or results that
17 * may be obtained by using this software or data. The NLM and the U.S.
18 * Government disclaim all warranties, express or implied, including
19 * warranties of performance, merchantability or fitness for any particular
20 * purpose.
21 *
22 * Please cite the author in any work or product based on this material.
23 *
24 * ===========================================================================
25 *
26 * Author: Michael Kholodov
27 *
28 * File Description:
29 * String representation of the database character types.
30 *
31 * ===========================================================================
32 */
33 
34 #include <ncbi_pch.hpp>
35 #include <corelib/ncbiapp.hpp>
36 #include <corelib/ncbiargs.hpp>
37 #include <corelib/ncbienv.hpp>
38 
39 #include <dbapi/simple/sdbapi.hpp>
41 
43 
44 
45 /////////////////////////////////////////////////////////////////////////////
46 // CSdbapiTest
47 
49 {
50 private:
51  virtual void Init();
52  virtual int Run();
53  virtual void Exit();
54 
56 };
57 
58 
60 {
61  argList = new CArgDescriptions();
62  argList->SetUsageContext(GetArguments().GetProgramBasename(),
63  "SDBAPI test program");
64  argList->AddDefaultKey("s", "string",
65  "Server name",
66  CArgDescriptions::eString, "DBAPI_MS_TEST");
68 }
69 
70 
72 {
73  const CArgs& args = GetArgs();
74  try {
75  string server = args["s"].AsString();
76 
77  CSDB_ConnectionParam db_params("dbapi://anyone:allowed@/DBAPI_Sample");
78  db_params.Set(CSDB_ConnectionParam::eService, server);
79  CDatabase db(db_params);
80  db.Connect();
81 
82  CQuery query = db.NewQuery();
83  string sql;
84  try {
85  cout << "Creating SelectSample table...";
86  sql = "if exists( select * from sysobjects \
87  where name = 'SelectSample' \
88  AND type = 'U') \
89  begin \
90  drop table SelectSample \
91  end";
92  query.SetSql(sql);
93  query.Execute();
94  query.RequireRowCount(0);
95  query.VerifyDone();
96 
97  sql = "create table SelectSample (\
98  int_val int not null, \
99  fl_val real not null, \
100  date_val smalldatetime not null, \
101  str_val varchar(255) not null, \
102  text_val text not null)";
103  query.SetSql(sql);
104  query.Execute();
105  query.RequireRowCount(0);
106  query.VerifyDone();
107 
108  sql = "insert SelectSample values (1, 2.5, '11/05/2005', 'Test string1', 'TextBlobTextBlobTextBlobTextBlobTextBlob') \
109  insert SelectSample values (2, 3.3, '11/06/2005', 'Test string2', 'TextBlobTextBlobTextBlobTextBlobTextBlob') \
110  insert SelectSample values (3, 4.4, '11/07/2005', 'Test string3', 'TextBlobTextBlobTextBlobTextBlobTextBlob') \
111  insert SelectSample values (4, 5.5, '11/08/2005', 'Test string4', 'TextBlobTextBlobTextBlobTextBlobTextBlob') \
112  insert SelectSample values (5, 6.6, '11/09/2005', 'Test string5', 'TextBlobTextBlobTextBlobTextBlobTextBlob')";
113  query.SetSql(sql);
114  query.Execute();
115  query.RequireRowCount(0);
116  query.VerifyDone();
117 
118  sql = "select int_val, fl_val, date_val, str_val from SelectSample";
119  cout << endl << "Testing simple select..." << endl << sql << endl;
120 
121  query.SetSql(sql);
122  query.Execute();
123  query.RequireRowCount(5);
124 
125  bool show_names = true;
126  for (const auto& row : query) {
127  if (show_names) {
128  for (unsigned i = 1; i <= row.GetTotalColumns(); ++i) {
129  cout << row.GetColumnName(i) << " ";
130  }
131  cout << endl;
132  show_names = false;
133  }
134 
135  for(unsigned i = 1; i <= row.GetTotalColumns(); ++i) {
136  cout << row[i].AsString() << "|";
137  }
138  cout << endl;
139 #if 1
140  cout << row[1].AsInt4() << "|"
141  << row[2].AsFloat() << "|"
142  << row["date_val"].AsString() << "|"
143  << row[4].AsString() << "|"
144  << endl;
145 #endif
146  }
147 
148  query.VerifyDone();
149  cout << "Rows : " << query.GetRowCount() << endl;
150  }
151  catch(CSDB_Exception& e) {
152  cout << "Exception: " << e.what() << endl;
153  }
154 
155  // Testing bulk insert w/o BLOBs
156  cout << endl << "Creating BulkSample table..." << endl;
157  sql = "if exists( select * from sysobjects \
158  where name = 'BulkSample' \
159  AND type = 'U') \
160  begin \
161  drop table BulkSample \
162  end";
163  query.SetSql(sql);
164  query.Execute();
165  query.RequireRowCount(0);
166  query.VerifyDone();
167 
168  sql = "create table BulkSample (\
169  id int not null, \
170  ord int not null, \
171  mode tinyint not null, \
172  date datetime not null)";
173  query.SetSql(sql);
174  query.Execute();
175  query.RequireRowCount(0);
176  query.VerifyDone();
177 
178  try {
179  //Initialize table using bulk insert
180  cout << "Initializing BulkSample table..." << endl;
181  CBulkInsert bi = db.NewBulkInsert("BulkSample", 5);
182  bi.Bind(1, eSDB_Int4);
183  bi.Bind(2, eSDB_Int4);
184  bi.Bind(3, eSDB_Byte);
185  bi.Bind(4, eSDB_DateTime);
186  for (int i = 0; i < 10; ++i) {
187  bi << i << (i * 2) << Uint1(i + 1) << CTime(CTime::eCurrent)
188  << EndRow;
189  }
190  bi.Complete();
191  }
192  catch(...) {
193  throw;
194  }
195 
196  // create a stored procedure
197  sql = "if exists( select * from sysobjects \
198  where name = 'SampleProc' \
199  AND type = 'P') \
200  begin \
201  drop proc SampleProc \
202  end";
203  query.SetSql(sql);
204  query.Execute();
205  query.RequireRowCount(0);
206  query.VerifyDone();
207 
210  sql = "create procedure SampleProc \
211  @id int, \
212  @f float, \
213  @o int output \
214  as \
215  begin \
216  select int_val, fl_val, date_val from SelectSample \
217  where int_val < @id and fl_val <= @f \
218  select @o = 555 \
219  select 2121, 'Parameter @id:', @id, 'Parameter @f:', @f, 'Parameter @o:', @o \
220  print 'Print test output' \
221  return @id \
222  end";
223  }
224  else {
225  sql = "create procedure SampleProc \
226  @id int, \
227  @f float, \
228  @o int output \
229  as \
230  begin \
231  select int_val, fl_val, date_val from SelectSample \
232  where int_val < @id and fl_val <= @f \
233  select @o = 555 \
234  select 2121, 'Parameter @id:', @id, 'Parameter @f:', @f, 'Parameter @o:', @o \
235  print 'Print test output' \
236  raiserror('Raise Error test output', 1, 1) \
237  return @id \
238  end";
239  }
240  query.SetSql(sql);
241  query.Execute();
242  query.RequireRowCount(0);
243  query.VerifyDone();
244 
245  float f = 2.999f;
246 
247  // call stored procedure
248  cout << endl << "Calling stored procedure..." << endl;
249 
250  query.SetParameter("@id", 5);
251  query.SetParameter("@f", f);
252  query.SetParameter("@o", 0, eSDB_Int4, eSP_InOut);
253  query.ExecuteSP("SampleProc");
254  query.RequireRowCount(1, kMax_Auto);
255 
256  for (const auto& row: query.SingleSet()) {
257  if (row[1].AsInt4() == 2121) {
258  cout << row[2].AsString() << " "
259  << row[3].AsString() << " "
260  << row[4].AsString() << " "
261  << row[5].AsString() << " "
262  << row[6].AsString() << " "
263  << row[7].AsString() << " "
264  << endl;
265  }
266  else {
267  cout << row[1].AsInt4() << "|"
268  << row[2].AsFloat() << "|"
269  << row["date_val"].AsString() << "|"
270  << endl;
271  }
272  }
273  query.VerifyDone();
274  cout << "Output param: "
275  << query.GetParameter("@o").AsInt4()
276  << endl;
277  cout << "Status : " << query.GetStatus() << endl;
278 
279  // Reconnect
280  cout << endl << "Reconnecting..." << endl;
281 
282  db.Close();
283  db.Connect();
284 
285  query = db.NewQuery();
286 
287  // create a table
288  cout << endl << "Creating BlobSample table..." << endl;
289  sql = "if exists( select * from sysobjects \
290  where name = 'BlobSample' \
291  AND type = 'U') \
292  begin \
293  drop table BlobSample \
294  end";
295  query.SetSql(sql);
296  query.Execute();
297  query.RequireRowCount(0);
298  query.VerifyDone();
299 
300  sql = "create table BlobSample (\
301  id int null, \
302  blob2 text null, blob text null, unique (id))";
303  query.SetSql(sql);
304  query.Execute();
305  query.RequireRowCount(0);
306  query.VerifyDone();
307 
308  // Write BLOB several times
309  const int COUNT = 5;
310 
311  //Initialize table using bulk insert
312  cout << "Initializing BlobSample table..." << endl;
313  CBulkInsert bi = db.NewBulkInsert("BlobSample", 1);
314  bi.Bind(1, eSDB_Int4);
315  bi.Bind(2, eSDB_Text);
316  bi.Bind(3, eSDB_Text);
317 
318  for (int i = 0; i < COUNT; ++i ) {
319  string im = "BLOB data " + NStr::IntToString(i);
320  bi << i << im << im << EndRow;
321  }
322  bi.Complete();
323 
324  // check if Blob is there
325  cout << "Checking BLOB size..." << endl;
326  query.SetSql("select 'Written blob size' as size, datalength(blob) \
327  from BlobSample where id = 1");
328  query.Execute();
329  query.RequireRowCount(1);
330 
331  for (const auto& row: query.SingleSet()) {
332  cout << row[1].AsString() << ": "
333  << row[2].AsInt4() << endl;
334  }
335 
336  query.VerifyDone();
337 
338  // ExecuteUpdate rowcount test
339  cout << "Rowcount test..." << endl;
340  sql = "update BlobSample set blob ='deleted'";
341  query.SetSql(sql);
342  query.Execute();
343  query.RequireRowCount(0);
344  query.VerifyDone();
345  cout << "Rows updated: " << query.GetRowCount() << endl;
346 
347  // drop BlobSample table
348  cout << "Deleting BlobSample table..." << endl;
349  sql = "drop table BlobSample";
350  query.SetSql(sql);
351  query.Execute();
352  query.RequireRowCount(0);
353  query.VerifyDone();
354  cout << "Done." << endl;
355  }
356  catch(out_of_range) {
357  cout << "Exception: Out of range" << endl;
358  return 1;
359  }
360  catch(exception& e) {
361  cout << "Exception: " << e.what() << endl;
362  return 1;
363  }
364 
365  return 0;
366 }
367 
368 
370 {
371 
372 }
373 
374 
375 /////////////////////////////////////////////////////////////////////////////
376 // MAIN
377 
378 int NcbiSys_main(int argc, ncbi::TXChar* argv[])
379 {
380  return CSdbapiTest().AppMain(argc, argv);
381 }
CArgDescriptions –.
Definition: ncbiargs.hpp:541
CArgs –.
Definition: ncbiargs.hpp:379
Object used to perform bulk-inserting operations to database.
Definition: sdbapi.hpp:816
void Complete()
Complete bulk insert.
Definition: sdbapi.cpp:2399
void Bind(int col, ESDB_Type type)
Bind column for bulk insert.
Definition: sdbapi.cpp:2393
virtual EServerType GetServerType(void) const
Database connection object.
Definition: sdbapi.hpp:1137
void Close(void)
Close database object.
Definition: sdbapi.cpp:2028
void Connect(void)
Explicitly (re)connect to the database server.
Definition: sdbapi.cpp:2016
CQuery NewQuery(void)
Get new CQuery object for this database.
Definition: sdbapi.cpp:2085
CBulkInsert NewBulkInsert(const string &table_name, int autoflush)
Get new CBulkInsert object.
Definition: sdbapi.cpp:2092
Object used to execute queries and stored procedures on the database server and retrieve result sets.
Definition: sdbapi.hpp:232
Convenience class to initialize database connection parameters from URL-like strings and/or applicati...
Definition: sdbapi.hpp:933
CSDB_ConnectionParam & Set(EParam param, const string &value, TSetFlags flags=0)
Set one of the "essential" database connection parameters, unless overridden in a configuration file.
Definition: sdbapi.hpp:1601
@ eService
Named service, interfaces-file alias, or raw server name, per http://ncbi.github.io/cxx-toolkit/pages...
Definition: sdbapi.hpp:1016
Exception class used throughout the API.
Definition: sdbapi.hpp:68
virtual void Exit()
Cleanup on application exit.
virtual void Init()
Initialize the application.
virtual int Run()
Run the application.
CArgDescriptions * argList
CTime –.
Definition: ncbitime.hpp:296
static char sql[1024]
Definition: putdata.c:19
virtual const CArgs & GetArgs(void) const
Get parsed command line arguments.
Definition: ncbiapp.cpp:305
int AppMain(int argc, const char *const *argv, const char *const *envp=0, EAppDiagStream diag=eDS_Default, const char *conf=NcbiEmptyCStr, const string &name=NcbiEmptyString)
Main function (entry point) for the NCBI application.
Definition: ncbiapp.cpp:819
virtual void SetupArgDescriptions(CArgDescriptions *arg_desc)
Setup the command line argument descriptions.
Definition: ncbiapp.cpp:1195
const CNcbiArguments & GetArguments(void) const
Get the application's cached unprocessed command-line arguments.
void SetUsageContext(const string &usage_name, const string &usage_description, bool usage_sort_args=false, SIZE_TYPE usage_width=78)
Set extra info to be used by PrintUsage().
Definition: ncbiargs.cpp:3304
void AddDefaultKey(const string &name, const string &synopsis, const string &comment, EType type, const string &default_value, TFlags flags=0, const string &env_var=kEmptyStr, const char *display_value=nullptr)
Add description for optional key with default value.
Definition: ncbiargs.cpp:2442
@ eString
An arbitrary string.
Definition: ncbiargs.hpp:589
virtual const char * what(void) const noexcept
Standard report (includes full backlog).
Definition: ncbiexpt.cpp:342
uint8_t Uint1
1-byte (8-bit) unsigned integer
Definition: ncbitype.h:99
static const SAutoMax kMax_Auto
Generic stand-in for type-specific kMax_* constants from ncbi_limits.h, useful in any context with ex...
char TXChar
Definition: ncbistr.hpp:172
static string IntToString(int value, TNumToStringFlags flags=0, int base=10)
Convert int to string.
Definition: ncbistr.hpp:5084
@ eCurrent
Use current time. See also CCurrentTime.
Definition: ncbitime.hpp:300
int i
Defines the CNcbiApplication and CAppException classes for creating NCBI applications.
Defines command line argument related classes.
Defines unified interface to application:
double f(double x_, const double &y_)
Definition: njn_root.hpp:188
CBulkInsert & EndRow(CBulkInsert &bi)
Manipulator ending row in the bulk-insert object.
Definition: sdbapi.cpp:2508
@ eSDB_Text
Like Transact-SQL TEXT.
Definition: sdbapi.hpp:184
@ eSDB_DateTime
Like Transact-SQL DATETIME.
Definition: sdbapi.hpp:182
@ eSDB_Int4
Like Transact-SQL INT (signed 32-bit integer)
Definition: sdbapi.hpp:175
@ eSDB_Byte
Like Transact-SQL BYTE (unsigned 8-bit integer)
Definition: sdbapi.hpp:173
@ eSP_InOut
Parameter can be returned from stored procedure.
Definition: sdbapi.hpp:196
int NcbiSys_main(int argc, ncbi::TXChar *argv[])
#define row(bind, expected)
Definition: string_bind.c:73
static string query
Modified on Tue Apr 16 20:12:08 2024 by modify_doxy.py rev. 669887