Powerbuilder PFC SQL script Files

Resolved Powerbuilder PFC SQL script Files

Misc

Votes

Undo

jorge gonzalez
PowerBuilder
Wednesday, 16 November 2022 20:21 PM UTC

Does anyone know where I can get a copy of the sql script files that create/populate certain small tables for the PFC. One of the tables created and populated is an error messages table.

Responses (6)

jorge gonzalez Accepted Answer Pending Moderation

Thursday, 17 November 2022 12:35 PM UTC
PowerBuilder
# 1

Thank you all!

Comment

There are no comments made yet.

Andreas Mykonios Accepted Answer Pending Moderation

Thursday, 17 November 2022 07:32 AM UTC
PowerBuilder
# 2

I also provide sql statements for security tables, for the case someone wants to use pfc security and don't have sql anywhere.

CREATE TABLE "DBA"."security_apps" (
	"application" VARCHAR(40) NOT NULL,
	"description" VARCHAR(64) NOT NULL,
	PRIMARY KEY ( "application" ASC )
) IN "system";
CREATE TABLE "DBA"."security_groupings" (
	"group_name" VARCHAR(16) NOT NULL,
	"user_name" VARCHAR(16) NOT NULL,
	PRIMARY KEY ( "group_name" ASC, "user_name" ASC )
) IN "system";
CREATE TABLE "DBA"."security_info" (
	"application" VARCHAR(40) NOT NULL,
	"window" VARCHAR(64) NOT NULL,
	"control" VARCHAR(128) NOT NULL,
	"user_name" VARCHAR(16) NOT NULL,
	"status" CHAR(1) NOT NULL,
	PRIMARY KEY ( "application" ASC, "window" ASC, "control" ASC, "user_name" ASC )
) IN "system";
CREATE TABLE "DBA"."security_template" (
	"application" VARCHAR(40) NOT NULL,
	"window" VARCHAR(64) NOT NULL,
	"control" VARCHAR(128) NOT NULL,
	"description" VARCHAR(254) NOT NULL,
	"object_type" VARCHAR(24) NOT NULL,
	PRIMARY KEY ( "application" ASC, "window" ASC, "control" ASC )
) IN "system";
CREATE TABLE "DBA"."security_users" (
	"name" VARCHAR(16) NOT NULL,
	"description" VARCHAR(32) NOT NULL,
	"priority" INTEGER NOT NULL DEFAULT 0,
	"user_type" INTEGER NULL,
	PRIMARY KEY ( "name" ASC )
) IN "system";

ALTER TABLE "DBA"."security_groupings" ADD CONSTRAINT "security_groupings_group" NOT NULL FOREIGN KEY ( "group_name" ASC ) REFERENCES "DBA"."security_users" ( "name" ) ON DELETE CASCADE;
ALTER TABLE "DBA"."security_groupings" ADD CONSTRAINT "security_groupings_user" NOT NULL FOREIGN KEY ( "user_name" ASC ) REFERENCES "DBA"."security_users" ( "name" ) ON DELETE CASCADE;

ALTER TABLE "DBA"."security_info" ADD CONSTRAINT "security_info_template" NOT NULL FOREIGN KEY ( "application" ASC, "window" ASC, "control" ASC ) REFERENCES "DBA"."security_template" ( "application", "window", "control" ) ON DELETE CASCADE;
ALTER TABLE "DBA"."security_info" ADD CONSTRAINT "security_info_users" NOT NULL FOREIGN KEY ( "user_name" ASC ) REFERENCES "DBA"."security_users" ( "name" ) ON DELETE CASCADE;

Andreas.

Comment

There are no comments made yet.

Andreas Mykonios Accepted Answer Pending Moderation

Thursday, 17 November 2022 07:22 AM UTC
PowerBuilder
# 3

Hi.

The database mentioned by John can be found on github (2021/pfc17.db at master · OpenSourcePFCLibraries/2021 (github.com)).

This one will run with sql anywhere. Username and password are the default (dba - sql).

It contains the following tables:

"DBA"."Messages"
"DBA"."security_apps"
"DBA"."security_groupings"
"DBA"."security_info"
"DBA"."security_template"
"DBA"."security_users"

From those tables only the first one is really needed. The security* tables are used by pfc security "add-on" which I haven't used for years. If you also don't need it then don't create those tables.

Follows the message table definition in sql anywhere. One thing I like to change is to make message table on dbo user. But it's up to you to see if having that table with dba as owner is ok or not.

