Back
  1. Home
  2. Give it a try on SQLBuilder tool

Open Exchange App Give it a try on SQLBuilder tool

Primary tabs

Henry Pereira's picture
Henry Pereira 27 March 2020
Contest, Docker, ObjectScript, InterSystems IRIS, InterSystems IRIS for Health, Open Exchange

SQLBuilder is a flexible and powerful SQL query string builder for InterSystems IRIS,

With SQLBuilder you have nice and clean object oriented methods, instead of having to use concatenation and substituition to generate dynamic queries.

A Dynamic SQL without SQLBuilder

A Dynamic SQL with SQLBuilder

 

If you like it, don't forget to vote in the IRIS Programming Contest

Check the related application on InterSystems Open Exchange.

Replies

Maks Atygaev's picture
Reply by Maks Atygaev 27 March 2020

Hello @Henry Pereira !

I think you created a fantastic tool with a great potential and roadmap.

I think your product is very wished by developers. Thank you to bring and share modern approaches to work with SQL.

0 0
 
Eduard Lebedyuk's picture
Reply by Eduard Lebedyuk 27 March 2020

My preferred approach is using a Query class element.

Here's how it can look like:

Class Sample.Person Extends %Persistent
{

Property Name As %String;

Query ByName(name As %String = "") As %SQLQuery
{
SELECT ID, Name
FROM Sample.Person
WHERE (Name %STARTSWITH :name)
ORDER BY Name
}

ClassMethod Try(name)
{
  set rset = ..ByNameFunc(name)
  do rset.%Display()
}

}

Short and concise.

+ 1 0
 
Henry Pereira's picture
Reply by Henry Pereira 27 March 2020

Hi @Eduard Lebedyuk 
Yes, you are totally right. Using query class element will be concise and faster.
The point to use dynamic query are when the user choose the parameters to you do the query. 
Using my simple example, when the method receive a name param or a age param to build the where clause. I really don't know how to solve it using query class.
In my $0.02 maybe will create an ByName and a ByAge methods, and another to combine both.
Like I said, it's a simple example that can solved by an OR, but in a complex report with the user need to choose by parameters use dynamic query could be an alternative
 

0 0
 
Vitaliy Serdtsev's picture
Reply by Vitaliy Serdtsev 27 March 2020

Using my simple example, when the method receive a name param or a age param to build the where clause. I really don't know how to solve it using query class.

Query FilterBy(
  Name As %String = "",
  Age As %Integer = "") As %SQLQuery(CONTAINID = 1, SELECTMODE = "RUNTIME") [ SqlName = SP_Sample_Filter_By, SqlProc ]
{
SELECT TOP 5 ID, Name, Age, SSN FROM Sample.Person
WHERE 
(nvl(:Name,'')='' or Name %STARTSWITH :Name)
AND
(nvl(:Age,'')='' or Age >= :Age)
}

Run Examples:
select * from Sample.SP_Sample_Filter_By(,47)
select * from Sample.SP_Sample_Filter_By('',47)

select * from Sample.SP_Sample_Filter_By('s',47)

select * from Sample.SP_Sample_Filter_By('s')
select * from Sample.SP_Sample_Filter_By('s','')
0 0
 
Evgeny Shvarov's picture
Reply by Evgeny Shvarov 27 March 2020

Method chains look nice. Very interesting project, Henry. Well done!

0 0