forums.ps2dev.org Forum Index forums.ps2dev.org
Homebrew PS2, PSP & PS3 Development Discussions
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

SQLite3 Sample

 
Post new topic   Reply to topic    forums.ps2dev.org Forum Index -> PSP Development
View previous topic :: View next topic  
Author Message
bayo



Joined: 11 Feb 2008
Posts: 4
Location: UK
PostPosted: Mon Feb 11, 2008 5:12 am    Post subject: SQLite3 Sample Reply with quote
Hello all guys,

I'm developing a mmorpg game for PSP and I want to save the character data in a DB.

I saw SQLite3 for PSP and I try to use it, but I can't insert values into a table. I know how to create the db and create a table, but I can't put this query in the DB: INSERT INTO pj (id, lvl, hp, mp) VALUES (1, 1, 100, 60)

For this, I need a example with creating table, insert values into table and selecting data.

Thanks in advance ;)
Back to top
View user's profile Send private message
sakya



Joined: 28 Apr 2006
Posts: 190
PostPosted: Mon Feb 11, 2008 8:29 am    Post subject: Reply with quote
Hi! :)

Code:
#include <pspkernel.h>
#include <pspctrl.h>
#include <psphprm.h>
#include <pspdebug.h>
#include <psppower.h>
#include <stdio.h>
#include <string.h>
#include <pspsdk.h>
#include <unistd.h>
#include <sqlite3.h>

#define RGB(r, g, b) ((b << 16) | (g << 8) | r)
#define BLACK RGB(0, 0, 0)
#define WHITE RGB(255, 255, 255)

PSP_MODULE_INFO("SQLite Test", 0, 1, 0);
PSP_MAIN_THREAD_ATTR(THREAD_ATTR_USER);
//PSP_HEAP_SIZE_KB(22000);

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// Callbacks:
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
/* Exit callback */
int exit_callback(int arg1, int arg2, void *common) {
    sceKernelExitGame();
    return 0;
}

/* Callback thread */
int CallbackThread(SceSize args, void *argp) {
    int cbid;

    cbid = sceKernelCreateCallback("Exit Callback", exit_callback, NULL);
    sceKernelRegisterExitCallback(cbid);

    sceKernelSleepThreadCB();

    return 0;
}

