Recursive SQL statements using WITH (CTE)

[Mads Kirstan]

Hi,

What is the status on EA not supporting the WITH clause in SQL?

Kind regards
Mads

[PeterHeintz]

EA only process sql select statements and I assume that this will not change in the near future.
However you could add views on you sql server, having "With" clauses and call these views by EA select statements. At least that is what we do.

[Uffe]

EA only process sql select statements and I assume that this will not change in the near future.

Well, in Repository.SqlQuery() that's true, but the undocumented Repository.Execute() doesn't have the same restrictions. Might be worth a try.

/Uffe

[qwerty]

Unfortunately Repository.Execute does not deliver any query results.

q.

[PeterHeintz]

Yes, we also tried Repository.SQLQurery and got no results. The only way we found to use "With" was to have the "with" stored on the server and call/select it from EA.

[McMannus]

What you can also try is directly working on the EAP project with C# DB access mechanisms. I did that once for updating absolute paths for toolbox profile icons to the current version control checkout path (helps enormously if different work on MDG development). I believe with this method, the syntax you are allowed to use is just the database technology itself, so you could try the with statement with this method:

Code: [Select]

                var profilesProject =  "profiles.eap";
                const string iconFolder = "\\icons";
                try
                {

                    OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;"
                                                               + "Data Source=" + profilesProject);
                    conn.Open();

                    using (DataTable dt = new DataTable())
                    {
                        using (OleDbDataAdapter adapter = new OleDbDataAdapter(@"SELECT [Default],ID FROM t_attribute WHERE Name='Icon'", conn))
                        {
                            adapter.Fill(dt);
                        }
                        List<string> queries = new List<string>();
                        foreach (DataRow row in dt.Rows)
                        {
                            string toolboxIconPath = row["Default"].ToString();
                            if (!toolboxIconPath.StartsWith(executingFolder))
                            {
                                int index = toolboxIconPath.IndexOf(iconFolder);
                                if (index > 0)
                                {
                                    string newPath = executingFolder + toolboxIconPath.Substring(index);
                                    Console.WriteLine("\tReplacement: " + toolboxIconPath + " -> " + newPath);
                                    queries.Add("UPDATE [t_attribute] SET [Default]='"+newPath+"' WHERE [ID]="+row["ID"]);
                                }
                            }
                        }

                        if (queries.Any())
                        {
                            Console.WriteLine("\nAre the replaced paths correct? Go on with 'y'");
                            ConsoleKeyInfo pressedChar = Console.ReadKey();
                            if (pressedChar.KeyChar == 'y')
                            {
                                foreach (string query in queries)
                                {
                                    using (OleDbCommand cmd = new OleDbCommand(query, conn))
                                    {
                                        cmd.ExecuteNonQuery();
                                    }
                                }
                                Console.WriteLine("\nToolbox Icon Paths updated successfully :-)");
                            }
                        }
                    }

                }
                catch (Exception e)
                {
                    Console.WriteLine("\nAn error occured during updating the toolbox icon image paths. Are you sure that you have the SVN lock on the profiles.eap file?");
                }
                Console.WriteLine("\n\nPress any key to exit...");
                Console.ReadKey();