[Help]SQLite3 Function not working... @_@

[IchigoKurosaki]

I'm in desperate need of help... I can not figure out why this function will not work "sqlite3_get_table" Here is the Source Code and it compiles perfectly fine no errors though when I run it, it outputs nothing...

Main.c:

#include <pspkernel.h>
#include <pspdebug.h>
#include <pspctrl.h>
#include <pspdisplay.h>
#include <sqlite3.h> 
#include <stdio.h>
#include <string.h>

PSP_MODULE_INFO("SQLite Sample", 0, 1, 0);
#define printf pspDebugScreenPrintf

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

int main(int argc, char *argv[]) {
	SceCtrlData pad;
	sqlite3 *db;
	char *zErrMsg = 0;
	int rc;
	char **results = NULL;
	int exit = 0;
	int rows;
	int i;

	pspDebugScreenInit();
	pspDebugScreenClear();

	sceCtrlSetSamplingCycle(0);
	sceCtrlSetSamplingMode(PSP_CTRL_MODE_ANALOG);

    	sceDisplayWaitVblankStart();
    	pspDebugScreenSetXY(0, 0);	

	rc = sqlite3_open("./flashmod.db", &db);
	if( rc ) {
		printf("Can't open database: %s\n", sqlite3_errmsg(db));
		sqlite3_close(db);
	}
	else {
		rc = sqlite3_get_table(db, "SELECT * FROM xmb", &results, &rows, NULL, &zErrMsg);
		if( rc != SQLITE_OK ) {
			printf("SQL error: %s\n", zErrMsg);
			sqlite3_free(zErrMsg);
		}

		for(i = 1; i < rows; i++) {
			printf("   %s: %s\n      By %s", results[i], results[i+rows], results[i+rows*2]);
		}

		sqlite3_close(db);
	}
	
	printf("\nPress X to exit.");

	while(exit == 0) {
		sceCtrlReadBufferPositive(&pad, 1); 
		if (pad.Buttons != 0){
			if (pad.Buttons & PSP_CTRL_CROSS){
				exit = 1;
			} 
		}
		sceDisplayWaitVblankStart();		
	}

	sceKernelExitGame();
	return 0;
}

makefile:

TARGET = sqlite
OBJS = main.o

INCDIR =
CFLAGS = -O2 -G0 -Wall
CXXFLAGS = $(CFLAGS) -fno-exceptions -fno-rtti
ASFLAGS = $(CFLAGS)

LIBDIR =
LDFLAGS =
LIBS = -lpspdebug -lpspsdk -lsqlite3 -lc

EXTRA_TARGETS = EBOOT.PBP
PSP_EBOOT_TITLE = sqlite

PSPSDK=$(shell psp-config --pspsdk-path)
include $(PSPSDK)/lib/build.mak

Also here is the Database file...

Thanks in advance

[trevis]

Looks to me like you are trying to display the results wrong. I didn't test this, but I think it's the issue here.

Documentation for sqlite3_get_table:

int sqlite3_get_table(
  sqlite3*,              /* An open database */
  const char *sql,       /* SQL to be executed */
  char ***resultp,       /* Result written to a char *[]  that this points to */
  int *nrow,             /* Number of result rows written here */
  int *ncolumn,          /* Number of result columns written here */
  char **errmsg          /* Error msg written here */
);
void sqlite3_free_table(char **result);

Note that the results are stored in char ***resultp that was passed to the sqlite3_get_table function. In your code, the rows variable is never set, so the for loop never gets entered. So instead of using a for loop, just print out whatever is inside your char **results variable that you passed to sqlite3_get_table. Hope this helps.

[IchigoKurosaki]

This may help out alittle...

/*
** This next routine is really just a wrapper around sqlite3_exec().
** Instead of invoking a user-supplied callback for each row of the
** result, this routine remembers each row of the result in memory
** obtained from malloc(), then returns all of the result after the
** query has finished.
**
** As an example, suppose the query result where this table:
**
** Name | Age
** -----------------------
** Alice | 43
** Bob | 28
** Cindy | 21
**
** If the 3rd argument were &azResult then after the function returns
** azResult will contain the following data:
**
** azResult[0] = "Name";
** azResult[1] = "Age";
** azResult[2] = "Alice";
** azResult[3] = "43";
** azResult[4] = "Bob";
** azResult[5] = "28";
** azResult[6] = "Cindy";
** azResult[7] = "21";
**
** Notice that there is an extra row of data containing the column
** headers. But the *nrow return value is still 3. *ncolumn is
** set to 2. In general, the number of values inserted into azResult
** will be ((*nrow) + 1)*(*ncolumn).
**
** After the calling function has finished using the result, it should
** pass the result data pointer to sqlite3_free_table() in order to
** release the memory that was malloc-ed. Because of the way the
** malloc() happens, the calling function must not try to call
** free() directly. Only sqlite3_free_table() is able to release
** the memory properly and safely.
**
** The return value of this routine is the same as from sqlite3_exec().
*/
int sqlite3_get_table(
sqlite3*, /* An open database */
const char *sql, /* SQL to be executed */
char ***resultp, /* Result written to a char *[] that this points to */
int *nrow, /* Number of result rows written here */
int *ncolumn, /* Number of result columns written here */
char **errmsg /* Error msg written here */
);

It should make the character string an array... Also I set row here...

rc = sqlite3_get_table(db, "SELECT * FROM xmb", &results, &rows, NULL, &zErrMsg);

It should equal the number of rows in the table XMB... I haven't checked to see if it does or not that may be a good thing to try I don't understand why this is not working

[IchigoKurosaki]

Bump... =/ Still can't figure it out...

[IchigoKurosaki]

This includes, but is not limited to: Promoting competitive websites, crossposting/doubleposting, referral links, and bumping before waiting at least 12 hours for your post to be replied to.

Bump again... Come on I know I'm not the only person who have used SQLite... Please can someone atleast tell me some alternative way of getting the information from a table in a database... T_T

[sakya]

Hi!

Originally Posted by IchigoKurosaki

Please can someone atleast tell me some alternative way of getting the information from a table in a database... T_T

I used this codeand it worked (I just tested sqlite):

pspDebugScreenPrintf("\n");
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");
}

And thecallback:

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);
}

Ciaooo
Sakya