About EasyMorph Tutorials & Examples Web-help

Detailed logs of SQL command sent

We are doing a one week evaluation (before purchase) of EasyMorph. We like a lot of what we see.

But to convince management we have to do an Insert into our MS-SQL DB (which is being managed by an ERP system called Epicor).

I just tried to do an SQL insert . The connection is working fine to the table, But I suspect the generated SQL needs more fields (I am only sending one).

Where can I see the exact SQL (over ODBC) that is being sent, and then I can tell you what we want the SQL to be.

in the picture below, I see what the columns that EasyMorph sees.

columns
20190923121426 Information Starting application version 3.9.5.6 with command line: “D:\Program Files\EasyMorph\Morph.exe”
20190923121428 Information New project
20190923121444 Information Open project D:\Epicor DEV\Federated Data\IMU.morph
20190923121528 Information File dropped: D:\Epicor DEV\Federated Data\TGIN-IMU.csv
20190923121528 Information Add table TGIN-IMU.csv to tab Tab 1
20190923121531 Information Changing selection to table TGIN-IMU.csv
20190923121719 Information Add action Calculate new column(s) to table TGIN-IMU.csv
20190923121719 Information Remove action Calculate new column(s) from table TGIN-IMU.csv
20190923121727 Information Add action Calculate new column(s) to table TGIN-IMU.csv
20190923121904 Information Changing selection to table TGIN-IMU.csv
20190923121907 Information Change properties of action “Calculate new column(s)” in table “TGIN-IMU.csv”:
Columns
20190923121952 Information Change properties of action “Calculate new column(s)” in table “TGIN-IMU.csv”:
Columns
20190923122005 Information Changing selection to table TGIN-IMU.csv
20190923122005 Information Changing selection to action “Calculate new column(s)” in table “TGIN-IMU.csv”
20190923122030 Information Add action Remove columns to table TGIN-IMU.csv
20190923122131 Information Changing selection to table TGIN-IMU.csv
20190923122133 Information Changing selection to table TGIN-IMU.csv
20190923122133 Information Changing selection to action “Remove columns” in table “TGIN-IMU.csv”
20190923122146 Information Change properties of action “Remove columns” in table “TGIN-IMU.csv”:
ColumnNames
20190923122232 Information Add action Export to database to table TGIN-IMU.csv
20190923122313 Information Change properties of action “Export to database” in table “TGIN-IMU.csv”:
TableName
20190923122344 Information Changing selection to table TGIN-IMU.csv
20190923122344 Information Changing selection to action “Import delimited text file” in table “TGIN-IMU.csv”
20190923122446 Information Changing selection to table TGIN-IMU.csv
20190923122446 Information Changing selection to action “Remove columns” in table “TGIN-IMU.csv”
20190923122501 Information Add action Rename columns to table TGIN-IMU.csv
20190923122504 Information Changing selection to table TGIN-IMU.csv
20190923122504 Information Changing selection to action “Remove columns” in table “TGIN-IMU.csv”
20190923122505 Information Changing selection to table TGIN-IMU.csv
20190923122505 Information Changing selection to action “Export to database” in table “TGIN-IMU.csv”
20190923122509 Information Changing selection to table TGIN-IMU.csv
20190923122514 Information Changing selection to table TGIN-IMU.csv
20190923122516 Information Move action “Export to database” in table “TGIN-IMU.csv” to position 4
20190923122517 Information Changing selection to table TGIN-IMU.csv
20190923122517 Information Changing selection to action “Rename columns” in table “TGIN-IMU.csv”
20190923122529 Information Change properties of action “Rename columns” in table “TGIN-IMU.csv”:
Names
20190923122531 Information Changing selection to table TGIN-IMU.csv
20190923122531 Information Changing selection to action “Export to database” in table “TGIN-IMU.csv”
20190923122540 Information Run project with reload
20190923122540 Error Run complete with 1 errors:
Error: Export of row #0 failed with the following error: The INSERT statement conflicted with the FOREIGN KEY constraint “FK_UD18_UD_UD18”. The conflict occurred in database “ERP10.2Test”, table “Ice.UD18”, column ‘SysRowID’.
The statement has been terminated.
Source: Action “Export to database” in table “TGIN-IMU.csv” in project “D:\Epicor DEV\Federated Data\IMU.morph”

Hi @DoctorYSG,

You can’t view SQL queries, executed by the “Export to database” action in EasyMorph. By you can try to find it in the SQL Server’s queries log.

As for you error messages. It seems that you also have to export values to “SysRowID” column which serves as a foreign key reference to another table.

@andrew.rybka Can I make a request that there be a tab so that one can see the raw SQL query in the client. It is sometimes hard to get on the database computer and too see the logs on the server. A little more detail if possible on the error would be useful sometimes.

@DoctorYSG, thank you for the suggestion. We will think how to address this problem.

As for error message - we just show the error which was issued by a database. With a small addition from our side (“Export of row #0 failed with the following error:”). What other details you would like to see in this error message?

***** moved to #feature-requests

Thank you for not sneering at me. If that is all the database replies, it would seem like what you are doing is as good as it gets.

But given that you want to aid-non DB admns, the messages about foreign keys, etc. are not going to be too helpful.

Here are three ideas of what you can do.

  1. allow non-tech to copy/paste the error and offending SQL and send it to a DBA who can then figure out what the real issue is.

  2. Since the error is on a row, and there might be many fields on that row, it can be hard to figure out what the offending field is. So after an error, you could iterate through the row, column by column and then report what field failed (I would not be surprized if you said this is a very low priority).

  3. Pop up a message with typical SQL CREATE issues that might be related to the error. E.g. bad-format dateTime strings, non unique guid when there is a constraint on that field, other field constraints such as foreign key, auto-filled, etc.

Being able to get the SQL generated under the hood is a rightful suggestion – we will see what we can do here.

Copying errors can already be done - click the copy icon in an error message to copy it (see below):
image

Solving a technical problem with a database for a non-technical person is a non-trivial task. While we do position EasyMorph for non-technical people, we also understand that at some point someone with technical skills may need to assist. Exporting to a database is a case when you probably don’t want to do heuristics much as there could be many things that can go wrong. It’s better for someone with a required skillset to take a look at the problem.

Thanks again for your suggestions. We’ve already decided to make a few improvements based on them.

Please keep posting your impressions and remarks about EasyMorph - that’s very helpful.

To learn more about EasyMorph visit easymorph.com.