Alpha DevCon 2018
Results 1 to 20 of 20

Thread: Inserting record in sql table with args as values

  1. #1
    Member bob9145's Avatar
    Real Name
    Bob Senski
    Join Date
    Jun 2009
    Posts
    736

    Default Inserting record in sql table with args as values

    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)
    Last edited by bob9145; 03-07-2018 at 10:42 PM.

  2. #2
    Member bob9145's Avatar
    Real Name
    Bob Senski
    Join Date
    Jun 2009
    Posts
    736

    Default 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)

  3. #3
    Member bob9145's Avatar
    Real Name
    Bob Senski
    Join Date
    Jun 2009
    Posts
    736

    Default 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)
    Last edited by bob9145; 03-08-2018 at 02:04 AM.

  4. #4
    VAR Pat Bremkamp's Avatar
    Real Name
    Pat Bremkamp
    Join Date
    Apr 2000
    Location
    Oregon, USA
    Posts
    2,575

    Default 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 + "',
    Pat Bremkamp
    MindKicks Consulting

  5. #5
    Member bob9145's Avatar
    Real Name
    Bob Senski
    Join Date
    Jun 2009
    Posts
    736

    Default 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'

  6. #6
    Member bob9145's Avatar
    Real Name
    Bob Senski
    Join Date
    Jun 2009
    Posts
    736

    Default 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.
    Last edited by bob9145; 03-08-2018 at 10:48 PM.

  7. #7
    Member bob9145's Avatar
    Real Name
    Bob Senski
    Join Date
    Jun 2009
    Posts
    736

    Default 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!
    Last edited by bob9145; 03-09-2018 at 02:07 AM.

  8. #8
    "Certified" Alphaholic glenschild's Avatar
    Real Name
    Glen Schild
    Join Date
    Apr 2000
    Location
    Frome, Somerset, UK
    Posts
    1,437

    Default 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
    Glen Schild



    My Blog


  9. #9
    Member
    Real Name
    Triffid Girl
    Join Date
    Feb 2018
    Location
    UK
    Posts
    11

    Default 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

  10. #10
    Member bob9145's Avatar
    Real Name
    Bob Senski
    Join Date
    Jun 2009
    Posts
    736

    Default 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
    Last edited by bob9145; 03-09-2018 at 12:17 PM.

  11. #11
    "Certified" Alphaholic glenschild's Avatar
    Real Name
    Glen Schild
    Join Date
    Apr 2000
    Location
    Frome, Somerset, UK
    Posts
    1,437

    Default 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()
    Glen Schild



    My Blog


  12. #12
    Member bob9145's Avatar
    Real Name
    Bob Senski
    Join Date
    Jun 2009
    Posts
    736

    Default 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()

  13. #13
    "Certified" Alphaholic glenschild's Avatar
    Real Name
    Glen Schild
    Join Date
    Apr 2000
    Location
    Frome, Somerset, UK
    Posts
    1,437

    Default Re: Inserting record in sql table with args as values

    Quote Originally Posted by bob9145 View Post
    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.
    Glen Schild



    My Blog


  14. #14
    Member bob9145's Avatar
    Real Name
    Bob Senski
    Join Date
    Jun 2009
    Posts
    736

    Default 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
    Last edited by bob9145; 03-12-2018 at 01:20 AM.

  15. #15
    Member
    Real Name
    Triffid Girl
    Join Date
    Feb 2018
    Location
    UK
    Posts
    11

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

    Code:
    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

  16. #16
    Member bob9145's Avatar
    Real Name
    Bob Senski
    Join Date
    Jun 2009
    Posts
    736

    Default Re: Inserting record in sql table with args as values

    Thanks Gina. Just when I thought I new something
    Bob
    Last edited by bob9145; 03-12-2018 at 01:28 PM.

  17. #17
    Member
    Real Name
    Triffid Girl
    Join Date
    Feb 2018
    Location
    UK
    Posts
    11

    Default Re: Inserting record in sql table with args as values

    Yes these things are sent to try us. :D

  18. #18
    Member bob9145's Avatar
    Real Name
    Bob Senski
    Join Date
    Jun 2009
    Posts
    736

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

  19. #19
    Member
    Real Name
    Triffid Girl
    Join Date
    Feb 2018
    Location
    UK
    Posts
    11

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

    Code:
    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

    Code:
    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

  20. #20

Similar Threads

  1. update record to reflect null values in a table
    By eduduru in forum Mobile & Browser Applications
    Replies: 17
    Last Post: 12-17-2015, 03:46 AM
  2. Summary Values - inserting elsewhere
    By dfricke10 in forum Mobile & Browser Applications
    Replies: 11
    Last Post: 11-28-2013, 04:07 PM
  3. Changing args Values not adding additional arrays
    By MRall in forum Application Server Version 11 - Web/Browser Applications
    Replies: 2
    Last Post: 05-14-2013, 01:53 PM
  4. Inserting records into an existing table from a different table.
    By Ted Giles in forum Alpha Five Version 11 - Desktop Applications
    Replies: 10
    Last Post: 01-11-2013, 05:07 PM
  5. Replies: 4
    Last Post: 10-06-2006, 02:55 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •