Struggling with SQLite

gtheus · Post by **gtheus** » Sat Feb 16, 2008 5:49 pm

Hello,
I'm struggling with this SQLite feature in RunRev 2.8.1: Can someone from RunRev tell me, which SQLite versions are really supported by the bundle, please? I have an existing SQLite 3 database with which I want to establish a connection in Database Query Builder from RunRev. Building the connection is indicated as "working" within Database Query Builder but choosing one of the tables in my database is not. RunRev tells me that the database is not existing, then. This is with SQLite databases versions 2 and 3!
Building SQLite databases by script within RunRev works just fine, but I want to use existing ones, too. And that's not working for me.

Best regards,
Gian-Reto.

Mark · Post by **Mark** » Sat Feb 16, 2008 7:48 pm

Gian-Reto,

Although I am not from RunRev, I hope you don't mind me trying to answer your questions.

The built-in SQLite database does not connect to the SQLite engine that you can download at sqlite.org. Revolution uses its own database engine and only needs to know where to find the database file.

To answer your question: Revolution doesn't "support" any SQLite version and I don't know whether the built-in engine is based on SQlite 2 or SQLite 3 source code. Revolution's SQLite should be compatible with any other SQLite, though.

(With built-in I actually mean the external included with Revolution).

Which error does Revolution report, exactly?

Best,

Mark

gtheus · Post by **gtheus** » Sun Feb 17, 2008 7:52 am

Mark wrote:Gian-Reto,

Although I am not from RunRev, I hope you don't mind me trying to answer your questions.

Mark,
thank you for your answer. And no, I don't mind...

What I try: I have a SQLite database (v3) file with some data. I'd like to build an interface for this data with RunRev. So, I use the built-in Database Query Builder: I change database type to "SQLite", select my existing SQLite database file on my drive and click on "Connect", to have the connection established, which is indicated as "connected" status. Then I change to the record set page. There is a drop down control where the tables in my database should be shown. But it's empty, although RunRev told me that my database file is connected. Entering an SQL query manually in the field below results in the follwing error (after clicking on "refresh query"):
Revdb error: Database error: SQL error or missing database. Query: select * from name; ("name" is the name of one of my tables here)
So I feel that RunRev is not really connecting to my database file in the first step. But why? Wrong version? As far as I have understood, RunRev has the SQLite engine built in. That's why I have not installed the SQLite engine on my computer. Opening my SQLite database file with another application (SQLiteManager) is working without any problems. So my database file is fine.
I tried the SQLite examples provided by RunRev - they worked. Difference is, that SQLite files were built by RunRev script on the run in this examples. But I need to connect to my existing databases...

Best regards,
Gian-Reto.

Nonsanity · Post by **Nonsanity** » Thu Apr 10, 2008 6:42 pm

I'm having pretty much the same problem here. I guess what we need to know for sure is what SQLite file formats Revolution recognizes (Studio, v2.8.1b470 here).

Knowing that, it should be possible to use other tools to convert the DB into one that Revolution can work with.

Mark · Post by **Mark** » Fri Apr 11, 2008 6:59 pm

Hi all,

I have been trying to import the database at

http://www.zuggsoft.com/index.php?page= ... file_id=64

(I have no idea what's in the file and apologise if it is inappropriate).

It looks like SQLite database files from third-party sources cannot be used with Revolution. I have posted a question about it to the Rev mail list and will get back here if it gets solved.

Best,

Mark

Janschenkel · Post by **Janschenkel** » Fri Apr 11, 2008 8:12 pm

Hi Mark,

That download looks like an SQLite 2.x file, while Revolution's driver is for SQLite 3.x - while one would hope that a higher version can read older files, it is possible that they need to go through some conversion procedure that is not compiled into the Revolution driver.

Hope this points you in the right direction somewhat.

Jan Schenkel.

Mark · Post by **Mark** » Sat Apr 12, 2008 10:21 am

Hi Klaus,

Yes, I noticed the version number in the database file. On the mail list, one appears to agree that Rev's SQLite reads 3.x files just fine.

Perhaps Gian and Nonsanity could send me their database file for testing...?

Gian, just a quick suggestion: what happens if you remove the semi-colon at the end of the syntax?

Best,

Mark

Janschenkel · Post by **Janschenkel** » Mon Apr 21, 2008 10:39 pm

While I was revisiting an old stack, I was bumping into weird errors as well. My problems went away after I cleaned a rogue cursor ID from the query object.
Open your stack, create a button and set the following script:

Code: Select all

on mouseUp
  put revQueryLongName("<the name of your query>") into tObject
  set the cRevDatabase["cursorid"] of tObject to empty
end mouseUp

Click the button, save your stack, quit and open Revolution again.

Does that bring your stack's database connection back to life?

Jan Schenkel.