Error saving SQL Query

  • Resolved rc169

    (@rc169)


    1 year, 11 months ago

    Hello,

I have noticed a problem in the Query Builder with saving a query, which I believe is a case of "unintended behaviour".

I have created a query to create an SQL View, which is similar to this:-

CREATE OR REPLACE VIEW combined_view
AS SELECT table_r.common_name
, TRIM(table_r.item_num) AS item_num
, table_r.item
, CONCAT('<a href="',table_a.file_url_1,'" target="_blank">File 1</a>') AS file_url_1
, CONCAT('<a href="',table_a.file_url_2,'" target="_blank">File 2</a>') AS file_url_2
, table_a.chg_note
, TRIM(table_a.object_num) AS object_num
, CONCAT('<a href="../item-list/?wpda_search_column_index=',table_r.index, '" target="_blank">Item details</a>') as item_details
FROM table_a
LEFT JOIN table_r ON table_a.index = table_r.index;

The query runs successfully, and creates the SQL View, with the flie_url fields formatted and functioning correctly. However, when I save the query for future use (e.g. when the view needs to be regenerated), in the saved version, the CONCAT lines are incomplete, and appear like this:-

, CONCAT('File 1') AS file_url_1
, CONCAT('File 2') AS file_url_2

The resulting entries in those columns are, of course, ineffective as links! This is rather frustrating, as the query to create an SQL View is a type that is particularly likely to be required for future use, e.g. if the system is copied to another machine.

I had added some edits to the query (to add the TRIM commands for some fields), and these have been saved correctly. I have also repeated the save of the query with a different name, but the error is the same.

I presume that is not the intended behaviour?

Thanks in advance for your help.

Nigel
Viewing 7 replies - 1 through 7 (of 7 total)
  • Plugin Contributor Kim L

    (@kimmyx)

    Hi Nigel,

    Thanks for letting us know.

    I’ve forwarded this issue to Peter so he can take a look.

    We’ll update (or Peter will) you here once we receive feedback.

    We appreciate your patience! ??

    Plugin Contributor Kim L

    (@kimmyx)

    Hi Nigel,

    Peter said that this was a bug! He fixed it right away. ??

    It will be available with the next update this week.

    Thank you for reporting!

    Thread Starter rc169

    (@rc169)

    Hi Kim and Peter,

    Thanks for your message and the update. The issue with saving the queries with “CONCAT” entries seems to be resolved, but can you clarify whether empty spaces (to separate the entries in the CONCAT field) always need to be identified by double quotes? I have found that with single quotes (either vertical or angled) I get an error when I try to run the query in the “Query Builder” in WP Data Acceses, although they seem to be acceptable in phpMyAdmin.

    Thanks in advance for your help.

    Nigel

    Plugin Contributor Kim L

    (@kimmyx)

    Hi Nigel,

    Usually, if single quotes are used as identifiers for column names, you need to use another identifier for the space. Can you try something like this (I had to screenshot because the forum editor automatically reformatted the code block): https://share.getcloudapp.com/OAuo8yLw

    Try using a slanted quote for the column name, and then the vertical one for the space.

    Let us know if it works!

    • This reply was modified 1 year, 10 months ago by Kim L. Reason: formatting
    Plugin Contributor Kim L

    (@kimmyx)

    Hi there,

    I’m going to mark this as resolved as we haven’t heard from you in a while.

    Feel free to create a new thread if you have other questions.

    Thanks!

    Thread Starter rc169

    (@rc169)

    Hi Kim,

    Thanks for your messages, and apologies for not responding earlier.

    I have had a quick look at the query this evening, and I suspect that the problem arises because one type of quotation mark is used within a section that is itself bounded by the other type. I still got an error when I tried it tonight in WPDA.

    Previously, I had tested the query in phpMyAdmin, and generated the SQL View there. The view itself works correctly in WPDA/WordPress, so that it is not a “show stopper” issue.

    Nigel

    Plugin Contributor Kim L

    (@kimmyx)

    Hi Nigel,

    Thanks a lot for the response!

    To be safe, you can refer to this page for a guide on when to use single quotes, double quotes, and backticks in your queries: Single quotes, Double quotes and Backticks in MySQL

    Hope this helps!

Viewing 7 replies - 1 through 7 (of 7 total)
  • The topic ‘Error saving SQL Query’ is closed to new replies.