Embedded Python programming with Claude Code: A bit of a trap

Claude Code has a strong understanding of IRIS, but unexpected issues still occur.

The first issue is one that has already happened several times and is likely to continue occurring if not properly addressed.

In IRIS, the collation for string data (%String) is set to SQLUPPER by default. As a result, when data is retrieved via SQL, it may be returned in uppercase (for example, when sorting and aggregating with GROUP BY).

If you are not aware of this behavior (and most people typically aren’t), and you give Claude Code instructions that depend on the actual values of that data, you may not get the expected results.

For example, if you instruct it to extract data where a column value equals “Support,” it may generate a condition like = 'Support' in the SQL query.

However, since the actual returned value is “SUPPORT,” the condition does not match.

This issue can likely be avoided either by explicitly documenting this behavior in Claude Code’s instructions or by changing the COLLATE setting in the class definition to SQLSTRING.

Another issue I encountered recently is something that would be quite difficult to handle.

As many of you may know, IRIS has a very useful feature called calculated fields.

While convenient, this feature is also extremely flexible, and if you’re not careful, it can lead to situations that seem questionable from an SQL perspective.
The issue this time involved the following calculated field:
 

Property ReportMD As %Integer [ Calculated, SqlComputeCode = {set {*} = $translate($justify({ReportMonth},2)," ",0)_$translate($justify({ReportDay},2)," ",0)}, SqlComputed ];

This calculated field formats the month and day into an MMDD format, fixed at four digits for printing and other purposes.

For example, March 3rd would be represented as 0303.

The issue here is that the type is defined as %Integer.

(The reason for using %Integer is, as mentioned below, to allow numerical comparisons.)

If this were a physical field, attempting to insert a value like 0303 directly would result in a validation error. However, with a calculated field, the value can be transformed into any format via code.

The problem arose when I had Claude Code generate an SQL query to compare dates using this calculated field.

The generated code did not work correctly, so I requested a revision.

At that point, Claude Code spent an unusually long time attempting to resolve the issue, eventually exhausting its available resources and reporting that it could not continue.

I then investigated by adding debug code to the generated output and found that the SQL query was not producing the intended results.

After analyzing the root cause, I discovered that the following condition:

where (reportmd >= ? and reportmd <= ?

was not behaving as expected.

From an SQL perspective, 0401 is not treated as a numeric value, so this behavior is understandable.

The solution turned out to be:

where (+reportmd >= ? and +reportmd <= ?

which forces numeric evaluation by prefixing the field with a plus sign.

This is the kind of issue that would be extremely difficult for Claude Code to identify, no matter how hard it tries.

(I finally understood why it exhausted its resources.)

The root cause lies in ambiguity inherent in the original definition, so when using generative AI for coding, it seems important to anticipate such issues in advance and design in a way that avoids them.

In any case, I expect more unexpected situations to arise, but I hope to accumulate knowledge and experience step by step.