ODBC Command parameters

Posted by Elsworth Burmeister on 21-Oct-2019 13:00

Hi Guys

Can anyone please give me a working example of how to use the command parameters in and insert statement into a sql db. Ive tried a few times and couldnt get it working. Can the example please include 2 different datatypes to insert into. i keep running into issues where it seems to be overriding the first parameter with the last one.

Example below is how i got it working using 1 parameter and its for an update statement:

cmd = NEW System.Data.Odbc.OdbcCommand(SUBSTITUTE("UPDATE table SET [mage] = ? WHERE [document] = '&1'","xyz"),connection).

cmd:Parameters:Add("image", OdbcType:VarBinary). 

cmd:Parameters["image"]:Value = oReturn.

cmd:ExecuteNonQuery().

OE: 11.7

TIA

All Replies

Posted by dbeavon on 21-Oct-2019 13:45

You may want to check out Tom Bergman's presentation at

pugchallenge.org/downloads2017.html

It is titled  ".Net, Not Gui For .Net ".  I think there was some sqlclient and odbc stuff in there.

Another approach you might consider is using the Progress product, dataserver for sql.  That might be the so-called "right" approach.  (In the past I think that Progress objected to allowing customers use the CLR bridge for access to other database resources.)

Also please be careful to remember that the work you are doing on the ODBC client will not integrate with your ABL transaction.

Posted by jamesmc on 21-Oct-2019 13:48

Does something like the below work...

cmd = NEW System.Data.Odbc.OdbcCommand("UPDATE table SET [image] = ? WHERE [document] = ?", connection).

cmd:Parameters:Add("image", OdbcType:VarBinary).
cmd:Parameters:Add("document", OdbcType:VarChar). 

cmd:Parameters["image"]:Value = oReturn.
cmd:Parameters["document"]:Value = "xyz".

cmd:ExecuteNonQuery().

I have successfully used the below through a reader...

DEFINE VARIABLE cmd AS CLASS System.Data.Odbc.OdbcCommand NO-UNDO.
DEFINE VARIABLE conn AS CLASS System.Data.Odbc.OdbcConnection NO-UNDO.
DEFINE VARIABLE rdr AS CLASS System.Data.Odbc.OdbcDataReader NO-UNDO.

conn = NEW System.Data.Odbc.OdbcConnection("Driver=Sql Server;Server=<name>;Database=<name>").

conn:OPEN().

cmd = NEW System.Data.Odbc.OdbcCommand().

cmd:commandText = "SELECT * FROM table WHERE Company = ? AND AcctPrefix = ?".

cmd:Parameters:Add("CompId", System.Data.Odbc.OdbcType:Int). 
cmd:Parameters:Add("AcctNo", System.Data.Odbc.OdbcType:VarChar). 

cmd:Parameters["CompId"]:Value = "1".
cmd:Parameters["AcctNo"]:Value = "12345".

cmd:connection = conn.

rdr = cmd:ExecuteReader().

DO WHILE (rdr:Read()):

    MESSAGE
        rdr[0]
        rdr[1]
        rdr[2]
        rdr[3]
        rdr[4]
        VIEW-AS ALERT-BOX INFORMATION BUTTONS OK.

END.

This thread is closed