NaviCat Issues with Views

Off-Topic
1

Been working with this for a few days, here are my observations:

  1. Clunky interface, not very intuitive
  2. The wizards, for SqlLite generate code that results in no data being displayed, so if you want any custom syntax, you have to create the query manually, as soon as you edit the wizard data, you cannot use any of the toolbars, like parameters, so you can filter data when deploying to Navicat report viewer
  3. Creating views works great…until you use them in a report, and then some of the fields have blank data

I could go on but I was looking for a decent report writer for SqlLite databases, and unfortunately I wasted $170 Cdn on this useless piece of trash

2

The auto generated code has the word main.tablename.column name, when I edit it and take the main out, it works, but then I cannot use the wizard toolbars anymore…

/end rant

3

Try to get a refund?
For quick SQLite edits, I’m a fan of SQLiteManager. It’s written in Xojo so it can open Xojo-encrypted databases.

It is not great for databases you get from a DBA though, they like to do fancy stuff…

4

I sent them an email in the contact us. What a frustrating product to work with

5

Sorry you’re having trouble. I find Navicat to be very good for PostgreSQL, MySQL and MSSQL.

6

And also sqlite

7

I use Navicat for SQLite on Windows. Took a little while to adjust to it coming from another product, but now I love it. I do a lot of my query testing / workups there too.

8

If you’re searching for a reporting tool then Navicat might not be an ideal choice. It’s a toy compared to real reporting generators. As macOS User I even do not have the Navicat Reporting included in my Premium Version. It’s strenght is in supporting multiple DBMS and data transfer between them. To move data from mySQL to SQLite is a piece of cake.

IMHO this is the No. 1 Report Generator List & Label though it’s .NET mainly and XOJO is somewhere listed at the end of a long list. So I can’t say something about this here. I’ve used List & Label in my former VB6 days 15 years ago. I do remember that Xojo did an announcement 1 or 2 years ago.

When talking about legacy then I wouldn’t say Navicats’ UI is clunky or not intuitive. I am kind of used to it for longer than a decade. I am more in favor of writing pure SQL queries than using all the fancy, visual, UI stuff for conditions or joins. And yes it can’t deal with crypted sqlite DBs created in Xojo. We’ve had this issue here in forum and as far as I remember this could be solved with a MBS addon. Maybe somebody else could provide a link or bit of information.

Sorry for having trouble with Navicat. I am using it for decades now.

9

For SQLite I use SQLMaestro.

An alternative?. Try Valentina Studio.

10

Hi Brent,

Sorry to hear about your frustrations, but perhaps you could clarify what you expected from the tool? You stated that you were looking for a report writer, but to my mind this product is aimed at database administration rather than report writing…

I’ve used it in the past and while I’ll admit that it is not the greatest tool in the world it is also not that bad. I’ve been using DataGrip, it is a good solid SQL editor, but very few bells and whistles. So I doubt if it is what you need. I have a very poor opinion of Valentina Studio and could not recommend it.

11

Yeah, I’ve been trying out both Vaelntina and Navicat and find Navicat more intuitive. I like what Valetina is trying to do, but it’s pretty rough around the edges I think.

Also, Valentina seems to be really limited free or double the price of Navicat if you only need one database type.

Unfortunately, Navicat seems to have really dowshifted on their sales promos which seemed to be every other month a year ago judging from their social media.

12

Thank you all for the replies. I Initially looked at NaviCat for database administration, and for that it is good. I also converted my Seed Database from RealsqlDB to Sqlite 3.x. I did so in order to be able to use other tools via ODBC, ergo Navicat, it was within my price range and had a banded report writer in the Enterprise edition. I’ve been use to products such as Crystal, and Oracle Reports (showing my age here) and they did the job. With the specific problem I’m having now. The product allows you to create a VIEW. Then I base a new report on that View. My view definition is as such:

