Database Error Help Needed

General
1

Database Error Help Needed

Does anyone know if the SQLiteDatabase is a direct replacement for REALSQLDatabase ?

I changed all the REALSQLDatabase commands with SQLiteDatabase and it does not work.

  'CGLogDB = New REALSQLDatabase
  CGLogDB = New SQLiteDatabase

Type mismatch error. Expected RealSQLDatabase, but got SQLiteDatabase
CGLogDB = New SQLiteDatabase

Any Ideas would be help full.

2

It seems you have defined CGLogDB as a RealsqlDb - maybe in a property? That’d need to change as well.

3

I’m having some troubles with SQLiteDatabase:

  1. I cannot update a record if there is not a primary key (with RealSQLDatabase there is no problem)
  2. I update a record with a primary key (Year, InvoiceNumber, LineNumber) but all the record of the key are involved…

I’m wondering what’s happened…

4

I did a global find and replace, should that not have gotten all the RealSQLDatabase changed to SQLiteDatabase ?

5

REALSQLDatabase and SQLiteDatabase are not 100% the same. There are a few nuisance differences. You are probably running into one of them.

sb

6

I think your right scott. If I change back to RealSQLDatabase all works ok but when
I run my program with the new SQLiteDatabase I get a program error of:

Program Database Error: 0
RecordSet is not editable because no primary key exist.

7

Ahh THAT error. Using the REALSQLDatabase you didn’t have to define a primary key for your table. If you didn’t have a PK, RealBasic would use the rowid as the PK for you. Which is not best practices as rowid can change on you. Now with SQLiteDatabase, they for you to define a PK for each table. Good news is SQLite will use your PK as the rowid if a PK is defined.

Good luck

8

Scott, have you any suggestion for my problem?
Thanks

9

[quote=11782:@Nedi Freguglia]Scott, have you any suggestion for my problem?
Thanks[/quote]

I do but I am not using a computer to access the forum and it would be difficult to type in right now. I will post something later today.

10

Would that mean I would have to redo the whole database. I did assign a primary key in tables that I needed to.

11

Or include rowid in your select statement. If you select “*”, it does not include rowid. You have to ask for it explicitly.

select rowid, * from mytable where …

12

dont have to redo the database. just need the PK field.

13

[quote=11799:@Tim Hare]Or include rowid in your select statement. If you select “*”, it does not include rowid. You have to ask for it explicitly.

select rowid, * from mytable where …[/quote]

selecting rowid gets around this issue. but you can not rely on it. rowid changes (at will) unless you have a PK defined.

14

[quote=11782:@Nedi Freguglia]Scott, have you any suggestion for my problem?
Thanks[/quote]

I would make sure every place you reference the data from the SQLiteDB, make sure you reference the PK also. So if you are making a new RecordSet (new records), make sure for every record, you have defined the PK (either the rowid or the one you defined). Every select needs to have the PK as one of the fields (if using rowid, you must manually/explicitly call it out). The PK is how the database delineates from one row to another.

does this make sense?

15

I have a table with 3 columns as primary key: year (integer), invoicenum (text), linenum (integer). I read a record from this table using a SQL statement (select * from rigven where year = 2013 and invoicenum = ‘10/13’ and linenum = 20) setting up a recordset. The code changes the values of some columns and then updates the recordset (recordset.edit recordset.update). With RealSQLDatabase this works fine, with SQLiteDatabase the same code updates all the records that have year = 2013. I cannot understand why.

16

That isn’t a proper primary key. It should be a single integer column set to autoincrement.

17

Thank you, Tim. This is perhaps the reason why the update involved all the records of the same Year: only the first column (an integer value) is considered the primary key.
I’ll modify the primary key and try again.
Thank you again.

18

Ok, now it works fine!!!

Thank you a lo, Tim!!