Regina Falangi tells All...Tonight at 10pm....

If you are a new Irrlicht Engine user, and have a newbie-question, this is the forum for you. You may also post general programming questions here.
Post Reply
deprecated
Posts: 62
Joined: Fri Jan 07, 2005 4:37 pm
Location: California

Regina Falangi tells All...Tonight at 10pm....

Post by deprecated »

Ok, cheap trick to get you in here... 8)
but I need help, please....

My class is a basic wrapper around SQLite3 to allow queries to be sent and results retrieved... the problem that I am having is trying to get the results back in a usable form... (ie; wchar_t*, or array?)

It works so far, and is fast....I am able to send a query from Irrlicht, but cannot retieve results yet, execpt with stdout.

I am planning on putting this class up for everyone to use or look at as soon as I can figure out how to complete it...

Your help is much appreciated....

Oh, and you'll need SQLite3 from
http://www.sqlite.org/


The code is the very pretty, but all i need help with is how to convert my char* into wchar_t* or an array, with all the correct results...

Please take a look...

ISQLite.h

Code: Select all


//required libs
#include <iostream>
#include <cstdlib>
#include <cstdio>
#include <string>
#include <vector>
#include <sqlite3.h>



#define SQLITE_OK_DESC "Successful result";
#define SQLITE_ERROR_DESC "SQL error or missing database";
#define SQLITE_INTERNAL_DESC "An internal logic error in SQLite";
#define SQLITE_PERM_DESC "Access permission denied";
#define SQLITE_ABORT_DESC "Callback routine requested an abort";
#define SQLITE_BUSY_DESC "The database file is locked";
#define SQLITE_LOCKED_DESC "A table in the database is locked";
#define SQLITE_NOMEM_DESC "A malloc() failed";
#define SQLITE_READONLY_DESC "Attempt to write a readonly database";
#define SQLITE_INTERRUPT_DESC "Operation terminated by sqlite3_interrupt()";
#define SQLITE_IOERR_DESC "Some kind of disk I/O error occurred";
#define SQLITE_CORRUPT_DESC "The database disk image is malformed";
#define SQLITE_NOTFOUND_DESC "(Internal Only) Table or record not found";
#define SQLITE_FULL_DESC "Insertion failed because database is full";
#define SQLITE_CANTOPEN_DESC "Unable to open the database file";
#define SQLITE_PROTOCOL_DESC "Database lock protocol error";
#define SQLITE_EMPTY_DESC "Database is empty";
#define SQLITE_SCHEMA_DESC "The database schema changed";
#define SQLITE_TOOBIG_DESC "Too much data for one row of a table";
#define SQLITE_CONSTRAINT_DESC "Abort due to contraint violation";
#define SQLITE_MISMATCH_DESC "Data type mismatch";
#define SQLITE_MISUSE_DESC "Library used incorrectly";
#define SQLITE_NOLFS_DESC "Uses OS features not supported on host";
#define SQLITE_AUTH_DESC "Authorization denied";
#define SQLITE_FORMAT_DESC "Auxiliary database format error";
#define SQLITE_RANGE_DESC "2nd parameter to sqlite3_bind out of range";
#define SQLITE_NOTADB_DESC "File opened that is not a database file";
#define SQLITE_ROW_DESC "sqlite3_step() has another row ready";
#define SQLITE_DONE_DESC "sqlite3_step() has finished executing";

//namespace irr
//{
//namespace io
//{

	class ISQLite
	{

		public:
			//wchar_t* queryResults;    //Query Results
  std::vector<std::string> vcol_head;
  std::vector<std::string> vdata;

  			ISQLite (const std::string tablename = "test.db"); //constructor

                        virtual ~ISQLite();  //destructor

			virtual int exec(std::string s_exec);  //Sends Query s_exec to SQL db, sends results to callback, returns Result Code.

                	virtual wchar_t* getResults();   //returns results from last query. Returns NULL string if no results found.

                        virtual void setResults(wchar_t* queryRow); //sets queryResults with each row.

                        virtual int SQLSelect();	//sends select queries to SQL db. Returns Result Code.

			virtual int SQLUpdate();	//sends Update Queries to SQL db. Returns Result Code.

			virtual int SQLInsert();	//sends Insert queries to SQL db. Returns Result Code.

			virtual int SQLDelete();	//sends Delete queries to SQL db. Returns Result Code.

                        virtual char* resultMsg( int t_rc );	//returns SQL error message String.

                private:

			//static int callback(void *NotUsed, int argc, char **argv, char **azColName);  //results callback

			sqlite3* db;		//Database object
			char* zErrMsg;  	//Internal error message
			int rc; 		//Result Code (success = 0,error <= 1)
			int nrow,ncol;
			int db_open;
                        char **result;

	};


