CODEWITHSUNDEEP
sqlintersystems-cacheintersystems
Tigran
Tigran

Reputation: 1057

SQL insert does not function

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???

Upvotes: 1

Views: 746

Answers (1)

DdP
DdP

Reputation: 438

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.

Upvotes: 3

Related Questions