CREATE TABLE "DBA"."Messages" (
	"msgid" CHAR(40) NOT NULL UNIQUE,
	"msgtitle" CHAR(255) NOT NULL,
	"msgtext" CHAR(255) NOT NULL,
	"msgicon" CHAR(12) NOT NULL,
	"msgbutton" CHAR(17) NOT NULL,
	"msgdefaultbutton" INTEGER NOT NULL,
	"msgseverity" INTEGER NOT NULL,
	"msgprint" CHAR(1) NOT NULL,
	"msguserinput" CHAR(1) NOT NULL,
	PRIMARY KEY ( "msgid" ASC )
) IN "system";
COMMENT ON TABLE "DBA"."Messages" IS 'Pre-Defined Messages.';
COMMENT ON COLUMN "DBA"."Messages"."msgid" IS 'Message ID';
COMMENT ON COLUMN "DBA"."Messages"."msgtitle" IS 'Title';
COMMENT ON COLUMN "DBA"."Messages"."msgtext" IS 'Text';
COMMENT ON COLUMN "DBA"."Messages"."msgicon" IS 'Icon';
COMMENT ON COLUMN "DBA"."Messages"."msgbutton" IS 'Button';
COMMENT ON COLUMN "DBA"."Messages"."msgdefaultbutton" IS 'Default Button.';
COMMENT ON COLUMN "DBA"."Messages"."msgseverity" IS 'Severity';
COMMENT ON COLUMN "DBA"."Messages"."msgprint" IS 'Print';
COMMENT ON COLUMN "DBA"."Messages"."msguserinput" IS 'UserInput';

And here are the default lines included in that table:

INSERT INTO dba.messages (msgid, msgtitle, msgtext, msgicon, msgbutton, msgdefaultbutton, msgseverity, msgprint, msguserinput)
VALUES('pfc_closequery_failsvalidation','Application','The information entered does not pass validation and must be corrected before changes can be saved.~r~n~r~nClose without saving changes?','Exclamation','YesNo',2,5,'N','N');

INSERT INTO dba.messages (msgid, msgtitle, msgtext, msgicon, msgbutton, msgdefaultbutton, msgseverity, msgprint, msguserinput)
VALUES('pfc_closequery_savechanges','Application','Do you want to save changes?','Exclamation','YesNoCancel',1,0,'N','N');

INSERT INTO dba.messages (msgid, msgtitle, msgtext, msgicon, msgbutton, msgdefaultbutton, msgseverity, msgprint, msguserinput)
VALUES('pfc_dsaccepttext','Application','Failed accepttext validation on datastore.  Column  %s  on row  %s.','Exclamation','Ok',1,20,'N','Y');

INSERT INTO dba.messages (msgid, msgtitle, msgtext, msgicon, msgbutton, msgdefaultbutton, msgseverity, msgprint, msguserinput)
VALUES('pfc_dwdberror','Application','%s','StopSign','Ok',1,20,'N','N');

INSERT INTO dba.messages (msgid, msgtitle, msgtext, msgicon, msgbutton, msgdefaultbutton, msgseverity, msgprint, msguserinput)
VALUES('pfc_dwlinkage_predelete','Application','Delete row(s)?','Exclamation','OkCancel',1,0,'N','N');

INSERT INTO dba.messages (msgid, msgtitle, msgtext, msgicon, msgbutton, msgdefaultbutton, msgseverity, msgprint, msguserinput)
VALUES('pfc_dwlinkage_requiredmissing','Application','Required value missing for %s on row %s.  Please enter a value.','Information','Ok',1,5,'N','N');

INSERT INTO dba.messages (msgid, msgtitle, msgtext, msgicon, msgbutton, msgdefaultbutton, msgseverity, msgprint, msguserinput)
VALUES('pfc_dwlinkage_rowchanging','Application','Updates are pending. ~r~nSave them now?','Exclamation','YesNoCancel',1,0,'N','N');

INSERT INTO dba.messages (msgid, msgtitle, msgtext, msgicon, msgbutton, msgdefaultbutton, msgseverity, msgprint, msguserinput)
VALUES('pfc_requiredmissing','Application','Required value missing for %s on row %s.  Please enter a value.','Information','Ok',1,5,'N','N');

INSERT INTO dba.messages (msgid, msgtitle, msgtext, msgicon, msgbutton, msgdefaultbutton, msgseverity, msgprint, msguserinput)
VALUES('pfc_requiredmissingondatastore','Application','Required value missing for %s on row %s.','StopSign','Ok',1,20,'N','Y');

INSERT INTO dba.messages (msgid, msgtitle, msgtext, msgicon, msgbutton, msgdefaultbutton, msgseverity, msgprint, msguserinput)
VALUES('pfc_systemerror','System Error','%s','StopSign','Ok',1,20,'N','N');