SELECT
SeedInventory.ContainerId,
SeedInventory.SubContainerId,
SeedInventory.Location,
(select PlantName from SeedInventory si2 where ContainerId=SeedInventory.SubContainerId) plantname ,
(select PlantVariety from SeedInventory si2 where ContainerId=SeedInventory.SubcontainerId) PlantVariety,
strftime(’%Y’,SeedInventory.PurchasedDate) as “Year”,
strftime(’%m’,SeedInventory.PurchasedDate) as “Month”,
CASE
WHEN strftime(’%m’,SeedInventory.PurchasedDate) = ‘01’ THEN ‘JAN’
WHEN strftime(’%m’,SeedInventory.PurchasedDate) = ‘02’ THEN ‘FEB’
WHEN strftime(’%m’,SeedInventory.PurchasedDate) = ‘03’ THEN ‘MAR’
WHEN strftime(’%m’,SeedInventory.PurchasedDate) = ‘04’ THEN ‘APR’
WHEN strftime(’%m’,SeedInventory.PurchasedDate) = ‘05’ THEN ‘MAY’
WHEN strftime(’%m’,SeedInventory.PurchasedDate) = ‘06’ THEN ‘JUN’
WHEN strftime(’%m’,SeedInventory.PurchasedDate) = ‘07’ THEN ‘JUL’
WHEN strftime(’%m’,SeedInventory.PurchasedDate) = ‘08’ THEN ‘AUG’
WHEN strftime(’%m’,SeedInventory.PurchasedDate) = ‘09’ THEN ‘SEP’
WHEN strftime(’%m’,SeedInventory.PurchasedDate) = ‘10’ THEN ‘OCT’
WHEN strftime(’%m’,SeedInventory.PurchasedDate) = ‘11’ THEN ‘NOV’
WHEN strftime(’%m’,SeedInventory.PurchasedDate) = ‘12’ THEN ‘DEC’
END AS full_month,
strftime(’%d’,SeedInventory.PurchasedDate) as “Day”,
SeedInventory.Notes
FROM
SeedInventory
WHERE SeedInventory.SubContainerId <> “”

But when I generate the report and add a data source via the Query Wizard it generates this:

SELECT “ViewONView”.“ContainerId”,
“ViewONView”.“Day” Day_2,
“ViewONView”.“full_month”,
“ViewONView”.“Location”,
“ViewONView”.“Month” Month_2,
“ViewONView”.“Notes”,
“ViewONView”.“plantname”,
“ViewONView”.“PlantVariety”,
“ViewONView”.“SubContainerId”,
“ViewONView”.“Year” Year_2
FROM “main”.“ViewONView” “ViewONView”

Note it sticks “main.” in front of the view. When I preview the data, some data in the columns is missing, ie null, when I “Edit the Sql” in that window and take out the “main.” and save, it works. But then the toolbar icons are not usable. I need that toolbar to set up search parameters. Because then I can search for specific info when I use the Navicat Report View. If anyone wants my DB, to test, I can email. Its just my vast seed collection (Big gardener I am)…Thanks all

13

Navicat is expensive, but it’s worth the money. It’s the investment in your toolbox.

Database administration and periodically maintenance is a significant part of keeping applications performing well.
And, next to that I would like to recommend using triggers and stored procedures / functions in order to let the database engine do the work since it’s good in it and saves traffic and security issues.
For remote databases I would not use SQLite , but it’s perfect for local storage, in between reporting storage and config-files.
As long as your SQLite database is not encrypted with dr. Hip (xojo) you can perfectly manage them with Navicat.

14

Joost. This is all for personal use. I use Crystal Reports at work. But wanted something affordable for home. I’ve looked at BIRT and a few others. Pentaho Community Edition is next to try

15

Hmmm, that could be an issue. Can you expand on that?

16

The encryption module we use is not available in some other products. I’ve been lobbying the folks at navicat to add this for years, but it hasn’t happened yet.

17

The database is not encrypted. As I mentioned above, if I edit the sql , take out the “main.” it works, and even if I add it back after, it then works. So it seems if you ‘touch’ the query and save it work, but then all toolbars are disable, and you can’t use wizard going foward

18

FWIW, the folks at navicat are very responsive when it comes to bug reports. Record what you are doing to show them how to reproduce it and file a bug report.

19

@Greg O’Lone: are we talking about the same company??? They took weeks to respond. The product is clunky as heck - at least the Mac versions.

20

I second Greg, and I am using Mac Version :wink: