[PARTIALLY SOLVED] Grid apps: TEXT fields wrongly created as BLOB

Scriptcase 9 General
#1

SC grid app SQL is:

SELECT
     log_text,
     log_text AS log_text2
FROM     
	view_log

‘log_text’ is a MEDIUMTEXT field in a mariadb database

Given the above, SC will automatically create two fields in the app:

log_text   SQL Type MEDIUMTEXT

log_text2  SQL Type BLOB

Why does SC create log_text2 as a BLOB?

This is wrong and quick search will not work on that field.

It seems that TEXT fields with AS in the SELECT. i.e.

fieldname AS new_fieldname

will make SC wrongly treat them as a BLOB fields

#2

Hi
use this in your select statement:

CAST(CONVERT(column1 USING utf8) AS CHAR(255)) AS column1

regards

#3

@aamartinezz

thanks for the reply

i did try that already, but it works only with small text.

I’ve just checked to see what is the CHAR(X) threshold that makes SC go wrong:

  • AS CHAR( <= 21843 ): the field is created as CHAR
  • AS CHAR( >= 21844 ): the field is wrongly created as BLOB

The same applies if instead of CHAR() i use VARCHAR()

#4

I had a look into this…
SC uses the query “SHOW COLUMNS FROM test” to check column data types. Since log_text2 isn’t there I assume it is defaulting it to be a blob.

We can fix this by using a view (which works well in a grid application).

CREATE view test_view AS 
SELECT log_text, 
log_text AS log_text2 
FROM test

Then the new select statement for SC is:

SELECT
     log_text,
     log_text2
FROM  test_view

And this works well, with both fields configured as medium_text

#5

@rperrett

thanks for your reply.

Ok (but not ok…) not being able to correctly auto detect the data type, but why is SC creating a BLOB for a field explicitly cast to a CHAR(x) with x >= 21844 ??

Anyway, yes with a view in the DB, thus avoiding using 'AS ’ in SC, the issue is fixed.
I’m doing that already with most of my sql selects used in grid.

But the actual SELECT (more complex than the one I used as an example) has SC global vars in it.

To “move”, from SC to the DB server, SELECT statement with global vars, I have to create parameterized views.

Views with parameters are not easy to write\manage and what if the DB server doesn’t support those views? (mine does)

So I hope @NetMake fix this issue in SC

#6

*bump

can anyone at NetMake take a look at this issue?

@Henrique_Barros please…

#7

I’ll take a look on this issue, but please don’t forget to report it also on bugs@scriptcase.net. The team receiving this e-mail will have a test environment ready for us developers to use.

I’ll report back in a few hours.

Thank you!

#8

@robydago,

After carrying out some tests, we identified the problem and reported it to the development team.

As soon as the fix is ​​released, we will be providing feedback through this thread.

Best regards!

#9

@Henrique_Barros \ @Danilo_Lima
Thanks guys.
I really appreciate your improvements lately in interacting with us in the forum.

#10

@robydago,

The fix was released in version 9.11.019, which is now available for update.

Best regards!

#11

Thanks Danilo!
Quick response.

#12

@Danilo_Lima

thanks.

It seems to work with a ‘simple’ AS.
But with a more complex select it doesn’t work:

SELECT
 	log AS log2,
 	if('[prj]' = 1, log, '') AS log3

log2 is now created as MEDIUMTEXT (thanks for the fix)

log3 is still created as LONG_BLOB in SC

(log is a MEDIMUTEXT field)

Note that in mariadb executing “CREATE OR REPLACE TABLE test_table AS SELECT …”, using the above select, will create a log3 field of type LONGTEXT.

#13

More info.

This line in the grid’s sql select:

cast(log as CHAR) AS log4

will create a BLOB in SC.
It should create a LONGTEXT