17
Views
11
Comments
Solved
Sql advance queries cannot insert
Question

Hi All,


I got error when I try to write queries in advance Sql cannot insert into "Incorrect syntax near the keyword 'into'"

in P11. But in P10 thats queries is work 

queries like this:

INSERT IGNORE INTO {TABLE_NAME}

     ({TABLE_NAME}.[TABLE_FIELD1],

      {TABLE_NAME}.[TABLE_FIELD2],

      {TABLE_NAME}.[TABLE_FIELD3],

SELECT  {TABLE_NAME2}.[TABLE_FIELD1],

     {TABLE_NAME2}.[TABLE_FIELD2],

{TABLE_NAME2}.[TABLE_FIELD3],

FROM {TABLE_NAME2}

WHERE {TABLE_NAME2}.[TABLE_FIELD1]=@PARAM

     

mvp_badge
MVP
Rank: #2
Solution

Hi Adam,

"INSERT IGNORE" seems MySQL-specific syntax. The database underlying OutSystems in P11 is either Oracle or SQL Server, MySQL support was dropped in P11. So my guess is the database got converted from MySQL to Oracle or SQL Server, which indeed means it doesn't work.

Note that database errors are always issued by the database, there's no translation done by the Platform. So the error "Incorrect syntax near the keyword 'INTO'" is what the database tells you, it's not Platform-version dependent.

Rank: #295

Hi,


Is your Query completed ?

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...); 


Thanks

Rank: #128

Hi Adam,

I would guess it's because of the IGNORE ?

I never seen that keyword, and a quick check with google suggests to me this is only useable on MySql databases.

So I'm guessing that when you used it successfully before, it was on a MySql database, and now you're trying to execute it on another dbms ??

Dorine

Rank: #295

I have created dummy query (with correct syntax) and let me share what I find

INSERT INTO {CategoryManual}
(
{CategoryManual}.[Label],
{CategoryManual}.[Order],
{CategoryManual}.[Is_Active])
SELECT 
{Category}.[Label],
{Category}.[Order],
{Category}.[Is_Active]
FROM {Category}
WHERE {Category}.[Id] = @CategoryId 

This is without IGNORE and work successfully

INSERT IGNORE INTO {CategoryManual}
(
{CategoryManual}.[Label],
{CategoryManual}.[Order],
{CategoryManual}.[Is_Active])
SELECT 
{Category}.[Label],
{Category}.[Order],
{Category}.[Is_Active]
FROM {Category}
WHERE {Category}.[Id] = @CategoryId 

Same error like you inform, so my guess is "IGNORE" statement.


mvp_badge
MVP
Rank: #2
Solution

Hi Adam,

"INSERT IGNORE" seems MySQL-specific syntax. The database underlying OutSystems in P11 is either Oracle or SQL Server, MySQL support was dropped in P11. So my guess is the database got converted from MySQL to Oracle or SQL Server, which indeed means it doesn't work.

Note that database errors are always issued by the database, there's no translation done by the Platform. So the error "Incorrect syntax near the keyword 'INTO'" is what the database tells you, it's not Platform-version dependent.