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:

  1. //Open the database in query mode with the simplest and fastest query possible,
  2. // "SELECT 1". Throw an error message if there was any problem connecting to
  3. // the data source
  4. if (!dbopen("FlexSim_test", "SELECT 1", 0, 0)) {
  5. stop();
  6. return msg("Error001", "early bail out - error in dbopen()");
  7. }
  8.  
  9. //get past FlexSim's 254 character import limitation.
  10. // initialize some variables
  11. int currentOffset = 1;
  12. int substrCharCount = 254;
  13. string temp = "";
  14. string data = "";
  15.  
  16. //loop over the datasource, getting substrings until stringlen of substring is
  17. // less than substrCharCount
  18. while (1) {
  19. //Run your SQL query. If there is any problem running this query, throw an
  20. // error message.
  21. if (!dbsqlquery(concat(
  22. "SELECT SUBSTRING(`json_varchar`,",
  23. numtostring(currentOffset,0,0),",",
  24. numtostring(substrCharCount,0,0),") FROM table_test"))) {
  25. stop();
  26. dbclose();
  27. return msg("Error002",
  28. "early bail out - error in dbsqlquery() SELECT query");
  29. }
  30. //save off the substr we just queried from the database
  31. temp = dbgettablecell(1,1);
  32. //append this substring onto our data string
  33. data = concat(data, temp);
  34. //if there was less than substrCharCount characters in temp, then we've
  35. // captured the whole string. Break from the while loop.
  36. if (stringlen(temp)<substrCharCount)
  37. break;
  38. //otherwise, we're going to make another db query. Increment the offset.
  39. currentOffset += substrCharCount;
  40. }
  41.  
  42. //save our completed data to the tree
  43. sets(node("Tools/data", model()), data);
  44.  
  45. //Our test query is complete. Close the data source.
  46. 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.