Author Topic: WTF memo text is truncated to 510 characters when SQL querying via search? (Read 18490 times)

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).

Geert Bellekens · « **Reply #1 on:** October 12, 2019, 01:59:35 am »

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

qwerty · « **Reply #2 on:** October 12, 2019, 06:42:47 am »

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.

Colin Coates · « **Reply #3 on:** October 24, 2019, 02:48:15 am »

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.

Mauricio Moya (Arquesoft) · « **Reply #4 on:** October 24, 2019, 03:40:38 am »

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.

Richard Freggi · « **Reply #5 on:** October 24, 2019, 02:12:51 pm »

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.

Paolo F Cantoni · « **Reply #6 on:** October 24, 2019, 02:38:31 pm »

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

Richard Freggi · « **Reply #7 on:** October 24, 2019, 09:59:06 pm »

Thanks wandered about the benefits of switching to Jet 4, I guess I'll try that first

Richard Freggi · « **Reply #8 on:** October 27, 2019, 05:54:20 pm »

My googling seems to show that this truncation issue is common to all Jet database versions... including 4.0. Can anyone confirm?

qwerty · « **Reply #9 on:** October 27, 2019, 08:41:42 pm »

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 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.

Richard Freggi · « **Reply #10 on:** October 29, 2019, 05:47:46 pm »

Quote from: qwerty on October 27, 2019, 08:41:42 pm

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 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).

qwerty · « **Reply #11 on:** October 29, 2019, 06:27:48 pm »

I don't have any experience. But anything is better than Mickeysoft Access.

q.

Geert Bellekens · « **Reply #12 on:** October 30, 2019, 05:39:17 pm »

Quote from: Richard Freggi on October 29, 2019, 05:47:46 pm

Quote from: qwerty on October 27, 2019, 08:41:42 pm
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 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

Richard Freggi · « **Reply #13 on:** October 30, 2019, 07:28:44 pm »

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...

Richard Freggi · « **Reply #14 on:** October 30, 2019, 08:30:17 pm »

Thanks! This is the sort of feedback I was looking for

Quote from: qwerty on October 30, 2019, 08:20:40 pm

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.

Sparx Systems Forum

News: