Inserting record in sql table with args as values

[bob9145]

Re: Inserting record in sql table with args as values

http://https://www.alphasoftware.com/documentation/pages/HowTo/UX/Saving%20Dialog%20Contents%20to%20a%20SQL%20Database.xml

[TriffidGirl]

Re: Inserting record in sql table with args as values

I am not sure as I haven't done it in Alpha yet. In other coding I would either not populate the argument so something like this...

Args.Add('order_id', 1)
if(len(order_no) != 0)
    Args.Add('order_no',747859)
end if

OR

I would populate it with a zero length string

Args.Add('order_id', 1)
if(len(order_no) != 0) 
    Args.Add('order_no','747859')
else
   Args.Add('order_no', '')
end if

It really depends on what the database is going to expect (int or string etc) and what constraints you have on your data.

HTH
Gina

[bob9145]

Re: Inserting record in sql table with args as values

So a related question: Sometimes one or more of the variables I'm using to populate the args is empty. This breaks the select statement. What would
be the best way to handle this? I'm parsing a curl reponse, but depending on the call elements some of the returned values may not be there.

[TriffidGirl]

Re: Inserting record in sql table with args as values

Yes these things are sent to try us. :D

[bob9145]

Re: Inserting record in sql table with args as values

Thanks Gina. Just when I thought I new something
Bob

[TriffidGirl]

Re: Inserting record in sql table with args as values

Hi Bob,

From what I can see your original code should work, with the addition of the args in your execute statement and tweak when you build your args...

dim conn as SQL::Connection
dim ins as SQL::InsertStatement
dim qry as SQL::Query
dim rs as SQL::ResultSet
dim cs as SQL::CallResult
dim connString as C
dim sql_insert as C
dim sql_query as C

connString = "::Name::ABC"
sql_insert = "insert into stripe_plan_2 (plan_id) values (:plan)"

dim args as SQL::Arguments
args.Add("plan","6789")

IF .not. conn.open(connString) THEN
end
END IF
IF .not. ins.parse(sql_insert) THEN
ui_msg_box("Error", ins.callresult.text)
end
END IF

ins.execute(conn, args)
cs = ins.CallResult
ui_msg_box("Call result", cs.Text)

...try them apples.

Gina

[bob9145]

Re: Inserting record in sql table with args as values

Think my problem is that I try to bend coding to my own will for my own purpose instead of taking the dial off the clock, and finding out what makes the watch tick. Just need to put the bracket here, and the comma there, copy and paste this snippet match it with this block, try it here, try it there until I've clicked myself into a frenzy.

dim conn as SQL::Connection
dim connString as C
dim ins as SQL::InsertStatement
dim sql_insert as C
dim args as sql::arguments
connString = "::Name::myconnection"
args.add("arg1","selwyn")
sql_insert = "insert into stripe_subscription_plans (created) values (:arg1)"
sql_insert = replace_arguments_in_string(Sql_insert ,args ,3)
conn.open(connString)
IF .not. conn.open(connString) THEN

end
END IF
IF .not. ins.parse(sql_insert) THEN
ui_msg_box("Error", ins.callresult.text)
end
END IF

ins.execute(conn)
cs = ins.CallResult
ui_msg_box("Call result", cs.Text)

Tick Tock

[glenschild]

Re: Inserting record in sql table with args as values

Running it in the interactive window. Still no joy.
Error: Missing tagged string end construct

Two points

1. If any of the fields are NOT characters and I suspect interval_count and ID are numeric then do not declare the values within speech marks they should be value1 = 12345

2. Using the
qry=<<%sql%
insert statement here
%sql%

is only needed if you place the statement on different lines. If you place the statement on a single line then qry="insert statement here" is all that is needed.

[bob9145]

Re: Inserting record in sql table with args as values

Running it in the interactive window. Still no joy.
Error: Missing tagged string end construct

DIM cn AS SQL::Connection
DIM rs AS SQL::ResultSet
DIM args AS SQL::arguments
DIM qry AS C
value1 ="12345"
value2 ="123456"
value3 ="1234567"
'set args
args.Set("arg1",value1)
args.Set("arg2",value2)
args.Set("arg3",value3)

