SQLQuery and error management

[eaDev35]

Hi all,

I am currently using SQLQuery method in a C# plugin to manipulate a custom table, as mentioned in the following thread: http://www.sparxsystems.com/cgi-bin/yabb/YaBB.cgi?num=1263805839.

I need to detect if that table exists or not prior to using it. Therefore I use a simple "SELECT 1 FROM MY_TABLE" query, which makes EA display an error ("DAO.Database [3078]"). Is there a way to catch that error and prevent EA from showing that dialog? I tried with a simple try/catch exception block with no luck: no exception is being generated.

Any EA/C# hint would be appreciated.

Thanks

EDIT: my repository is a EAP file

[beginner]

You can examine the connection string of your repo. According to that you can open your own connection (through ODBC or any native connection you know being in use). Then you can do any db magic you wan't. (Edit: interesting typo. Maybe "won't" would be more correct than "want")

b.

[eaDev35]

Thanks for that quick answer.

Unfortunately I forgot to mention something: I am using a file-based repository (EAP file), and the ConnectionString property indicates the file path.

Is it still possible to use plain ODBC on file-based repository?

Thanks

[beginner]

Sure. You need to have the ODBC connection at hand. Respectively it can be build from the connection string. Your configuration may vary...

b.

[eaDev35]

Hi b.

Your solution worked well: I managed to perform the SQL query using OLEDB API (Jet 4.0 provider) and the EAP file location.

I will use that technique whenever SQLQuery isn't sufficient enough.

Thanks a lot.

[Geert Bellekens]

Hi,

I think there is a better solution for that.
First of all, to know whether a table exists or not you can query the system tables. (google for "access system tables") and you should find all info you need.
Secondly, Repository.SQLQuery indeed only allows "read-only" queries, but there is an undocumented Repository.Execute(sqlString) that you can use to execute an arbitrary SQL string on the database.

So no need to mess around with your own db connection.

Geert

[eaDev35]

Hi Geert,

Thanks for this info. I tried querying MSysObjects table (through Repository.SQLQuery method) to know if my custom table exists. Unfortunately, this gave me the following error: "DAO.Database [3112] Record(s) can't be read, no read permission on 'MSysObjects'".

Looking to that link: http://www.packtpub.com/article/ms-access-queries-with-oracle-sql-developer-one-point-two-tool, it appears I would need to give read permissions to that table (using Access). This is not a viable solution for me right now.

FYI I also use Repository.Execute method to perform other type of queries in my plugin (INSERTs, UPDATEs, etc.)

Thanks anyway