Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Update SQL Query NOT working

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    Update SQL Query NOT working

    Hi I am trying to create a button on click action.
    Copy2 function is working
    Update3 function is not working
    They both have almost same code except for the SQL Query


    Test_A and Test_B are two tables I am trying to update a field in Test_B from Test_A on the matching criteria of A.ID field which is in both the tables











    function Copy2 as c (e as p)

    dim cn as sql::connection
    dim flag as l
    flag = cn.open("::Name::ABC_System")
    if flag = .f. then
    'there was an error
    dim errorText as c
    errorText = cn.callresult.text
    end
    end if
    'turn on portable SQL
    cn.PortableSQLEnabled = .t.
    dim SQL as c
    sql = "INSERT INTO Test_B(A_ID,First_Name,Last_Name) select Test_A.[A_ID] ,Test_A.[First_Name] ,Test_A.[Last_Name] from Test_A"

    'execute the SQL
    flag = cn.execute(sql)
    if flag = .f. then
    'there was an error
    dim errorText as c
    errorText = cn.callresult.text
    cn.close()
    end
    end if
    dim rowsInserted as n
    'get the number of rows that were inserted
    rowsInserted = cn.CallResult.rowsaffected
    cn.close()
    end function





    function Update3 as c (e as p)
    dim cn as sql::connection
    dim flag as l
    flag = cn.open("::Name::ABC_System")
    if flag = .f. then
    'there was an error
    dim errorText as c
    errorText = cn.callresult.text
    end
    end if
    'turn on portable SQL
    cn.PortableSQLEnabled = .t.
    dim SQL as c
    sql = "Update [ABC_System].[dbo].[Test_B] set [Test_B].Phone = (select [Test_A].Phone_Number from [ABC_System].[dbo].[Test_A] where [Test_B].A_ID =[Test_A].A_ID and [Test_B].B_ID > 0)"
    'execute the SQL
    flag = cn.execute(sql)
    if flag = .f. then
    'there was an error
    dim errorText as c
    errorText = cn.callresult.text
    cn.close()
    end
    end if
    dim rowsInserted as n
    'get the number of rows that were inserted
    rowsInserted = cn.CallResult.rowsaffected
    cn.close()
    end function
    Last edited by tcloud75; 07-18-2018, 04:56 PM.

    #2
    Re: Update SQL Query NOT working

    The SQL query is working fine in SQL Server

    Comment


      #3
      Re: Update SQL Query NOT working

      Hi Priyesh,

      When you say it works in Sql Server, I would look at the sql statement for Update3 in the Xbasic debugger, it is probably because your subselect does not return the proper value for the update.

      Is it correct you wish to update all the rows in Test_B or are you missing a where clause for the update?

      Comment


        #4
        Re: Update SQL Query NOT working

        Your database has returned the following error code and description to Alpha Five.
        Consult your database documentation for further information.

        26 - 'line 1:25: expecting A5SQLTOKEN_ASSIGNEQUAL, found '.''


        I am getting this error in my SQL code.
        Can you help me how can i write this SQL query again?
        Last edited by tcloud75; 07-19-2018, 11:18 AM.

        Comment


          #5
          Re: Update SQL Query NOT working

          Yes I want to update all rows

          Comment


            #6
            Re: Update SQL Query NOT working

            The best way to learn this is by using the xbasic sql genie and construct the sql and xbasix there. Here is a sample made for the Northwind database using arguments, that should be helpful

            dim cn as sql::connection
            dim flag as l
            flag = cn.open("::Name::Northwind")
            if flag = .f. then
            'there was an error
            dim errorText as c
            errorText = cn.callresult.text
            end
            end if
            'turn on portable SQL
            cn.PortableSQLEnabled = .t.
            dim SQL as c
            sql = "UPDATE Customers SET CompanyName = :CompanyName WHERE Country = :whatCountry"+chr(13)+chr(10)
            dim args as sql::arguments
            args.add("CompanyName","'Test'")
            args.add("whatCountry","USA")

            'execute the SQL
            flag = cn.execute(sql,args)
            if flag = .f. then
            'there was an error
            dim errorText as c
            errorText = cn.callresult.text
            cn.close()
            end
            end if
            dim rowsUpdated as n
            'get the number of rows that were updated
            rowsUpdated = cn.CallResult.rowsaffected
            cn.close()

            'TIP: You can also use the sql_update() helper function rather than individual Xbasic statements

            Comment


              #7
              Re: Update SQL Query NOT working

              My issue is I wan to copy value from one table(Test_A) to another table( Test_B) and update test_B

              Here you knew "USA" and "Test" values.
              I do not know these values and these values will come from Test_A and will update Test_B table based on a matching key field.

              did you get my issue?
              Please help

              Comment


                #8
                Re: Update SQL Query NOT working

                Yes, it was really just an example

                Try removing the [ABC_System] from the update statement, it's already implicit in the connection string

                BTW, You could also update the Phone column directly in the copy statement

                Comment


                  #9
                  Re: Update SQL Query NOT working

                  I tried that , still it is not working.
                  do you want to screenshare sometime?

                  Comment


                    #10
                    Re: Update SQL Query NOT working

                    No, sorry, I really don't have the time for that

                    I would advice you to play around with the Xbasic sql genie, if you make the update work for 1 row, it's easy to do it for several rows, or search the forum, there are tons of posts on creating sql statements in Xbasic

                    Comment


                      #11
                      Re: Update SQL Query NOT working

                      Considering what it seems to be doing, why not just use after triggers ?
                      Gregg
                      https://paiza.io is a great site to test and share sql code

                      Comment


                        #12
                        Re: Update SQL Query NOT working

                        I would not advice anyone to use triggers unless you really, really have no other option

                        The query here is quite simple, and I think the only problem is an xbasic issue, but in order to test it properly, the best method is upload a test component using the Northwind database, then it's easy to test it.

                        Comment


                          #13
                          Re: Update SQL Query NOT working

                          Nils, Respectfully, triggers are typically faster and far more efficient.
                          With that said, I did my best to create tables based on the information provided to test the code below.
                          The change I made that finally got the function to work was to eliminate the cn.PortableSQLEnabled = .t.
                          statement in the update3 function.

                          Code:
                          function Copy2 as c ()
                          ' I moved dim statements to the top of the script for my convenience and readablity
                          ' Anything I moved or changed in the script is noted by '''
                          ' I removed the e as p because it was not required for this function.
                          dim cn as sql::connection
                          dim flag as l 
                          dim errorText as c 
                          dim SQL as c 
                          dim rowsInserted as n 
                          '''debug(1)
                          flag = cn.open("::Name::ABC_System")
                          if flag = .f. then 
                          'there was an error
                          '''dim errorText as c 
                          errorText = cn.callresult.text
                          end
                          end if 
                          'turn on portable SQL
                          cn.PortableSQLEnabled = .t.
                          '''dim SQL as c 
                          '''sql = "INSERT INTO Test_B(A_ID,First_Name,Last_Name) select Test_A.[A_ID] ,Test_A.[First_Name] ,Test_A.[Last_Name] from Test_A"
                          ' I added a where statement so this would only affect records not already in test_B
                          SQL = <<%txt%
                          INSERT INTO Test_B(A_ID,First_Name,Last_Name) select Test_A.[A_ID] ,Test_A.[First_Name] ,Test_A.[Last_Name] from Test_A
                          where a_id not in (select a_id from test_B)
                          %txt%
                          'execute the SQL
                          flag = cn.execute(sql)
                          if flag = .f. then 
                          'there was an error
                          '''dim errorText as c 
                          	errorText = cn.callresult.text
                          	Copy2= errorText
                          '''cn.close()
                          '''end
                          '''end if 
                          else
                          '''dim rowsInserted as n 
                          'get the number of rows that were inserted
                          rowsInserted = cn.CallResult.rowsaffected
                          '''cn.close()
                          
                          	copy2 = "Just inserted "+alltrim(str(rowsInserted))+" into TestB"
                          end if
                          cn.Close()
                          end function
                          Code:
                          function Update3 as c ()
                          ' I moved dim statements to the top of the script for my convenience and readablity
                          ' Anything I moved or changed in the script is noted by '''
                          ' I removed the e as p because it was not required for this function.	
                          dim cn as sql::connection
                          dim flag as l 
                          dim errorText as c 
                          dim SQL as c 
                          dim rowsInserted as n 
                          ''debug(1)
                          flag = cn.open("::Name::ABC_System")
                          if flag = .f. then 
                          'there was an error
                          '''dim errorText as c 
                          errorText = cn.callresult.text
                          end
                          end if 
                          'turn on portable SQL
                          '''cn.PortableSQLEnabled = .t.
                          '''dim SQL as c 
                          '''sql = "Update [ABC_System].[dbo].[Test_B] set [Test_B].Phone = (select [Test_A].Phone_Number from [ABC_System].[dbo].[Test_A] where [Test_B].A_ID =[Test_A].A_ID and [Test_B].B_ID > 0)"
                          SQL = <<%txt%
                          Update [Test_B] 
                          set [Test_B].Phone = (select [Test_A].Phone_Number from [Test_A] where [Test_B].A_ID = [Test_A].A_ID and [Test_B].B_ID > 0);
                          %txt%
                          'execute the SQL
                          flag = cn.execute(SQL)
                          if flag = .f. then 
                          'there was an error
                          ''dim errorText as c 
                          errorText = cn.callresult.text
                          Update3 = errorText
                          '''cn.close()
                          '''end
                          '''end if 
                          else
                          '''dim rowsInserted as n 
                          'get the number of rows that were inserted
                          rowsInserted = cn.CallResult.rowsaffected
                          Update3 = alltrim(str(rowsInserted))+" rows have been updated"
                          end if
                          cn.close()
                          end function
                          Gregg
                          https://paiza.io is a great site to test and share sql code

                          Comment


                            #14
                            Re: Update SQL Query NOT working

                            Originally posted by madtowng View Post
                            Nils, Respectfully, triggers are typically faster and far more efficient.
                            With that said, I did my best to create tables based on the information provided to test the code below.
                            The change I made that finally got the function to work was to eliminate the cn.PortableSQLEnabled = .t.
                            statement in the update3 function.

                            Code:
                            function Copy2 as c ()
                            ' I moved dim statements to the top of the script for my convenience and readablity
                            ' Anything I moved or changed in the script is noted by '''
                            ' I removed the e as p because it was not required for this function.
                            dim cn as sql::connection
                            dim flag as l 
                            dim errorText as c 
                            dim SQL as c 
                            dim rowsInserted as n 
                            '''debug(1)
                            flag = cn.open("::Name::ABC_System")
                            if flag = .f. then 
                            'there was an error
                            '''dim errorText as c 
                            errorText = cn.callresult.text
                            end
                            end if 
                            'turn on portable SQL
                            cn.PortableSQLEnabled = .t.
                            '''dim SQL as c 
                            '''sql = "INSERT INTO Test_B(A_ID,First_Name,Last_Name) select Test_A.[A_ID] ,Test_A.[First_Name] ,Test_A.[Last_Name] from Test_A"
                            ' I added a where statement so this would only affect records not already in test_B
                            SQL = <<%txt%
                            INSERT INTO Test_B(A_ID,First_Name,Last_Name) select Test_A.[A_ID] ,Test_A.[First_Name] ,Test_A.[Last_Name] from Test_A
                            where a_id not in (select a_id from test_B)
                            %txt%
                            'execute the SQL
                            flag = cn.execute(sql)
                            if flag = .f. then 
                            'there was an error
                            '''dim errorText as c 
                            	errorText = cn.callresult.text
                            	Copy2= errorText
                            '''cn.close()
                            '''end
                            '''end if 
                            else
                            '''dim rowsInserted as n 
                            'get the number of rows that were inserted
                            rowsInserted = cn.CallResult.rowsaffected
                            '''cn.close()
                            
                            	copy2 = "Just inserted "+alltrim(str(rowsInserted))+" into TestB"
                            end if
                            cn.Close()
                            end function
                            Code:
                            function Update3 as c ()
                            ' I moved dim statements to the top of the script for my convenience and readablity
                            ' Anything I moved or changed in the script is noted by '''
                            ' I removed the e as p because it was not required for this function.	
                            dim cn as sql::connection
                            dim flag as l 
                            dim errorText as c 
                            dim SQL as c 
                            dim rowsInserted as n 
                            ''debug(1)
                            flag = cn.open("::Name::ABC_System")
                            if flag = .f. then 
                            'there was an error
                            '''dim errorText as c 
                            errorText = cn.callresult.text
                            end
                            end if 
                            'turn on portable SQL
                            '''cn.PortableSQLEnabled = .t.
                            '''dim SQL as c 
                            '''sql = "Update [ABC_System].[dbo].[Test_B] set [Test_B].Phone = (select [Test_A].Phone_Number from [ABC_System].[dbo].[Test_A] where [Test_B].A_ID =[Test_A].A_ID and [Test_B].B_ID > 0)"
                            SQL = <<%txt%
                            Update [Test_B] 
                            set [Test_B].Phone = (select [Test_A].Phone_Number from [Test_A] where [Test_B].A_ID = [Test_A].A_ID and [Test_B].B_ID > 0);
                            %txt%
                            'execute the SQL
                            flag = cn.execute(SQL)
                            if flag = .f. then 
                            'there was an error
                            ''dim errorText as c 
                            errorText = cn.callresult.text
                            Update3 = errorText
                            '''cn.close()
                            '''end
                            '''end if 
                            else
                            '''dim rowsInserted as n 
                            'get the number of rows that were inserted
                            rowsInserted = cn.CallResult.rowsaffected
                            Update3 = alltrim(str(rowsInserted))+" rows have been updated"
                            end if
                            cn.close()
                            end function
                            Gregg,

                            Yes, they are, but they have several disadvantages, which is why I would not advice using them for most database tasks.

                            What if you wish to change your update of the phone field, you would have to change your trigger and your tables, which may mean downtime etc.
                            What if you only want the update to occur for certain data, not easily done with a trigger
                            What if you make a bulk insert, and not have the trigger fire?

                            It's much better to do these kind of updates with a stored procedure, where you have much more control

                            In general I would only use triggers for these tasks

                            1: Update a date-time modified field
                            2: Write to an audit table or log table

                            What I would never do is update data in other related tables

                            If your database is small on not missions critical, you can of course disregard these guidelines, but the point is that there are much easier methods of doing these tasks that using a trigger

                            Nils

                            Comment


                              #15
                              Re: Update SQL Query NOT working

                              Nils,

                              I would agree if either of these functions used data created or modified Alpha.
                              As it is, we know the backend is far more efficient, and the backend will report an error.
                              Changing the functionality of a trigger means with a few exceptions, you will not have to
                              republish your project to make the changes take effect.

                              Using triggers in this case means the actions will be taken even if data is entered into test_A
                              outside of the alpha Anywhere project.
                              Gregg
                              https://paiza.io is a great site to test and share sql code

                              Comment

                              Working...
                              X