[SOLVED] SQLite database

[vdubeau]

Hello all.

For the past 15 years I"ve been programming with Filemaker and XOJO. I've actually come to hate Filemaker and the cost of an annual license for XOJO has gotten to high. I'm looking to Purebasic as a viable alternative. Based on the language reference (online) , I'm trying to access an SQLite database. It seems that my code actually opens the database but beyond that I'm at a loss on how to read the data. My test code is below:

Code: Select all

EnableExplicit     ; Force variables to be declared before use
; ********************************************************************
;  Variable definitions
; ********************************************************************

Define Filename.s

UseSQLiteDatabase()

Filename = "Test.db"

If OpenDatabase(0,Filename,"" ,"")
  Debug "COnnected to Database"
EndIf


 If DatabaseQuery(0, "SELECT * FROM Titles") ; Get all the records in the 'employee' table
  
    While NextDatabaseRow(0) ; Loop for each records
      Debug GetDatabaseString(0, 0) ; Display the content of the first field      
    Wend
  
    FinishDatabaseQuery(0)
  EndIf

I'm not getting any result from the query.
Obviously I'm not doing something correctly. Any help would be greatly appreciated.

Computer configuration:
Early 2014 Mcbook Air
4GB of memory. Macox Big Sur.

[Kiffi]

It could be that the table 'Titles' does not exist.

Code: Select all

If DatabaseQuery(0, "SELECT * FROM Titles")
  
  ; Your stuff
  
Else
  
  Debug DatabaseError()
  
EndIf

[spikey]

You say in the comment that "; Get all the records in the 'employee' table". If you're looking to get a 'title' field from records in the 'employee' table the query should look like:

Code: Select all

If DatabaseQuery(0, "SELECT title FROM employee")

[vdubeau]

Kiffi,

You were correct. I originally called the table "Titles" but forgot that I dropped the "s". Now I just need to work on returning the data properly.

[skywalk]

TI-994A's sqlite tutorial is a good start

[vdubeau]

The tutorial by TI-994A is very helpful.

I wish to express my thanks to the forum members who have tried to help. A while ago I also looked at the Lazarus IDE and posted a basic beginners question. I found the people there to be dismissive, maybe even a little confrontational. The kind of attitude definitely puts one off on using the product.

I think the members here have been helpful and welcoming.

Thanks

[skywalk]

As long as you use EnableExplicit and put

Code: Select all

your code here

nobody will complain.

[C87]

In the early stages in databases in PureBasic, I suggest that you take a look at
viewtopic.php?p=557538&hilit=yada#p557538
by kpeters58 - search key YADA. You will find the latest version (1.1 ??) or later helpful in some ways.

However, shifting from Filemaker I'd have thought you'd be better looking at Access which has a RAD, with quick development of forms and reports. In fact the development of forms and reports is excellent in Access and probably as good as you'll find in any other software development. You can get decent support at sites like Access-programmers.co.uk

You can download a copy of Access2022 for around £40 (no need for 365 and annual payments) from someone like pckeys uk. I was looking for an Access replacement in Purebasic but it is simply too time consuming. I simply spent far too much time trying to create necessary functions to replace standard functions that are built into Access. In the end I had to go back to using Access and abandon all my PureBasic work. I have to say I have used Access for over 20 years but finding a replacement with the same or better features for a reasonable cost is, I have found, impossible and time wasting. If you search for "Access alternative" all you will see is a list of results by someone without a clue, none of which are suitable as alternatives.

As an example, after months of trying to create a family tree application in PureBasic for my own use. I started it from scratch in Access and in a couple of weeks had it working. Granted, with only 25 tables, 39 forms and 18 reports it it isn't a large or complex application. Obviously it took some more time to round it off into the standard I personally require but you get the point.

Just a word of warning; Access must always have a constant link to a database, without which it can result in corruption. Without going into the technicalities, you cannot use the Access database over a WAN, WiFi, OneDrive etc but it will access any database supporting ODBC. Despite its often bad press it is very much a solution worth considering. You can issue protected code and a Runtime version of Access for your clients free of any license charges.

I am not criticising PureBasic, it does have many advantages and uses. But I have difficulty in seeing it as a database development environment.

[vdubeau]

