none
How to insert records through cursor? RRS feed

  • Question

  • Dear All,
    lnRes=SQLEXEC(iHandle,"SELECT id,first_name,last_name,city,state FROM PList WHERE ID=1","csrTemp")
    
    lnRes=SQLEXEC(iHandle,"INSERT INTO Owners(id,first_name,last_name,city,state) SELECT id,first_name,last_name,city,state FROM csrTemp")
    
    MESSAGEBOX(lnRes)
    Here I'm retrieving data from SQL table PList to cursor csrTemp and trying to insert all data from that cursor but failed and returning the value-1. Can anyone suggest me what proper code should be through single line query at here? I'm using MS SQL Server 2008.

    Regards
    ShahidSQL
    Saturday, July 13, 2019 12:15 PM

All replies

  • Yes, this can't work as sqlexec sends sql to a server that executes this sql there. And within SQL Server there is no access to your VFP workareas, so also not your cursor.

    You make can make a remote view updatable, a cursoradapter too or a cursor created by a sql SELECT query in the first place. Pick whatever you're familiar with the most. So in your case with a little extra CURSORSETPROPS you could make crsTemp updatable towards the PLIst table where data came from.

    The CURSORSETPROPS necessary are about telling where data will go, what's the primary key there and how that data maps and which fields are updatable at all.

    You'll need to use CURSORSETPROP at least on this minimum set of preroperties: SendUpdates, Buffering, Tables, KeyFieldlist, UpdatableFieldList and UpdateNameList, which are exactly the settings you also do when you define updatable remote views

    The call to commit changes to SQL Server will be Tableupdate, just like with views. And that'll then create a whole list of all necessary insert statements and updates and deletes , if you first load data and modify or delete that.

    But no, in that simple manner that doesn't work, you can't extend the access of SQL Server to VFP workareas, just because the sql query comes from VFP.

    Even shorter: Read about remote database usage with SPT: https://docs.microsoft.com/en-us/previous-versions/visualstudio/foxpro/s4z20fzf%28v%3dvs.71%29

    and

    https://docs.microsoft.com/en-us/previous-versions/visualstudio/foxpro/xhs04axc(v=vs.71)

    Bye, Olaf.

    Sunday, July 14, 2019 9:08 AM
  • If you're not doing any kind of transformation of the data on the VFP side, just combine the two into a single command you send to the server.

    lnRes=SQLEXEC(iHandle,"INSERT INTO Owners(id,first_name,last_name,city,state) SELECT id,first_name,last_name,city,state FROM PList WHERE ID=1")

    Tamar


    Monday, July 15, 2019 8:19 PM
    Moderator
  • Thanks Tamar,
    Actually csrTemp is the VFP cursor I created for example. You may take this also as:
    SELECT id,first_name,last_name,city,state FROM C:\PLIST.DBF INTO CURSOR csrTemp NOWAIT
    Thanks Olaf,
    I'm trying to work on you advice too.
    • Edited by ShahidSQL Tuesday, July 16, 2019 3:04 AM
    Tuesday, July 16, 2019 3:02 AM
  • Right. I understood that. I'm saying that if all you want to do is copy some records from one SQL table to another, you don't need to put them in a VFP cursor along the way. Just ask SQL Server (or MySQL or whichever you're using) to do the job.

    Now, if you want to display the data and let the user make changes, or you need to use some local data to modify the records before you save them to the other table, that's another story.

    Tamar

    Tuesday, July 16, 2019 8:19 PM
    Moderator