insert does not work

[ghost]

I might be doing something wrong, but i have a dao on top of fmdb, i am trying to insert records into the databse. first fmdb seems to be copying the database file to some temporary location and is not performing the inserts on the database file i open (i.e. the one included in the project), and second, insert is work ok no errors and returns a successful result, but the insert does not happen in the database, even in the db location fmdb is writing to.

i am openning the databse everytime i do the insert, i will change that eventually, but any ideas why this is happening. i know i am posting enough details, but any hints would be ver much appreciated.

Thanks!

Isaac

[ccgus]

Sorry, but I can't even being to help you out on this, since there are so many things that could be going wrong.

I would suggest you add a bunch of logging to your classes, and make sure every input that you're giving to the various methods is exactly as you expect it to be. Either that, or break the problem down into the simplest possible example, and if there's a bug in that- we can start there.

[ghost]

Hi, thanks for your patience. This is the insert statement I am using:

    [fmdb beginTransaction];
    [fmdb executeUpdate:@"INSERT INTO T_ANSWER (PATIENT_VISIT_ID, QUESTION_ID) VALUES (?, ?)",     [_patientVisit patientVisitId], [_question questionId]];
    [fmdb commit];
    if ([fmdb hadError]) {
        [fmdb close];
        NSLog(@"db error");
        return FALSE;
    }  

and then, to check, I execute a count(*) select, and it returns that 0 records are in the table!?

no errors returned executeUpdate returns true, and rc in the inner method is 0.. i don't know what is it.. i open the connection multiple times before the insert, but i close it everytime.. that's the only sketchy thing i have in the app right now..

but my other concern is it does not "insert" into the db location I have it writes to this one:

i open the database like this:
fmdb = [FMDatabase databaseWithPath: [[NSBundle mainBundle] pathForResource:@"database" ofType:@"db"]];

any thoughts? this is the first time i use inserts in the application using fmdb, been always manually inserting data while developing..

again, thank you VERY much for your support!

Isaac

[ghost]

forget about "but my other concern is it does not "insert" into the db location I have it writes to this one:" because it actually inserts in the right database.. i just check that.

[ccgus]

Sorry, I'm not sure what to tell you. Unless you can provide for me a stripped down reproducible sample, there isn't anything I can do.

[ghost]

FMDatabase * fmdb = [FMDatabase databaseWithPath: [[NSBundle mainBundle] pathForResource:@"ortho" ofType:@"db"]];
[fmdb setLogsErrors: TRUE];
[fmdb setTraceExecution: TRUE];
[fmdb open];
[fmdb beginTransaction];
[fmdb executeUpdate:@"INSERT INTO T_ANSWER (PATIENT_VISIT_ID, QUESTION_ID) VALUES (?, ?)", [NSNumber numberWithInt: 99], [NSNumber numberWithInt: 99]];
[fmdb commit];
if ([fmdb hadError]) {
[fmdb close];
NSLog(@"db error");
return;
}

FMResultSet * rs = [fmdb executeQuery:@"select count(*) from T_ANSWER where PATIENT_VISIT_ID = 99"];
if ([fmdb hadError]) {
    NSLog(@"cannot execute clean_start check query");
    return;
}
NSLog(@"count inserted = %d", [rs intForColumnIndex: 1]);
[fmdb close];

2012-01-04 22:29:53.867 Tests[4099:207] SessionCommandProcessorGHTestCase/testSimpleInsert
2012-01-04 22:29:58.983 Tests[4099:207] Re-running: SessionCommandProcessorGHTestCase/testSimpleInsert <GHTest: 0x4c25590>
2012-01-04 22:29:58.985 Tests[4099:207] <FMDatabase: 0x4c2f190> executeUpdate: BEGIN EXCLUSIVE TRANSACTION;
2012-01-04 22:29:58.986 Tests[4099:207] <FMDatabase: 0x4c2f190> executeUpdate: INSERT INTO T_ANSWER (PATIENT_VISIT_ID, QUESTION_ID) VALUES (?, ?)
2012-01-04 22:29:58.988 Tests[4099:207] obj: 99
2012-01-04 22:29:58.988 Tests[4099:207] obj: 99
2012-01-04 22:29:58.989 Tests[4099:207] <FMDatabase: 0x4c2f190> executeUpdate: COMMIT TRANSACTION;
2012-01-04 22:29:58.992 Tests[4099:207] <FMDatabase: 0x4c2f190> executeQuery: select count(*) from T_ANSWER where PATIENT_VISIT_ID = 99
2012-01-04 22:29:58.993 Tests[4099:207] count inserted = 0
2012-01-04 22:29:58.996 Tests[4099:207] SessionCommandProcessorGHTestCase/testSimpleInsert ✔ 0.01s

table:
CREATE TABLE T_ANSWER (
ANSWER_ID INTEGER PRIMARY KEY ASC AUTOINCREMENT,
PATIENT_VISIT_ID INTEGER NOT NULL,
QUESTION_ID INTEGER NOT NULL UNIQUE,
EFF_TS INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP,
EXP_TS INTEGER
)

weird!?

[ccgus]

Works ok for me (using this JSTalk script):

var dbPath = "/tmp/jstalk.sqlite";

var db = [JSTDatabase databaseWithPath:dbPath];
if (![db open]) {
print("Could not open database");
}

[db executeUpdate:"""CREATE TABLE T_ANSWER (
ANSWER_ID INTEGER PRIMARY KEY ASC AUTOINCREMENT,
PATIENT_VISIT_ID INTEGER NOT NULL,
QUESTION_ID INTEGER NOT NULL UNIQUE,
EFF_TS INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP,
EXP_TS INTEGER
)"""];

[db executeUpdate:"INSERT INTO T_ANSWER (PATIENT_VISIT_ID, QUESTION_ID) VALUES (?, ?)", [NSNumber numberWithInt:99], [NSNumber numberWithInt:99]];

var rs = [db executeQuery:"select * from T_ANSWER"];

while ([rs next]) {
print([rs resultDict]);
}

var rs = [db executeQuery:"select count(*) from T_ANSWER where PATIENT_VISIT_ID = 99"];

while ([rs next]) {
print("b: " + [rs stringForColumnIndex:0]);
}

---

{
"answer_id" = 1;
"eff_ts" = "2012-01-05 17:50:58";
"exp_ts" = "";
"patient_visit_id" = 99;
"question_id" = 99;
}
b: 1

[ghost]

Thank you! I'll do my homework!