I am trying to use Selenium to enter SQL code into a Code Mirror textbox. I'll use the site http://www.gudusoft.com/sqlflow/#/ as an example for the purposes of this question.
My Problem:
I am unable to submit MSSQL SQL code into the code text box if the code contains a carriage return or line feed.
As a work around, I am removing all of them before writing it to the textbox using a JavaScript function but the end result is very ugly word-wrapped SQL.
I've also tried using the SendKeys method on a Selenium webElement object to send the code into the textbox, but I am unsure which element to "Find." Using SendKeys requires that the textbox be selected and when I try to invoke the Click" and "SendKeys" method on that object, I often get a error that the element does not permit user interaction.
If I could consistently find an element that I could interact with, like a TextArea, I would try to paste the contents of my clipboard into it rather than send a very large number of keystrokes to the textbox. For example, the following usually gives me the "unable to interact with this object" error but occasionally works, depending on the current content of the textbox, presumably.
Clipboard.SetText(sql);
var txtbx = codeMirror.FindElement(By.CssSelector("textarea"));
txtbx.Click();
txtbx.SendKeys(OpenQA.Selenium.Keys.Control + "v");
I'm thinking that my best chance of setting the text is using the Execute script method to execute the setValue JavaScript method on the CodeMirror object as shown below. Again, this works if the SQL has no CR / LF characters but how do I change my code to allow for these characters?
I've seen many many postings on this but my JavaScript knowledge may not be good enough to get me to the end result. I'm hoping that someone can reconstruct a working example using the following code. Here's the relatively short instructions.
Create a C# project (Console app, winForms, etc) and add the following 3 Nuget packages:
Selenium.Chrome.WebDriver
Selenium.WebDriver
Selenium.WebDriver.ChromeDriver
Create a class "SeleniumHelperGudusoft" and paste in the following code:
using OpenQA.Selenium;
using OpenQA.Selenium.Chrome;
namespace SqlSmoke.Classes
{
public class SeleniumHelperGudusoft
{
private IWebDriver driver;
public SeleniumHelperGudusoft()
{
var chromeDriverService = ChromeDriverService.CreateDefaultService();
chromeDriverService.HideCommandPromptWindow = false;
ChromeOptions options = new ChromeOptions();
options.AddAdditionalCapability("useAutomationExtension", false);
this.driver = new ChromeDriver(chromeDriverService, options);
}
public void NavigateToMain()
{
driver.Url = @"http://www.gudusoft.com/sqlflow/#/";
}
public void SetLanguageToMsSql()
{
string languageButtoncssSelector = "#Root > div > div.Main > div > div.Route.Row.x-start.y-stretch > div.SQLFlowEditor > div.SQLFlowEditorOperations.Row.x-start.y-center > div.DbVendor > div > div > svg";
var languageButton = driver.FindElement(By.CssSelector(languageButtoncssSelector));
languageButton.Click();
string msSqlCssSelector = "#Root > div > div.Main > div > div.Route.Row.x-start.y-stretch > div.SQLFlowEditor > div.SQLFlowEditorOperations.Row.x-start.y-center > div.DbVendor > ul > li:nth-child(10)";
var msSql = driver.FindElement(By.CssSelector(msSqlCssSelector));
msSql.Click();
}
public void SetSqlText(string sql)
{
IJavaScriptExecutor js = (IJavaScriptExecutor)driver;
var codeMirror = driver.FindElement(By.ClassName("CodeMirror"));
js.ExecuteScript("arguments[0].CodeMirror.setValue(\"" + sql + "\");", codeMirror); //<<<<----Fails here with the error message shown below in my post
}
public void ClickVisualizeButton()
{
string buttonCssSelector = "#Visualize > div";
var button = driver.FindElement(By.CssSelector(buttonCssSelector));
button.Click();
}
}
}
Exercise the code in the above class to try pasting in two different SQLs, one without line feed characters and one with.
string sql;
var lineageHelper = new SeleniumHelperGudusoft();
lineageHelper.NavigateToMain();
lineageHelper.SetLanguageToMsSql();
sql = "SELECT COL1, nCOL2 FROM TABLE1"; //all on one line works
lineageHelper.SetSqlText(sql);
lineageHelper.ClickVisualizeButton();
sql = "SELECT COL1, \r\nCOL2 FROM TABLE1";
lineageHelper.SetSqlText(sql); //<<<----- Fails here with the following error message
lineageHelper.ClickVisualizeButton();
I get the following error on the second call to the SetSqlText method on the line:
js.ExecuteScript("arguments[0].CodeMirror.setValue(\"" + sql + "\");", codeMirror);
Message "javascript error: Invalid or unexpected token\n (Session info: chrome=84.0.4147.105)" string
How can I modify the example to get the second query to be entered into the CodeMirror textbox?
Code Mirror documentation is found here: https://codemirror.net/doc/manual.html
Here's a full Call stack of the error:
OpenQA.Selenium.WebDriverException
HResult=0x80131500
Message=javascript error: Invalid or unexpected token
(Session info: chrome=84.0.4147.105)
Source=WebDriver
StackTrace:
at OpenQA.Selenium.Remote.RemoteWebDriver.UnpackAndThrowOnError(Response errorResponse)
at OpenQA.Selenium.Remote.RemoteWebDriver.Execute(String driverCommandToExecute, Dictionary`2 parameters)
at OpenQA.Selenium.Remote.RemoteWebDriver.ExecuteScriptCommand(String script, String commandName, Object[] args)
at OpenQA.Selenium.Remote.RemoteWebDriver.ExecuteScript(String script, Object[] args)
at MyNAME.Selenium.SeleniumHelperGudusoft.SetSqlText(String sql) in C:\Users\MYLANID\Desktop\SqlSmoke Code\MyNAME.Selenium\SeleniumHelper.cs:line 41
at MyNAME.Selenium.Form1.button3_Click(Object sender, EventArgs e) in C:\Users\MYLANID\Desktop\SqlSmoke Code\MyNAME.Selenium\Form1.cs:line 201
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at MyNAME.Selenium.Program.Main() in C:\Users\MYLANID\Desktop\SqlSmoke Code\MyNAME.Selenium\Program.cs:line 19
When setting the value in JavaScript, you likely need to re-escape the carriage return and newline characters:
var sql = @"SELECT foo
FROM bar";
var jsString = sql.Replace("\r", "\\r")
.Replace("\n", "\\n");
js.ExecuteScript("arguments[0].CodeMirror.setValue(\"" + jsString + "\");", codeMirror);
The resulting JavaScript line would be:
arguments[0].CodeMirror.setValue("SELECT foo\n\rFROM bar")
Be aware that any double-quotes inside your SQL string will also need to be escaped so they do not prematurely end the JavaScript string:
var sql = @"SELECT foo AS '"bar"'
FROM baz";
var jsString = sql.Replace("\r", "\\r")
.Replace("\n", "\\n")
.Replace("\"", "\\\"");
js.ExecuteScript("arguments[0].CodeMirror.setValue(\"" + jsString + "\");", codeMirror);
So the resulting JavaScript is:
arguments[0].CodeMirror.setValue("SELECT foo AS '\"bar\"'\n\rFROM baz");