Topics

SQLite For BBC Basic

Bill Greenfield <bjg1940@...>
 

Members-

Can anyone tell me where I can find the SQLite library for BBC Basic. I
have tried
Jon Ripley's site numerous times, but it appears to not be functioning?

Bill Greenfield

 

Same issue from me too!
I guess there is nobody at home on this subject matter.

Richard Russell
 

I expect Jon's library simply provided a wrapper around the SQLite function calls to present a rather more 'BASIC-like' interface, so whilst its loss may be an inconvenience it's not something that actually prevents the use of SQLite with BBC BASIC.

Assuming that SQLite is ODBC-compliant, which I'm pretty confident it is with the right drivers, my suggestion would be to go the ODBC route rather than to write any code which is specific to one particular database manager.  For a long time an ODBC library for BBC BASIC has been on the 'wish list' and it should not be difficult to write.  If somebody would like to volunteer to do that it would be useful, but if not I'll do it myself!

Can you confirm that an ODBC library would meet your needs?

Richard.

Richard Russell
 

On Fri, Oct 13, 2017 at 05:50 am, Richard Russell wrote:
Can you confirm that an ODBC library would meet your needs?
Well?  I have confirmed that an ODBC library would be able to access a SQLite database, but if there is no interest I won't pursue it any further.

Richard.

 

Many thanks Richard for chipping in on this thread.
For me yes indeed. An ODBC library would be ideal indeed.
Many thanks for the offer.

 

Richard,

though at this moment I have no immediate use for the ODBC library, I definitely would have used it in the past. A year or so ago I had a need for it but had to find a simpler, less flexible/less powerfull solution to deal with my data.

I think the ODBC library really would be an asset to the BB4W programming environment in general (and for my own future programming needs)

regards,
Eddy


On Sunday, October 15, 2017, 11:26:50 PM GMT+2, Richard Russell <news@...> wrote:


On Fri, Oct 13, 2017 at 05:50 am, Richard Russell wrote:
Can you confirm that an ODBC library would meet your needs?
Well?  I have confirmed that an ODBC library would be able to access a SQLite database, but if there is no interest I won't pursue it any further.

Richard.

Richard Russell
 

On Mon, Oct 16, 2017 at 01:30 am, Edja wrote:
A year or so ago I had a need for it but had to find a simpler, less flexible/less powerfull solution
It's a shame you didn't choose to write your own ODBC library, which you could have shared with the rest of us!  From the experimentation I've done so far, ODBC is perfectly straightforward to interface with in BBC BASIC (no pesky COM/OOP functions or anything like that, just ordinary C-style functions which can be trivially converted to SYS statements).  There's lots of code examples on the web that can be translated to BBC BASIC 'methodically' without needing any understanding.

Anyway, if I write a simple ODBC library - even though anybody else could do it - would you (or somebody else) be prepared to test it?  I have no personal experience of working with databases so although I can translate the code I can't devise test routines to exercise it thoroughly.

Richard.

 

Sure. Just sling it across and I shall try and fit in some time to test it.
Cheers,
Chandra

 

Richard,

It's a shame you didn't choose to write your own ODBC library .....

It was not so much a matter of choice. The idea of using a database approach to solve my problem never came out of the conceptual stage. I seem to remember that I searched for SQLLite, found some bits and pieces but no (clear) documentation and/or programming examples. Pls. don't take this all too literally, this is already a distant memory. But bottom line : at the time this potential solution, that never came to fruition,  was way above my programming skills and I resorted to a simpler solution being a file reading/writing approach in conjunction with Excel with the help of COMLIB. And it worked fine.

would you (or somebody else) be prepared to test it?
Short answer : Yes
Long answer : 

