Navicat Data Modeler

Not really a Xojo add-on, but I think some people here use it. I am making models in Navicat Data Modeler but have no idea how to turn the model into an SQLite database Xojo can simply read. I’m also using Navicat for SQLite but nothing seems available.

I exported the .sql file which contains table code like this:

CREATE TABLE "main"."testing_scale" (
"id_scale" Integer NOT NULL,
"id_rank" Integer,
"id_performance_testing_station" Integer NOT NULL,
"scores" Text NOT NULL,
"results" Text NOT NULL,
PRIMARY KEY ("id_scale") ,
CONSTRAINT "lnk_scale_rank" FOREIGN KEY ("id_rank") REFERENCES "list_rank" ("id_rank"),
CONSTRAINT "lnk_scale_station" FOREIGN KEY ("id_performance_testing_station") REFERENCES "performance_testing_station" ("id_performance_station")
);

I mean, I could write a Xojo app with “createdatabase” method and copy this in, but there must be a better way. Does Navicat allow doing this? Perhaps Valentina Studio? I’ve so far only free versions of all these database apps.

In Navicat i am using the “Data Transfer” tool (right click on DB in left navigator pane) and not any export or dump functions to SQL files. So I can write directly into a database. In Xojo I just open this and do not need to Execute SQL scripts. Maybe this helps?

I don’t believe the Navicat Data Modeler can create databases. It is more of a design tool. For designing a single table it is definitely overkill. I can’t imagine it will add substantial value until you are dealing with multiple databases containing many tables.

To create databases you need is one of their other products like Navicat Essentials, Navicat for SQLite or Navicat Premium. The biggest difference for me between Essentials and the standard editions is the ability to schedule batch jobs. Their product matrix is quite large and buying only what you need can save you substantial money. Premium which contains everything is quite nice and priced much less than the other tools that have that level of functionality.

All versions are available on a 14 day trial basis so I’d suggest giving one of the other versions a try.

open the sql field of the sqlmaster table of your database.
it contains the right string you only have to sqlexecute in xojo to get your table created.

Whoops, by bad! I didn’t see this:

In that case:
Open Navicat for SQLite.
Click Connection Button and select SQLite.
Select New SQLite3 and give the database a name.
Open the connection, go to Queries and press the New at the top of the window.
Paste your SQL statement in the window and press Run.
Close and reopen the connection and your table should be under main.

Tested and works here!

[quote=315685:@Lewis Gardner]Whoops, by bad! I didn’t see this:

In that case:
Open Navicat for SQLite.
Click Connection Button and select SQLite.
Select New SQLite3 and give the database a name.
Open the connection, go to Queries and press the New at the top of the window.
Paste your SQL statement in the window and press Run.
Close and reopen the connection and your table should be under main.

Tested and works here![/quote]

Ok, this sounds good, but I’m a really slow guy…I open the program and click the connection button and get this window:

I select “New SQLite 3” as you see. But whether I name the connection (at the top) or the database file (in the middle) or both, nothing seems to work. I get the error “Unable to open the database” if I enter anything there, and if not “The database file must have a value”…

I tried this before posting the original conversation, too, but to no avail.

Navicat for SQLite even has a modelling function, but even there I don’t see any button that simply does “Take this model and make it into a database you can use”.

Also, my model has like 78 tables, scores of Foreign keys and constraints, I only pasted one of them above to show as an example.

[quote=315669:@Tomas Jakobs]In Navicat i am using the “Data Transfer” tool (right click on DB in left navigator pane) and not any export or dump functions to SQL files. So I can write directly into a database. In Xojo I just open this and do not need to Execute SQL scripts. Maybe this helps?

[/quote]

But then I have to write an empty sqlite database to have somewhere to import to? I am not a database expert (obvsiously) but I’m really confused that a program that models databases does not simply have a button “Turn this model into a real database”. I mean what is the point of making a super nice perfect model and then having to write the database from scratch? And import the model to a different database. Why? It just seems so lacking in logic that I’m 100% confident I’m missing the big picture.

[quote=315675:@Jean-Yves Pochez]open the sql field of the sqlmaster table of your database.
it contains the right string you only have to sqlexecute in xojo to get your table created.[/quote]

I do not want to create a new table in an existing database. I want to create an entire new database using an .sql file.

Navicat for Windows offers more functionality than the Mac version … For example reporting. (premium)
Great tool ! Cannot do my job without.

menu connection - new - sqlite
then choose “new sqlite3” and fill a name in the database field and you have your empty sqlite file

Jean-Yves Pochez gave the right anser I guess…

You create a database once. Your Xojo Software just connects to the same DB (to the specified file) on disk.

[quote=315708:@Jean-Yves Pochez]menu connection - new - sqlite
then choose “new sqlite3” and fill a name in the database field and you have your empty sqlite file[/quote]

Got the missing piece. You can’t just type in a name in the blank and think it is saving to the current folder. You have to actually specify the folder/path with the abnormal button to the right of that field. I thought that button only went to an existing database.

I suppose it does not use the normal macOS way of doing things because they are focused more on Windows? (I’m guessing from the comment above that says they have more funtionality in windows.)

So, it’s all operating in Navicat for SQLite and now I can start having fun doing the work in Xojo.

Thanks to everyone who commented here. You helped me beat my head against the wall in the right places and some of the bricks finally gave way.

navicat is all BUT a good user interface for macos …

I’m liking it. Yes, the user interface is a bit wacky. But it seems like a solid program. Mainly I want to do forward engineering.

I’ve still got 9 days on my trial, and now I think I can make more headway.

Again thank you (and everyone else) for your patience in trying to help.

it’s nice to test some sql queries.
it’s really awfully bad made when you build a database structure.
lots of clics for nothing. painfully slow if the database is remote. (I have a slow adsl)

…the huge advantage: it supports all main DBMS out there MSSQL, MySQL, MariaDB, SQLite, PostgreSQL and Oracle. Like Xojo it’s one tool to rule them all. For a decade it’s my first choice and the look & feel is kind of legacy,

it’s easy to make a loop on all the “tables” records of the sqlmaster table to concat the sql fields into one string
this is your entire database statement you can then sqlexecute in xojo to get your database.
the time to understand navicat on how to do it, it’s done in xojo …

I used navicat a long time ago, now I’ve made the routines I need to convert in xojo and I don’t need navicat anymore.
as I am on a mac, there could be a use for mssql, but then I could use a VM to do it on my mac anyway with xojo.

by the way, how can it be that xojo does not support mssql on the mac, and that these products like navicat can do it ?
do they pay MS for a plugin ? do they develop it themselves ?

@Jean-Yves Pochez : Take a look in Xojo’s Extra folder:

Never had problems on Mac (except cursors and speed). Windows was more difficult because of sqlncli32 and 64 redist packages. Sometimes I’ve had to divert to basic ODBC DSN connection in order to get connectivity to MSSQL.

in the developper page : http://developer.xojo.com/mssqlserverdatabase
the mssql plugin is described as “windows only” … so ?
Connects to Microsoft SQL Server. Use MSSQLServerPlugin.xojo_plugin (Windows-only).
This plug-in is a Windows-only feature. Other platforms will get a PlatformNotSupportedException when any method is called.