Please be aware that the SAP SQL Anywhere Forum will be shut down on August 29th, 2024 when all it's content will be migrated to the SAP Community.

We used "sa_get_table_definition" system procedure for a long time in SA 11.0.1.

After upgrading to SA16 we have noticed that Foreign Keys information is no longer present.

Only Self-keys are present in the results of sa_get_table_definition.

Maybe a bug? Should I open an Incident?

Thanks.

asked 31 Aug '17, 09:40

Thiago%20Reis's gravatar image

Thiago Reis
33181120
accept rate: 40%

The question to the audience, is this topic relevant for this question: Running pre-16.0 system procedures as invoker or definer?

(31 Aug '17, 11:26) Vlad

I checked an I´m using the DEFINER model:

"SELECT IF ((HEXTOINT(SUBSTRING(DB_PROPERTY('Capabilities'),1,LENGTH(DB_PROPERTY('Capabilities'))-20)) & 8) = 8) THEN 1 ELSE 0 END IF"

It returned 0.

(31 Aug '17, 11:50) Thiago Reis

Looks like there's a bug an undocumented behavior change in SQL Anywhere 12, 16 and 17.

----------------------------------------------------------------
SELECT @@VERSION;
SELECT sa_get_table_definition ( 'GROUPO', 'SalesOrderItems' );

@@VERSION
'11.0.1.3158'

CREATE TABLE "GROUPO"."SalesOrderItems" (
    "ID"                             integer NOT NULL
   ,"LineID"                         smallint NOT NULL
   ,"ProductID"                      integer NOT NULL
   ,"Quantity"                       integer NOT NULL
   ,"ShipDate"                       date NOT NULL
   ,CONSTRAINT "SalesOrderItemsKey" PRIMARY KEY ("ID" ASC,"LineID" ASC) 
)
;

COMMENT ON TABLE "GROUPO"."SalesOrderItems" IS 
    'individual items that make up the sales orders'
;

GRANT SELECT ON "GROUPO"."SalesOrderItems" TO "GROUPR" 
;

GRANT SELECT,INSERT,DELETE,UPDATE ON "GROUPO"."SalesOrderItems" TO "GROUPRW" 
;

commit work
;
ALTER TABLE "GROUPO"."SalesOrderItems"
    ADD NOT NULL FOREIGN KEY "FK_ProductID_ID" ("ProductID" ASC)
    REFERENCES "GROUPO"."Products" ("ID")

;

ALTER TABLE "GROUPO"."SalesOrderItems"
    ADD NOT NULL FOREIGN KEY "FK_ID_ID" ("ID" ASC)
    REFERENCES "GROUPO"."SalesOrders" ("ID")
    ON DELETE CASCADE 
;

commit work
;
----------------------------------------------------------------
SELECT @@VERSION;
SELECT sa_get_table_definition ( 'GROUPO', 'SalesOrderItems' );

@@VERSION
'12.0.1.4231'

CREATE TABLE "GROUPO"."SalesOrderItems" (
    "ID"                             integer NOT NULL
   ,"LineID"                         smallint NOT NULL
   ,"ProductID"                      integer NOT NULL
   ,"Quantity"                       integer NOT NULL
   ,"ShipDate"                       date NOT NULL
   ,CONSTRAINT "SalesOrderItemsKey" PRIMARY KEY ("ID" ASC,"LineID" ASC) 
)
;

COMMENT ON TABLE "GROUPO"."SalesOrderItems" IS 
    'individual items that make up the sales orders'
;

GRANT SELECT ON "GROUPO"."SalesOrderItems" TO "GROUPR" 
;

GRANT SELECT,INSERT,DELETE,UPDATE ON "GROUPO"."SalesOrderItems" TO "GROUPRW" 
;

commit work
;
commit work
;
----------------------------------------------------------------
SELECT @@VERSION;
SELECT sa_get_table_definition ( 'GROUPO', 'SalesOrderItems' );

@@VERSION
'16.0.0.2344'

CREATE TABLE "GROUPO"."SalesOrderItems" (
    "ID"                             integer NOT NULL
   ,"LineID"                         smallint NOT NULL
   ,"ProductID"                      integer NOT NULL
   ,"Quantity"                       integer NOT NULL
   ,"ShipDate"                       date NOT NULL
   ,CONSTRAINT "SalesOrderItemsKey" PRIMARY KEY ("ID" ASC,"LineID" ASC) 
)
;

COMMENT ON TABLE "GROUPO"."SalesOrderItems" IS 
    'individual items that make up the sales orders'
;

GRANT INSERT,DELETE,UPDATE ON "GROUPO"."SalesOrderItems" TO "MODIFY_ROLE" 
;

GRANT SELECT ON "GROUPO"."SalesOrderItems" TO "READ_ROLE" 
;

commit work
;
commit work
;
----------------------------------------------------------------
SELECT @@VERSION;
SELECT sa_get_table_definition ( 'GROUPO', 'SalesOrderItems' );

@@VERSION
'17.0.7.3399'

CREATE TABLE "GROUPO"."SalesOrderItems" (
    "ID"                             integer NOT NULL
   ,"LineID"                         smallint NOT NULL
   ,"ProductID"                      integer NOT NULL
   ,"Quantity"                       integer NOT NULL
   ,"ShipDate"                       date NOT NULL
   ,CONSTRAINT "SalesOrderItemsKey" PRIMARY KEY ("ID" ASC,"LineID" ASC) 
)
;

COMMENT ON TABLE "GROUPO"."SalesOrderItems" IS 
    'individual items that make up the sales orders'
;

GRANT INSERT,DELETE,UPDATE ON "GROUPO"."SalesOrderItems" TO "MODIFY_ROLE" 
;

GRANT SELECT ON "GROUPO"."SalesOrderItems" TO "READ_ROLE" 
;

commit work
;
commit work
;
permanent link

answered 31 Aug '17, 12:52

Breck%20Carter's gravatar image

Breck Carter
32.5k5417271050
accept rate: 20%

edited 31 Aug '17, 12:57

Comment Text Removed

Thanks Carter, I will open an incident.

(01 Sep '17, 14:06) Thiago Reis

This issue has been fixed in 16.0.0.2549 and 17.0.8.4043.

The sa_get_table_definition built-in system procedure should return the SQL statements required to create the specified table and its indexes, foreign keys, triggers, and granted privileges.


Currently, it is not including foreign key constraints. This procedure last worked correctly in 12.0.1.4181 and 16.0.0.1975 and has not worked correctly in 17.0.

This problem has been fixed.

This fix will also revert dbunload to its earlier behavior where the unloading of a subset of tables (-t option) could include foreign key references to tables that are not included in the unload.

permanent link

answered 08 Sep '17, 11:50

Mark%20Culp's gravatar image

Mark Culp
25.0k10142298
accept rate: 41%

converted 08 Sep '17, 11:53

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×261
×31

question asked: 31 Aug '17, 09:40

question was seen: 1,767 times

last updated: 08 Sep '17, 11:52