Article
· Sep 15 2m read

From "Oops" to "Aha!" - Avoiding Beginner Mistakes in ObjectScript

#Globals #Languages #ObjectScript #Caché #Ensemble #HealthShare #InterSystems IRIS #InterSystems IRIS for Health #Global Masters #Other

Starting out with ObjectScript, it is really exciting, but it can also feel a little unusual if you're used to other languages. Many beginners trip over the same hurdles, so here are a few "gotchas" you'll want to watch out for. (Also few friendly tips to avoid them)

NAMING THINGS RANDOMLY

We have all been guilty of naming something Test1 or MyClass just to move on quickly. But once your project grows, these names become a nightmare. 

➡ Pick clear, consistent names from the start. Think of it as leaving breadcrumbs for your future self and your teammates.

MIXING UP GLOBALS AND VARIABLES

Globals (^GlobalName) can be confusing at first. They're not just normal variables. They live in the database and stick around even after your code stops running.

➡ Use them only when you really need persistent data. For anything else, stick with local variable. (This also saves storage.)

FORGETTING TRANSACTIONS

Imagine updating a patient record, and your session crashes halfway. Without a transaction, you are left with half-baked data.

➡ Wrap up important updates in TSTART/TCOMMIT. It is like hitting "save" and "undo" at the same time.

BUILDING SQL IN STRINGS

It is tempting to just throw SQL into strings and execute it. But that quickly gets messy and hard to debug.

➡ Use embedded SQL. It's cleaner, safer and easier to maintain.

EXAMPLE:

❌ Building SQL in Strings

Set id=123
Set sql="SELECT Name, Age FROM Patient WHERE ID="_id
Set rs=##class(%SQL.Statement).%ExecDirect(,sql)

✅ Using Embedded SQL

&SQL(SELECT Name, Age INTO :name, :age FROM Patient WHERE ID=:id)
Write name_" "_age,!

SKIPPING ERROR HANDLING

Nobody likes seeing their app crash with a cryptic message. That usually happens when error handling is ignored.

➡Wrap risky operations in TRY/CATCH and give yourself meaningful error messages.

IGNORING BETTER TOOLS

Yes, the terminal works. But if you only code there, you are missing out.

➡ Use VS Code with the ObjectScript extension. Debugging, autocomplete, and syntax highlighting make life so much easier.

REINVENTING THE WHEEL

New developers often try writing their own logging or JSON handling utilities, not realizing ObjectScript already has built-in solutions.

➡ Explore%Library and dynamic objects before rolling your own.

WRITING "MYSTERY CODE"

We have all thought "I'll remember this later."

⚠️SPOILER: YOU WON'T! 

➡ Add short, clear comments. Even a single line explaining why you did something goes a loooong way.

 

FINAL THOUGHTS : )

Learning Objectscript is like learning any other new language. It takes a little patience, and you will make mistakes along the way. The key is to recognize these common traps early and build good habits from the start. That way, instead of fighting the language, you will actually enjoy what it can do. :)

Discussion (15)7
Log in or sign up to continue
David Hockenbroch · Sep 15

FYI, in your "Building SQL In Strings" section, you can also still use %SQL.Statement like this:

set stmt = ##class(%SQL.Statement).%New()
// Note the question mark in the query.
set query = "SELECT Name, Age FROM Patient WHERE ID=?"
set sc = stmt.%Prepare(query)
// You can add error handing here if the above status results in an error
// Providing variables to the %Execute method will insert them where the question marks are in the query, in order
set rs = stmt.%Execute(id)
2 1
Theo Stolker · Sep 23

Thanks @David Hockenbroch! 

I strongly discourage the use of embedded sql, it has many disadvantages. For complex queries you can use the following construct (a bit overkill for this example):

    set query = 0
    set args = 0
    set query($Increment(query)) = "SELECT Name, Age"
    set query($Increment(query)) = "FROM Patient"
    set query($Increment(query)) = "WHERE Age >= ?"
    set args($Increment(args)) = age

    set result = ##class(%SQL.Statement).%ExecDirect(, .query, args...)
    
    if result.%SQLCODE < 0
    {
        // Your error handling here
        throw ##class(%Exception.SQL).CreateFromSQLCODE(result.%SQLCODE,result.%Message)
    }

    while result.%Next()
    {
        write result.Name," ",result.Age,!
    }
