New post
Article
· Apr 8 1m read

using Procedure Parameters with ODBC/JDBC

#JDBC #ObjectScript #ODBC #SQL #InterSystems IRIS

Hi,

I was struggling with a procedure that was meant to receive a string and use it as a filter, I've found that since I want the procedure to do some data transformation and return a dataset, I needed to use objectScript language.

I've created the procedure using the SQL GUI in the portal, and everything works fine when calling the procedure from the SQL GUI but not through a JDBC connection here is the call "call spPatientOS('2024-04-07T12:35:32Z')"

the bottom line is that the procedure was created with the parameter defined as STRING(MAXLEN=1) which means that my parameter from the jdbc was truncated to "call spPatientOS('2')"  

changing the MAXLEN fixed the ISSUE

HTH,

Eyal

Discussion (2)2
Log in or sign up to continue
Kevin Kindschuh · Apr 15

When you created the SP in with SQL DDL, did you have to implement Query/Exec/Fetch/Close methods to return the filtered dataset, or did you use some other cleaner method?

When you create the SP in SQL it inherits from class %SQLQuery, which implements Query/Exec/Fetch/Close, but if you create the SP with ObjectScript it inherits from %Query with does not -- making it easy to return a single value but messy to return a resultset. Am I missing a trick?

0 0
Eyal Levin · Apr 18

Hi Kevin,

here is an example of my procedure


create procedure bidata.spObservationOS(IN maxDate VARCHAR) RESULT SETS
    LANGUAGE OBJECTSCRIPT
{
    do $system.SQL.Execute("TRUNCATE table bidata.ObservationsStg")
    do $system.SQL.Execute("INSERT %NOINDEX into bidata.ObservationsStg (ID, IssuedTime, Key, ObservationCategoryCode, ObservationCategoryDisplay, ObservationCode, ObservationCodeText, ObservationDisplay, ObservationEncounterReference, ObservationIdentifier, ObservationLastUpdated, ObservationSpecimenReference, ObservationStatus, ObservationSubjectReference, ObservationValueCode, ObservationValueDisplay, ObservationValueText, PerformerDisplay, PerformerIdentifier) select ID, IssuedTime, Key, ObservationCategoryCode, ObservationCategoryDisplay, ObservationCode, ObservationCodeText, ObservationDisplay, ObservationEncounterReference, ObservationIdentifier, ObservationLastUpdated, ObservationSpecimenReference, ObservationStatus, ObservationSubjectReference, ObservationValueCode, ObservationValueDisplay, ObservationValueText, PerformerDisplay, PerformerIdentifier FROM BIProjection.Observation where ObservationLastUpdated > maxDate")
    do $system.SQL.Execute("BUILD INDEX FOR TABLE bidata.ObservationsStg")
    do $system.SQL.Execute("UPDATE bidata.Observations set p.IssuedTime = s.IssuedTime, p.ObservationCategoryCode = s.ObservationCategoryCode, p.ObservationCategoryDisplay = s.ObservationCategoryDisplay, p.ObservationCode = s.ObservationCode, p.ObservationCodeText = s.ObservationCodeText, p.ObservationDisplay = s.ObservationDisplay, p.ObservationEncounterReference = s.ObservationEncounterReference, p.ObservationIdentifier = s.ObservationIdentifier, p.ObservationLastUpdated = s.ObservationLastUpdated, p.ObservationSpecimenReference = s.ObservationSpecimenReference, p.ObservationStatus = s.ObservationStatus, p.ObservationSubjectReference = s.ObservationSubjectReference, p.ObservationValueCode = s.ObservationValueCode, p.ObservationValueDisplay = s.ObservationValueDisplay, p.ObservationValueText = s.ObservationValueText, p.PerformerDisplay = s.PerformerDisplay, p.PerformerIdentifier = s.PerformerIdentifier from bidata.Observations p join bidata.ObservationsStg s on p.ID = s.ID")
    do $system.SQL.Execute("INSERT %NOINDEX into bidata.Observations (ID, IssuedTime, Key, ObservationCategoryCode, ObservationCategoryDisplay, ObservationCode, ObservationCodeText, ObservationDisplay, ObservationEncounterReference, ObservationIdentifier, ObservationLastUpdated, ObservationSpecimenReference, ObservationStatus, ObservationSubjectReference, ObservationValueCode, ObservationValueDisplay, ObservationValueText, PerformerDisplay, PerformerIdentifier) SELECT ID, IssuedTime, Key, ObservationCategoryCode, ObservationCategoryDisplay, ObservationCode, ObservationCodeText, ObservationDisplay, ObservationEncounterReference, ObservationIdentifier, ObservationLastUpdated, ObservationSpecimenReference, ObservationStatus, ObservationSubjectReference, ObservationValueCode, ObservationValueDisplay, ObservationValueText, PerformerDisplay, PerformerIdentifier FROM bidata.ObservationsStg where ID not in (select ID from bidata.Observations)")
    do $system.SQL.Execute("BUILD INDEX FOR TABLE bidata.Observations")
    do $system.SQL.Execute("UPDATE BIData.seqRunLog set LastRunTime = (select max(ObservationLastUpdated) from BIData.ObservationsStg) where processName = 'Observation'")
        
}

in the studio it looks like 

spObservationOS(maxDate As  String(MAXLEN=1))

I had to manually change the maxlen

0 0