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 ;)
SQLite3 Sample
Hi! :)
Ciaooo
Sakya
Code: Select all
#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: Select all
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.makSakya
Hi! :)
Just note that if you don't want to use the callback when selecting records you can use this method:
Ciaooo
Sakya
No problem, I had this on my hd. ;)bayo wrote:Yeahh sakya thanksssss :)
Just note that if you don't want to use the callback when selecting records you can use this method:
Code: Select all
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);Sakya
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:
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
When I select data from a table, I need to have the result in array. Sample:
Code: Select all
Table test_tbl
col1 col2 -> col name
data1 data2 -> data storedPD: 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