Andreas.

Comment

There are no comments made yet.

John Fauss Accepted Answer Pending Moderation

Thursday, 17 November 2022 01:19 AM UTC
PowerBuilder
# 4

Hi, Jorge -

The "PFC User's Guide" documentation from PB v9 mentions a local database named PFC.DB that was supposed distributed with the PFC, but there is no information about how to create your own version of this database. According to this document, this local database is/was accessible via ODBC.

I do not have a copy of this database file, but perhaps someone else in the Appeon Community does.

Comment

There are no comments made yet.

jorge gonzalez Accepted Answer Pending Moderation

Wednesday, 16 November 2022 23:16 PM UTC
PowerBuilder
# 5

I don't think this is it. There is a script that is run to create certain tables used by the pfc. one of the tables is called MESSAGES. The messages table could be in the file used to create the pfc security tables. Thanks

Comment

There are no comments made yet.

Olan Knight Accepted Answer Pending Moderation

Wednesday, 16 November 2022 23:08 PM UTC
PowerBuilder
# 6

Is this what you seek?
I'd attach the file, but .SQL is not allowed as an attachment type. Nor is .TXT.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

//
// This is the script to create the Powerbuilder system tables.
//
// Be sure to:
// -   change all occurrences of 'CHADBA' to the name that will own
//      the Powerbuilder system tables
// -   change all occurrences of 'database.tablespace' to the
//      appropriate value
// -   put in appropriate statement delimiters for the tool you use
//      to execute the sql (i.e. GO statement for ISQL)
// -   strip comments and blank lines if necessary

CREATE TABLE CHADBA.PBCATTBL
       ( PBT_TNAM CHAR(30),
         PBT_TID INTEGER,
         PBT_OWNR CHAR(30),
         PBD_FHGT SMALLINT,
         PBD_FWGT SMALLINT,
         PBD_FITL CHAR(1),
         PBD_FUNL CHAR(1),
         PBD_FCHR SMALLINT,
         PBD_FPTC SMALLINT,
         PBD_FFCE CHAR(18),
         PBH_FHGT SMALLINT,
         PBH_FWGT SMALLINT,
         PBH_FITL CHAR(1),
         PBH_FUNL CHAR(1),
         PBH_FCHR SMALLINT,
         PBH_FPTC SMALLINT,
         PBH_FFCE CHAR(18),
         PBL_FHGT SMALLINT,
         PBL_FWGT SMALLINT,
         PBL_FITL CHAR(1),
         PBL_FUNL CHAR(1),
         PBL_FCHR SMALLINT,
         PBL_FPTC SMALLINT,
         PBL_FFCE CHAR(18),
         PBT_CMNT CHAR(254))
         TABLESPACE tblspc_dev12c;


CREATE UNIQUE INDEX PBCATTBL_IDX
       ON CHADBA.PBCATTBL
       (PBT_TNAM,
       PBT_OWNR);

GRANT SELECT, UPDATE, INSERT, DELETE ON
   CHADBA.PBCATTBL TO PUBLIC;

CREATE TABLE CHADBA.PBCATCOL
       ( PBC_TNAM CHAR(30),
         PBC_TID INTEGER,
         PBC_OWNR CHAR(30),
         PBC_CNAM CHAR(30),
         PBC_CID SMALLINT,
         PBC_LABL CHAR(254),
         PBC_LPOS SMALLINT,
         PBC_HDR CHAR(254),
         PBC_HPOS SMALLINT,
         PBC_JTFY SMALLINT,
         PBC_MASK CHAR(31),
         PBC_CASE SMALLINT,
         PBC_HGHT SMALLINT,
         PBC_WDTH SMALLINT,
         PBC_PTRN CHAR(31),
         PBC_BMAP CHAR(1),
         PBC_INIT CHAR(254),
         PBC_CMNT CHAR(254),
         PBC_EDIT CHAR(31),
         PBC_TAG CHAR(254))
       TABLESPACE tblspc_dev12c;

CREATE UNIQUE INDEX PBCATCOL_IDX
       ON CHADBA.PBCATCOL
       (PBC_TNAM,
       PBC_OWNR,
       PBC_CNAM) ;

GRANT SELECT, UPDATE, INSERT, DELETE ON
   CHADBA.PBCATCOL TO PUBLIC ;

CREATE TABLE CHADBA.PBCATFMT
       ( PBF_NAME CHAR(30) NOT NULL,
         PBF_FRMT CHAR(254) NOT NULL,
         PBF_TYPE SMALLINT NOT NULL,
         PBF_CNTR INTEGER)
       TABLESPACE tblspc_dev12c;

