OTL 4.0, Example 688 (MS SQL Server 2005/2008 BACKUP command)

This example demonstrates how to use the MS SQL Server BACKUP command with OTL. BACKUP command returns right away (after SQLExecDirect() call), so otl_connect::direct_exec() can't be used. Calls to SQLGetDiagRec() and SQLMoreResults() need to be made in order to get the BACKUP command's output diagnostic records.

Source Code

#include <iostream>
using namespace std;

#include <stdio.h>

// function that recognizes BACKUP and DBCC commands, and returns "true".
inline bool sql_statement_with_diag_rec_output(const char* stm_text)
{
if(strncmp(stm_text,"BACKUP",6)==0)
return true;
else if(strncmp(stm_text,"DBCC",4)==0)
return true;
else
return false;
}

// #define OTL_ODBC_MSSQL_2005 // Compile OTL 4/ODBC, MS SQL 2005
#define OTL_ODBC_MSSQL_2008 // Compile OTL 4/ODBC, MS SQL 2008
#define OTL_ODBC_SQL_STATEMENT_WITH_DIAG_REC_OUTPUT sql_statement_with_diag_rec_output

#include <otlv4.h> // include the OTL 4.0 header file

otl_connect db; // connect object
int main()
{
 otl_connect::otl_initialize(); // initialize the environment
 try{

  db.rlogon("scott/tiger@mssql2008",1);
// connect to the database in the "auto-commit" mode
// in order to avoid generating transactions.

otl_nocommit_stream stm;
// the OTL "nocommit" stream doesn't generate any COMMITs,
// which is needed for the BACKUP command.

short int rec_ndx=1; // diagnostic record index
SQLCHAR sql_state[1000]; // "SQL state" buffer
SQLCHAR msg[SQL_MAX_MESSAGE_LENGTH+1]; // diagnostic record / message buffer
int native_error; // native error code
SQLRETURN ret; // return value (for SQLMoreResults() calls)

const char* cmd = "BACKUP DATABASE master TO DISK='d:\\master.bak'";

stm.open(1,cmd,db);
// the stream recognizes the command as an "SQL statement
// with diagnostic record output" with the help of
// the sql_statement_with_diag_rec_output() function.
// the stream executes the statement right away with an
// SQLExecDirect() function call, which, in the case
// of a BACKUP statement returns right away.

bool done;
// get all diagnostic records from the BACKUP command
do{
done=stm.get_next_diag_rec
(rec_ndx,
sql_state,
msg,
sizeof(msg),
native_error);
cout<<msg<<endl;
// BACKUP command generates multiple results,
// so we need to check if there are more results
ret=SQLMoreResults(stm.get_stm_handle());
if(ret==SQL_SUCCESS_WITH_INFO){
done=false;
rec_ndx=1; // in order to start a new diagnostic record
// sequence, the record index needs to be set
// to 1.
}
}while(!done);
stm.close();

 }

 catch(otl_exception& p){ // intercept OTL exceptions
  cerr<<p.msg<<endl; // print out error message
  cerr<<p.sqlstate<<endl; // print out SQLSTATE 
  cerr<<p.stm_text<<endl; // print out SQL that caused the error
  cerr<<p.var_info<<endl; // print out the variable that caused the error
 }

 db.logoff(); // disconnect from the database

 return 0;

}

Output

[Microsoft][SQL Server Native Client 10.0][SQL Server]Processed 376 pages for database 'master', file 'master' on file 2.
[Microsoft][SQL Server Native Client 10.0][SQL Server]Processed 3 pages for database 'master', file 'mastlog' on file 2.
[Microsoft][SQL Server Native Client 10.0][SQL Server]BACKUP DATABASE successfully processed 379 pages in 0.380 seconds (7.781 MB/sec).


Examples ContentsGo Home

Copyright © 1996-2017, Sergei Kuchin, email: skuchin@gmail.com, skuchin@gmail.com .

Permission to use, copy, modify and redistribute this document for any purpose is hereby granted without fee, provided that the above copyright notice appear in all copies. THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.