Confused about Databases

Hi… I’m considering my first application with Xojo. But reading online I’m confused about how it works with databases.

I guess a database doesn’t come with automatically… so how is data stored without one?

I want to design and sell some desktop software so I’ll need something that will be installable on client computers. Can this be done with Xojo with a database included? I want it to be Windows and Mac compatible.

I would consider online software but it seems like the hosting costs are pretty expensive. This would be for dog show secretarial software (where the secretary takes show entries and sends out email confirmations, designs running orders, etc). The dog community is pretty small so my market is pretty limited. And dog clubs don’t have much money for yearly hosted fees.

I currently am selling a software in Filemaker and I don’t like how strongly it ties to the database. I would like a little more freedom.
Thanks!

A database can be built by your application at the same time it is installed, many use sqlite which comes with XOJO although it is not considered a concurrent multi-user database. Alternatively there are other options for sqlite like multi user database based on sqlite, as well as options such as a dedicated server database system that would require more set up like Postgres. Predominantly all the systems I have developed used databases in some way or another and its relatively easy with XOJO once you have done one the pattern for development works for future applications.

Ah okay thank you. I’m looking at the Xojo examples that came with the download.
This may be a really basic question, but how do I access the database directly, to create and modify tables?
Thanks!

Check out the documentation at SQLite Database and the other links at that page. You can start with ‘SelectSQL’ and ‘ExecuteSQL’.

You basically write code to manipulate your database and provide your user interface and there are some great tutorials and sample applications shipped with XOJO to get you started. I also just completed a 5 part tutorial for Web platform, although would work similar on desktop which creates and manipulates a simple database so you may want to reach out to XDEV magazine and library for similar articles once you have looked at what ships with XOJO. I was initially overwhelmed when I started a similar path as you but with the help of the community here and the examples you can get where you need to be and create your application. Once developed at least there are no ongoing licensing costs once you have built your application as long as you have the build licence to build it initially. Use the XOJO IDE for free and play with the system. As you have an idea for an application go ahead and build it which for me I find as the best way to learn.

Thanks, Paul. I am Googling SQLite viewers too, as I would like a really easy way to view the entire database including the data in the tables. I also think I found where the database files are stored (and that they end with the .sqlite extension).

I guess I can also build a table view into the application itself. I guess I just like knowing exactly where the database lives on the computer itself and what the table layouts and data looks like.

I am looking at some of the examples that came with Xojo.

Go to sqlitebrowser.org and use their browser it’s perfect for this and will do all you need for free. I also cover that in the XDEV article.

take a look at the “eddie electronic” example in the xojo folders
it is a (quite) full featured simple database application, running on mac win linux or even web.
once you understand how it works, you will see xojo forces and failures…
don’t hesitate to ask us if you’re lost.

ps: and xojo is really better than filemaker, but you have to program most of the things.
in filemaker, most of the things are pre-programmed, and generally not like you would like to, and you can’t modify anything
in xojo you can make almost all what you want, but you have to make them first !



The SQLite databases don’t have to have an extension; none of mine do. You haven’t said which platform you are on , but the SQLite command line app (sqlite3.app or sqlite3.exe) is available from https://www.sqlite.org/cli.html.

My app builds a number of sqlite databases at run time, and puts them in the user’s Documents folder.

Ok looks like, to save myself some time… I maybe can use the AddressBook example, and add a database to that. However, that example isn’t working on my laptop. :stuck_out_tongue:

I was able to add a database to my starter project, so that’s good.

I wonder if there is a way to export my Filemaker tables as SQL so I can just run the SQL in Xojo and create them quickly. I should look into a Filemaker conversion tool, too.

Thanks!

How are you using your world processor to create, modify, save, print, etc. a letter (snow mail) ?

You do not need to place the database into the Wojo project.

You do not need to “export” in any way the data to create the TABLEs, read the examples:

http://documentation.xojo.com/api/databases/sqlitedatabase.html
http://documentation.xojo.com/api/databases/database.html#database-executesql

// db is a SQLite database Var sql As String sql = "CREATE TABLE Team (ID INTEGER NOT NULL, Name TEXT, Coach TEXT, City TEXT, PRIMARY KEY(ID));" Try db.ExecuteSQL(sql) MessageBox("Team table created successfully.") Catch error As DatabaseException MessageBox("DB Error: " + error.Message) End Try

All you have to do is to change the data in the line:

sql = "CREATE TABLE Team (ID INTEGER NOT NULL, Name TEXT, Coach TEXT, City TEXT, PRIMARY KEY(ID));"

Remove: Clear NAME TEXT, Coach TEXT, City TEXT

and replace them with your actual data.

Remember, there is always a learning curve, and that takes time.

File Extension:

File extension is one way to tell the running OS what application is compatible with me (the application that can be used to read / write / modify… my file).

Not setting is possible, but this is like if you call your family, friends with “Hey You” instead of “Hey John, can we talk ?”.

I usually never answer to the former when I answer to the later.

Where to place the data (the Data Base file) ?
Where you want, but preferably in the OS Application Data folder.

You are right wanting to expand your views (look at Xojo), there is only a learning curve. Continue to ask here, try to use Xojo and so on (read the documentation / come back here / read theis Forum / Poll this Forum, this already have been asked, many times).

I would just like to export the table structure from my Filemaker tables, and then recreate them in Xojo so I don’t have to write the SQL all manually. Would be easier.

Here’s a free tool to help convert FileMaker data into an SQL dump file which can be imported into a SQL database: http://campsoftware.com/products/fmdump.php

I hope that helps you. :slight_smile:

@Hal Gumbert : do you have this tool fmdump, but for filemaker 13 ? this would be very appreciated.
thanks.

[quote=478502:@Jean-Yves Pochez]@Hal Gumbert : do you have this tool fmdump, but for filemaker 13 ? this would be very appreciated.
thanks.[/quote]

It’s an fmp12 file, so it should open in FileMaker 12 and later. That said, the script might use newer features than FM 13 has. The download filename states December 2017 which would have been FIleMaker 16 at the time. So, it might just work in FileMaker 13 unless I used a greater than 13 feature.

FYI, FMDump uses the ‘BaseElements’ FileMaker plugin [free] to write out text files.

if I open it it says “created with fm15 and can’t be opened with fm13”

Fixed! Just download FMDump again: http://campsoftware.com/products/fmdump.php

works fine now. many thanks !
then just a suggestion: make a sqlite version of your tool !

SQLiteStudio is also very good and free: https://github.com/pawelsalawa/sqlitestudio/releases