CREATE UNIQUE INDEX PBCATFMT_IDX
       ON CHADBA.PBCATFMT
       (PBF_NAME);

GRANT SELECT, UPDATE, INSERT, DELETE ON
   CHADBA.PBCATFMT TO PUBLIC;

CREATE TABLE CHADBA.PBCATVLD
       ( PBV_NAME CHAR(30) NOT NULL,
         PBV_VALD CHAR(254) NOT NULL,
         PBV_TYPE SMALLINT,
         PBV_CNTR INTEGER,
         PBV_MSG CHAR(254))
       TABLESPACE tblspc_dev12c;

CREATE UNIQUE INDEX PBCATVLD_IDX
       ON CHADBA.PBCATVLD
       (PBV_NAME) ;

GRANT SELECT, UPDATE, INSERT, DELETE ON
   CHADBA.PBCATVLD TO PUBLIC;


CREATE TABLE CHADBA.PBCATEDT
       (PBE_NAME CHAR(30) NOT NULL,
        PBE_EDIT VARCHAR(254),
        PBE_TYPE SMALLINT,
        PBE_CNTR INTEGER,
        PBE_SEQN SMALLINT,
        PBE_FLAG INTEGER,
        PBE_WORK CHAR(32))
       TABLESPACE tblspc_dev12c;

CREATE UNIQUE INDEX PBCATEDT_IDX
       ON CHADBA.PBCATEDT
       (PBE_NAME,
       PBE_SEQN);

GRANT SELECT, UPDATE, INSERT, DELETE ON
   CHADBA.PBCATEDT TO PUBLIC;

//
// The following fills the Powerbuilder Format table with
// the default values.
//

INSERT INTO CHADBA.PBCATFMT VALUES (
   '[General]',
   '[General]',
   81,
   0); ;
INSERT INTO CHADBA.PBCATFMT VALUES (
   '0',
   '0',
   81,
   0);
INSERT INTO CHADBA.PBCATFMT VALUES (
   '0.00',
   '0.00',
   81,
   0);
INSERT INTO CHADBA.PBCATFMT VALUES (
   '#,##0',
   '#,##0',
   81,
   0);
INSERT INTO CHADBA.PBCATFMT VALUES (
   '#,##0.00',
   '#,##0.00',
   81,
   0);
INSERT INTO CHADBA.PBCATFMT VALUES (
   '$#,##0;($#,##0)',
   '$#,##0;($#,##0)',
   81,
   0);
INSERT INTO CHADBA.PBCATFMT VALUES (
   '$#,##0;[RED]($#,##0)',
   '$#,##0;[RED]($#,##0)',
   81,
   0);
INSERT INTO CHADBA.PBCATFMT VALUES (
   '$#,##0.00;($#,##0.00)',
   '$#,##0.00;($#,##0.00)',
   81,
   0);
INSERT INTO CHADBA.PBCATFMT VALUES (
   '$#,##0.00;[RED]($#,##0.00)',
   '$#,##0.00;[RED]($#,##0.00)',
   81,
   0);
INSERT INTO CHADBA.PBCATFMT VALUES (
   '0%',
   '0%',
   81,
   0);
INSERT INTO CHADBA.PBCATFMT VALUES (
   '0.00%',
   '0.00%',
   81,
   0);
INSERT INTO CHADBA.PBCATFMT VALUES (
   '0.00E+00',
   '0.00E+00',
   81,
   0);
INSERT INTO CHADBA.PBCATFMT VALUES (
   'm/d/yy',
   'm/d/yy',
   84,
   0);
INSERT INTO CHADBA.PBCATFMT VALUES (
   'd-mmm-yy',
   'd-mmm-yy',
   84,
   0);
INSERT INTO CHADBA.PBCATFMT VALUES (
   'd-mmm',
   'd-mmm',
   84,
   0);
INSERT INTO CHADBA.PBCATFMT VALUES (
   'mmm-yy',
   'mmm-yy',
   84,
   0);
INSERT INTO CHADBA.PBCATFMT VALUES (
   'h:mm AM/PM',
   'h:mm AM/PM',
   84,
   0);
INSERT INTO CHADBA.PBCATFMT VALUES (
   'h:mm:ss AM/PM',
   'h:mm:ss AM/PM',
   84,
   0);
INSERT INTO CHADBA.PBCATFMT VALUES (
   'h:mm:ss',
   'h:mm:ss',
   84,
   0);