I have never worked with SQL, databases (yes, once, but it was not a success), ODBC and the likes. I wouldn't know where to start. I occasionally use a saw and a screwdriver. That doesn't make me a carpenter. The same goes for my programming skills. Sometimes I surprise myself by writing a really nice program. But all in all you would be horrified looking at my code. On the bright side :  I'm having fun.
All the above just to make you aware  that, while I'm prepared to help out in testing the ODBC library, you may end up disappointed. Therefore others better participate to come to useful test results.


FYI :  I have Excel 2007 and LibreOffice Base on my PC. No Oracle or other sofisticated databases.

Eddy

Richard Russell
 

I disagree, but it won't achieve anything to argue the point.

I've uploaded an initial attempt at a library (ODBCLIB.BBC), and a test/demonstration program (ODBCTEST.BBC), to the Files area of this group as odbclib.zip.  I should say that two days ago I had not the slightest idea about databases and how to access them, so these have been cobbled together in a very short time from information gleaned from the web (which luckily is extensive).  Literally anybody could have done this.

Richard.

 

Hi Richard,

Many thanks for your efforts thus far on this.
Will take a look at the library over the weekend when time permitting.

Regards,
Chandra

Richard Russell
 

On Tue, Oct 17, 2017 at 07:48 am, Richard Russell wrote:
I've uploaded an initial attempt at a library (ODBCLIB.BBC), and a test/demonstration program (ODBCTEST.BBC), to the Files area of this group as odbclib.zip
Naturally I would encourage as many people as possible to try this.  Even if you have no immediate need for (or interest in) accessing a database, it would be helpful to know whether it works for you, and whether you find the explanation clear and the library easy to use.  The ODBCTEST program includes instructions for downloading a test database and installing the necessary SQLite3/ODBC driver.  Needless to say you will need admin privileges to copy ODBCLIB.BBC into your BB4W LIB folder (if you copy it using Windows File Explorer, with UAC enabled, it will prompt you for permission).

Richard.

 

Richard,

I've downloaded ODBCLIB.BBC and ODBCTEST.BBC to the right directories. I've then downloaded the chinook.db from http://www.sqlitetutorial.net/sqlite-sample-database/ and the SQLite ODBC driver 'sqliteodbc.exe'  from  http://www.ch-werner.de/sqliteodbc. All as per your instructions.
I then executed ODBCTEST.BBC, typed in the path name to chinook.db ..... and it  all worked fine.

Just a warning to others who may participate in the testing. As I have Win64 installed, I've first downloaded the 64-bit driver  sqliteodbc_w64.exe. This may seem logic at first, but it didn't work. So I've re-checked your instructions and loaded the 32-bit driver  'sqliteodbc.exe' . This one worked first time.

OK, so far, so good. I will now go through the tutorial on http://www.sqlitetutorial.net/
Together with your REM comments in the test program, the tutorial should help me figure out what the test program does and moreover what kind of testing would be helpful.

 

Eddy

Richard Russell
 

On Thu, Oct 19, 2017 at 03:43 am, Edja wrote:
As I have Win64 installed, I've first downloaded the 64-bit driver  sqliteodbc_w64.exe. This may seem logic at first, but it didn't work.
Yes.  BB4W is (of course) a 32-bit application so needs 32-bit DLLs and drivers.  Fortunately Windows provides excellent support for 32-bit applications (with no plans to remove it, unlike certain other OSes!).  It's worth remembering that Visual Studio is a 32-bit app!

> I will now go through the tutorial on http://www.sqlitetutorial.net/

I am personally unclear how 'universal' SQL commands are, and therefore whether you can assume that native SQLite3 commands documented there will always be identical to the 'DBMS agnostic' SQL commands accepted by ODBC.  I did wonder whether I should prefix the library function names with SQL rather than ODBC and would welcome your opinion on this.

Richard.

 

