Re: [sqlite] Update fail without ERRORS
SOLVED! Thanks to all and expecially to Dennis I've found MY MISTAKE!!! :-) My head is safe ;-) The error was in my_custom_function. Inside this function I need to query the same table to retrieve some values needed to calculate the float value. Inside my_custom_function I use the prepare ,step, finalize functions, but I forgot to use sqlite3_finalize before to exit, so the table was locked, or better, was locked the same RECORD i need to update, because the where statement is the same. In this conditions the update fail, but the strange thing is that I don't receive any error. The result of update statement is SQLITE_OK (this is very strange, it's normal or it's a sqlite bug ??). Follow I attach my code "CORRECT". Dennis, please can You check the correctness ? float expander::upd_ai_val(int Address, int Port) { int rc; char sql[2048],[512]; char *zErrMsg = 0; char *zSql; pkt rcvd_frm; long int value; time_t rawtime; float valore; char val[25]; time(&rawtime); printf("Address=%d - Port=%d\n",Address,Port); inp_ana_inp_req(Address, Port, &rcvd_frm); value = (rcvd_frm.arg[1]*256 + rcvd_frm.arg[2]); valore = my_custom_function(Address, Port, value); //valore=value; if (debug > 1) {printf("Arg[1]=%x - Arg[2]=%x - Valore letto=%ld\n", rcvd_frm.arg[1],rcvd_frm.arg[2],value);} zSql = sqlite3_mprintf ("UPDATE inputai SET Value=%.2f, Timestamp=%d WHERE Address=%d and PortNumber=%d;",valore , Port,Address, Port); if (debug > 1) {printf ("upd_ai_val:%s\n",sql);} printf("upd_ai_val:%s\n",zSql); rc = sqlite3_exec(db,zSql, NULL, NULL, &zErrMsg ); if( rc!=SQLITE_OK ) { fprintf(stderr, "SQL error: %s\n", zErrMsg); sqlite3_free (zErrMsg); return(-1); } return(valore); } float expander:: my_custom_function(int Address, int PortNumber, int Valore) { char sql [2048]; int i; float RangeMin, RangeMax, Offset; sqlite3_stmt *stmt; /* Init Variables */ RangeMin=0; RangeMax=0; Offset=0; //char sA[3]; //sprintf(sA,"%s",Address); //printf("calcola_valore: Address=%d/%s - Porta=%d/%s\n",Address, sA, PortNumber, itos (PortNumber)); strcpy(sql,"SELECT RangeMin, RangeMax, Offset FROM inputai WHERE Address="); strcat(sql,itos(Address)); strcat(sql," and PortNumber="); strcat(sql,itos(PortNumber)); printf("CV_SQL=%s\n", sql); if (sqlite3_prepare(db, sql, -1, &stmt, 0) != SQLITE_OK) { // printf("CV_Errore prepare\n"); return(-1); } if (sqlite3_step (stmt)==SQLITE_ROW) { for (i=0; i< sqlite3_column_count (stmt); i++) { /* Column are in the SELECT order */ if (strcasecmp(sqlite3_column_name(stmt,i),"RangeMin") == 0) RangeMin = atof((char *)sqlite3_column_text(stmt,i)); else if (strcasecmp(sqlite3_column_name(stmt,i)," RangeMax") == 0) RangeMax = atof((char *) sqlite3_column_text(stmt,i)); else if (strcasecmp(sqlite3_column_name(stmt,i),"Offset") == 0) Offset = atof((char *)sqlite3_column_text(stmt,i)); } } // The follow row is that one I forgot :-) sqlite3_finalize (stmt); return(Valore * (RangeMax - RangeMin)/1023 + RangeMin + Offset); } ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update fail without ERRORS
[EMAIL PROTECTED] wrote: > I've found what's matter, but I don't understand why ? Can you add the following line to dump the SQL string just before you call sqlite3_exec()? printf("%s\n", zSQL); Now run the program with and without the commented assignment and report the two strings back here. Also I noticed that you are using different case (zSQL vs zSql) in the calls to sqlite3_mprintf() and sqlite3_exec(). I assume that is another typo. It really would be best if you could post the actual code you were running to avoid such issues. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update fail without ERRORS
[EMAIL PROTECTED] wrote: > > I don't know where I must crash my head :-( > It looks like the database is fine. Are you certain that you are using the same database file from the command line and in your program? Your program uses the file test.db in the current directory when it runs (unless it changes the current directory). Is that the same path used from the command line? One way to be sure is to use an absolute path name in your program. Can you try simplifying your test? int main(int argc ,char *argv[]) { expander expio; expio.open_db("/complete/path/to/the/file/test.db"); if (expio.sqlraw("UPDATE inputai SET Value=12.3 WHERE Address=7 and Port=1") == 0) printf("success\n"); else printf("failure\n"); return 0; } One other remote possibility that comes to mind is that your program is running as a different user than the command line shell program, and that user doesn't have write permission to the file or directory which are needed to create the journal file and to modify the database file. I would have expected an error message return from sqlite3_exec() if that were the case though. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update fail without ERRORS
I've found what's matter, but I don't understand why ? I've tried to use the sqlite3_mprintf() to prepare my sql. If my numeric fValue is integer the SQL works fine, but if I've got a floating value the sql doesn't update the table. And the bad is that I can get any error I'll try to explain me better using a part of my code. int iLow, iHigh; long int liValue; float fValue; char *zSQL; int Address; int Port; liValue = iHigh*256 + iLow; fValue = my_custom_function (liValue); // If I uncomment the follow row the code works fine // fValue = liValue; zSQL = sqlite3_mprintf("UPDATE inputai SET Value=%. 2f, Timestamp=%d WHERE Address=%d and PortNumber=%d;",fValue , Port, Address, Port); rc = sqlite3_exec(db,zSql, NULL, NULL, &zErrMsg ); if( rc!=SQLITE_OK ) { fprintf(stderr, "SQL error: %s\n", zErrMsg); sqlite3_free(zErrMsg); return(-1); } This function doesn't works!!! I can't update my values!!! BUT... if remove the comment and the program execute fValue=liValue; statement the table is updated !!! ?? What's my mistake ?? Pierluigi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update fail without ERRORS
>The code looks OK except for the typo (i.e. sPre[2048[ should be > sPre[2048]). Yes, It's a mistype on the mailinglist, the code it's ok! > I assume that your real table isn't named "table" since that is a keyword. The real table name is "inputai" > Can you open the database file using the sqlite3 command line utility > and execute a select query to return or count the number of rows that > match your update condition? Yes. I can open the database and I can run with success the same command. I copy and paste the program output (printf sql) to sqlite3 command line utility then I press Return and the table is updated!!! >select count(*) from your_table where Address=7 and Port=1 > If that gives a zero result you have your answer. I've got "1" >You might also want to show the create statement you used for the table >you are trying to update. I create the table using some code: int create_input_ai() { int rc; char sql[512]; char *zErrMsg = 0; printf("CREATE_INPUT_AI\n"); strcpy(sql,"CREATE TABLE inputai (Address INTEGER,PortDescription TEXT,PortNumber INTEGER,PortType TEXT, PortResolution INTEGER,Value REAL,UM TEXT,"\ "RangeMin REAL,RangeMax REAL,Offset REAL,MinL REAL,MinLL REAL,MaxH REAL,MaxHH REAL,Delay INTEGER,Hysteresis INTEGER,Alarm TEXT,Users TEXT,"\ "Action TEXT, Monitor TEXT,TimeStamp TEXT,TimeAlarm TEXT);"); rc = sqlite3_exec(db, sql, NULL, NULL, &zErrMsg ); if( rc!=SQLITE_OK ) { fprintf(stderr, "SQL error: %s\n", zErrMsg); sqlite3_free(zErrMsg); return(-1); } return(0); } I don't know where I must crash my head :-( Pierluigi Bucolo ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update fail without ERRORS
[EMAIL PROTECTED] wrote: > > When I execute the code, sqlraw > function print the pSql string, and this is the same I pass. > The > Database descriptor is the same returned from open function, and status > code is OK!!! > > But table value isn't updated. > The code looks OK except for the typo (i.e. sPre[2048[ should be sPre[2048]). I assume that your real table isn't named "table" since that is a keyword. Can you open the database file using the sqlite3 command line utility and execute a select query to return or count the number of rows that match your update condition? select count(*) from your_table where Address=7 and Port=1 If that gives a zero result you have your answer. You might also want to show the create statement you used for the table you are trying to update. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update fail without ERRORS
Well I'm very new to SQLite but shouldn't: UPDATE table SET Value=12.3 WHERE Address=7 and Port=1 be: UPDATE table SET Value='12.3' WHERE Address='7' and Port='1'; sqlite3_vmprintf() is the recommended method to build SQL with parameters. Saturday, March 1, 2008, 6:31:50 PM, you wrote: tti> The code is very long, I'll try to put here the core of my application. tti> I'm using a C++ Class where one function is "sqlraw" that I use to tti> execute a SQL statement: tti> CLASS DEFINITION tti> sqlite3 *db; tti> int expander:: tti> open_db(char * pDbName) tti> { tti>int rc; tti>rc = sqlite3_open(pDbName, &db); tti> if( rc ) tti>{ tti> fprintf(stderr, "Can't open database: %s\n", tti> sqlite3_errmsg(db)); tti> sqlite3_close(db); tti> exit(1); tti>} tti>return(0); tti> } tti> int expander::sqlraw(char *pSql) tti> { tti>int rc; tti>char *zErrMsg = 0; tti> printf("SQLRAW: SQL=%s\n",pSql); tti>printf("Database %d\n",db); tti>rc = tti> sqlite3_exec(db,pSql, NULL, NULL, &zErrMsg ); tti>printf("SQLRAW: Stato=%d tti> - OK=%d\n",rc, SQLITE_OK); tti>if( rc!=SQLITE_OK ) tti>{ tti>fprintf(stderr, tti> "SQL error: %s\n", zErrMsg); tti>sqlite3_free(zErrMsg); tti>return(-1); tti>} tti> return(0); tti> } tti> int main(int argc ,char *argv[]) tti> { tti>expander expio; tti> char sPre[2048[; tti>expio.open("test.db"); tti>strcpy(sPre,"UPDATE tti> table SET Value=12.3 WHERE Address=7 and Port=1"); tti>if (expio.sqlraw tti> (sPre) == 0) tti>{ tti>/ / Action for no error tti>} tti>else tti>{ tti> // Manage error conditions tti>} tti> When I execute the code, sqlraw tti> function print the pSql string, and this is the same I pass. tti> The tti> Database descriptor is the same returned from open function, and status tti> code is OK!!! tti> But table value isn't updated. tti> I don't understand tti> what's matter, and because i haven't any error message I can't debug tti> it. tti> Any suggestion is VERY VERY appreciate tti> Pierluigi Bucolo -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update fail without ERRORS
The code is very long, I'll try to put here the core of my application. I'm using a C++ Class where one function is "sqlraw" that I use to execute a SQL statement: CLASS DEFINITION sqlite3 *db; int expander:: open_db(char * pDbName) { int rc; rc = sqlite3_open(pDbName, &db); if( rc ) { fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db)); sqlite3_close(db); exit(1); } return(0); } int expander::sqlraw(char *pSql) { int rc; char *zErrMsg = 0; printf("SQLRAW: SQL=%s\n",pSql); printf("Database %d\n",db); rc = sqlite3_exec(db,pSql, NULL, NULL, &zErrMsg ); printf("SQLRAW: Stato=%d - OK=%d\n",rc, SQLITE_OK); if( rc!=SQLITE_OK ) { fprintf(stderr, "SQL error: %s\n", zErrMsg); sqlite3_free(zErrMsg); return(-1); } return(0); } int main(int argc ,char *argv[]) { expander expio; char sPre[2048[; expio.open("test.db"); strcpy(sPre,"UPDATE table SET Value=12.3 WHERE Address=7 and Port=1"); if (expio.sqlraw (sPre) == 0) { / / Action for no error } else { // Manage error conditions } When I execute the code, sqlraw function print the pSql string, and this is the same I pass. The Database descriptor is the same returned from open function, and status code is OK!!! But table value isn't updated. I don't understand what's matter, and because i haven't any error message I can't debug it. Any suggestion is VERY VERY appreciate Pierluigi Bucolo ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update fail without ERRORS
[EMAIL PROTECTED] wrote: > > How can I debug this issu ? > You will have to show some of the code you are having problems with before anyone here can help you with this. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update fail without ERRORS
On Fri, Feb 29, 2008 at 11:01 AM, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > I'm working on a program using sqlite library, but I've got an issue > that I can't solve. > Suddenly, my program don't update the tables > I > don't understand whats matter because, if I write SQL instructions > using Sqlite3 client, UPDATE works fine, and I haven't any ERROR CODE. > sqlite3_exec function return SQLITE_OK, but tables aren't updated. > > How > can I debug this issu ? > > Pierlugi Could you provide some examples of the UPDATE statements you're using? -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Update fail without ERRORS
I'm working on a program using sqlite library, but I've got an issue that I can't solve. Suddenly, my program don't update the tables I don't understand whats matter because, if I write SQL instructions using Sqlite3 client, UPDATE works fine, and I haven't any ERROR CODE. sqlite3_exec function return SQLITE_OK, but tables aren't updated. How can I debug this issu ? Pierlugi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users