INSERT INTO CHADBA.PBCATFMT VALUES (
   'm/d/yy h:mm',
   'm/d/yy h:mm',
   84,
   0);

//
// The following fills the Powerbuilder Edit Style table with
// the default values.
//

INSERT INTO CHADBA.PBCATEDT VALUES (
   '###,###.00',
   '###,###.00',
   90,
   1,
   1,
   32,
   '10');
INSERT INTO CHADBA.PBCATEDT VALUES (
   '#####',
   '#####',
   90,
   1,
   1,
   32,
   '10');
INSERT INTO CHADBA.PBCATEDT VALUES (
   '###-##-####',
   '###-##-####',
   90,
   1,
   1,
   32,
   '00')
INSERT INTO CHADBA.PBCATEDT VALUES (
   'MM/DD/YY',
   'MM/DD/YY',
   90,
   1,
   1,
   32,
   '20');
INSERT INTO CHADBA.PBCATEDT VALUES (
   'DD/MM/YY',
   'DD/MM/YY',
   90,
   1,
   1,
   32,
   '20');
INSERT INTO CHADBA.PBCATEDT VALUES (
   'MM/DD/YYYY',
   'MM/DD/YYYY',
   90,
   1,
   1,
   32,
   '20');
INSERT INTO CHADBA.PBCATEDT VALUES (
   'DD/MM/YYYY',
   'DD/MM/YYYY',
   90,
   1,
   1,
   32,
   '20');
INSERT INTO CHADBA.PBCATEDT VALUES (
   'DD/MMM/YY',
   'DD/MMM/YY',
   90,
   1,
   1,
   32,
   '20');
INSERT INTO CHADBA.PBCATEDT VALUES (
   'DDD/YY',
   'DDD/YY',
   90,
   1,
   1,
   32,
   '20');
INSERT INTO CHADBA.PBCATEDT VALUES (
   'DDD/YYYY',
   'DDD/YYYY',
   90,
   1,
   1,
   32,
   '20');
INSERT INTO CHADBA.PBCATEDT VALUES (
   'HH:MM:SS',
   'HH:MM:SS',
   90,
   1,
   1,
   32,
   '30') ;
INSERT INTO CHADBA.PBCATEDT VALUES (
   'HH:MM:SS:FFF',
   'HH:MM:SS:FFF',
   90,
   1,
   1,
   32,
   '30') ;
INSERT INTO CHADBA.PBCATEDT VALUES (
   'HH:MM:SS:FFFFFF',
   'HH:MM:SS:FFFFFF',
   90,
   1,
   1,
   32,
   '30');
INSERT INTO CHADBA.PBCATEDT VALUES (
   'MM/DD/YY HH:MM:SS',
   'MM/DD/YY HH:MM:SS',
   90,
   1,
   1,
   32,
   '40');
INSERT INTO CHADBA.PBCATEDT VALUES (
   'DD/MM/YY HH:MM:SS',
   'DD/MM/YY HH:MM:SS',
   90,
   1,
   1,
   32,
   '40');
INSERT INTO CHADBA.PBCATEDT VALUES (
   'MM/DD/YYYY HH:MM:SS',
   'MM/DD/YYYY HH:MM:SS',
   90,
   1,
   1,
   32,
   '40') ;
INSERT INTO CHADBA.PBCATEDT VALUES (
   'DD/MM/YYYY HH:MM:SS',
   'DD/MM/YYYY HH:MM:SS',
   90,
   1,
   1,
   32,
   '40') ;
INSERT INTO CHADBA.PBCATEDT VALUES (
   'DD/MMM/YY HH:MM:SS',
   'DD/MMM/YY HH:MM:SS',
   90,
   1,
   1,
   32,
   '40') ;
INSERT INTO CHADBA.PBCATEDT VALUES (
   'DDD/YY HH:MM:SS',
   'DDD/YY HH:MM:SS',
   90,
   1,
   1,
   32,
   '40') ;
INSERT INTO CHADBA.PBCATEDT VALUES (
   'DDD/YYYY HH:MM:SS',
   'DDD/YYYY HH:MM:SS',
   90,
   1,
   1,
   32,
   '40') ;
INSERT INTO CHADBA.PBCATEDT VALUES (
   'DD/MM/YY HH:MM:SS:FFFFFF',
   'DD/MM/YY HH:MM:SS:FFFFFF',
   90,
   1,
   1,
   32,
   '40') ;

Comment

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.

Please login to post a reply

You will need to be logged in to be able to post a reply. Login using the form on the right or register an account if you are new here. Register Here »

Forgot Password?

Resolved Powerbuilder PFC SQL script Files

Find Questions by Tag