BigInt strange behaviour

Wappler General Bugs
1

Wappler 5.8.1, NodeJS 14 Debian 11 - Bullseye, MariaDB 10.5.13, Docker, Digital Ocean, Ubuntu 21.04.1

I have a table setup like this

CREATE TABLE `rezdy_tripletex_vat_split_mapping` (
  `rtvsm_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `rtvsm_rezdy_product_code` varchar(30) NOT NULL,
  `rtvsm_rezdy_price_option_id` bigint(20) unsigned DEFAULT NULL,
  `rtvsm_rezdy_price_option_label` varchar(255) DEFAULT NULL,
  `rtvsm_tripletex_number` int(10) unsigned DEFAULT NULL,
  `rtvsm_tripletex_product_id` int(10) unsigned DEFAULT NULL,
  `rtvsm_demo_tripletex_product_id` int(10) unsigned DEFAULT NULL,
  `rtvsm_rezdy_advertised_price` decimal(8,2) DEFAULT NULL,
  `rtvsm_price_split` decimal(8,2) DEFAULT NULL,
  `rtvsm_final_percentage` varchar(30) DEFAULT NULL,
  `rtvsm_status` varchar(50) DEFAULT 'Existing',
  PRIMARY KEY (`rtvsm_id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4;

I have some entries inside it like this

rtvsm_id rtvsm_rezdy_product_code rtvsm_rezdy_price_option_id rtvsm_rezdy_price_option_label rtvsm_tripletex_number rtvsm_tripletex_product_id rtvsm_demo_tripletex_product_id rtvsm_rezdy_advertised_price rtvsm_price_split rtvsm_final_percentage rtvsm_status
2260 DYNRSRFACONLTL 1819186131514122012 Quantity 1375 61022811 21666538 6285.00 4776.60 0.76 Confirmed 2023-12-28
2261 DYNRSRFACONLTL 1819186131514122012 Quantity 2375 61022812 21666539 6285.00 754.20 0.12 Confirmed 2023-12-28
2262 DYNRSRFACONLTL 1819186131514122012 Quantity 3375 61022813 21666540 6285.00 754.20 0.12 Confirmed 2023-12-28

The column I am interested in is rtvsm_rezdy_price_option_id, I do a standard database query and get the following result returned

2023-12-29_01-03-05
2023-12-29_01-03-051685Ă—977 63 KB

As you can see the number changes from 1819186131514122012 to 1819186131514122000 like it just drops my last 2 digits and changes them from 12 to 00

I have tried this as a custom query with no change, and when i run it in my app the output is also incorrect just as it displays in my screenshot supplied.

I can not change from bigint to something else as the number comes from an external API, and is also sent to an external accounting software API, so I am kind of stuck using big int, or as the accounting system calls it int64.

Anyway, wondering if anyone has any ideas here how i can output the same number as it appears in the database instead of this slightly changed number,

2

Hey Paul,

My first thought was to treat it like string…
After a search in Google found that:
To handle these numbers in Javascript, you must either work with them as if they were text strings or use a package for long numbers.

If you change your query to

SELECT CAST(bigint_column AS CHAR) bigint_column, other, else, etc
  FROM serversettings 
  ...

You’ll get the character strings.

Since you cannot change the type of data in database, I think it’s the best solution…

Here is the link:

1 Like
3

Thank you, much appreciated, that certainly got me around it, I did search before i even posted, but never found that, thank you once again.

1 Like
4

@Teodor, just checking if there is some other built in fix for this before i go and alter multiple scripts to support this?

5

@sid fixed this issue by adding

,
      "supportBigNumbers": true,
      "bigNumberStrings": true

Into the database connection for 3 different files just under "database": "your-database"
/app/modules/connections
/.wappler/targets/your_target/app/modules/connections
/.wappler/targets/your_target/databases

1 Like
6

Solved and that’s important thanks to @sid.

But It’s kind of weird…
BigInteger type is supported by the database manager but database builder can’t handle it

7

The DB builder is not the problem, JavaScript is.
Once the query is executed by knex, it fetches the right values. But when it converts the result into JSON using JS, JS cannot handle such big numbers, because of the reason explained in the link you have shared.

With the additional config, MySQL DB knows to not return the big numbers as number, but strings instead. So now when knex converts the result into JSON using JS, no data is lost.

For PostgreSQL, there is a function which does something similar… although, from the looks of it, that would have to be configured in one of the core Wappler lib files to make it work. Haven’t tried.

Also, this is the first time I am actually seeing a bigInt number which is actually big. Hadn’t encountered this issue before ever.

3 Likes