SQLQuery

[JBR]

I'm writting add-in for my company (C#) and I want to execute SQL query to EA's DB.
I saw the method SQLQuery for repository but I can't find how to use it (even in EA tutorial  :-/)
Did someone use this method ? and how ?
Do you know how to execute SQL query in a add-in ?

thank you.

[cpelster]

Hi,

the SqlQuery returns a string containing the result set as XML.
You have to parse the XML output to get the Result.

regards,

  christian

[JBR]

Thank you.
I see that after I posted my message  .

My second problem with SQLQuery was that it dosn't work if names of Table and Columns (in the SQL query) weren't in uppercase.

Now it works.

[pdelporte]

Hello,

I am also intersted in fetching some data from some tables of EA. Could you tell me how to use the SQLQuery function, as I can't find it. A very short exemple of how to use it would be nice.

Regards,
Pierre

[thomaskilian]

How about  using ADO (possibly using ODBC if needed)?

[pdelporte]

Sure, you can use ADO, it is not a problem, but what I would like to avoid is to hardcode the connectString. I rather prefer to find a way to retrieve the connectString that EA used. Unfortunately, I do not know how I can do this. I was hoping that the above mentionned SQLQuery would help me.

If needed I can provide an exemple of ADO connection against Oracle.

Yours,
Pierre

[«Midnight»]

Pierre,

You can create your own connection string by inspecting the repository (to get an idea of where you want to connect) and building a connection string as usual. Remember that if there will be two channels (you and EA for example) you should not open the repository with exclusive access.

You can also discover the connection string EA uses by looking at the Files menu after you've connected and disconnected from a repository. You should be able to find this string in the registry. You can also do a Save As to a desktop icon when you've got a repository open; EA will save the connection string, which you can then cut and paste.

By the way, you can connect to an EAP file with EA in the same way as you would connect to a DBMS. This would get you the appropriate parameters if you want to open an EAP file from ADO.

David

[thomaskilian]

There are also some thread where Sparxian tell how to setup the connection string. Should be possible to find them.

[bmioch]

I don't do a lot of automation stuff, but does Repository.ConnectionString help?

[pdelporte]

Hello,

Yes, the Rpository.ConnectionString is exactely what I was looking for. I solved my problem to get a connection to the DB used.

Thx.
Pierre

[markymark]

I do a lot of work directly on the database -- sometimes from C# it's just way faster to put together a nice sproc and operate right on the underlying database than to deal with the automation interface (I mix and match a decent amount and pray that things won't change too much with the next version of the DB :)  Here's a quick ConnectionString example (using SQL Server) and a short data access function.

The EA connectionString actually has some extra junk that my SqlConnection() didn't like, so I sliced and diced a little bit as you can see.

Code: [Select]


               // Repository.ConnectionString = "SERVERNAME/Windows Authentication --- DBType=1;Connect=Provider=SQLOLEDB.1;
               // Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DATABASENAME;Data Source=192.168.0.1";
               // Get the current ConnectionString that the current Repository is using.
               // However, this has EA-specific connection information, such as the name, the type
               // of connection/client, etc.  Based on inspection of the existing ConnectionString,
               // the actual SQL Server ConnectionString in the proper sense is located to the
               // right of "SQLOLEDB.1;", so get the substring from this text to the end of the string
               string cs = Repository.ConnectionString.Substring(Repository.ConnectionString.IndexOf("SQLOLEDB.1;") + "SQLOLEDB.1;".Length);



Code: [Select]


       protected SqlDataReader ConnectorGetDestinations(int _ElementID, string cstring)
       {
           SqlConnection dbConnection =  new SqlConnection();
           dbConnection.ConnectionString = cstring;
           dbConnection.Open();

           SqlCommand sqlData = new SqlCommand("ConnectorGetDestinations", dbConnection);
           sqlData.CommandType = CommandType.StoredProcedure;

           SqlParameter sp = new SqlParameter("@ProvidedInterfaceObjectID", SqlDbType.Int);
           sp.Value = _ElementID;
           sqlData.Parameters.Add(sp);

           return (sqlData.ExecuteReader(CommandBehavior.CloseConnection));
       }