C++ CppSQLite3DB::compileStatement方法代码示例

//##ModelId=474D30760272
bool CClip_ImportExport::ExportToSqliteDB(CppSQLite3DB &db)
{
	bool bRet = false;
	try
	{
		//Add to Main Table
		m_Desc.Replace(_T("'"), _T("''"));
		db.execDMLEx(_T("insert into Main values(NULL, %d, '%s');"), CURRENT_EXPORT_VERSION, m_Desc);
		long lId = (long)db.lastRowId();

		//Add to Data table
		CClipFormat* pCF;
		CppSQLite3Statement stmt = db.compileStatement(_T("insert into Data values (NULL, ?, ?, ?, ?);"));

		for(int i = m_Formats.GetSize()-1; i >= 0 ; i--)
		{
			pCF = & m_Formats.ElementAt(i);

			stmt.bind(1, lId);
			stmt.bind(2, GetFormatName(pCF->m_cfType));
			long lOriginalSize = GlobalSize(pCF->m_hgData);
			stmt.bind(3, lOriginalSize);

			const unsigned char *Data = (const unsigned char *)GlobalLock(pCF->m_hgData);
			if(Data)
			{
				//First compress the data
				long lZippedSize = compressBound(lOriginalSize);
				Bytef *pZipped = new Bytef[lZippedSize];
				if(pZipped)
				{				
					int nZipReturn = compress(pZipped, (uLongf *)&lZippedSize, (const Bytef *)Data, lOriginalSize);
					if(nZipReturn == Z_OK)
					{
						stmt.bind(4, pZipped, lZippedSize);
					}

					delete []pZipped;
					pZipped = NULL;
				}
			}
			GlobalUnlock(pCF->m_hgData);

			stmt.execDML();
			stmt.reset();

			m_Formats.RemoveAt(i);
		}

		bRet = true;
	}
	CATCH_SQLITE_EXCEPTION_AND_RETURN(false)

	return bRet;
}

//------------------------------------------------------------------------------
/// \brief
//------------------------------------------------------------------------------
void SqBcList::impl::CreateInsertStmt ()
{
  CppSQLite3DB *f = sqLiteDbForPackage(m_pack);
  ASSERT(f);
  if (!f) return;
  std::stringstream ss;
  ColMap &colMap(iSpTableCols());
  CStr tabName = m_pack->GetPackage()->PackageName();
  std::vector<CStr> &colStrs = colMap[tabName].first;
  tabName = "LST_STRESS_PERIODS";
  ss << "INSERT INTO " << tabName << " VALUES(?";
  for (size_t i=1; i<colStrs.size(); ++i) ss << ", ?";
  ss << ");";
  m_stmt_insert = f->compileStatement(ss.str().c_str());
} // SqBcList::impl::CreateInsertStmt

//.........这里部分代码省略.........
        // use CppSQLiteTable::setRow() method
        //////////////////////////////////////////////////////////////////
        cout << endl << "getTable() test" << endl;
        CppSQLite3Table t = db->getTable("select * from emp order by 1;");
        
        for (fld = 0; fld < t.numFields(); fld++)
        {
            cout << t.fieldName(fld) << "|";
        }
        cout << endl;
        for (int row = 0; row < t.numRows(); row++)
        {
            t.setRow(row);
            for (int fld = 0; fld < t.numFields(); fld++)
            {
                if (!t.fieldIsNull(fld))
                    cout << t.fieldValue(fld) << "|";
                else
                    cout << "NULL" << "|";
            }
            cout << endl;
        }
        
        ////////////////////////////////////////////////////////////////////
        // Test CppSQLiteBinary by storing/retrieving some binary data, checking
        // it afterwards to make sure it is the same
        //////////////////////////////////////////////////////////////////
        cout << endl << "Binary data test" << endl;
        db->execDML("create table bindata(desc char(10), data blob);");
        
        unsigned char bin[256];
        CppSQLite3Binary blob;
        
        for (i = 0; i < sizeof bin; i++)
        {
            bin[i] = i;
        }
        
        blob.setBinary(bin, sizeof bin);
        
        bufSQL.format("insert into bindata values ('testing', %Q);", 
                      blob.getEncoded());
        db->execDML(bufSQL);
        cout << "Stored binary Length: " << sizeof bin << endl;
        
        q = db->execQuery("select data from bindata where desc = 'testing';");
        
        if (!q.eof())
        {
            blob.setEncoded((unsigned char*)q.fieldValue("data"));
            cout << "Retrieved binary Length: " 
            << blob.getBinaryLength() << endl;
        }
        
        const unsigned char* pbin = blob.getBinary();
        for (i = 0; i < sizeof bin; i++)
        {
            if (pbin[i] != i)
            {
                cout << "Problem: i: ," << i << " bin[i]: " 
                << pbin[i] << endl;
            }
        }
        
        /////////////////////////////////////////////////////////
        // Pre-compiled Statements Demo
        /////////////////////////////////////////////////////////////
        cout << endl << "Transaction test, creating " << nRowsToCreate;
        cout << " rows please wait..." << endl;
        db->execDML("drop table emp;");
        db->execDML("create table emp(empno int, empname char(20));");
        tmStart = time(0);
        db->execDML("begin transaction;");
        
        CppSQLite3Statement stmt = db->compileStatement(
                                                      "insert into emp values (?, ?);");
        for (i = 0; i < nRowsToCreate; i++)
        {
            char buf[16];
            sprintf(buf, "EmpName%06d", i);
            stmt.bind(1, i);
            stmt.bind(2, buf);
            stmt.execDML();
            stmt.reset();
        }
        
        db->execDML("commit transaction;");
        tmEnd = time(0);
        
        cout << db->execScalar("select count(*) from emp;") 
        << " rows in emp table in ";
        cout << tmEnd-tmStart << " seconds (that was even faster!)" << endl;
        cout << endl << "End of tests" << endl;
    }
    catch (CppSQLite3Exception& e)
    {
        cerr << e.errorCode() << ":" << e.errorMessage() << endl;
    }
    
}

