Results 1 to 2 of 2

Thread: ODBC Connection to Postgres tables and fields that have hyphens example: order-line

  1. #1
    Junior Member
    Join Date
    Aug 2013
    Posts
    1

    ODBC Connection to Postgres tables and fields that have hyphens example: order-line

    I'm so happy that VFP has come sooooo far and current! Here's one dilema though - I'm trying to connect to our Postsgres database via ODBC (with DSN). Unfortunately, most, if not all tables and some fields were by themselves an import from a previous database and have hyphens on them. While I can connect to the database via console, I get errors whenever I do a select or when I utilize the import facility:

    [ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Table/View/Synonym not found (7519).

    I did the normal workarounds but still the the error. Here are the workarounds I attempted to do a simple select, one line per attempt:

    select * from pub."alt-vendor"
    select * from "pub.alt-vendor"
    select * from pub.'alt-vendor'
    select * from pub.`alt-vendor`
    select * from `pub.alt-vendor`

    I also tried above without the schema and got the same error.

    Here's a select I used with WinSQL and PERL (sort of) that worked using the above workaround:

    select "alt-vendor".company, "alt-vendor"."item-number", item."item-name", vendor."vend-name"
    from (("PUB"."alt-vendor" "alt-vendor"
    INNER JOIN PUB.item item ON "alt-vendor"."item-number" = item."item-number")
    INNER JOIN PUB.vendor vendor ON "alt-vendor"."vend-number" = vendor."vend-number")
    where "alt-vendor".company = 'mac'

    Any thoughts would be appreciated - HowardT

  2. #2
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    5,966
    Yes well thats an interesting situation when accessing legacy data with hyphens in the column names.

    All ODBC data sources can be setup as Virtual Tables in Lianja. The create/alter virtual table has a "properties" clause which maps "known" property names in a Cursor Adaptor.

    Lianja performs all CRUD operations through the Cursor Adaptor and generates the necessary SQL statements.

    It could therefore map column names which contain a "-" to "_" when reading and map the "_" to "-" when inserting, updating or deleting.

    This is not something we have on the development schedule but we would be prepared to do this work as "Sponsored Feature" if its something you cannot live without.
    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

Tags for this Thread

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