Wappler return DECIMAL as TEXT

Wappler General
1

I’m not sure if it’s a bug or designed in that way but I have NodeJS + Docker + PostgreSQL 15.3
I have a pretty basic table create with SQL Query:

DROP TABLE IF EXISTS "budgetGuideline" CASCADE;
CREATE TABLE "budgetGuideline" (
	id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
	this_is_text TEXT DEFAULT NULL,
	this_is_decimal DECIMAL DEFAULT NULL,
	this_is_interger INT DEFAULT NULL
);

INSERT INTO "budgetGuideline" (this_is_text, this_is_decimal, this_is_interger) VALUES
('Test 1 ', 12.2, 5),
('Test 2', 15.8, 3),
('Test 3', 11.00, 11);

Problem is that when Wappler returns value for this_is_decimal it returns as a text, not number.

image
image621×688 23.4 KB

@patrick @Teodor any specific reasons for this?

2

The decimal values do not fit the JavaScript number type so it becomes a string.
You need to convert them to number if you want to apply numeric formatters to them or do any calculations.

1 Like
3

Any change we can get automatic convert toNumber() automatically by Wappler for this?
Imagine you have thousands of tables with decimal value and thousands of calculations - and you need .toNumber() to each of them?

1 Like
4

It’s the database driver that returns the data this way, it’s not Wappler converting it.

5

I do lots of decimal calculations in server actions… after the database query, I just do a series of Set Value commands that include the .toNumber() and then do the calculations based on those values…

So like this:

Query get_invoice
- amount_invoiced
- amount_unpaid
- amount_sales_tax

Set Value amount_invoiced = get_invoice.amount_invoiced.toNumber()
Set Value amount_unpaid= get_invoice.amount_unpaid.toNumber()
Set Value amount_sales_tax= get_invoice.amount_sales_tax.toNumber()
...

In this situation, you would be better to create a database View and do the calculations there…
Database Views are the least talked about and most powerful feature I know! :tada:

1 Like
6

Looks like we need a proper video guide regarding DB Viewes from the expert! :wink:

2 Likes
7

Views are indeed interesting. Unfortunately you need an external app such as Navicat to create them. Wappler supports views, just not creating them.

1 Like
8

I create views using MySQL Workbench…

There are lots of videos on YouTube about them… if you have lots of database tables to combine for a given collection of data to work with they are absolutely amazing…

1 Like
9

Hope to add views creation and management to Wappler’s database manager soon :slight_smile:

4 Likes
10

Another amazing one to add would be MySQL Event Scheduler, I find it amazing to work with: https://hevodata.com/learn/mysql-event-scheduler/

11

@George, will that include case statements too? :thinking:

They are soooo powerful…

12

Personally I am not a fan of views.
Views are effectively a general query at db level providing a “pseudo” table.
If their result is exactly what you need the that’s fine.
If you query a view you add a second level of processing which, in many cases, can not be effectively optimised by the DB engine.
I would almost always look to use a query in preference to a view for these reasons.
If you are not worried about the performance hit then yes, they can make things easier

Parameterised stored procedures are more effective

3 Likes
13

Hi there Brian!
For views that don’t use aggregate functions there I have found no noticeable performance issues… and if I need an aggregate function I just do it in the server action instead of the view.

14

This gain is rapidly lost once you start to employ formatters on the Client side. Far better to do that within the database itself, especially in the case of numbers, and lots of them (currency and calculations). For us its swings and roundabouts as if you are utilising a well configured database server the performance loss you mention is minuscule (and the performance gains can be considerable), in comparison to rendering speed impact with formatting the data in the browser.

1 Like
15

Exactly @cheese… that was the reason I moved to using Views… my app was grinding to a halt in the client side.

I now use views to serve up all the text I need to display on the client side, which can often be coming from 6-8 different database tables.

Life changing for a complex crud app! :tada:

16

@Antony But what about calculation with DECIMAL values? Does “views” can handle them?
Biggest problem is that wne you trying to do a SUM, for instance DECIMAL + DECIMAN - it becomes as string in Wappler and you have to add DECIMAL.toNumber() in to every calclation in your backend.

17

@Notum, yes, you can do decimals in a view…

But doing the .toNumber() each time as I showed in the previous post is really not a problem…

It is just the way you need to do it!

18

Noted.
Thanks.

19

Absolutely, I avoid any type of client side processing where possible, especially the likes of “where” clauses in favour of subtables and multi references which added huge performance benefits. Very little cant be done with a query now.

1 Like