//.........这里部分代码省略.........


		////////////////////////////////////////////////////////////////////////////////
		// Demonstrate multi-statement DML
		// Note that number of rows affected is only from the last statement
		// when multiple statements are used
		////////////////////////////////////////////////////////////////////////////////
        cout << endl << "Multi-Statement execDML()" << endl;
		const char* szDML = "insert into parts values(5, 'part5', 500, 5.55);"
							"insert into parts values(6, 'part6', 600, 6.66);"
							"insert into parts values(7, 'part7', 700, 7.77);";
		int nRows = db.execDML(szDML);
		cout << endl << nRows << " rows affected" << endl;
        cout << db.execScalar("select count(*) from parts;") << " rows in parts table" << endl;
		            szDML = "delete from parts where no = 2;"
							"delete from parts where no = 3;";
		nRows = db.execDML(szDML);
        cout << endl << nRows << " rows affected" << endl;
        cout << db.execScalar("select count(*) from parts;") << " rows in parts table" << endl;


		////////////////////////////////////////////////////////////////////////////////
		// Demonstrate new typing system & BLOBS
		// ANy data can be stored in any column
		////////////////////////////////////////////////////////////////////////////////
        cout << endl << "Data types and BLOBs()" << endl;
        db.execDML("create table types(no int, "
				"name char(20), qty float, dat blob);");
        db.execDML("insert into types values(null, null, null, null);");
        db.execDML("insert into types values(1, 2, 3, 4);");
        db.execDML("insert into types values(1.1, 2.2, 3.3, 4.4);");
        db.execDML("insert into types values('a', 'b', 'c', 'd');");

        CppSQLite3Statement stmt = db.compileStatement("insert into types values(?,?,?,?);");
        unsigned char buf[256];
		memset(buf, 1, 1); stmt.bind(1, buf, 1);
		memset(buf, 2, 2); stmt.bind(2, buf, 2);
		memset(buf, 3, 3); stmt.bind(3, buf, 3);
		memset(buf, 4, 4); stmt.bind(4, buf, 4);
		stmt.execDML();
        cout << db.execScalar("select count(*) from types;") << " rows in types table" << endl;

        q = db.execQuery("select * from types;");
        while (!q.eof())
        {
			for (int i = 0; i < q.numFields(); i++)
			{
				switch (q.fieldDataType(i))
				{
				case SQLITE_INTEGER  : cout << "SQLITE_INTEGER|"; break;
				case SQLITE_FLOAT    : cout << "SQLITE_FLOAT  |"; break;
				case SQLITE_TEXT     : cout << "SQLITE_TEXT   |"; break;
				case SQLITE_BLOB     : cout << "SQLITE_BLOB   |"; break;
				case SQLITE_NULL     : cout << "SQLITE_NULL   |"; break;
				default: cout << "***UNKNOWN TYPE***"; break;
				}
			}
            q.nextRow();
			cout << endl;
        }

        nRows = db.execDML("delete from types where no = 1 or no = 1.1 or no = 'a' or no is null;");
		cout << endl << nRows << " rows deleted, leaving binary row only" << endl;

        q = db.execQuery("select * from types;");
		const unsigned char* pBlob;