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