//}  //end io
//} //end irr
ISQLite.cpp

Code: Select all

#include "ISQLite.h"
#include <cstring>

//namespace irr
//{
//namespace io
//{

  ISQLite::ISQLite (const std::string tablename)
  {
		rc = sqlite3_open(tablename.c_str(), &db);
	if( rc ){
		std::cout << "Can't open database:" << sqlite3_errmsg(db) << "\n";
		rc = sqlite3_close(db);
	}
  }

/*old stuff ... delete me...
  int ISQLite::callback(void *NotUsed, int argc, char **argv, char **azColName){
    char* tmp;
    //const char* cTmp;
    NotUsed=0;
    int i;
    for(i=0; i<argc; i++){
    // std::cout << azColName[i] << "=" <<  argv[i] << "\n";
     //argv[i] =? argv[i] : "NULL"
     sprintf(tmp, "%s = %s\n", azColName[i], argv[i] );
std::cout << tmp;

     //const char* cTmp = (const char *)tmp;
    queryResults = L"testtest/n";
    // queryResults = strcat(queryResults, (const char *)tmp);

     ;
     }
   //  std::cout << queryResults;
    // std::cout << "\n";

    return 0;
  }
*/


  void ISQLite::setResults(wchar_t* queryRow)
  {

 	//queryResults = queryRow;
  }


  char* ISQLite::resultMsg(int t_rc){
     char* er;
     switch (rc){
 	case SQLITE_OK:
 		er =  SQLITE_OK_DESC;break;
 	case SQLITE_ERROR:
 		er =  "SQL syntax error or missing database";break;
 	case SQLITE_INTERNAL:
   		er =  "An internal logic error in SQLite";break;
 	case SQLITE_PERM:
     		er =  "Access permission denied";break;
 	case SQLITE_ABORT:
      		er =  "Callback routine requested an abort";break;
 	case SQLITE_BUSY:
       		er =  "The database file is locked";break;
 	case SQLITE_LOCKED:
     		er =  "A table in the database is locked";break;
 	case SQLITE_NOMEM:
		er =  "A malloc() failed";break;
 	case SQLITE_READONLY:
     		er =  "Attempt to write a readonly database";break;
 	case SQLITE_INTERRUPT:
   		er =  "Operation terminated by sqlite_interrupt()";break;
 	case SQLITE_IOERR:
       		er =  "Some kind of disk I/O error occurred";break;
 	case SQLITE_CORRUPT:
    		er =  "The database disk image is malformed";break;
 	case SQLITE_NOTFOUND:
    		er =  "(Internal Only) Table or record not found";break;
 	case SQLITE_FULL:
      		er =  "Insertion failed because database is full";break;
 	case SQLITE_CANTOPEN:
    		er =  "Unable to open the database file";break;
 	case SQLITE_PROTOCOL:
   		er =  "Database lock protocol error";break;
 	case SQLITE_EMPTY:
     		er =  "(Internal Only) Database table is empty";break;
 	case SQLITE_SCHEMA:
     		er =  "The database schema changed";break;
 	case SQLITE_TOOBIG:
      		er =  "Too much data for one row of a table";break;
 	case SQLITE_CONSTRAINT:
  		er =  "Abort due to contraint violation";break;
 	case SQLITE_MISMATCH:
    		er =  "Data type mismatch";break;
 	case SQLITE_MISUSE:
      		er =  "Library used incorrectly";break;
 	case SQLITE_NOLFS:
       		er =  "Uses OS features not supported on host";break;
 	case SQLITE_AUTH:
      		er =  "Authorization denied";break;
 	case SQLITE_ROW:
	      	er =  "sqlite_step() has another row ready";break;
 	case SQLITE_DONE:
  	    	er = "sqlite_step() has finished executing";break;
 	default:
	 	er="Unknown error.";break;
     };
     return er;

  }
