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

XBasic: Transfer data from tables in two Connections

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

    XBasic: Transfer data from tables in two Connections

    I'm not sure this is possible, but I was hoping someone could help:

    I have a single application that has data tables in two different databases (ms Access - that I inherited - and SQL Server Standard). I have two connections to send and retrieve data from the tables.

    Until now, I had no need to use data from both databases at the same time.

    I'd like to do something like this:
    sqlCommand = "INSERT INTO connection_a.Table_1 (JobNum, Description, Cost) SELECT (Jobnum, Description, Cost) FROM connection_b.Table_2 WHERE JobNum=:JobNum;"

    I don't know if it's possible, because I'm using 2 Connections ("connection_a" and "connection_b"). This would work easily if both Table_1 and Table_2 were in the same Database, using a single connection.

    In my XBASIC, I've opened the two connections, as follows:
    flag = cn.open("::Name::connection_a")
    flag = cn2.open("::Name::connection_b")

    I just don't know the correct syntax to identify a Table from one "connection" and another Table from another "connection."

    If anyone has any thoughts, I'd appreciate it.

    Thanks

    #2
    Re: XBasic: Transfer data from tables in two Connections

    I'm not sure that you can reference one connection from inside another connection.

    Do you have many records to select and then insert?
    If not, then you can open both connections... select from one... format it's results to fit an insert statement... and then insert into connection two.

    Comment


      #3
      Re: XBasic: Transfer data from tables in two Connections

      Hi David-

      Yes... that could work. But I need a little guidance.

      At most, the amount of data I'm working with, for any transfer is 3 Records with 4 fields each (2 numeric and 2 short text), so the amount of data is tiny. Creating the SELECT statement to get the data from the first Table is simple: SELECT JobNum, Description, Notes, Cost FROM tblJobDetail WHERE JobNum=:JobNum; I've tested that code and it works perfectly.

      Then, I can open the other Connection and run this: INSERT INTO CommSealInv (JobNum, Description, Notes, Cost) SELECT (JobNum, Description, Notes, Cost) FROM ???????;. Or even: INSERT INTO CommSealInv (JobNum, Description, Notes, Cost) VALUES (:JobNum, :Description, :Notes, :Cost);.

      Here's the part I can't figure out: After I run the "Select" Statement, I don't know how to refer to the 1-3 records that have been retrieved, so that I can insert them into the new table (that's what the question marks are, above). Does that question make sense? I don't know how to refer to the records SELECTed, so that I can INSERT them into the other table. If you can offer any thoughts on this, that will certainly solve my dilemma.

      Thank you so much for your help and for your time. I really appreciate it.

      Phil

      Comment


        #4
        Re: XBasic: Transfer data from tables in two Connections

        I'm not sure if there are better XBasic methods to get this done. I thought there might be a method to take a resultSet and turn it into a set of values... but I couldn't find one. The doc is a bit tough to slog through at times.

        However, here's what works...

        Using the resultSet from your Select statement...

        Code:
        sqlStmt2 = "SELECT FIRSTNAME, LASTNAME, COMPANY, CUSTAMOUNT FROM tblCustomers"
        flag = cn2.Execute(sqlStmt2)
        dim rs as sql::ResultSet
        dim insData as c
        rs = cn2.ResultSet 'the select connection
        insData = rs.ToString(-1,1,.f.,",")
        Now that all the records are in a string... change stuff around so that it's in the proper "values" format...

        Code:
        insData = "(\"" + insData
        insData = stritran(insData,",","\",\"")
        insData = stritran(insData,crlf(),"\"),(\"")
        insData = insData + "\")"
        This code adds an opening paren and double quote to the beginning the the data string.
        Then we change the field separating commas to quote-comma-quote.
        Then we change the record separator from crlf to comma
        Then we end the string with a double quote and closing paren.

        Then your insert sql statement is...

        Code:
        sqlStmt1 = "INSERT INTO tblCustomers (FIRSTNAME, LASTNAME, COMPANY, CUSTAMOUNT) VALUES " + insData

        Comment


          #5
          Re: XBasic: Transfer data from tables in two Connections

          Thanks, David-

          Getting closer!

          Your code makes perfect sense, to me. I didn't think of using resultSet to hold the data (and, in the future, I should be able to figure out how to use "rs" to populate my table, using something like "INSERT INTO Table_A (Field1, Field2, Field3, Field4) SELECT (rs.Field1, rs.Field2, rs.Field3, rs.Field4) FROM rs"... or something like that. However, for now, I just wanted to try your code, exactly the way you presented it, to make sure it works for me.

          Unfortunately, it doesn't populate my table. I used the debugger, and verified that the INSERT command looks good (see below):

          This is what's returned by the code, prior to running the cn.Execute command. sqlCommand2 returns this value:
          INSERT INTO CommSealDetail (JobNum, Description, Notes, Cost) VALUES ("35792","Crack-Fill","mach inj majors and seams cracks must be completly dry","775")

          For this test, there's only 1 Record to insert. I don't know why it doesn't insert, but I'm probably just missing something obvious. Maybe, you can spot it.

          Here's the full XBASIC code I'm using. It works all the way through, without errors, and it looks like it "should" INSERT... but it doesn't:

          JobNum = e._currentRowDataOld.Job_ID
          args.set("JobNum",JobNum)

          flag = cn2.open("::Name::RocBaugh")
          sqlCommand = "SELECT JobNum, Desc, Notes, Cost FROM tblJobDetail WHERE JobNum=:JobNum"
          flag = cn2.Execute(sqlCommand,args)

          rs = cn2.ResultSet
          insData = rs.ToString(-1,1,.f.,",")
          insData = "("" + insData
          insData = stritran(insData,",","","")
          insData = stritran(insData,crlf(),""),("")
          insData = insData + "")"

          flag = cn.open("::Name::Commercial")
          sqlCommand2 = "INSERT INTO CommSealDetail (JobNum, Description, Notes, Cost) VALUES " + insData
          flag = cn.execute(sqlCommand2)

          I hope you can see something. It looks perfect, to me... BUT, I must be missing something, because the values are NOT inserted into the table. I've even changed the table to include ONLY these 4 fields AND I changed the fields so that all of them are strings (nvarchar(150))... Ultimately, the JobNum and Cost fields will be numeric). I can always convert the fields (e.g to numeric) later. I've tried it several different ways, but the data isn't inserted into the table.

          Thanks again for all your help. I'm confident that I'm just a small step away. Perhaps, if I look at it, in the morning, I'll see what's missing, but if you have any thoughts, I'd appreciate it.

          With appreciation,
          Phil

          Comment


            #6
            Re: XBasic: Transfer data from tables in two Connections

            Put a debug(1) statement into your code before cn.execute. Once in debug enter cn.CallResult.Text into the Expression section at the bottom of the debugger.

            Now step through your cn.execute code and you should see the error message.

            Is JobNum a key field in the target table? An auto-key?
            Maybe it's just the posting, but your code is missing the backslash escape characters... escaping the double quotes.

            My tests included a decimal field in the target table. Even though all data in the insert statement are strings, the insert still works... and ends up as a number in the target table if that what the target field is.
            I'm using MySQL for testing.

            Comment


              #7
              Re: XBasic: Transfer data from tables in two Connections

              So, I learned something else. I didn't know about the cn.CallResult.Text function. I will use that often, so thank you.

              Yes, I got the error message. In short, the system didn't know how to insert the ResultSet data, because it was looking for "column names" and I am supplying the actual data. I've shown the error message below (I put it into columns to make it easier for you to read (FYI - My Database is SQL Server Standard):[INDENT][COLOR="#FF0000"]Database API specific error

              Comment


                #8
                Re: XBasic: Transfer data from tables in two Connections

                The system cutoff my full response, so I'll finish it here:

                Yes, I got the error message. In short, the system didn't know how to insert the ResultSet data, because it was looking for "column names" and I am supplying the actual data. I've shown the error message below (I put it into columns to make it easier for you to read (FYI - My Database is SQL Server Standard):
                [INDENT][COLOR="#FF0000"]Database API specific error

                Comment


                  #9
                  Re: XBasic: Transfer data from tables in two Connections

                  I don't know why the system is cutting off my response. There are some non-ascii "paragraph" symbols. Perhaps, that's causing a problem. I'll try again.

                  Yes, I got the error message. In short, the system didn't know how to insert the ResultSet data, because it was looking for "column names" and I am supplying the actual data. I've shown the error message below (I put it into columns to make it easier for you to read (FYI - My Database is SQL Server Standard):
                  Database API specific error. Your database has returned the following error code and description to Alpha Five. Consult your database documentation for further information. 207 - '
                  [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid column name '35792'. SQL State is: 42S22
                  [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid column name 'Crack-Fill'. SQL State is: 42S22
                  [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid column name 'mach inj majors and seams cracks must be completly dry'.SQL State is: 42S22
                  [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid column name '775'. SQL State is: 42S22'[/COLOR]

                  I need to get the ResultSet to have column names. This is especially important if the SELECT statement has 2 or 3 records, instead of the single record in this test.

                  I will do some research to see what my options are, but, of course, if you have any suggestions, I'd appreciate them.

                  Thanks again for all your help and for your super-fast responses.

                  Phil

                  Comment


                    #10
                    Re: XBasic: Transfer data from tables in two Connections

                    It's an odd error message... and isn't really what's going on at all. My tests were Select from SQL Server and Insert into MySQL. If I change this to Inserting into SQL Server I get the same error.

                    The issue is quotes... specifically double quotes. Change the code where insData is modified to use single quotes and all will be well.

                    Code:
                    insData = "('" + insData
                    insData = stritran(insData,",","','")
                    insData = stritran(insData,crlf(),"'),('")
                    insData = insData + "')"

                    Comment


                      #11
                      Re: XBasic: Transfer data from tables in two Connections

                      Hi Phil,

                      I had a similar situation recently so did quite a bit of research and testing on this. I have 23 tables, each with up to 40 columns, that I needed to copy between to MySql databases on different servers in different hosting centers. I also tried selecting the data into a string, and then inserting the string into the remote table, but the cleanup was too cumbersome so I wound up pushing the data into temp tables on the sending db (with an "Insert Into Select From" statement) and then used Navicat to transfer the data (using a scheduled routine).

                      One thing that you should look at is to use ODBC on your Access database to link to the Sql tables. You should then be able to use your "Insert Into Select From" with a single connection string to the Access DB. When Access transfers data to an ODBC table on another host (using an Access Update query) it can be a bit slow since it appears it is doing a row by row insert into the ODBC table. However, given the very limited amount of data you're transferring, it should work quite nice for you.

                      Comment


                        #12
                        Re: XBasic: Transfer data from tables in two Connections

                        Phil,

                        I do something similar (select fields from sql server db to insert into a MySQL db).
                        I haven't tested this, but the code below should work. (I left out most error checking)
                        Code:
                        dim cn as ::sql::connection
                        dim rs as ::sql::resultset
                        dim args as ::sql::arguments
                        dim sqlcode as c
                        dim accesstable as c = "::name::accesstableConnection"
                        dim sqltable as c = "::name::sqltableConnection"
                        dim recnum as n
                        
                        sqlcode = "SELECT JobNum, Description, Notes, Cost FROM tblJobDetail WHERE JobNum=:JobNum;"
                        'I will let you choose your own error checking and assume you already setup the args to this point
                        cn.open(accesstable)
                        cn.execute(sqlcode,args)
                        rs = cn.resultSet
                        rs.topropertyarray(rsdata)
                        cn.close()
                        ' This now gives you your results in a property array. Now for the rest of the fun
                        sqlcode = "INSERT INTO CommSealInv (JobNum, Description, Notes, Cost) VALUES (:JobNum, :Description, :Notes, :Cost);"
                        cn.open(sqltable)
                        if recnum >0 then
                        for recnum = 1 to rsdata.size()
                        args.set("JobNum",rsdata[recnum].JobNum)
                        args.set("Description",rsdata[recnum].Description)
                        args.set("Notes",rsdata[recnum].Notes)
                        args.set("Cost",rsdata[recnum].Cost)
                        cn.execute(sqlcode,args)
                        next
                        cn.close()
                        
                        end if 'recnum if
                        Gregg
                        https://paiza.io is a great site to test and share sql code

                        Comment


                          #13
                          Re: XBasic: Transfer data from tables in two Connections

                          Hi Jay,

                          Have you considered/tried dumping the results from server A into a text file,
                          then using load data local infile to import those results to server B ?
                          Gregg
                          https://paiza.io is a great site to test and share sql code

                          Comment

                          Working...
                          X