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();
printf("Address=%d -
Port=%d\n",Address,Port);
inp_ana_inp_req(Address, Port, _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, );
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, , 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, );
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, );
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, );
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, );
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, );
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, );
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
