Inserting record in sql table with args as values

[bob9145]

Re: Inserting record in sql table with args as values

Thanks Pat still struggling with the args, but like an old English teacher told me punctuation is everything period! You spurred me to seek the result of sql_insert my answer was a ? mark away all the time!

sql_insert = "INSERT INTO stripe_subscription_plans (amount,object,currency) values ('" + amount + "','" + object + "','" + currency + "')"
?sql_insert
= "INSERT INTO stripe_subscription_plans (amount,object,currency) values ('1234','4321','usd')"

After 20 hours of Chinese Click torture I'm strung out. I need a break and a beer 'Exclamation Point'

[Pat Bremkamp]

Re: Inserting record in sql table with args as values

Couple things,
First, if you want to use arguments, then your execute should be something like
dim flag as L
flag = conn.execute(sql_insert,args)

when I'm building the statement as a string I like to use evaluate_string like
sql_insert = "insert into stripe_plan_2 (Amount_Confirmed,Date_Created,Mode,Product_GUID,Interval_Frequency,Currency_Type) values ( {Amount_Confirmed},{Date_Created},{Mode},{Product_GUID},'year',{Currency_Type})"

sql_insert = evaluate_string(sql_insert)

If you want to use your approach, then you need to remember to single quote string values such as
values ('" + Amount_Confirmed + "','" + Date_Created + "',

[bob9145]

Re: Inserting record in sql table with args as values

Trying to put together a string this way doesn't work well inconsistent.

Keep getting this error when I start to build out the statement to multiple fields:
Internal Parser Error-Node type cannot be mapped for the requested object

Funny was able to build the string out 4 fields and if I include a 'literal' more but when I put the fifth variable I get the error

Thinking it is not building the string as needed. My sql back end is mysql
Thinking the args would work better, or is the problem one in the same?
Built the table to match the controls on a ux used the Alpha Genie all character fields.

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

Amount_Confirmed = "4000"
Date_Created = "345676"
Currency_Type = "usd"
Plan_ID = "3456_ty"
Interval_Frequency = "year"
Mode = "1"
Product_Type = "Type"
Product_GUID = "49586799"
Trial_length = "45"

connString = "::Name::rmqv12"
sql_insert = "insert into stripe_plan_2 (Amount_Confirmed,Date_Created,Mode,Product_GUID,Interval_Frequency,Currency_Type) values (" + Amount_Confirmed + "," + Date_Created + "," + Mode + "," + Product_GUID + ",'year'," + Currency_Type + ")"
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)

[bob9145]

Re: Inserting record in sql table with args as values

This works but seems pretty low tech and likely to frustrate a dyslexic A D D type like myself

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 plan as c
plan = "3456"
connString = "::Name::ABC"
sql_insert = "insert into stripe_plan_2 (plan_id) values (" + plan + ")"

'dim args as SQL::Arguments
'args.set("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)
cs = ins.CallResult
ui_msg_box("Call result", cs.Text)

[bob9145]

This code succeeds but the arg value is empty? So a record is inserted into the database but the plan_id field is empty. Running it in the interactive window.
Looked simple in the examples and posts that I found. What am I missing?

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.set("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)
cs = ins.CallResult
ui_msg_box("Call result", cs.Text)