Advice needed

Getting Started
1

Hello as a new user to xojo, i want to make a database for my work
I want to make database of candys in the store with their expiration date, so when i type in the current date, all candy that expere withing a month i want a list
ANy tips how to make it?

thnx anyway

2

Hi @Guido_Cautreels1 ,

Welcome to the community!

Here are some broad things to think about:

  1. Web or Desktop?
  2. More than one concurrent user?
  3. I would write out the bare minimum requirements
  4. You will want to lay out your database using good design principles. Search YouTube for videos on “how to design a database using 3NF or Third Normal Form”

Explanations:

  1. This tells you the kind of Xojo project you will need to build and it will help you understand the infrastructure you may or may not need. For example, if Web then will the application be hosted on an internal or external web server? Each of these has a variety of implications.
    If it is Desktop which platforms will you be building for? Mac, Windows, or Linux or some combination?
  2. Concurrent user count impacts what server “things” you will have to consider. For example, if you are planning on this being less than 10 users on a Web application then a single instance on an internal server would be fine. If more than 1 concurrent user on a Desktop then you will not be able to use SQLite and will need to use a database server of some kind like PostgreSQL, MariaDB, SQL Server, etc.
  3. This will impact the needed windows and functionality. For example, if this is multiuser will there need to be a login screen so you know who is logged in and made what changes?

Based on what you have noted above I suspect many of the answers will be pretty simple and straight forward.
From a database perspective you will want a “product” table with a surrogate key that autoincrements or that you autoincrement in your code.
Your product table might look like this:
product_id integer Primary Key (unique across all rows)
product_nm varchar(50) NOT NULL
product_expiration_day_count integer NOT NULL ~ if your candy can expire at different rates then you would need to know that for each product. For example Skittles will expire in 30 days, but Hazelnut Truffles expire in 14 days.
create_timestamp
update_timestamp ~ create and update timestamps are generally considered best practice to have, but you may not need these for now.

Inventory Table
inventory_id Primary Key (autoincrement)
product_id integer NOT NULL ~ you need to have a foreign key from the product table into the inventory table so you know which product you are talking about
received_date date NOT NULL - so you know when to start the clock
on_hand_quantity integer NOT NULL so you know how much you have or how much you received
create_timestamp
update_timestamp

From here you then have a screen that takes the received_date and subtracts that from the current date and compares that to the product_expiration_day_count. If the count is equal or greater to this value then you know you need to do something.

If you are new to Xojo then I would start with a Desktop application and do the following:

  • Build out the SQLite database to suit your needs. Maybe you do it like above or not.
  • Build a window to input product records
  • Build a window to input inventory records
  • Build a window to show those candies that are about to expire or have expired.
  • Ask questions here as you go. This is a very helpful community who wants to see you succeed.

That should be plenty to do to start.
Good luck!
Craig

1 Like
3

First of all, Welcome to the Xojo community! :partying_face:

What you want can be done with some SQL queries.

== SETUP ==

  • First you need to create (or connect to) a database. (SQLite, for instance)
    In This DB you need to create the nessecary tables with the fields to store the data of the products (the candies).
  • Table1: CandyType
    fields: ID, CandyName, CandyColor, CandyPrice, etc.
  • Table 2: CandyItem
    fields: CandyID (as reference to Table1), expirationDate, etc

== QUERIES ==

  • Use a SQLSelect statement to fetch the filtered records.

That’s it… well, in concept. This is of course not the code you were looking for. Later, if I have more time, I can try to slap some code together that will give you an idea.

4

I just asked ChatGPT to write something quickly. I haven’t tested it. Below youfind what it came up with.
I would use a relational database instead. But that could be another question to ask ChatGPT.

  • Setting up the Database: Create a new Xojo project. Under the App object in the navigator, add a property named db of type SQLiteDatabase.
  • Connecting to the Database: Create a method to initialize the database. Call it ConnectToDB. The code should be something like:
Try
    db = New SQLiteDatabase
    db.DatabaseFile = GetFolderItem("candyStore.sqlite")
    If db.DatabaseFile.Exists Then
       If Not db.Connect Then
           MessageBox("Error connecting to database: " + db.ErrorMessage)
           Return
       End If

    Else

       If Not db.TableExists("candies") Then
          db.SQLExecute("CREATE TABLE candies (id INTEGER PRIMARY KEY, name TEXT, expirationDate DATE)")
       End If
   End If
Catch e As DatabaseException
    MessageBox("DB Error: " + e.Message)
End Try
  • Adding Candy to the Database: Create another method AddCandy. This method will have two parameters: candyName As String and expDate As Date.
Try
    Dim sql As String = "INSERT INTO candies (name, expirationDate) VALUES (?, ?)"
    db.SQLExecute(sql, candyName, expDate)
Catch e As DatabaseException
    MessageBox("Error adding candy: " + e.Message)
End Try

End If
  • Fetching Candies Expiring Within a Month: Create a method named GetExpiringCandies. This method will have one parameter: currentDate As Date.
Dim expiringList As RowSet
Dim expiringCandies() As String

Try
    Dim targetDate As New Date(currentDate.Year, currentDate.Month + 1, currentDate.Day)
    expiringList = db.SQLSelect("SELECT name FROM candies WHERE expirationDate <= ?", targetDate)

    For Each row As DatabaseRow In expiringList
        expiringCandies.Append(row.Column("name").StringValue)
    Next
Catch e As DatabaseException
    MessageBox("Error fetching candies: " + e.Message)
End Try

Return expiringCandies

