Written by

Question Alexandr Ladoshkin · Aug 3, 2018

Problem with Inserting ID

#Caché #Object Data Model #SQL

Hello there,

I have some problem with inserting date to table using dynamic sql.

I have Country class. This table has relationship with class Continent as parent and child. In addition I have another statistic class where property Country has type of Country class. I get ID such as "1||1" next I execute dynamic sql INSERT INTO Stats(Country) VALUES("_CountryId_"). Then I select Stats table and see that value of Country column is "11") As a result a can't get Country object.

Please advice solution of this problem

Thank you

Discussion (2)1

Add reply

Comments

Robert Cemper · Aug 3, 2018

|| is the concatenation operator in SQL

1||1 results in 11 as you have seen

'1||1' in single quotes should do it

0 0

Dan Pasco · Aug 3, 2018

Another option that abstracts the caller completely from the quoting requirements is to use a parameter. Parameters in Dynamic SQL are positional. Also, keep in mind that literals are replaced automatically in dynamic SQL statements so using a parameter for this will not add any overhead to the processing.

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

  do statement.prepare("insert into Stats(Country) VALUES (?)")

  set result = statement.execute(CountryId)

Keep in mind the lowercase form of prepare and execute work as their %Prepare/%Execute counterparts but they throw exceptions instead of using %Status interfaces.

0 0