none
Paradox/OLEDB/Jet Escape Character For Exclamation Mark RRS feed

  • Question

  • First up, apologies if this is not the correct area to be asking this. I spent a little while trying to determine which forum was, but may have got it wrong.

    I'm having a bit of a problem with Paradox tables, specifically utilising them through Jet 4.0 via an OLEDB connection with the extended properties of 'Paradox 5.x'. I'm developing using C# .NET 2.0.

    It's not that the connection doesn't work, selects and updates are mostly fine, until that is I get to one of a number of these Paradox tables that are prefixed with the character '!', (exclamation mark). Whenever I try and do anything with these tables, I get
    the following OLEDB exception:

    "... is not a valid name.  Make sure that it does not include invalid characters or punctuation and that it is not too long."

    I am however able to see them via the
    OLEDB schema table, this shows the table name with an underscore prefix, ('_'), instead of the exclamation mark; but using this instead of the '!' in any statement also produces an exception.

    My question is basically this, is there an escape character for Jet 4.0 for the exclamation point in an SQL statement? I figure that if I can resolve this then the queries should run correctly and I should get access to these tables. Alternatively if there is another solution that you can offer, I would welcome that as well.

    Unfortunately at this stage the Paradox tables I'm dealing with are from quite an old application that is still active and therefore it is not feasible to rename (permanently) or port them to a new database. I'm trying to write a small application to access them in a minor way.

    I'm thinking I'll probably have to simply lock the tables in some way, rename them and then rename them back when I'm done, but it's definately not ideal. I was just hoping there was someone out there who might have a solution.

    Thanks,
    Periander.
    Friday, October 10, 2008 3:33 AM

All replies

  • If table or column name contans characters that are not recognized as valid identifier characters, you could wrap these (or all) names into sqaret brackets, like

     

    SELECT [MyColumnName], .....  FROM [!MyParadoxTableName] ....

     

    Friday, October 10, 2008 11:48 AM
    Moderator
  • Thanks for the reply, unfortunately it must strip them out some where because I still get an exception like:

    "'!MyParadoxTableName' is not a valid name..."

    I've decided I'm going with the slightly dodgy method of 'rename, perform basic action and rename back'. I believe Paradox uses simple file based locks, so I may be able to lock and unlock those tables programmatically too.
    Sunday, October 12, 2008 11:26 PM