Query Builder support for DATEPART

Questions, tips and tricks and techniques for scripting in Servoy

Query Builder support for DATEPART

Postby SteveInLA » Tue Jan 08, 2019 3:55 am

I see that QBFunction supports extracting month, day, year, hour, minute and second but can it also support the rest of the T-SQL DATEPART components? Specifically I am looking for quarter and dayofyear but week, weekday and millisecond would also be handy. See this link for reference.
SteveInLA
 
Posts: 233
Joined: Thu Jul 29, 2004 12:00 am
Location: Southern Oregon, USA

Re: Query Builder support for DATEPART

Postby paronne » Tue Jan 08, 2019 3:47 pm

Hi Steve,

QBFunction indeed doesn't support the other DATEPART components since they are not supported by the engine the QBuilder is build on.
To perform such type of queries using the query builder you can create a custom function of your own in your SQL server, which would return the DATEPART.

For instance for a weekday in SQL Server it could look like

Code: Select all
CREATE FUNCTION dbo.myweekday (@input datetime)
RETURNS int
AS BEGIN
    RETURN datepart(weekday, @Input)
END


Code: Select all
select dbo.myweekday(current_timestamp)
==> returns 6 (Friday)


then in the QueryBuilder you would use

Code: Select all
q.result.add(q.functions.custom('dbo.myweekday',q.columns.startdatetime));


please take into account that such function needs to be included in all you DBs, development DB, testing DB, production DB etc..

Regards,
Paolo
paronne
 
Posts: 199
Joined: Fri Nov 02, 2012 3:21 pm

Re: Query Builder support for DATEPART

Postby SteveInLA » Tue Jan 08, 2019 5:36 pm

Is there a way to apply a custom function like this to a query based on an in memory datasource?
SteveInLA
 
Posts: 233
Joined: Thu Jul 29, 2004 12:00 am
Location: Southern Oregon, USA

Re: Query Builder support for DATEPART

Postby SteveInLA » Wed Jan 09, 2019 1:36 am

Does this only work in 8.4? I am using 8.3.3 and there is code completion for query.functions.custom(name, args...) but when I try it it gives this error:
Code: Select all
ERROR com.servoy.j2db.util.Debug - Not a valid sql name: dbo.datepart_year

This error occurs executing this code:
Code: Select all
application.output(databaseManager.getSQL(query));

The functions do appear in the database's Procedures folder.
SteveInLA
 
Posts: 233
Joined: Thu Jul 29, 2004 12:00 am
Location: Southern Oregon, USA

Re: Query Builder support for DATEPART

Postby paronne » Wed Jan 09, 2019 11:21 am

Hi Steve,

the query builder function query.functions.custom(name, args...) is supported also in 8.3.3 and previous versions. This function allow to run any custom function available in the DB within the Query Builder.
Not sure why you get the error, maybe a typo; have you tried to test the function directly within your database (using any database tool) ?

About in-memory datasources, that is a very interesting question, honestly i don't know if is possible at all.. i would try at first to test if i can run any of the built in function of HyperSQL as a custom function (http://hsqldb.org/doc/guide/builtinfunctions-chapt.html)
If the built in functions can't be used to obtain the datepart, i don't know if is possible to apply the same solution as for SQL Server; since the in-memory DB is created dynamically using UUID as Table and Column names, i am not sure if you can alter at all the DB using rawSQL.. there is surely a challenge to map the table/column names to the proper UUIDs.

Maybe one of our R&D experts can tell something more about it.

Regards,
Paolo
paronne
 
Posts: 199
Joined: Fri Nov 02, 2012 3:21 pm

Re: Query Builder support for DATEPART

Postby SteveInLA » Wed Jan 09, 2019 10:17 pm

No typos. Functions work in SQL Server Management Studio using 'SELECT dbo.datepart_dayofyear(o.orderdate) FROM orders as o'. What is odd is that this method works fine from within Servoy:
Code: Select all
var q = 'SELECT dbo.datepart_dayofyear(o.orderdate) FROM orders as o'
var ds = databaseManager.getDataSetByQuery('servoy_test_mssql',q,null,10);

I have submitted a case here: https://support.servoy.com/browse/SVY-13196 with sample solutions.
I was able to get this to work in PostgreSQL using query builder but had to exclude the schema and just send the name of the function, but when I do that in MS SQL, I get a different error.
Thanks for looking into this,
Steve
SteveInLA
 
Posts: 233
Joined: Thu Jul 29, 2004 12:00 am
Location: Southern Oregon, USA


Return to Methods

Who is online

Users browsing this forum: No registered users and 4 guests