Database question

[Davy]

If I use PB to create an sqlite db, can I then run queries on it using standard SQL "Select" independently of PB? I'm guessing the answer is no.

[srod]

Not sure what you mean exactly?

A DB created by PB is still an SQLite DB and can be accessed by any app capable of interfacing with SQLite, regardless of programming language.

[Davy]

Thanks srod. That's what I wanted to know. I'm a complete noob regarding databases - never used one. I wasn't sure how to access the db interactively outside of pb. All I need is a command line shell, and you can get one from the sqlite website.

[deeproot]

Thanks srod. That's what I wanted to know. I'm a complete noob regarding databases - never used one. I wasn't sure how to access the db interactively outside of pb. All I need is a command line shell, and you can get one from the sqlite website.

The SQLite command line is a very good thing to know, but I would also strongly recommend a graphical interface such as the one that comes as a free add-on for the Firefox browser - SQLIte Manager. It makes things very easy! See https://code.google.com/p/sqlite-manager/

srod is absolutely right (he usually is ), you can share database files with other applications, and even on different OS platforms.

[Davy]

Hi deeproot,

Thanks for the link, a GUI for the DB would be very handy. Forgive my ignorance, but I'm a bit puzzled why you would need it as an add-on for a browser, is this specifically for managing online databases?

[deeproot]

No, nothing to do with online stuff - it's for local files. After launching the add-in from the browser tools menu, it runs in a separate window as normal application. I guess that it simply makes use of the fact that Firefox already includes the SQLite library, for managing bookmarks and so on, so it's very lightweight and quickly accessable . Presumably useful for Mozilla add-on developers, but just as handy for any other database development.

If you are just starting out with PB database coding, you might find the add-on's "Execute SQL" tab particularly useful - allows a quick way to work though SQL statements, especially complex ones, before transferring them into your program code. BTW PureBasic + SQLite makes an awesome combination - enjoy!

[Davy]

Very nice tool, thanks again for putting me onto it.

I have a question - how to lock the db (so only I can access it)? In the OpenDatabase() command there is an option for username & password, after adding them to the command I expected to be prompted for them when trying to open the database (I mean from outside PB) but that didn't happen... or am I misunderstanding something?

Actually I think that "lock" is perhaps the wrong word, maybe encrypted would be better. Basically what I want to do is prevent the db being accessed by anyone other than myself.

[deeproot]

@Davy

I'm afraid that SQLite does not have any password/account type security, it relies on the OS filesystems security. This is an SQLite thing, not PB. I believe the OpenDatabase command options are for other database types and have no effect here. If you are storing sensitive data then some kind of encryption is the way to go. If you just want to prevent curious users from messing with your db file, then you could just encrypt a small part of the file, or even spin something yourself in the PB application code.

[Davy]

Thanks deeproot.