Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (6)

Resolved Powerbuilder PFC SQL script Files

  1. Misc
0
Votes
Undo
  1. jorge gonzalez
    jorge gonzalez
  2. PowerBuilder
  3. 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.

Change in DW Data
Rotate a Datawindow 90 degrees. Is it possible?
Responses (6)
jorge gonzalez
jorge gonzalez Accepted Answer Pending Moderation
  1. Thursday, 17 November 2022 12:35 PM UTC
  2. PowerBuilder
  3. # 1

Thank you all!

Comment
There are no comments made yet.
Andreas Mykonios
Andreas Mykonios Accepted Answer Pending Moderation
  1. Thursday, 17 November 2022 07:32 AM UTC
  2. PowerBuilder
  3. # 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
Andreas Mykonios Accepted Answer Pending Moderation
  1. Thursday, 17 November 2022 07:22 AM UTC
  2. PowerBuilder
  3. # 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
John Fauss Accepted Answer Pending Moderation
  1. Thursday, 17 November 2022 01:19 AM UTC
  2. PowerBuilder
  3. # 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
jorge gonzalez Accepted Answer Pending Moderation
  1. Wednesday, 16 November 2022 23:16 PM UTC
  2. PowerBuilder
  3. # 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
Olan Knight Accepted Answer Pending Moderation
  1. Wednesday, 16 November 2022 23:08 PM UTC
  2. PowerBuilder
  3. # 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.