1. Kwadwo Boahene
  2. PowerBuilder
  3. Monday, 10 February 2020 14:32 PM UTC

In a effort to migrate from db2 to sql server i have run into a problem where an embedded sql command 'insert into' into a keyword column is not working in Sql Server but worked in db2.

///

//db2

INSERT INTO xxxxx.WS_TEMPLATE
                           (TEMPLATE_ID,
                            ORDER,
                            SOURCE_ID)
                     VALUES (:ll_template_id,:ll_order,:ll_source_id)
                USING SQLCA;

////no problems here in db2

//sql server

INSERT INTO xxxxx.WS_TEMPLATE
                           (TEMPLATE_ID,
                            [ORDER],
                            SOURCE_ID)
                     VALUES (:ll_template_id,:ll_order,:ll_source_id)
                USING SQLCA;

/////i get an incorrect syntax near 'ORDER'.

 

I am using pb2017 and sql server 2016.

 

Any ideas?

Michael Kramer Accepted Answer Pending Moderation
  1. Monday, 10 February 2020 16:26 PM UTC
  2. PowerBuilder
  3. # 1

PowerScript embedded SQL doesn't like reserved words used as identifiers. Same goes for DataWindow SQL syntax.

However, it IS possible - in both instances - this way:

  1. Ensure your DB connection uses DelimitIdentifier=1
  2. CONSEQUENCE => All strings in embedded SQL and in DataWindow SQL MUST use single quotes.
  3. Surround every identifier = "reserved word" with double quotes.

You could switch identifier delimiter on and off at runtime - but don't! It becomes an insane mess to keep app alive.

Code samples:

// Example Table ---------------------------------------------------------------
create table TEST(ID int IDENTITY, NAME nvarchar(50), [ORDER] int, constraint _ primary key (ID));

// DB connection setup (MSSQL 2019) --------------------------------------------
SQLCA.DBMS = "SNC"
SQLCA.DBParm = "...,DelimitIdentifier=1"
CONNECT USING SQLCA;

// Embedded SQL ----------------------------------------------------------------
INSERT INTO TEST(NAME, "ORDER") VALUES ('Test embedded SQL', 4);
SELECT newID INTO :id FROM (SELECT SCOPE_IDENTITY() newID) data;
COMMIT;

SELECT count(*) INTO :ll_count FROM TEST WHERE "ORDER" = 4;

// Using DataWindow in DataStore -----------------------------------------------
/* DW's SQL Painter */ SELECT ID, NAME, [ORDER] FROM TEST
/* Generated SELECT */ SELECT ID, NAME, [ORDER] FROM TEST
/* Generated INSERT */ INSERT INTO "TEST" ( "NAME", "ORDER" ) VALUES ( 'Test via DW SQL', 4 )

HTH /Michael

 

Comment
There are no comments made yet.
Matthew Balent Accepted Answer Pending Moderation
  1. Monday, 10 February 2020 16:20 PM UTC
  2. PowerBuilder
  3. # 2

Am still going through a database migration from SYBASE ASE to SQL Server.  Had a very similar issue.  The best thing to do is to bite the bullet and rename the column.  You are much better off in the long run.

Comment
There are no comments made yet.
Brad Mettee Accepted Answer Pending Moderation
  1. Monday, 10 February 2020 14:51 PM UTC
  2. PowerBuilder
  3. # 3

Have you tried using double quotes around the column name? By using double quotes, "order" will tell the parser that it's a table/column name and not a keyword.

In general, it's a bad idea to use keywords as table or column names, and should be avoided if possible.

Comment
  1. Chris Pollach @Appeon
  2. Monday, 10 February 2020 21:05 PM UTC
Hi Brad ... I agree 100%, rename all DBMS entity names that correspond to both DBMS and PB "reserved" words! The use of those is a 100% no, no that can lead to all kinds of bad issues!
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.