Sparx Systems Forum
Enterprise Architect => General Board => Topic started by: Richard Freggi on October 12, 2019, 12:52:08 am
-
I have EA 13.10 using the default Jet 3.5 database on my own PC (not remote database) and I'm writing queries to extract elements and their tagged values from my model to generate reports.
The content of Memo type tagged values is truncated to 510 characters. Googling it seems to be another nice 'feature' of this database engine. This limitation of 510 characters is ridiculous.
I need to get my queries working and get the full text of memo tagged values in the reports. What can I do? Is there any setting I can change? Would changing the default database engine to Jet 4 help? Any danger of compromising my model?
Thanks for any help (urgent)
(Edit: more googling shows that this Jet engine piece of crap (pardon my expression) "Access truncates the memo if you ask it to process the data based on the memo: aggregating, de-duplicating, formatting, and so on.")
What a F**&$ joke. Why is EA based on a toy database? Why not use SQLite as default? It's free forchrissakes.
I'll try to rewrite my queries (I have to format due to 13.5 INEXPLICABLE LACK of tab delimited export of query results. Apparently, in Sparx land commas can be used only as field separators and for nothing else).
-
Yes, indeed. Make sure you don't do things like comparing the string and you get the full string.
Otherwise it's truncated to 255 characters (in my experience, you seem to get double).
The only real solution is to switch to a real database such as SQL Server or MySQL
Geert
-
Actually it's anyone choice. You can setup and run MySQL, SQLServer and other "real" databases and you should if you're not just toying with EA. Unfortunately, Sparx does not tell you that. Marketing strategy, most likely. I mentions Sirius Cybernetics Corporation formerly...
q.
-
You could use a FEAP rather than an EAP.
FEAPs use the embedded database server library of FirebirdSQL, which was developed from the Borland Interbase source code after it was open sourced several years ago. FEAPs have built-in support within Sparx Cloud Services if required.
-
You can upgrade to EA 15 (or 14, I think) and use FEAP format. It is really easy to transfer the project to a new feap file.
-
Thanks this is useful, I'll look into it!
In the meantime I solved the problem by kludging my query. I add column and line breaks in the query as text markers, then open the query result in Notepad++ running a small macro that replaces the Sparx column and line breaks with the markers. Then open it in Excel as tab delimited file. Crude but works.
-
Hi Richard,
I noticed you're still using the JET 3.5 engine. Maybe just a quick upgrade to Jet4 and a Project Transfer to a Jet 4 (Access 2000) DB might solve your problem (depends on which version of the JET engine your Googling targetted). I haven't used Jet 3.5 for over a decade. Jet 3.5 went out with Windows 95 (over 20 years ago).
Paolo
-
Thanks wandered about the benefits of switching to Jet 4, I guess I'll try that first
-
My googling seems to show that this truncation issue is common to all Jet database versions... including 4.0. Can anyone confirm?
-
I can't confirm but would not think that Jet 4 remedies that issue. The main improvements are listed below Jet 4.0 gained numerous additional features and enhancements (https://en.wikipedia.org/wiki/Microsoft_Jet_Database_Engine#History) So I'd guess that it should be the preferred version if any. Else try that other local DB format (fire-something was it, right?) supported by EA. Anyhow, for a production use in a team I'd never use EAP. That's just for my local playground.
q.
-
I can't confirm but would not think that Jet 4 remedies that issue. The main improvements are listed below Jet 4.0 gained numerous additional features and enhancements (https://en.wikipedia.org/wiki/Microsoft_Jet_Database_Engine#History) So I'd guess that it should be the preferred version if any. Else try that other local DB format (fire-something was it, right?) supported by EA. Anyhow, for a production use in a team I'd never use EAP. That's just for my local playground.
q.
I'll move the project to Firebird with local feap file next time I have a free weekend to rewrite my queries from Access to Firebird. Can any wise elders out there advise if I should expect any surprises from Firebird? (my experience is with SQLite for local file-based databases).
-
I don't have any experience. But anything is better than Mickeysoft Access.
q.
-
I can't confirm but would not think that Jet 4 remedies that issue. The main improvements are listed below Jet 4.0 gained numerous additional features and enhancements (https://en.wikipedia.org/wiki/Microsoft_Jet_Database_Engine#History) So I'd guess that it should be the preferred version if any. Else try that other local DB format (fire-something was it, right?) supported by EA. Anyhow, for a production use in a team I'd never use EAP. That's just for my local playground.
q.
I'll move the project to Firebird with local feap file next time I have a free weekend to rewrite my queries from Access to Firebird. Can any wise elders out there advise if I should expect any surprises from Firebird? (my experience is with SQLite for local file-based databases).
Is there a compelling reason why you are moving to Firebird instead of to a real database such as SQL Server?
Price can't be it. There is a free version of SQL Server available that should cover all your needs.
Geert
-
Good question Geert and I'm taking some time to think about it.
I need something 100% standard and future-proof so if Sparks treats Access and Firebird as their default file-based local databases then they seem like logical choices.
Also I don't want to set up a server in my own PC to access a file in my own hard drive. I have not looked into the overhead needed to use Oracle etc. but simplicity is important for me.
I'm not looking at remote hosted database for a number of reasons (no need for collaboration with distribute team, need to work offline, etc.)
MySQL seems like anther possible choice, who knows.
If Firebird uses ANSI-92 SQL without quirks and little 'gifts' like the string truncation of Access, then it may fit the bill for me. But I'm open to suggestions!
Really, SQLite is the only file-based databases that 100% rovides what I need. Serverless, fully standard SQL language, super-fast and efficient, very well documented and a host of excellent open-source front ends I could use to write better queries faster that the Sparx editor. But, one can just dream...
-
Thanks! This is the sort of feedback I was looking for
I also started switching to Firebase (why did I wait so long?). The only "drawback" it that it initially uses more space than EAP. Not sure how that works out on a large database. But even so, space isn't something todays storage needs to worry about. ANSI SQL is pretty cool compared to that Access gibberish.
q.
-
I have removed my previous reply since I was deceived by probably some missing coffee. Firebase is no SQL database. It's a NOSQL one. Hmpf. Back to that eap gibberish again :-(
q.
-
I have removed my previous reply since I was deceived by probably some missing coffee. Firebase is no SQL database. It's a NoSQL one. Hmpf. Back to that eap gibberish again :-(
q.
Yes, Firebird is the SQL DB, Firebase is the NoSQL one... ;) The possibility of confusion abounds...
Paolo
-
Aren't you concerned that you'll end up with a solution that works on .feap or .eap, but not on SQL Server?
Most professional users use SQL server or another "real" database, not .eap or .feap
Geert
-
For professional use: no question. My customer unfortunately uses Oracle which probably explains their bad performance. But that's another story. Anyhow, for working off-site and there's the wish to have a lightweight SQL variant. For some time I had a SQL Server installed locally. Also for some time I used MySQL locally. But carrying additional 20G or so for SQL Server just for that little use I make of it and the configuration overhead for installing repositories does not outweigh this Access solution. Maybe if Apple makes me want to buy its next machine I'll look for a Docker solution.
Still, why there's no way to have a SQLite connection for EA native? EA isn't relying on complicated SQL syntax since the query everything one by one anyway (I'd guess). It won't come.
q.
-
Me again. That wasn't my day. Somehow I got trapped with that Fire-something - and got hooked up with Firebase, a NoSQL DB. Until I noticed that EA uses Firebird, an open source MS SQL replacement (99% or so). So back in business. If that proves to be true, there's no stopping in actually switching over from EAP. I'll report back what my endeavors will bring to light.
q.
-
Some progress (after to usual googling).
Minor observation: as said the initial file is about 18M. I added a 6M demo repo and the size stayed the same. I currently have only playgrounds of minor size here, so I can't test more. But it looks like the initial .feap comes with an empty bucket. Will see if I can get my hands on a larger repo.
Accessing that from outside (you won't need that for normal EA operation). That proved to be a more tough one. So here are my steps that got me working.
- Installed version 2.5.9 from the Firebird homepage. Just the vanilla MSI with no changes. That installs some server stuff which is obviously needed for clients
- Next I got me DBeaver, which is a free Java-based client. That installs with ease and when you try opening a Firebird DB it automatically downloads some additional drivers and installs them.
- Finally the tricky part: opening a .feap. When get the open dialog you can select the file with the Browse button. Under User name enter SYSDBA and for the password enter "masterkey" without the quotes. That was hard to find out for a first time user. Luckily StackOverflow had an answer.
Now you should be up and running. In contrast to EA you have a nice SQL editor here which allows to create and save multiple SQLs. Right now it looks like you can not concurrently access a .feap with the client and EA. EA croaks with a strange ODBC error (what else?) when you try to open a .feap that is open in the external client. Interestingly EA can open a .feap concurrently in multiple EA instances. Maybe there's some setting?
First impression (after a few minutes testing): looks very good. I'll continue my research.
q.
-
One minor point with firebird models. They can slow down over time. If you notice that happening use Configure > Model > Integrity > Manage Firebird > Update Index Statistics.
-
DBeaver is realy nice! But only IDEF data modeling notation as I remember. They say they are working on crow's foot, that would be a nice improvement (IDEF gives me the hives....)
-
You could use a FEAP rather than an EAP.
But be careful, there are gotchas with these such as not being able to live on a network drive, and being case sensitive.
-
You could use a FEAP rather than an EAP.
But be careful, there are gotchas with these such as not being able to live on a network drive, and being case sensitive.
Thank you, I am actively looking for more gotchas and pitfalls before I make the switch, I am in the middle of major project and can't afford any big surprise... don't want to just swap the warts of Access for another set of warts. Any more insight, please share!