SQL insert does not function

Question

I perform design in cache and it looks like it does not allow multiple insert, i.e.

insert into Ruler (...) values (...), values()....

or

insert into Ruler (...) values (...), (....)

So I decided to create method to perform insert. The problem - it does not work. Each insert is fine. Delete also worked, but not insert. No error, just empty table.

Method Fill() As %Integer
{
    &sql(insert into Ruler (nameRuler, biography, idRuler) 
        values  ('Peter the Great','Born in Moscow, Russia on June 9, 1672, Peter the Great was a Russian czar in the late 17th century who is best known for his extensive reforms in an attempt to establish Russia as a great nation. He created a strong navy, reorganized his army according to Western standards, secularized schools, administered greater control over the reactionary Orthodox Church, and introduced new administrative and territorial divisions of the country.', 1)
        )
    &sql(insert into Ruler (nameRuler, biography, idRuler) values ('Boris Godunov','was de facto regent of Russia from c. 1585 to 1598 and then the first non-Rurikid tsar from 1598 to 1605. The end of his reign saw Russia descend into the Time of Troubles.', 2))

    //&sql(delete from Ruler)   
    &sql(SELECT COUNT(*) INTO :count 
        FROM Ruler)

    Quit "Total: "_count
}

Any ideas???

"Each insert is fine. Delete also worked, but not insert"? Do you mean separate insert statements worked fine but the [table value constructor](http://technet.microsoft.com/en-us/library/dd776382.aspx) method doesn't work? — T I, Nov 23 '13 at 10:21
Well, Yes. Separate insert work fine, if executed via Portal (this is analog for phpmyadmin). Two and more insert do not work in this portal (as syntax is wrong, so I supposed that this is not supported). None of insert work if called from Object Method. No error. I do not know about table value constructor, but do not think that it's it. I do not create new table, I use existing one. Now I am trying to do as ClassMethod, non Object.. May be it will work. (P.S. If you know how to call ClassMethod in Cache, please, tell. Their documentation is awfull. — Tigran, Nov 23 '13 at 10:30
write ##class(Ruler).Fill() - this is how ClassMethod called. And result is 0 (no insert again) — Tigran, Nov 23 '13 at 10:40

score 3 · Answer 1 · answered Nov 23 '13 at 16:55

As a starting point, Cache does not support multiple inserts in a single statement.

To answer your question about the failure, I suspect that you are being blocked from performing the inserts because by default Cache doesn't permit the ID to be inserted if the ID is automatically assigned. Your code isn't doing any checking of SQLCODE, so it's pretty tricky to confirm whether that is the case.

I would strongly recommend that your object code use dynamic SQL to perform your inserts, as that's a bit easier to maintain and perform error checking with. So your code could look something like the following:

ClassMethod Fill(Output pErrorMessage As %String) As %Integer
{
    Set pErrorMessage = ""
    Set tCount = 0
    Set tStatement = ##class(%SQL.Statement).%New()
    // If you want to use unqualified schema names then update the schema path
    Set tStatement.%SchemaPath = "MySchema,DEFAULT_SCHEMA"
    Set tStatus = tStatement.%Prepare("INSERT INTO Ruler (nameRuler, biography, idRuler) VALUES(?,?,?)")
    If $system.Status.IsError(tStatus) {
        Set pErrorMessage = $system.Status.GetErrorText(tStatus)
        Quit tCount
    }
    Set tRS1 = tStatement.%Execute("Peter the Great", "Born ...", 1)
    If (tRS1.%SQLCODE = 0) { // no logic for SQLCODE = 100 as this is an INSERT
        Set tCount = tCount + tRS1.%ROWCOUNT
    }
    Else {
        // Return an error
        Set pErrorMessage = "SQLCODE = " _ tRS1.%SQLCODE _ "; Message = " _ tRS1.%Message
        Quit tCount
    }
    // Repeat for subsequent rows
    // ...
    Quit tCount
}

The above is pretty verbose, but I can supply you a sample of checking the SQL code for your inserts using embedded SQL if you prefer.

Thank you, but I found a solution with simple objects... set o=##class(Ruler).%New() set o.nameRuler="Felix Fort" set o.idRuler=9 do o.%Save() — Tigran, Nov 23 '13 at 18:06
I will use my version, but may be somebody in the future will use SQL. — Tigran, Nov 23 '13 at 18:08
Also, there are a lot of good codelines... Better than official documentation :) — Tigran, Nov 23 '13 at 18:10
I agree with you that for a lot of simple cases the object approach is a bit easier to work with. I would still suggest that you check the return value from `o.%Save()` - there are always things that might prevent your save from succeeding, so it's best to make sure you can handle the unexpected! — DdP, Nov 24 '13 at 06:01

SQL insert does not function

1 Answers1