/* Sets up the callback thread and returns its thread id */
int SetupCallbacks(void) {
    int thid = 0;

    thid = sceKernelCreateThread("update_thread", CallbackThread, 0x11, 0xFA0, 0, 0);
    if(thid >= 0) {
            sceKernelStartThread(thid, 0, 0);
    }

    return thid;
}

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// SQLite callback:
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
int SQLiteCallback(void *NotUsed, int argc, char **argv, char **azColName){
    int i;
    for(i=0; i<argc;i++){
        pspDebugScreenPrintf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    pspDebugScreenPrintf("\n");
   return(0);
}

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// Main:
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
int main() {
   char dbFile[257] = "";
    char sql[1000] = "";
   int retValue;
   char *zErr = 0;

   sceCtrlSetSamplingCycle(0);
   sceCtrlSetSamplingMode(PSP_CTRL_MODE_ANALOG);
   SetupCallbacks();

    sprintf(dbFile, "./test.db");

   pspDebugScreenInit();
   pspDebugScreenSetTextColor(WHITE);
   pspDebugScreenSetBackColor(BLACK);

   //Create an empty db:
   pspDebugScreenSetXY(0, 0);
   pspDebugScreenPrintf("Creating empty database %s...", dbFile);
   sqlite3 *db;
   retValue = sqlite3_open(dbFile, &db);
    if (retValue){
      pspDebugScreenPrintf("\n");
      pspDebugScreenPrintf("Error creating database: %s", sqlite3_errmsg(db));
   }else{
        pspDebugScreenPrintf("OK");
      //Create table:
      pspDebugScreenPrintf("\n");
      pspDebugScreenPrintf("Creating a table...");
      sprintf(sql, "create table test_table (column_1 varchar(2), column_2 numeric(3,0));");
      retValue = sqlite3_exec(db, sql, SQLiteCallback, 0, &zErr);
      if (retValue != SQLITE_OK){
         pspDebugScreenPrintf("\n");
         pspDebugScreenPrintf("Error creating table: %s", zErr);
            sqlite3_free(zErr);
      }else{
            pspDebugScreenPrintf("OK");
         //Insert 100 records:
          pspDebugScreenPrintf("\n");
            pspDebugScreenPrintf("Inserting 100 records...");
            int i;
            int error = 0;
            for (i=0; i<100; i++){
                sprintf(sql, "insert into test_table(column_1, column_2) values('%i', %i)", i, i);
                retValue = sqlite3_exec(db, sql, SQLiteCallback, 0, &zErr);
                if (retValue != SQLITE_OK){
                    pspDebugScreenPrintf("\n");
                    pspDebugScreenPrintf("Error inserting record %i: %s", i, zErr);
                    error++;
                }
            }
            if (!error){
                pspDebugScreenPrintf("OK");
                //Reading 100 records:
                pspDebugScreenPrintf("\n");
                pspDebugScreenPrintf("Reading 100 records...");
                sprintf(sql, "Select * from test_table order by column_1");
                retValue = sqlite3_exec(db, sql, SQLiteCallback, 0, &zErr);
                if (retValue != SQLITE_OK){
                    pspDebugScreenPrintf("\n");
                    pspDebugScreenPrintf("Error reading records: %s", zErr);
                    error++;
                }else{
                    pspDebugScreenPrintf("OK");
                }
            }
      }
   }
   //Close db:
    pspDebugScreenPrintf("\n");
    pspDebugScreenPrintf("Closing database");
   sqlite3_close(db);

   pspDebugScreenSetXY(0, 27);
   pspDebugScreenPrintf("Press X to quit");

   SceCtrlData pad;
   while (1){
      sceCtrlReadBufferPositive(&pad, 1);
      if (pad.Buttons & PSP_CTRL_CROSS){
         break;
      }
   }
   sceKernelExitGame();
   return(0);
}


Code:
TARGET = SQLite
OBJS = main.o
CFLAGS = -O2 -G0 -Wall
CXXFLAGS = $(CFLAGS) -fno-exceptions -fno-rtti
ASFLAGS = $(CFLAGS)
LIBDIR =

BUILD_PRX=1
LIBS = -lpsppower -lsqlite3
LDFLAGS =
EXTRA_TARGETS = EBOOT.PBP
PSP_EBOOT_TITLE = SQLite test
PSPSDK=$(shell psp-config --pspsdk-path)
include $(PSPSDK)/lib/build.mak


Ciaooo
Sakya
Back to top
View user's profile Send private message Visit poster's website
bayo



Joined: 11 Feb 2008
Posts: 4
Location: UK
PostPosted: Tue Feb 12, 2008 1:27 am    Post subject: Reply with quote
Yeahh sakya thanksssss :) I can continue my game now! ^^

It works perfectly hehe

Ciaoo :P
Back to top
View user's profile Send private message
sakya



Joined: 28 Apr 2006
Posts: 190
PostPosted: Tue Feb 12, 2008 2:24 am    Post subject: Reply with quote
Hi! :)
bayo wrote:
Yeahh sakya thanksssss :)

No problem, I had this on my hd. ;)
Just note that if you don't want to use the callback when selecting records you can use this method:
Code:
sqlite3_stmt *stmt;

int retValue = sqlite3_prepare(db, "Select * from tableName", -1, &stmt, 0);
if (retValue != SQLITE_OK){
    ERROR!
}

while(sqlite3_step(stmt) == SQLITE_ROW) {
    PROCESS EACH ROW
}
sqlite3_finalize(stmt);


Ciaooo
Sakya
Back to top
View user's profile Send private message Visit poster's website
bayo



Joined: 11 Feb 2008
Posts: 4
Location: UK
PostPosted: Tue Feb 12, 2008 2:55 am    Post subject: Reply with quote
Ok, it works, but now I need a more difficult thing:

When I select data from a table, I need to have the result in array. Sample:

Code:

Table test_tbl
col1        col2        -> col name
data1      data2     -> data stored


When I select * from test_tbl I want to have this: array[0] = data1 and array[1] = data2. It's possible? :P

PD: Other question hehe: It's needed to have the Select query in a while??? I say this because if I do a select and with the callback it writes the result to the screen, it's only 1 sec in screen, after this, the result disappear. But if I have the select query in a while, the result is always in screen, but it's always reading from MS.

Edit: Solved! ^^

Thanksss
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    forums.ps2dev.org Forum Index -> PSP Development All times are GMT + 10 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group