Creating peoplesoft FIELD through SQLs

This approach will be helpful, if you want to create lot of similar fields in peopletools.
Whenever we create a FIELD using AppDesigner in peoplesoft, then it will insert one row each in the following tables
PSDBFIELD -- It has lot of columns, it stores information about the datatype, length..etc..
PSDBFIELDNAMEVW -- Has only one column field name
PSDBFLDLABL -- Stores fieldname, label, long name, shortname, default label

You can query for the existing fields and create insert statements based on thoose data.
Execute thoose statements by connecting to the database to create the FIELDs.
Similarly when we add a FIELD to a RECORD, then one row will get inserted in each of the below tables
PSRECFIELD
PSRECFIELDALL
PSRECFIELDDB
PSRECFLDNOSR

Connecting database in JAVA through PeopleCode API

I wrote one utility method in java, through which we can query from database using peoplecode API. This code will work only if java code is deployed in people soft application.

Usage : query(record-name,where-condition ,bind-parametrs , select-column-list );

It will return the results in as list of Object arrays. Here each object array represents one row.
Objects are in the same order as mentioned in select-column-list.


public static List query(String tableName, String whereCondition, List bindVars, Object[] columns)
{
List results = new ArrayList();
Object row[] = new Object[columns.length];

// Creates the PeopleCode Query, table name also goes as a bind parameter
// So adding the table bind parameter
int nextBindVar = bindVars.size() + 1;
String peopleQuery = "%SelectAll(:" + nextBindVar + ") WHERE " + whereCondition;

Record menuRec = Func.CreateRecord(new Name("RECORD", tableName));
bindVars.add(menuRec);
SQL menuSQL = Func.CreateSQL(peopleQuery, bindVars.toArray());

//Fetch the rows
while (menuSQL.Fetch(new Object[]{menuRec}))
{ //Fetch the columns
for(int i = 0; i < columns.length; ++i)
{
Field field = menuRec.GetField(new Name("FIELD", columns[i].toString()));
row[i] = field.getValue();
}
results.add(row);
}
return results;
}