question

qiu xiaohong avatar image
0 Likes"
qiu xiaohong asked Ben Wilson answered

How to read json data length more than 254 from mysql?

Flexsim reads the json data from mysql , but dbgettablestr read text length less than 254, If the length of the json data more than 254, It can't be full read. So how to read json data length more than 254 from mysql?

Choose One
read json from mysql
5 |100000

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

1 Answer

Ben Wilson avatar image
4 Likes"
Ben Wilson answered

I have tested and can confirm that as of FlexSim 16.1.2 there does not seem to be a way to import more than 254 characters at once. The development team has been alerted and hopefully this limitation will be researched and eliminated in a future release.

For now, you can use FlexScript+SQL to work around the limitation. You could put your query logic into a loop that will query substrings until you have captured the entire string. Obviously this is less than ideal, but if your database import is asynchronous from your model run, then the extra overhead shouldn't be a problem.

Here is some example code:

//Open the database in query mode with the simplest and fastest query possible,
// "SELECT 1". Throw an error message if there was any problem connecting to
// the data source
if (!dbopen("FlexSim_test", "SELECT 1", 0, 0)) {
       stop();
       return msg("Error001", "early bail out - error in dbopen()");
}

//get past FlexSim's 254 character import limitation.
// initialize some variables
int currentOffset = 1;
int substrCharCount = 254;
string temp = "";
string data = "";

//loop over the datasource, getting substrings until stringlen of substring is
// less than substrCharCount
while (1) {
    //Run your SQL query. If there is any problem running this query, throw an
    // error message.
    if (!dbsqlquery(concat(
        "SELECT SUBSTRING(`json_varchar`,",
            numtostring(currentOffset,0,0),",",
            numtostring(substrCharCount,0,0),") FROM table_test"))) {
        stop();
        dbclose();
        return msg("Error002",
            "early bail out - error in dbsqlquery() SELECT query");
    }
    //save off the substr we just queried from the database
    temp = dbgettablecell(1,1);
    //append this substring onto our data string
    data = concat(data, temp);
    //if there was less than substrCharCount characters in temp, then we've
    // captured the whole string. Break from the while loop.
    if (stringlen(temp)<substrCharCount)
        break;
    //otherwise, we're going to make another db query. Increment the offset.
    currentOffset += substrCharCount;
}

//save our completed data to the tree
sets(node("Tools/data", model()), data);

//Our test query is complete. Close the data source.
dbclose();

Comment below with any questions, comments, or fixes.

5 |100000

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.