Advantage Database 9.1, Create Table with PK

Question

create table WEBLOG
(
ORDERNO CHAR(9) NOT NULL,
USERNAME CHAR(50) NOT NULL,
ACTION CHAR(255) NOT NULL,
NOTE MEMO,
UPDATEDATE DATE NOT NULL,
UPDATETIME TIME NOT NULL,
IP CHAR(15),
PK CHAR(36) NOT NULL,
PRIMARY KEY(PK)
)

the above query does not work, the error message say,

ERROR IN SCRIPT: poQuery: Error 7200:  AQE Error:  State = HY000;   NativeError = 2215;  [iAnywhere Solutions][Advantage SQL Engine]Invalid create options:  
NOCPTRANS and nullable column type options are only valid with ADS_VFP table type. -- Location of error in the SQL statement is: 23 (line: 3 column: 1)

what's wrong with that query?

My Advantage Database version is 9.1.

anybody know, please advice me.

Thanks!

Change your SQL to `PK CHAR(36) PRIMARY KEY` instead. I believe it's the extraneous `,` after the PK CHAR(36) NOT NULL` and the addition of the final line (which is syntactically invalid) that's causing it. — Ken White, Aug 06 '12 at 20:25
@Ken White I change to 'PK CHAR(36) PRIMARY KEY', but still same error message. — Expert wanna be, Aug 06 '12 at 20:33
What language are you working in? What AdsTableType have you set for your query? — Ken White, Aug 06 '12 at 20:35
@Ken White I'm using Advantage Database Architect program, and I want to set table Type as ADT but I don't know how to set Table type in query. — Expert wanna be, Aug 06 '12 at 20:46
If you're using Arc32, you set the table type up when you create the connection (alias) for your database. (For an existing connection, right-click the connection in the `Connection Repository`, choose `Connection Properties`, set `TableType`. For a new connection, use `Connection` from the main menu, and either `New Connection Wizard` or `Create New Connection`.) — Ken White, Aug 06 '12 at 20:52
@Ken White Thank you I just checked the table type, it is ADT. — Expert wanna be, Aug 06 '12 at 20:54
@Ken White Do you know what cause that error message? If I remove NOT NULL and primary key in the query then it works. — Expert wanna be, Aug 06 '12 at 21:05

score 4 · Accepted Answer · edited Jun 20 '20 at 09:12

This error is caused by using the NOT NULL, which is only supported on the ADS_VFP (Visual FoxPro) table type.

The usualy way to handle NOT NULL with Advantage is to make the connection via an ADS Data Dictionary, and then use the CONSTRAINT NOT NULL syntax instead. It won't work on free table (tables not in a data dictionary) connection, however - trying to just change it to CONSTRAINT NOT NULL on a free table results in the error The field-level or record-level constraint is invalid. Field level constraints are not supported on free table.

This is documented in the ADS 9.1 help, specifically under CREATE TABLE:

vfp-option ::= NULL | NOT NULL | NOCPTRANS

These options apply to Visual FoxPro tables (ADS_VFP) and can be used with free tables and data dictionary tables. The NULL (and NOT NULL) option indicate whether the column will be able to physically hold a NULL value. This is different from a NOT NULL constraint. If a Visual FoxPro column is created without the NULL option, then an error will be generated if an attempt is made to store a NULL in that column. The NOCPTRANS option applies to Visual FoxPro character and memo field types. If this option is provided, the data will not be translated across codepages (ANSI/OEM conversions).

BTW, when you get syntax error in ARC32, and your SQL is structured like yours is, the cursor is on the line causing the error. In this case, the error was caused on the very first column definition (ORDERNO CHAR(9) NOT NULL). Changing that single column definition to remove the NOT NULL makes the error occur on the next line.

Advantage Database 9.1, Create Table with PK

1 Answers1