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

Go to the SVN repository for this file.

1 /* $Id: dbapi_cursor.cpp 94775 2021-09-10 22:03:51Z ucko $
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 
27 #include <ncbi_pch.hpp>
28 #include <map>
29 #include "dbapi_cursor.hpp"
30 #include "../dbapi_sample_base.hpp"
31 #include <common/test_assert.h> /* This header must go last */
32 
33 
35 
36 // This program will CREATE a table with 5 rows , UPDATE text field,
37 // PRINT table on screen (each row will begin with <ROW> and ended by </ROW>)
38 // and DELETE table from the database.
39 
40 /////////////////////////////////////////////////////////////////////////////
41 // CDbapiCursorApp::
42 //
43 
45 {
46 public:
47  CDbapiCursorApp(void);
48  virtual ~CDbapiCursorApp(void);
49 
50 protected:
51  virtual int RunSample(void);
52  int RunOneSample(const string& blob_type);
53 
54 protected:
55  string GetTableName(void) const;
56  /// function CreateTable is creating table in the database
57  void CreateTable(const string& table_name,
58  const string& blob_type);
59 };
60 
62 {
63 }
64 
66 {
67 }
68 
69 void
70 CDbapiCursorApp::CreateTable(const string& table_name, const string& blob_type)
71 {
72  string sql;
73 
74  // Drop a table with same name.
75  sql = string(" IF EXISTS (select * from sysobjects WHERE name = '");
76  sql += table_name + "' AND type = 'U') begin ";
77  sql += " DROP TABLE " + table_name + " end ";
78 
79  unique_ptr<CDB_LangCmd> lcmd(GetConnection().LangCmd (sql));
80  lcmd->Send();
81 
82  while ( lcmd->HasMoreResults() ) {
83  unique_ptr<CDB_Result> r(lcmd->Result());
84  }
85 
86  // Create a new table.
87  sql = " create table " + table_name + "( \n";
88  sql += " int_val int not null, \n";
89  sql += " fl_val real not null, \n";
90  sql += " date_val datetime not null, \n";
91  sql += " str_val varchar(255) null, \n";
92  sql += " txt_val " + blob_type + " null, \n";
93  sql += " primary key clustered(int_val) \n";
94  sql += ")";
95 
96  lcmd.reset(GetConnection().LangCmd ( sql ));
97  lcmd->Send();
98 
99  while ( lcmd->HasMoreResults() ) {
100  unique_ptr<CDB_Result> r(lcmd->Result());
101  }
102 
103  lcmd.reset();
104 
105  CDB_Int int_val;
106  CDB_Float fl_val;
107  CDB_DateTime date_val(CTime::eCurrent);
108  CDB_VarChar str_val;
109  CDB_Text pTxt;
110  int i;
111  if (blob_type[0] == 'n') {
113  }
114  pTxt.Append("This is a test string.");
115 
116  unique_ptr<CDB_BCPInCmd> bcp(GetConnection().BCPIn(table_name));
117 
118  // Bind data from a program variables
119  bcp->Bind(0, &int_val);
120  bcp->Bind(1, &fl_val);
121  bcp->Bind(2, &date_val);
122  bcp->Bind(3, &str_val);
123  bcp->Bind(4, &pTxt);
124 
125  for ( i = 0; *file_name[i] != '\0'; ++i ) {
126  int_val = i;
127  fl_val = float(i + 0.999);
128  date_val = date_val.Value();
129 
130  str_val = file_name[i];
131 
132  pTxt.MoveTo(0);
133  bcp->SendRow();
134  }
135  bcp->CompleteBCP();
136 }
137 
138 
139 inline
140 string
142 {
143  return "#crs_test";
144 }
145 
146 // The following function illustrates a usage of dbapi cursor
147 int
148 CDbapiCursorApp::RunOneSample(const string& blob_type)
149 {
150  try {
151  // Change a default size of text(image)
152  GetDriverContext().SetMaxBlobSize(1000000);
153 
154  // Create table in database for the test
155  CreateTable(GetTableName(), blob_type);
156 
157  CDB_Text txt;
158 
159  txt.Append ("This text will replace a text in the table.");
160  for (int i = 0; i < 4000; ++i) {
161  // Embedding U+2019 to test Unicode handling at various layers.
162  // With client_charset=UTF-8, round-tripping via a legacy TEXT
163  // column evidently stores the byte sequence as nominal
164  // ISO-8859-1, from which FreeTDS produces bogus UTF-8
165  // corresponding to U+00E2 U+0080 U+0099. Round-tripping via
166  // VARCHAR(MAX) fails differently: the server converts from
167  // UCS-2 to Win1252 (storing \x92), but passes the result off
168  // as ISO-8859-1, so FreeTDS proceeds to return a UTF-8
169  // sequence corresponding to U+0092. Only NVARCHAR(MAX),
170  // IMAGE, and VARBINARY(MAX) round-trip cleanly. Meanwhile,
171  // the ODBC driver supports none of those types in this context
172  // (at least not in conjunction with CDB_Text), and yields \x92
173  // when told UTF-8 is in use.
174  txt.Append(" Let\xe2\x80\x99s make it long!");
175  }
176 
177  // Example : update text field in the table CursorSample where int_val = 2,
178  // by using cursor
179 
180  unique_ptr<CDB_CursorCmd> upd(GetConnection().Cursor("upd",
181  "select int_val, txt_val from " + GetTableName() +
182  " for update of txt_val", 0));
183 
184  //Open cursor
185  unique_ptr<CDB_Result> crres(upd->Open());
186  //fetch row
187  while ( crres->Fetch() ) {
188  CDB_Int v;
189  crres->GetItem(&v);
190  if ( v.Value() == 2 ) {
191  txt.MoveTo(0); // rewind
192  //update text field
193  upd->UpdateBlob(1, txt);
194  }
195  }
196  //close cursor
197  upd->Close();
198 
199  //print resutls on the screen
200  cout << "\n<RESULTSET blob_type=\"" << blob_type << "\">\n\n";
201  ShowResults("select int_val,fl_val,date_val,str_val,txt_val from " +
202  GetTableName());
203  cout << "</RESULTSET>\n";
204 
205  //Delete table from database
207 
208  } catch ( CDB_Exception& e ) {
210  return 1;
211  }
212 
213  return 0;
214 }
215 
216 int
218 {
219  if (GetServerType() == eMsSql) {
220  SetDatabaseParameter("client_charset", "UTF-8");
221  }
222 
223  int status = RunOneSample("text");
224  if (GetDriverName() == "ftds") {
225  status |= RunOneSample("image");
226  }
227  if (GetServerType() == eMsSql) {
228  if (GetDriverName() != "odbc") {
229  status |= RunOneSample("varchar(max)");
230  status |= RunOneSample("varbinary(max)");
231  status |= RunOneSample("nvarchar(max)");
232  }
233  }
234  return status;
235 }
236 
237 int main(int argc, const char* argv[])
238 {
239  return CDbapiCursorApp().AppMain(argc, argv);
240 }
241 
242 
CDB_Exception –.
Definition: exception.hpp:118
int RunOneSample(const string &blob_type)
string GetTableName(void) const
virtual ~CDbapiCursorApp(void)
void CreateTable(const string &table_name, const string &blob_type)
function CreateTable is creating table in the database
virtual int RunSample(void)
void DeleteTable(const string &table_name)
I_DriverContext & GetDriverContext(void)
Get the driver context (for the driver specified in the command line)
void SetDatabaseParameter(const string &name, const string &value)
Set database connection parameter.
const string & GetDriverName(void) const
Return current driver name.
CDB_Connection & GetConnection(void)
Get connection created using server, username and password specified in the application command line.
EServerType GetServerType(void) const
Return current server type.
void ShowResults(const string &query)
Delete table if it exists ShowResults is printing resuts on screen.
const char * file_name[]
int main(int argc, const char *argv[])
USING_NCBI_SCOPE
static const char table_name[]
Definition: bcp.c:249
static char sql[1024]
Definition: putdata.c:19
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
string
Definition: cgiapp.hpp:687
static CDB_UserHandler & GetDefault(void)
Definition: exception.cpp:628
virtual bool HandleIt(CDB_Exception *ex)=0
Handle the exceptions resulting from a native API call, one-by-one.
virtual bool SetMaxBlobSize(size_t nof_bytes)=0
Set maximal size for BLOB data.
virtual size_t Append(const void *buff, size_t nof_bytes)
Definition: types.cpp:2202
Int4 Value() const
Definition: types.hpp:373
virtual bool MoveTo(size_t byte_number)
Definition: types.cpp:2008
const CTime & Value(void) const
Definition: types.cpp:2491
void SetEncoding(EBulkEnc e)
Definition: types.cpp:2197
@ eBulkEnc_RawUCS2
Definition: types.hpp:87
@ eCurrent
Use current time. See also CCurrentTime.
Definition: ncbitime.hpp:300
int i
double r(size_t dimension_, const Int4 *score_, const double *prob_, double theta_)
Modified on Wed Apr 17 13:09:25 2024 by modify_doxy.py rev. 669887