Alpha Video Training
Results 1 to 13 of 13

Thread: XBasic: Transfer data from tables in two Connections

  1. #1
    Member
    Real Name
    Phil Samuels
    Join Date
    Jan 2011
    Posts
    192

    Default 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. #2
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,665

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

  3. #3
    Member
    Real Name
    Phil Samuels
    Join Date
    Jan 2011
    Posts
    192

    Default 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

  4. #4
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,665

    Default 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

  5. #5
    Member
    Real Name
    Phil Samuels
    Join Date
    Jan 2011
    Posts
    192

    Default 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

  6. #6
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,665

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

  7. #7
    Member
    Real Name
    Phil Samuels
    Join Date
    Jan 2011
    Posts
    192

    Default 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

  8. #8
    Member
    Real Name
    Phil Samuels
    Join Date
    Jan 2011
    Posts
    192

    Default 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

  9. #9
    Member
    Real Name
    Phil Samuels
    Join Date
    Jan 2011
    Posts
    192

    Default 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

  10. #10
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,665

    Default 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 + "')"

  11. #11
    Member
    Real Name
    jay oken
    Join Date
    Sep 2012
    Posts
    88

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

  12. #12
    "Certified" Alphaholic
    Real Name
    Gregg Schmidt
    Join Date
    Mar 2001
    Location
    Milwaukee
    Posts
    1,351

    Default 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

  13. #13
    "Certified" Alphaholic
    Real Name
    Gregg Schmidt
    Join Date
    Mar 2001
    Location
    Milwaukee
    Posts
    1,351

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

Similar Threads

  1. Transfer MYSQL tables and web app to server?
    By Keith Hubert in forum Application Server Version 11 - Web/Browser Applications
    Replies: 3
    Last Post: 02-08-2013, 10:54 AM
  2. Activate AlphaDAO Connections Dialog from xBasic
    By pboomwork in forum Alpha Five Version 11 - Desktop Applications
    Replies: 3
    Last Post: 01-11-2013, 02:33 PM
  3. Transfer data in tables
    By Evert in forum Alpha Five Version 7
    Replies: 11
    Last Post: 08-25-2006, 05:18 AM
  4. Transfer VB code to Xbasic for Cutlist Program
    By Jim Lovejoy in forum Alpha Five Version 7
    Replies: 5
    Last Post: 11-27-2005, 09:12 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
  •