I've installed the chinook database in C:\chinook\chinook.db  (for no real or compelling reason) and consequently had to change the  connection string in ODBCTEST.BBC to :           Connect$ = "Driver=SQLite3 ODBC Driver;" + "Database=" + "C:\chinook\chinook.db;"
As a result I received the error message : "Cannot create file C:\Program Files (x86)\BBC BASIC for Windows\LIB\ODBCTEST.BBC.temp". I've then clicked the OK-button on the error message and the program continues correctly. Is this OK? I'm not sure what this means.
I did wonder whether I should prefix the library function names with SQL rather than ODBC and would welcome your opinion on this
As far as I understand you've made a library that allows to execute SQLite statements through an ODBC layer that you activate with FN_ODBCinit and at the end release again with PROC_ODBCfree, PROC_ODBCdisconnect, PROC_ODBCclose and PROC_ODBCexit.
Other procedures and functions within the library could rather be called FN_SQLsomething and PROC_SQLsomething (replacing ODBC by SQL) since they are  mapped onto a SQLite statement. It feels more "natural".
And then there are some FN's and PROC's (such as FN_ODBCncols and the likes) for which I have not found a counterpart in the SQLite statement list (I hope I'm right). Not sure how these should be named best.
If, however, you would prefer only one out of the two candidates (ODBC or SQL) throughout, perhaps for reasons of uniformity (?), then my feeling is that SQL would be the preferred choice for naming the PROC' and FN's.

I would suggest to go for uniformity and use ODBC everywhere or SQL everywhere with strong preference for SQL, the latter also being your suggestion.

I'll come back when I'm further in the learning process.

Eddy



 

On Wed, 25 Oct 2017, at 16:30, Edja via Groups.Io wrote:

I would suggest to go for uniformity and use ODBC everywhere or SQL
everywhere with strong preference for SQL, the latter also being your
suggestion.
As SQLite is not the same as (full) SQL it would surely be better, if the things are to be renamed, to indicate that.

--
Jeremy Nicoll - my opinions are my own.

Richard Russell
 

On Wed, Oct 25, 2017 at 08:30 am, Edja wrote:
Is this OK? I'm not sure what this means.
It's definitely not OK.  It would appear to mean that you've stored the ODBCTEST.BBC program in BB4W's LIB folder, which of course is an entirely unsuitable place for it.  The zip archive I made available contains two files: ODBCLIB.BBC, which (as a library) must be stored in BB4W's LIB folder, and ODBCTEST.BBC which (as a test program that may need to be modified by the user) must not be stored in BB4W's LIB folder - or indeed anywhere that isn't normally writable.

> I would suggest to go for uniformity and use ODBC everywhere or SQL everywhere 

There's never been any question about that - I certainly wouldn't want to use 'mixed' prefixes - but I'm not sure that I'm any nearer being able to decide whether to use ODBC or SQL.  As you imply, the FN_ODBCquery function is unquestionably a 'SQL' function because the string you pass is an SQL statement, but most if not all of the other procedures and functions in the library are not specifically anything to do with the 'language'.

Incidentally when you refer to SQLite (the specific database manager) I would rather substitute SQL (the generic Structured Query Language); there is hopefully nothing specific to SQLite in my library!

Richard.

 

ODBCTEST.BBC .........must not be stored in BB4W's LIB  folder - or indeed anywhere that isn't normally writable
Of course. I've changed it. Now all is well.

Eddy

svein svensson
 

Downloaded and installed as instructed.
Test program working ok, i'll try to write some testcode later.
Have no SQL experience, but since there is a library present, i might as well take a look at the tutorial.

Notes, whatever they are worth.
Test program did not connect at first attempt simply because the downloaded database extracted to \chinook\chinook.db by default.
When starting ODBCTEST  (without IDE), the dialogue appears behind the main window. When closing the main window, the dialogue remains open.

Svein

Richard Russell
 

On Thu, Oct 26, 2017 at 02:59 am, svein svensson wrote:
When starting ODBCTEST  (without IDE), the dialogue appears behind the main window. When closing the main window, the dialogue remains open.
What dialogue is that?  I don't get a dialogue box here, and I can't see anything in the program that would generate one (all output is via plain PRINT statements).

Richard.