I appreciate your reply. I used Access for many years. When I switched to a Mac I found Filemaker and RealBaisc (now XOJO) as viable alternatives on a Mac. I will admit that compared to Filemaker I find Access to be lacking. The other problem is that Access is not cross platform. As a developer I like to keep that option open.

[skywalk]

As an example, after months of trying to create a family tree application in PureBasic for my own use. I started it from scratch in Access and in a couple of weeks had it working. Granted, with only 25 tables, 39 forms and 18 reports it it isn't a large or complex application.
~~~
I am not criticising PureBasic, it does have many advantages and uses. But I have difficulty in seeing it as a database development environment.

What do you consider complicated? 100 forms?

Even webpages with more than 3 pages are a nightmare.

You have to develop your own GUI lib or purchase one and then your forms can be textually populated and their methods exposed for your code. Way way faster then drawing and dragging boxes in a RAD tool.

Purebasic + SQLite is greased lightning and cross platform. And even simple web pages with SpiderBasic.
Run away from Access whenever you are tempted.

[the.weavster]

I don't think PB is a good choice for desktop database applications, not least because it lacks a decent grid gadget.
The database and FastCGI commands do make PB good for creating some database backed APIs though.

I wouldn't really consider using MS Access either but if you do make sure to connect your project to a decent backend ( e.g. MariaDB ). If you try to share a native MS Access database on a network you're heading into a world of pain.

[skywalk]

I agree a performant grid is a hole in the PB arsenal, but there are fine libs(Said and/or Thorsten) that get us really close.
In the end, I minimize the grid anyway. I am loathe to reproduce a spreadsheet experience to what end?
Focus the user on what they want to report or visualize or confirm against some metric and automate that.
Don't expose a massive grid so they can hazard through data and hope to reproduce that effort some days or months later?
Canvas is your friend in this case. Draw plots and text summaries and pics all in the canvas in a blink.

[C87]

What do you consider complicated? 100 forms?



Not far out! My waste management software has 43 tables and 111 Forms, plus 105 reports. My production control and work in progress for structural engineers is similar but a little smaller. Waste management often has over 50 terminals spread over several different locations/company depots and is basically processing many thousands of tickets a year which are collated into invoices, printed or emailed, then posted to the sales and nominal ledgers in accounts. The steel program tends to be up to 40 seats per company but on the same site.

With these types of systems you really need a solid naming protocol, otherwise it will be a nightmare to support. I did prepare mine adapted for PureBasic and keep meaning to post the PDF, which would probably help new users.

Many of the forms are subs of course, so a few main forms with three tabbed screens and one of them having three linked grids to view. So one screen for the user that is built up with eleven forms. One issue I had with PureBasic was with multiple modal screens and controlling the variables with enumeration. More or less had it sorted but not 100%. So it was hands in the air, beggar this for a game of soldiers, and back to Access!

[the.weavster]

In the end, I minimize the grid anyway. I am loathe to reproduce a spreadsheet experience to what end?
Focus the user on what they want to report or visualize or confirm against some metric and automate that.
Don't expose a massive grid so they can hazard through data and hope to reproduce that effort some days or months later?
Canvas is your friend in this case. Draw plots and text summaries and pics all in the canvas in a blink.

A visualization isn't a substitute for a grid
Let me give you just one real world example from my 25 years as a director of a manufacturing company:

A supplier calls and informs us due to circumstances beyond their control the lead time on a component has been pushed out by several weeks. It's an unusual part so I can't just source them from a distributor pro tem. I now need to be able to open my current backlog and filter down to any jobs that require that component so I can figure out how I'm going to prioritize my current stock holding. I want that grid to be filterable and sortable (so I can sort the jobs by due date) and preferably I want to be able to edit my production schedule directly from that same view ...

[skywalk]

And why is that not possible with Said or Thorsten's grid libs?
It is even doable with the PB list and some code.
The grid is only for human display.
The action is mainly handled by a SQLite memory db that maps to your visual representation.
Once you have SQL for a what-if, you can begin to parameterize it and automate and even wrap it in a custom gui dialog.

Back to your sourcing problem...
Assuming you have a working database of inventory and customer orders/shipments, everything you seek is within a few SQL queries. The order and sorting and formatting is handled in SQL. The results can be displayed in a text editor instead of a grid.