0 1
Enrico Parisi · Sep 23

I strongly discourage the use of embedded sql, it has many disadvantages.

Sorry Theo but I don't agree at all.

The differences (disadvantageous or not) between Embedded SQL and Dynamic Queries have evaporated since few years now, please have a look to this article from @Benjamin De Boe :

New in 2020.1: the Universal Query Cache

In general, I advice to use the query type (Embedded vs. Dynamic) that best fit in given code pattern, for example Embedded SQL is very convenient for query that returns 1 row.

Or maybe you have other issues with Embedded SQL?

4 0
Benjamin De Boe · Sep 23

I agree it's become more of a style preference thing, so absolutely something to have strong opinions and religious debates about :-)

The only thing to avoid is using Dynamic SQL to conveniently build your query and then feed in query parameters through string concatenation rather than as true ?-style parameters. That's a security risk you wouldn't run with Embedded SQL. That's all. Back to the debate! :-)

3 0
Gabriel Ing · Oct 21

I believe concatenation runs the risk of SQL injection - a good example is given in the article Optimizing SQL LIKE Security, Performance and Best Practices | InterSystems. The example in the article shows someone add an `OR 1=1` parameter to be added to a WHERE clause to access all available data, rather than just the data they should access. As I understand it, preparing the statement with ? for parameters prevents this risk.

2 0
Theo Stolker · Sep 23

Hi @Enrico Parisi ,

I did have warnings from the objectscriptQuality for VSCode plugin on the embedded SQL for side effects but now I re-check that warning no longer seems to be there.

I never liked the different behavior of embedded sql, like when you have no result, you have no easy way to find that out. More importantly, when you have multiple rows as result, you need to start using a cursor and the code becomes complicated.

With  ##class(%SQL.Statement).%ExecDirect(), the code is identical whether there is one or there are many results and that way maintainability is better.

But as @Benjamin De Boe already said: It is probably more of a style preference.

1 0
David Hockenbroch · Sep 23

@Theo Stolker I'm not sure what you mean by, "I never liked the different behavior of embedded sql, like when you have no result, you have no easy way to find that out." When you run an embedded query, it sets a variable called SQLCODE. If SQLCODE = 0, query completed with results. If SQLCODE = 100, query completed with no results. If SQLCODE < 0, there was an error with the query.

4 0
Anthony Filaski · Oct 1

Would like to add to this. Embedded and Dynamic queries have their place. So picking one over the other really depends upon the problem you're trying to solve.

Embedded is better suited for simple queries. Use when you need to perform non-complex queries with single row result. Can additionally use embedded with cursor for multi-row results.

Dynamic, as it's properly named, is better suited for programmatically formed complex queries with many expected results. In other words, your query isn't or can't be static.

All depends on the requirements and scenario. Picking the appropriate one can help make your code cleaner and easier to read.

*Beginner Tip
One beginner mistake to look out for is ensure you understand which mode your query is using. Display Mode and Logical Mode behave differently and it is a common beginner mistake to code the query for the incorrect mode.

2 0
Evgeny Shvarov · Sep 24

Thanks @David Hockenbroch !

An almost ideal pattern to run an SQL query in ObjectScript. 

If you could also share with error handling around?

My typical pattern looks like this one below. Includes try-catch in case when I need to rollback something:

try {

set tStatement = ##class(%SQL.Statement).%New()

set query = "ALTER Table "_table_" ADD PRIMARY KEY ("_column_")"

$$$TOE(st,tStatement.%Prepare(query))

set rset=tStatement.%Execute()

if rset.%SQLCODE < 0 {

write "Error adding primary key: "_rset.%SQLCODE_" "_rset.%Message

set ex=##class(%Exception.SQL).CreateFromSQLCODE(rset.%SQLCODE,rset.%Message)

$$$ThrowStatus(ex.AsStatus())

}

}

catch ex {

set st=ex.AsStatus()

}
0 0