'qry string
qry=<<%sql% INSERT INTO (stripe_subscription_plans nickname,interval_count,id)VALUES (:arg1,:arg2,:arg3)%sql%

'open connection
cn.open("::Name::myconnection")

flag = cn.Execute(qry,args)
if flag = .f. then
ui_msg_box(cn.CallResult.text)
end if


'close connection
cn.close()

[glenschild]

Re: Inserting record in sql table with args as values

For me too much is going on! Below is a template I use which works, note the order of everything.

DIM cn AS SQL::Connection
DIM rs AS SQL::ResultSet
DIM args AS SQL::arguments
DIM qry AS C

'set args
args.Set("arg1",value)
args.Set("arg2",value)
args.Set("arg3",value)

'qry string
qry=<<%sql%
INSERT INTO tablename (
field1,
field2,
field3
)VALUES(
:arg1,
:arg2,
:arg3)
%sql%

'open connection
cn.open("::NAME::conn")

flag = cn.Execute(qry,args)
if flag = .f. then
ui_msg_box(cn.CallResult.text)
end if


'close connection
cn.close()

[bob9145]

Re: Inserting record in sql table with args as values

Ok back to args: So sql_insert is building a string for the statement so it would seem to me that you still have turn it into a string ""?

sql_insert = "INSERT INTO stripe_subscription_plans (nickname,interval_count) values (:arg1,:arg2)"

However :arg1 and arg2: just become dimmed text in the editor. Here is the complete simple block. Shouldn't be this hard.

dim conn as SQL::Connection
dim ins as SQL::InsertStatement
dim qry as SQL::Query
dim rs as SQL::ResultSet
dim cs as SQL::CallResult
dim connString as C
dim sql_insert as C
dim sql_query as C
dim args as SQL::Arguments



First_Name = "Joe"
Last_Name = "Smith"

connString = "::Name::rmqv12"
sql_insert = "INSERT INTO stripe_subscription_plans (First_Name,Last_Name) values (:arg1,:arg2)"
args.Set("arg1",First_Name)
args.Set("arg2",Last_Name)
ins.Execute(sql_insert, args)



IF .not. conn.open(connString) THEN
end
END IF
IF .not. ins.parse(sql_insert) THEN
ui_msg_box("Error", ins.callresult.text)
end
END IF

[TriffidGirl]

Re: Inserting record in sql table with args as values

Hi,

I don't know if this is any help, but you need to pass the arguments in as well when you execute.

ins.Execute(sql, args)

I think you have set the args correctly in your original post.

HTH
Gina

[glenschild]

Re: Inserting record in sql table with args as values

Coming into this thread late and if I have missed anything said earlier then apologies but as a rule of thumb always use arguments to populate out a sql statement.

Also I always "force" the field type to be sure by using convert_type() unless it is a character field.

So if you have a sql insert statement as:

INSERT INTO tablename (number, text, text, logical) VALUES (:arg1, :arg2, :arg3, :arg4)

and to populate the arguments

args.Set("arg1",convert_type(numberfield,"N"))
args.Set("arg2",textfield1)
args.Set("arg3",textfield3)
args.Set("arg4",convert_type(logicalfield,"L"))

Regards

[bob9145]

Re: Inserting record in sql table with args as values

Wrestling match: Building a string this way is temperamental, what I found is the end of the string is buggy. Whatever mechanism is writing to the file or creating the string is creating some typos.
I was able to get it working by getting a couple column statement to work. Instead of building the string by adding additional columns and values to the back I added to the front. I also returned the string to the ux working preview so the exact string was displayed. So it is either my machine or Alpha's machine. Welcome to the machine comes to mind for you old heads.

'value' ')' I checked 10 times I was not creating this the boogeyman was!

[bob9145]

Re: Inserting record in sql table with args as values

If someone could post a very simple complete block with args as the values in a insert statement it would be helpful. Saw a couple more complicated examples in other posts but can't get a basic one to work. My args are empty.