/*old func.
  int ISQLite::exec(std::string s_exec) {

		rc = sqlite3_exec(db, s_exec.c_str(),ISQLite::callback, 0, &zErrMsg);

	if( rc!=SQLITE_OK ){
        	std::cout << "\nError:" << resultMsg(rc) << "\nReturned:" << zErrMsg << std::endl;
	}

	return rc;
  }
*/

  int ISQLite::exec(std::string s_exec) {
      rc = sqlite3_get_table(
			db,              /* An open database */
			s_exec.c_str(),       /* SQL to be executed */
			&result,       /* Result written to a char *[]  that this points to */
			&nrow,             /* Number of result rows written here */
			&ncol,          /* Number of result columns written here */
			&zErrMsg          /* Error msg written here */
			);

     // if(vcol_head.size() > 0) {vcol_head.clear();}
     // if(vdata.size()>0) {vdata.clear();}
    std::cout << "SQL RESULTS\n";
     if( rc == SQLITE_OK ){
      for(int i=0; i < ncol; ++i)
	//vcol_head.push_back(result[i]); /* First row heading */
        std::cout << result[i].c_str() <<"\n";
      for(int i=0; i < ncol*nrow; ++i)
	//vdata.push_back(result[ncol+i]);
        std::cout << result[ncol+i].c_str() <<"\n";
     }
     sqlite3_free_table(result);
      return rc;
  }

  ISQLite::~ISQLite()		//destructor, clean up sql
  {
		sqlite3_close(db);  //close database
  }

  wchar_t* ISQLite::getResults()
  {
   //Get SQL results from callback and return string/array.
    return 0;  //queryResults;
  }

  int ISQLite::SQLSelect()
  {
   //SQL SELECT statement

  	return 0;
  }

  int ISQLite::SQLUpdate()
  {
  //SQL UPDATE
  //currently does nothing...
	return 0;
  }

  int ISQLite::SQLInsert()
  {
  //SQL INSERT
  //currently does nothing...
	return 0;
  }

  int ISQLite::SQLDelete()
  {
  //SQL DELETE
  //currently does nothing...
	return 0;
  }

//} //end io
//}//end irr
main.cpp

Code: Select all

 #include <irrlicht.h>
 //#include "irrsqlite.h"
 #include "CSQLite.h"

using namespace irr;
using namespace gui;
using namespace core;

int main()
{
   IrrlichtDevice *device = createDevice( video::EDT_OPENGL,
core::dimension2d<s32>(800,600), 32, false, true, false );

   video::IVideoDriver* driver = device->getVideoDriver();
   scene::ISceneManager* smgr = device->getSceneManager();

IGUIEnvironment* env = device->getGUIEnvironment();
 env->addButton(rect<s32>(10,100,100,130), 0, 101, L"Exec Query");
//IGUIFont* font = env->getFont("../../media/fonthaettenschweiler.bmp");

wchar_t* querytext = L"select * from t1;";
IGUIEditBox* querybox = 0;
        querybox = env->addEditBox(querytext,rect<s32>(10, 150, 250, 180),1,0,-1);

//stringw queryBoxText = querybox->getText();
stringc cQueryText = querybox->getText();

IGUIStaticText* queryResultsBox=0;

queryResultsBox = env->addStaticText(L"no results", rect<s32>(10,190,250,380), 1,0,0,-1);
//queryResultsBox->setText(queryResults);
queryResultsBox->setOverrideColor(video::SColor(255,205,200,200));

//wchar_t* tmp;

  //run query-

  ISQLite::ISQLite* sql = new ISQLite::ISQLite("test.db");
  sql->exec(cQueryText.c_str());
  //stringc cQueryResults = sql->getResults();
  //stringw* wQueryResults = sql->getResults();
  //stringc cQueryResults = wQueryResults;
   wchar_t* sqlResults = sql->getResults();
   //sqlResults = sqlResults.c_str();

 // if (wQueryResults.c_str()){
    queryResultsBox->setText(sqlResults);
 // }else{
  //  queryResultsBox->setText(L"no results");
  //}
  //tmp = sql->getResults();
  //std::cout << tmp;

   while(device->run())
   {
      driver->beginScene( true, true, 0 );

	smgr->drawAll();
	env->drawAll();

      driver->endScene();

        device->setWindowCaption( L"Sql Test");

   }

   device->drop();
   return 0;
} 
Last edited by deprecated on Tue Feb 15, 2005 11:16 pm, edited 1 time in total.
Spintz
Posts: 1688
Joined: Thu Nov 04, 2004 3:25 pm

Post by Spintz »

Hey, I know you'd sent me this code b4, but I forgot all about it, sorry! :(

Anyways, I have a class I wrote a few years ago that was a wrapper around ODBC for Linux/Windows and would return results from queries, let me see if I can dig it up for you.

I believe the way I did it, was had a char** row and char** col and for each query, I'd allocate the memory for the results and assign the row/char arrays of char*'s. It'd be easier to use STL or irrlicht list/array/stringc or stringw now.

I like that way, because you can retrieve a query, row by row, column by column, or even a specific value for a row/col. The exec should also return how many rows/columns were returned in the query, BTW.

Hopefully I can find that code, was a long time ago! :)
Image
deprecated
Posts: 62
Joined: Fri Jan 07, 2005 4:37 pm
Location: California

Post by deprecated »

Can anyone help with this?

It is a simple c++ syntax problem... I dont know what I am doing enough to pass the values from my class back to main() in a unable way... (ie; wchar_t, or array)

It would probably take 5 minutes....

Please help!

Thanks :D
Post Reply