Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Modifiy a Virtual Table ODBC

  1. #1
    Member
    Join Date
    Oct 2019
    Posts
    62

    Modifiy a Virtual Table ODBC

    I would like to open an ODBC database with the help of the "Virtual Table". I have tested it through the console and it works fine. nstathand = sqlconnect ("Driver = Devart ODBC Driver for xBase; DBFFormat = VisaualFoxPro")
    Can open the table display change etc.

    Now I'm trying to open the table via Modify A Virtual Table. I carry in the ODBC Connect the string "Driver = Devart ODBC Driver for xBase; DBFFORMAT = Visual FoxPro" and carry under SQL command use table name shared I get the message Access denied

  2. #2
    Lianja MVP
    Join Date
    Feb 2012
    Location
    Berea, KY, USA
    Posts
    1,934
    What do you mean by modify?

    Do you mean, e.g., ALTER TABLE xxxx Add Column ..... If so that's

    Code:
    lnHandle = sqlstringconnect(....)
    lnRet = sqlexec(lnHandle,"ALTER TABLE xxxx Add Column ...")
    Or do you mean just USE the table -- if so that's

    Code:
    lnHandle = sqlstringconnect(...)
    use table xx connhandle lnHandle
    or

    Code:
    use table xx connstr "ODBC Driver 17 for SQL Server,...."
    Hank

  3. #3
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    5,739
    You can test odbc connections in the console just be selecting them from the Combobox in the ODBC tab. After selecting a connection in the IDBC tab you can type

    dir
    use
    etc

    You can verify a virtual table definition in the console using “Dir” which will display details of all tables including virtual tables.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  4. #4
    Lianja Team yvonne.milne's Avatar
    Join Date
    Feb 2012
    Location
    Berkshire, UK
    Posts
    1,371
    Hi Schlingmeier,

    The SQL command in the Create/Modify a Virtual Table dialog needs to be a SQL command for the target data source:

    Code:
    select * from tablename
    or a local stored procedure, e.g.

    Code:
    call sp_storedproc()
    Regards,

    Yvonne

  5. #5
    Member
    Join Date
    Oct 2019
    Posts
    62
    Name:  odbc2.PNG
Views: 74
Size:  21.8 KB

    Sorry, I probably did not express myself correctly. So I'll try again. I want to open an ODBC table. This works well on the console. Now I would like to include the table in the App Inspector Data Files as "New Virtual Table ..." so that I can use the table in Pages by dragging and dropping.

    What do I have to enter in OBC Connection so that I can use this table.
    The command in the console is:
    connStrng ="DRIVER=Devart ODBC Driver for xBase;Description=Citadel Desk ODBC;Database=C:\Moselstern-Hotels_C\Brixiade\Data\;DBFFormat=VisualFoxPro"
    nst = sqlconnect(connStrng)
    use C:\Moselstern-Hotels_C\Brixiade\Data\address shared
    alternative
    nst = sqlconnect("DSN=DeskODBC")

    No matter what I enter in the ODBC connection it does not lead to success.
    What do I have to consider?

  6. #6
    Lianja Team yvonne.milne's Avatar
    Join Date
    Feb 2012
    Location
    Berkshire, UK
    Posts
    1,371
    Hi Schlingmeier,

    Remove the quotes ("") from around the connection string in ODBC Connection.

    Enter a valid SQL command - see my previous post.

    Regards,

    Yvonne

  7. #7
    Lianja MVP
    Join Date
    Dec 2012
    Location
    Croatia, Zagreb
    Posts
    1,099
    Hi Schlingmeier,

    I would recommend this article: https://www.lianja.com/doc/index.php/Virtual_Tables

    Josip

  8. #8
    Senior Member
    Join Date
    Feb 2012
    Location
    Rome - Italy
    Posts
    1,862
    Hi Schlingmeier,
    when you use ODBC yon can't open the remote table..

    on the SQL Command, write:
    select * from address

    ciao
    Fabio

  9. #9
    Lianja MVP
    Join Date
    Feb 2012
    Location
    Berea, KY, USA
    Posts
    1,934
    Hi Fabio,

    https://www.lianja.com/doc/index.php/USE

    Check out items 4.4 and 4.5, use of Connstr and Connhandle in the USE command.

    Remote tables can indeed be opened as tables.

    If I remember correctly, use of Connstr or Connhandle overrides the connection string in VT's, also.

    Hank

  10. #10
    Member
    Join Date
    Oct 2019
    Posts
    62
    At this point, my thanks for the support.
    Unfortunately, I still have not progressed. That may be due to my bad English, so I'll try again.
    1. In Lianja App Builder I go to the console and click ODBC as described to test if the connection is via ODBC. I enter in Connstring DeskODBC. I get the message ODBC connection to DeskODBC successful. As far as pleasing. I now try to open the table with select * from address, I get the message ** Error -1 **
    I can not open the table. In the console I click on Lianja / VFP and enter the following commands:
    nst = SQLConnect ( "DeskODBC")
    use address shared
    brow
    the table is shown to me correctly. What do I overlook?

Page 1 of 2 12 LastLast

Bookmarks

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
  •  
Journey into the Cloud
Join us