Setting Up the User Interface:

  • In your window, add a TextField for entering the current date.
  • Add a Button to trigger the search.
  • Add a ListBox to display the expiring candies.For the Button’s Action event, fetch the date from the TextField, call GetExpiringCandies and populate the ListBox with the results.
5

You’ve received some excellent replies so far, so I’ll not repeat what they’ve said, but I did want to add that when you start Xojo, you will see the Project Chooser. At the bottom of the Project Chooser you’ll see Examples, and in there is a ton of code to get you started. Sometimes it’s easier to pull apart existing code and see how something works. Check out the Databases folder, and I’d guess you’ll want to look at the SQLite example. Xojo is perfect for this kind of app. Let us know how you get on.

4 Likes
6

ok thnx for all the replys i will got to work with this

7

ok tried to examples and i got an error saying in this line
If Not db.TableExists(“candies”) Then
db.SQLExecute(“CREATE TABLE candies (id INTEGER PRIMARY KEY, name TEXT, expirationDate DATE)”)
End If

it appears about the item tableexists

can you help me as a starter?

8

how can i create a new database?

9

Add a space between TABLE and EXISTS…

Read:SQLite

Excellent page.

10

it used obsolete classes and methods.

"how can i create a new database?"

via management tools like DB Browser (SQLite) or DBeaver.
database server if you like to expand PostgreSQL, Firebird (via ODBC), MS SQL Server

11

No need to go to fancy places; for once, there is a nice page in Xojo documentation (not counting the new DBKit Example…

And talking about tool management, it is more knowledgeable to built yourself your tools instead of using someone other tool.

@MARKUSR: Talking about old code, do you know that you still can use REALSQLDatabase with recent Xojo ?

Guido, are-you ready to code ?

12

as i remember correct the old version did not had arguments.
SelectSQL have this new feature.

14

Yeah, using ChatGPT needs a lot of re-prompting, to use the API 2 way of coding.
But, it usually gets me started, in case I need a way to approach a problem.

15

xojo documentation could be better organized …

1 Like
16

Xojo have hired a new Tech Writer, so I’m sure things like this will improve over time.

3 Likes
17

sorry none of the above sample codes are working got me always db syntax error

18

  1. There is no such method as “TableExists”. So you are bound to get an error if you use it.

  2. It would help to know which OS you are using, and which version of Xojo. And, are you new to programming or just new to Xojo?

  3. SQLite does not have a DATE datatype, but dates can be stored as TEXT items within an SQLite database.

  4. I shall assume macOS until you tell me differently.

Lets assume you want this database to be on the Desktop. Then when you run your program, it should create the database if it does not already exist.

Var  f as FolderItem, dbh as SQLiteDatabase, sql, myDate, mycandy as String

f = new FolderItem ("/Users/yourname/Desktop/candyStore", FolderItem.PathModes.Native)
dbh = new SQLiteDatabase
dbh.DatabaseFile = f

if  (f.exists=False)  then
  Try
    dbh.CreateDatabase ()       // Create the database, then its tables
  Catch err as DatabaseException
    system.DebugLog ("Could not create database, code " + err.ErrorNumber.ToString + ": " + err.Message)
    Quit ()
  end Try
  dbh.ExecuteSQL ("CREATE TABLE candies (id INTEGER PRIMARY KEY, name TEXT, expirationDate DATE)")
else
  dbh.Connect ()
end if

.
Now try adding a candy. You will have to work out how you want to make your dates. Note that the date you put into your database will be a String in Xojo, NOT a Date or DateTime . If you have such a string, like “2023-09-19 19:12:00” then you can do like this:

myDate  = "2023-09-19 19:12:00"
mycandy = "green blob"
sql = "INSERT INTO candies (name, expirationDate) VALUES (?, ?)"
dbh.ExecuteSQL (sql, mycandy, mydate)
19

By the way, because it is very boring putting try/catch around every little thing, I tend to have wrapper methods which do the error handling and also the error reporting. For testing and until you build an application, something like like:

// For when we are doing a non-select SQL action. Returns False if an error occurred, otherwise True.
// Reports error to the debuglog (OK for debugging) or could be modified to write to a log file.

Var  errnum As Integer, errmsg As String

try
  dbh.ExecuteSQL (sql, args)
  Return True                                  // Worked fine, just return
Catch e as DatabaseException
  app.dbErrorsCount = app.dbErrorsCount + 1
  errnum = e.ErrorNumber
  errmsg = e.Message
end try

system.debuglog ("Error doing '" + sql + "', code " + errnum.ToString + ": " + errmsg)

Return False

.
You can declare this one as:

Method name: dbexec
Parameters: Extends dbh as sqlitedatabase, sql as string, ParamArray args as Variant
Return type: boolean

and call it thus:

Var  sql as String, result as Boolean

sql = "INSERT INTO candies (name, expirationDate) VALUES (?, ?)"
result = dbh.dbexec (sql, mycandy, mydate)
if  (result=False)  then return
20

hello i. am so far i can add example
but now i got an out of memory error

Any advice?

21

Show us your code. :slight_smile:
We can’t guess what you are doing, and trying to write your app line by line is quite slow through the forum.

out of memory error usually happens when you have re-entrant code… (a method that calls itself in a loop until memory runs out)

If you have an old Xojo it uses different syntax to newer ones - one reason why the ChatGPT example does not work for you.

You were asked what version of Xojo you are using, and what OS you are using.
You haven’t told us that yet either, so please:

What version of Xojo are you using?
Mac , PC, or Linux?

1 Like