Powerbuilder Interview question _part 7

NULL

1. What is NULL? What happens when you use NULL in expressions?
Null is an Undefined, Unknown value. If we use Null in arithmetic expression the expression becomes Null. If we use Null in a Boolean expression, Null and some value, expression becomes Null (False). If we use Null in a Boolean expression, the Null or some values it gives value and becomes True. PB does not initialize to NULL. To set NULL to the variable we use SetNull() function. Use SetNull() to set a variable to NULL before writing it to the DB. Note that PB does not initialize variables to NULL; it initializes variables to the default initial value for the data type unless you specify a value when you declare the variable. If you assign a value to a variable whose data type is Any and then set the variable to NULL, the data type of the NULL value is still the data type of the assigned value. You cannot un-type an Any variable with the SetNull function.

2. How can a variable become NULL?
Can be read from database or SetNull() function can be used

3. How to test whether variable or expressions is NULL?
Using ISNULL()

4. How does NULL behave in boolean expressions?
In AND logical operators that contain NULL result is always False In OR logical operators that contain NULL result is always True.

21. What is NULL? What happens when you use NULL in expressions?
What NULL means:
NULL means undefined. Think of NULL as unknown. It is not the same as an empty string or zero or a date of 0000-00-00. For example, NULL is neither 0 nor not 0.
Null is an unknown variable. When you use null in expressions they become null.
When a NULL value is read into a variable, the variable remains NULL unless it is changed in a script.
NULL means “no value”, “undefined”, “unknown”. It is not the same as an empty string or zero, or false. A common way for a variable to get a NULL value is when you retrieve its value from a DB column that has never been filled in –a missing zip code for a customer, for example.
Any variable, regardless of its type, can be set to NULL using the PowerScript SetNull function. E.g.
string i1
SetNull (i1)
If any part of the expression is NULL, the result of the entire expression is NULL.
OBJECT BROWSER

1. How do you look up functions for control (for example, ListBox) ?
Using Object Browser. We have to select the control and click on object browser.

2. What is the purpose of the Object Browser?
Purpose of an Object Browser is to look up for function, attributes, events which belong to the specified object .In the Window and User Object painters, PowerBuilder searches all attributes, scripts, variables, functions, and structures associated with the controls in the Window or User Object for the search string.

3. Where can you see all system object data types?
From library painter opening Select Browser class Hierarchy from the utilities menu.



OLE

OLE (object linking and embedding) is a standard that allows Windows programs to share both data and program functionality
1. In OLE, what is a server application?
A server application is the program that creates, displays, and edits embedded objects.

2. What is the role of a container application?
A container application is an application that contains references to an object embedded within.

3. Explain in-place activation.
The server application appears in the foreground and its menus replace those of the DataWindow painter or PowerBuilder application.

4. Where can you find information on OLE server applications for your Windows
environment?

5. How do you know what data type to use to store blobs in your Database?
Different DBMS have different data types that can be used to hold blobs.
Sybase SQL Anywhere ---- long binary
Oracle ---- long row
Sybase SQL Server ---- image

6. How should a blob column be defined in a data table?
Blob columns in the table definition must allow NULL values. (When a row is inserted or updated in the table and the Update() function is called, all non-blob columns are handled first using standard SQL statements. The blob column is then updated separately).

7. In what ways can you use OLE with a DataWindow?
As a presentation style.

8. How do you place a blob column in a DataWindow object?
Include blobs in a DataWindows object and define the data source.
1. What is the difference between an instance variable and a shared variable?


They have different scope. The value of the shared variable is available
from all instances of the same object. Instance variables are associated with one instance of
an object such as a window and each instance can have different values in instance
variable.

2.Why should you optimize libraries?


The library entity can become fragmented over time, increasing seek times.

3. What is the difference between reserved words Parent and ParentWindow when
they are used in the scripts for the menu item ?


When you use Parent in the script for a MenuItem, Parent refers to the
MenuItem on the level above the MenuItem the script is for.


The reserved word ParentWindow refers to the window a menu is associated
with.

4. How can you override user object's function?


To override an object's function , create a descendant object and declare
in there a function with the same name and arguments as in its ansestor.

5. What are object name pronouns and how do you use them?


You use them to refer to objects or controls instead of using actual
name. Pronouns are reserved words This,Super, Parent and ParentWindow.

This is used to refer to an object or control from within any script coded
for it.
Super reffers to an immediate anscestor of the object.
Parent is used to refer the window from any script for a control on the
window.
ParentWindow is used to refer to a window from a menu script.

6. How many clustered and non-clustered indexes can you create for a table in
Sybase?


You can create 1 clustered and up to 249 non-clustered indexes in Sybase.

7. What is the difference between Where and Having clauses?


Where clause eliminates unneeded rows before creating a result set as
opposed
to Having clause that gets rid of unwanted rows after the result set is
built.

8. How can you create multiple instances of the same window object?

To create an instance of a window type w_name
1.Declare a variable of type w_name : w_name w_mywindow
2. To open a new instance (and display the window) use the function call
like Open (w_mywindow)

Another way of creating multiple instances is by declaring a global
array having
type of your window (w_name w_mywindow[]) and use this array to store
references to
multiple instances when you open the window (Open (w_mywindow[iCounter]).


9. What's the usage of DECODE() function in Oracle?

Remove the pseudocolumn reference or function call from the procedural statement. Or, replace the procedural statement with a SELECT INTO statement; for example, replace

bonus := DECODE(rating, 1, 5000, 2, 2500, ...);

with the following statement:

SELECT DECODE(rating, 1, 5000, 2, 2500, ...) INTO bonus FROM dual;


Usually you use it in Select statements to provide descriptions for encoded values. Decodes could be nested. It's like a statement within select. For example, if you have a code table for Condition codes for a Repair shop, you do
Select item_id, decode(Condition_Code,
'DS', 'Dents',
'ES', 'Electrical short','Unnown condition code')
from repair_items.

10. How can you dynamically place an User Object on the Window?


You do this by using function OpenUserObject().
If you want to open the User Object with passing some parameters to it
you use
the function OpenUserObjectWithParm().
If you don't need this object anymore you have to call the function
CloseUserObject.

11. If there are both - foreign key and triggers on a Sybase table which one
will be fired (checked) first?

Foreign keys.

12. Let's say you've developed a window w_customers. After some time you
developed a base ancestor window w_base_win and want to use all functionality that you put
in this ancestor in your w_customer window. How can you do this?


If you developed a window without using any ancestors, it will be
inherited from PowerBuilders Window Object. To change the inheritance you should
Export existing window in a text file (Library Painter), change manually all references to
a standard Window object to w_base_win and Import the window back from this text file.

13. What are automatic and manual drag modes?


In automatic drag mode, PowerBuilder automatically switches the
application into drag mode when the control is clicked.
With manual drag mode, PowerBuilder does not switch the application into
drag mode when the control is clicked. You must control when the drag mode is
initiated.

14. What is ROWID in Oracle?

A ROWID is pseudo column for a table with the logical address for each row. It is unique and can be used in Select and Where clauses.It's the fastest way of getting the row from a table (where ROWID=..)


15. What is an object? a class? an instance?

Class - A definition of object
Object- A manifestation of a class
Instance - A copy of an object in the memory

16. What are the benefits of using encapsulation? Give an example of
encapsulation in PowerBuilder.


Encapsulation gives you the way to hide and protect data inside an object . To hide data we use private and protected variables. Private variables are accessible from scripts of the object only, and protected variables are available not only for the object's scripts, but also for all descendants' scripts.

For example, the code providing User Object functionality is written in the scripts and functions inside the User Object .

17. Is PowerBuilder object-oriented language? Why?

Yes it is. It has 3 main features of object-oriented languages - inheritance, encapsulation and polymorphism.

18. What types of objects can be inherited?

Windows, menus and user objects .

19. What utility could be used to create stored procedure in a database?


You can use PowerBuilder's Database Administrator painter,
ISQL utility in Sybase , SQL*Plus utility in Oracle.

20. How can you run another application from within PowerBuilder?


You could do it using PowerBuilder's function Run(). For example:
Run("C:\winword.exe Letter.doc", Maximized!)

This statement runs the MS Word maximized and passes it the parameter
letter.doc to open the file letter.doc.

21. How can you create in PowerBuilder an executable that can receive
parameters?


PowerBuilder has a function CommandParm() that retrieves the parameter
string, if any, when the application was executed. For example, you could put in an
Open event of the application something like string ls_command_line
ls_command_line = CommandParm( )
If the command line holds several parameters, you can use string functions
like Pos(),Left() ... to separate the parameters..


22. What is polymorphism? Give an example.

With a polymorphism, the same functions calls give different result on
different objects.
For example, you can define an UpdateDatabase() function for different
kinds of objects. Each has its own definition of what Update should do.


23. If you have a column that gets updated frequently, what kind of index will
you create on this column to improve the performance - clustered or non-cludtered ?


Non-clustered. If you create a clustered index on this column, the
data will be physically re-arranged in the database after each update and this will slow down
the performance.



24. How can you instantiate non-visual object ( custom class) in memory?


You need to declare a variable of your object's type and use CREATE
command:
uo_business_rules my_object
my_object = CREATE uo_business
Version 5.0 has the new syntax of this command - CREATE USING...

After you are done with this object , you should destroy it to avoid
memory leaking:
DESTROY my_object

In PB5 you can check the Autoinstantiate attribute of NVO and it'll create
an instance of NVO automatically when you declare a variable of this object type,
i.e. uo_business_rules my_object.

An instance of this NVO will be destroyed automatically when object where
it was declared is closed,destroyed or, in case of local variables when the script
is finished.



25. Let's say you have stored procedure B that is being called from procedure A;
you performed commit in B and after that procedure A failed. Will changes that
have been made in the procedure B be rolled back?


Yes. In case of nested stored procedures changes are commited
only after successful commit in the outermost stored procedure.


26. How can you convert data from one datatype to another using Transact-SQL
in Sybase?


There is a function Convert() in Transact SQL which is used for
data conversion. For example, to convert DateTime column into a string you could write
something like
Select convert(char(8), birthdate,1)
in this case third parameter specifies format of returned string ("mm/dd/yy")



27. How do you manage Database Transactions in PowerBuilder?



You use AutoCommit attribute of the Transaction Object for that.
If the value of AutoCommit is TRUE, PowerBuilder just passes your SQL
statements directly to the Server without issuing a Begin Transaction command..

If you set the value of AutoCommit to FALSE (default value),
PowerBuilder requires that you use the COMMIT and ROLLBACK keywords to commit
and rollback your transactions when you are finished with them. In this case
PowerBuilder begins the new transaction automatically as soon as you CONNECT,COMMIT or
ROLLBACK.

When you work with stored procedures in Sybase, Autocommit must be
set to true to be able to execute DDL statements and work with temporary tables
from within stored procedures.

Autocommit attribute of SQLCA is ignored when you work with Oracle.



28. How could you change a DataWindow object, associated with the DataWindow
control during the runtime?

DataWindow control has the attribute DataObject. If you want to change an associated DataWindow object during runtime you need to put a line in the script that changes the value of the DataObject attribute. For example:
dw_cust.DataObject = "d_customer"

30. How can you set DataWindow to a particular row?
You can do it by using the function ScrollToRow().

31. What should be done to make sure that several radiobuttons understand
that they belong to the same group (the user can select only one button
in the group)?
You should place them in a Group box on your window.

32. When is usage of indexes not effective?
When the expected result set has more that 5-10% of all rows in the table.

33. How many triggers can you specify for one database table?

In Sybase any table can have up to three triggers: one update trigger, one
insert
trigger, and one delete trigger. However you can create a single trigger that
will apply to
all three user actions (Update, Delete and Insert).

In Oracle a single table can have up to 12 types of triggers -
6 row level triggers (BEFORE AFTER INSERT, UPDATE, DELETE) and
6 statement level triggers.

34.How can you check if there are any indexes on a table in Oracle?
You can do a select from such views as ALL_INDEXES or USER_INDEXES where
TABLE_NAME = your table name.

35. What's the difference between Composite and Nested reports?
Composite report does not have a data source - it's just a container for
several datawindows (reports).
Nested reports have datasource and main datawindow can pass data to the
nested datawindow through retrieval arguments.

36. How can you use stored procedures in PowerBuilder?
You could use them either as a data source for the DataWindows (only in
SQL Server)
or you can execute them using EXECUTE command in PB script.
If the stored procedure returns a result set you have to:
1.DECLARE variable for stored procedure for example,
DECLARE emp_pro PROCEDURE FOR
pr_GetName @emp_name = :EditEmpName, @emp_salary = 5000
2. EXECUTE (e.g. execute emp_pro)
3. FETCH in a loop
4. CLOSE procedure (CLOSE emp_pro).

If the stored procedure also has a return code and output parameters,
you need to do one extra FETCH to get them.


37. What are the four levels of validation ?

1. Has anything changed?
2. Is the data of the correct type?
3. Does the data pass validation rules (which could be defined for some
columns)?
4. Does the data pass the ItemChanged event?

38. How can you print multiple DataWindows?


You should open the print job by calling the PrintOpen() function, after
that you can print DataWindows by using PrintDataWindow () function and after that you
close the print job by calling PrintClose().
You can also create Nested Reports or use the Composite presentation style
of DataWindows to print multiple datawindows as one Print Job.


39. Did you use any Version Control systems such as PVCS?


No. We just used Check In/Check Out features of PowerBuilder. Version 5 of PB
comes with a new version control software - Object Cycle but we did not use it yet.


40. How can you call an ancestor script from a script from a descendant object?


There is a CALL command that calls an ancestor script from a script for a
descendant object. For example, CALL w_emp::Open

Also you can use the pronoun Super to refer to the immediate ancestor.
For example, CALL Super::Clicked

To execute ancestor's function you just do Super::of_calc_fee()


41. How could you define additional Transaction object? When might you need it?


To create an additional transaction object, you first declare a variable of
type transaction, then you create the object and then define the attributes that will
be used.

Transaction TO_Sybase
TO_Sybase=CREATE transaction

TO_Sybase.DBMS = "Sybase"
TO_Sybase.database = "Personnel"
TO_Sybase.LogId = "DD25"
...
You need to create (an) additional transaction object(s) if you retrieve data
in your application from more than 1 database.


42. What is the usage of the DUAL table in Oracle?


It's an Oracle worktable with only one row and column in it. It's used to
make some calculations or get the values of some pseudo columns that are not
dependent upon the columns in a table, i.e.
Select SYSDATE from DUAL
SELECT 2*35 from DUAL

43. Where would you put the code to make sure that everything has been saved on
the window when it's being closed?


I would write this code under CloseQuery event of the window.
A CloseQuery event occurs every time when the window is about to be
closed. At this moment PowerBuilder checks the value of the attribute ReturnValue of the
Message Object. If after performing some processing you don't want to allow user to
close the window, set the value of the Message.ReturnValue to 1.
In version 5 you just need to do Return 1.


44. What is the difference between SetItem and SetText functions?


When you use SetText function, it places the value into the edit control on
a DataWindow and it must pass through the validation process.
SetItem function inserts the value of a specific cell directly into the
DataWindow buffer and skips the validation.


45. How can you get access to a Child DataWindow?


To retrieve manually a child DataWindow, we use the GetChild() function to
obtain reference to it, then we assign a transaction object to it, and then retrieve.
For example:
DataWindowChild dwc
int rtncode
rtncode = dw_emp.GetChild("emp_id",dwc)
dwc.SetTransObject(SQLCA)
dwc.Retrieve("argument")

46. What is the usage of the Yield() function?


Yields control to other graphic objects, including objects that are not
PowerBuilder objects. Yield () checks the message queue and if there are messages
in the queue, it pulls them from the queue.
You can use Yield() to allow users to interrupt a tight loop. When you use
Yield() to interrupt a loop, you must call the Yield function and check for an interrupt
each time you pass through the loop.

47. How can you change select statement of the DataWindow at runtime?

You can change Select statment using functions SetSqlSelect() or
by changing the attribute DataWindow.Table.Select with the function
Modify().For example,

dw_1.Modify("datawindow.Table.Select='Select * from customers'")

In version 5 you can change attributes of datawindow object directly
by using a keyword Object like
dw_1.Object.DataWindow.Table.Select='Select * from customers'

SetSqlSelect works slower since it checks the syntax of SQL statement.

48. What kinds of DataWindow buffers do you know?

There are 4 buffers in Powerbuilder: Primary!, Filter!, Delete! and
Original! buffer.
Primary! buffer has the current data values;
Filter! buffer stores the rows that where filtered out as a result of
SetFilter() and Filter() functions;
Delete! Buffer keeps the rows that have been deleted by DeleteRow()
function;
Original! buffer is used by PowerBuilder to store original data values
retrieved into a datawindow. PowerBuilder uses this buffer to generate the Where clause
for the Update() function.

49. What is the recommended size of .pbl ?

The recommended size used to be less than 800kb and not more than 50-60 entries,
but from my experience even bigger pbl's work just fine.

50. Can you inherit DataWindow?


You cannot do it directly, but you can create a Standard User Object of
type DataWindow (which can be inherited) and use it instead of DataWindow controls.
You use the same method when you need to inherit any other control.

51. Where can external functions be declared?

External functions can be declared in User Objects or Windows.

52. Give a definition of a Union.

The Union operator is used to combine the result sets of two or more
Select statements. The source of the data could be different, but each Select statement
in the Union must return the same number of columns having the same data types.
If you use Union All it will return all rows including duplicates.


53. What functions can you use to pass parameters between windows in
PowerBuilder?

Open WithParm( ) , OpenSheetWithParm( ), and CloseWithReturn( ).

54. How do OpenWithParm() and CloseWithReturn() functions pass parameters?

They pass parameters using Message Object's attributes StringParm,
DoubleParm or PowerObjectParm.


55. Can you delete a menuitem in the descendant menu if it was created in the
ancestor one?


No you cannot. If you don't need an object that was defined in the
ancestor menu just make it invisible by setting its Visible attribute to FALSE or using
function Hide().


56. What are the 2 objects every application has?


Every PowerBuilder application has the MessageObject and the ErrorObject.

57. Describe the sequence of events which are triggered when you call the
Datawindow's function Update().
UpdateStart! event,
SQLPreview! event for every modified row,
UpdateEnd! event.

58. What's the difference between computed columns and computed fields in
DataWindow object?


You define a computed column in the Select painter and it's calculated by
the DBMS when the data is retrieved.
Computed field is defined in the datawindow painter and is calculated after the data
has been retrieved.
If you change values of some columns that are part of the computed field
expression they will be recalculated immediately. Computed columns are not
recalculated after the data is retrieved.

59. What are the system databases in Sybase?

Beside user's databases Sybase has databases: Master, Model,TempDB,
Pubs2 and some others like SYBSYSTEMPROCS, PUBS2.
Master is used internally by Sql Server to store information about users'
databases;
Model is used as a template to create a new user's database and TempDB is
used to store temporary information like temporary tables if you use them in
stored procedures.


60. What is a class or non-visual user object?

Non-visual user objects (or classes) are objects that encapsulate
attributes and functions, but are not visible to the user. You can use them, for example, to
code some business rules which are used in different windows.


61. Hou can you return user-defined error message from a stored procedure?

In SYBASE you use RAISERROR statement;

In ORACLE you need to declare a User exception and raise it using RAISE
statement or you can use special procedure RAISE_APPLICATION_ERROR (error_number,
error_message)

62. How can you share buffers between two DataWindows?

To share buffers between 2 DataWindow controls, we use ShareData()
function.
The result set description for both DataWindow objects must be the same,
but the presentation style could be different.
To turn off the sharing of data buffers for a DataWindow control you use
the function ShareDataOff().

63.What is the difference between TriggerEvent() and PostEvent() functions?

The TriggerEvent() function causes the event to occur immediately. This
function does not return until the processing for the event is complete.
PostEvent() function causes the event to occur when the current event is
finished executing.


66. Give an example of an outer join.

An outer join forces a row from one of the participating tables to appear
in the result set if there is no matching row. An asterisk is added to the equal
sign to specify outer joins.
For example, if some customers might not have orders yet, we could use:
Select customer.id, name
from customer, orders
where customer.id *= orders.id

(In Oracle you use (+) after the column name)

67. Give an example of Cartesian Product.

Cartesian product is the set of all possible rows resulting from a join of
more than one table. For example, if you forget about a join condition:
Select customer.name, orderno
from customers, orders
If customer had 100 rows and orders had 500 rows, the Cartesian product
would be every possible combination - 50000 rows.

68. What is the primary key?

The primary key of a table is the column or set of columns that are used
to uniquely identify each row. If you want to find a specific row in a table, you
refer to it using the primary key. Usually, you specify the primary key when you create a
table:
Create table customers ( id char(3),
lname char (20),
fname char(20),
primary key (id))
69. What is a foreign key?

A foreign key references a primary key in another table. When one table's
column values are present in another table's column, the column from the first
table refers to the second.

70. What is the usage of indexes and how to create them?

Indexes optimize data retrieval since the data can be found without scanning an entire table.
Indexes can also force unique data values in a column.
For example, to create an index named custind using the id column of the
customers table, you have to issue the following command:
create index custind on customers(id)
To create unique index:
create unique index custind on customers(id)
There are clustered and nonclustered indexes in Sybase.
Indexes are not effective if the result set has more that 10% of rows in a table.

71. Do indexes in Sybase affect the physical order of data in the table ?


If there is a clustered index on the table, the actual data is sorted
in the index order.
Nonclustered indexes do not reorder the actual data.

72. Which system objects are useful when you write triggers?

In SYBASE: The Inserted and Deleted table are used when writing triggers.
Inserted table holds any rows added to a table when you execute Insert or
Update Sql statements.
Deleted table holds any rows removed from the table when you execute
Delete or Update Sql statements.

In Oracle: You use :old.columname to refer to original value of the column
and :new.columname to get the new value for a column.

73. What is the message object and how is it used when passing parameters
between windows?

A message object is a PowerBuilder-defined global object populated with
information about an event. It has such attributes as WordParm and LongParm.
It is used to pass data between windows or objects. Data will be passed through
the Message.StringParm, Message.DoubleParm or Message.PowerObjectParm depending
on the type of data which you want to pass.

74. What is function overloading?

A function is overloaded when there are multiple versions of
this function with the same name but with different arguments.
When the function is called, PowerBuilder finds the function
whose argument list matches the function call from the script.
In PowerBuilder 4 you had to create a descendant object for every
overloaded function.
In Version 5 you can declare all overloaded functions in the same object.

75. What could you use the SQLPreview event for?


You could use SqlPreview event if you want to use stored procedures to
perform update in the database.
You can also use the SQLPreview event to display the SQL statement right
before its execution for the debugging purposes. To get the curent Sql Statement you use
GetSqlPreview() function.
In version 5 you don't even need to call this function since
SQLPreview event has an argument with SQL Statement.

76. What should be distributed to your user with your application?

The .EXE, .PBDs (if any), and the Database Development and Deployment Kit DLLs.

77. What is a .PBD and when do you use them?

A .PBD file is PowerBuilder dynamic libraries. It contains all the
compiled objects that the .PBL contains.
You use them to make the size of your executable smaller, and objects that you
put in PBD's will be loaded at runtime only when needed.



81. If you have an MDI Window, sheet1 with menu and sheet2 without the menu. You
open sheet2 after sheet1. Which menu will sheet2 have - MDI menu or Sheet1
menu?

It will have the menu of Sheet1 as the last menu that was displayed on the
MDI window.


84. What kind of windows can be closed using the function CloseWithReturn()?
Response Windows.

85. What do you know about Views?
A View is simply a select statement that has been STORED IN THE DATABASE.
Usually, Views are used to limit the access of users to data. Views are database
objects, so you can grant user permissions to them.

86. What do you know about fill factor in Sybase?
The fill factor is a percentage specifying how full you want your index
and/or data pages when the index is created.
A lower fill factor leaves more free space in the pages. It could improve
performance in environments where there are a lot of inserts and updates to the
data.
A higher fill factor is useful with relatively static data.

87. How can you change an object's ancestor without recoding it?
Export the object using Library Painter, then
change all references to the ancestor using any text editor and
import the object back.

88. How can you find out what's the object under mouse pointer in a datawindow?

The function GetObjectAtPointer() will tell you what's under the mouse -
column, header, etc.
The function GetBandAtPointer() will tell you the band name such as
detail, header, etc.

89. How do you handle Null values in a database?
In Sybase you use IsNull() function, in Oracle - NVL() function.

90. How can you generate unique numbers in a database?
In Sybase you can define an extra column of Identity type in a table.

In Oracle you create a Sequence. For example,

Create sequence Customer_id_seq;
Select Customer_id_Seq.nextval from dual;

91. What types of database constraints do you know?

Primary key, NOT NULL, Unique, Check, Foreign Key

92. What's a basic structure of PL/SQL block?
DECLARE
BEGIN
EXEPTION
END

93. What's an exception in Oracle?
In PL/SQL it's a warning or error condition.

94. What composite data types do you know in PL/SQL?

Record and Table.
PL/SQL Record is similar to a structure in other languages.
PL/SQL Table is similar to a one-dimensional array.

95. If you use a cursor in Oracle PL/SQL block, how can you determine when
fetching data when to enter
and exit the loop?

You use cursor attributes such as %ISOPEN, %NOTFOUND, %FOUND, %ROWCOUNT.

96. What's the minimal data item that can be locked in a database?

In Sybase System 10 it's a page (usually it's 2 kb);
In ORACLE it's a row.

97. What are the benefits of using stored procedures?

SP are stored in compiled mode in a database, reduce network traffic, modularize
application development, makes maintenance easier, provide additional security.

98. How many types of Dynamic SQL do you know in PowerBuilder?

There are 4 formats: 1. Non- result-set, no input parameters;
2. Non-result-set with input parameters;
3. Result-set, known input parameters and result-set
columns;
4. Result-set, unknown input parameters and/or result set
columns.
For format 1 you must use EXECUTE IMMEDIATE statement;
For formats 2,3 and 4 you need to prepare a dynamic staging area (SQLSA);
For format 4 you also need to use dynamic description area (SQLDA).

99. How can you obtain the source code of a trigger in Oracle?
You can get from Oracle's view USER_TRIGGERS from column TRIGGER_BODY.

100. What pseudo columns do you know in Oracle?

USER, SYSDATE, ROWID, ROWNUM, NULL...

101. How can you disable or enable trigger in Oracle?
You can do it using Alter trigger statement. For example:

ALTER TRIGGER delete_cusomer DISABLE

ALTER TRIGGER update_orders ENABLE

102. How can you check the definition of a table in SQL*Plus?

You can do it using command DESC. For example:

SQL>desc customers.

103. What's the difference between Class Library and Application Framework?

Class Library is usually a set of independent objects, while in an
application framework objects depend on each other's variables, messaging
and functions.

104. What kind of problems might you have if you want to use existing
application on laptop computers?

You need to take care of resizing windows and controls. You can do it by
placing code under resize event of the window (base window) or create a special NVO
to do resizing.

105. How can you lock a row in Oracle?

You need to do select of this row using syntax SELECT ...FOR UPDATE.
This way the row will be locked until the program issues commit or
rollback.

106. What are the new concepts of OOP that are implemented in PFC?

It's been built using service-based architecture.

The idea is to keep the ancestor object 'thin' and plug services into it when needed.
The services are implemented as NVO's.

107. How can you see execution plan of the SQL statement?

In Oracle you can use command Explain Plan 'select ...' in SQL*Plus.

In SYBASE you need to set an option SET SHOWPLAN ON in ISQL and then type
your select statement.

108. What are two methods of optimization in Oracle?

It could be either Cost based or Rule based optimization.

109. How can you avoid retrieving duplicate rows into a result set?

You should use DISTINCT keyword in the Select clause.

(In Oracle you can use the word UNIQUE instead of DISTINCT)

110. How can you see the list of existing objects in Oracle database such as
tables, views, synonyms?

Oracle has special view USER_CATALOG and you can do a select form this
view (or select * from CAT. CAT is a synonym of USER_CATALOG)

111. What is a synonym in Oracle?

It's an alias name assigned to a table or view. You can use it to simplify
SQL statements, for example, you can use synonims to hide links to remote
databases.

112. What are the normalization rules in RDBMS?

1st Normal form: There should be only one value for each row and column
intersection.
2nd Normal form: All non-primary columns must depend on the entire primary
key.
3rd Normal form: Non-primary columns should not depend on other non-primary
columns.

113. What is a Snapshot in Oracle?

Snapshots are a local copies of a remote table. Usually they are crated
automatically in specified time intervals.

114. What is a cluster in Oralce?

Cluster is a method to store related database tables in the same area on a
disk.
To use clusters you need to specify a cluster key column when you create
tables.

115. How are tables physically stored in Oracle database?

Table are created in a special areas on the disk called tablespace.
Each has segments and extents. Maximum number of extents is 121.

116. How can you see complilation errors in SQL*Plus when you create
stored procedure, function...?

If stored procedure had errors SQL*Plus gives a message that object was
created with compilation errors and you can see the errors by typing

SHOW ERRORS
or
Select * from user_errors.

117. How can you debug Powerbuilder's executable?

You need to put a /pbdebug key in the run line of any powerbuilder exe.
This will create a text file with extention .dbg where you can see
which events and functions were executed by your application.

118. How can you undelete a row in a datawindow?

You need to move row using a function RowsMove() from the Delete! buffer
into the Primary! buffer.

119. Where is it not recommended to put a breakpoint in the debugger?

It's not recommended under Activate and GetFocus Events.

120. Can you post event to an application?

No, you cannot.

121. How can you find a number of rows returned by an embedded SQL statement?

You can find it in the attribute SQLCA.SQLNRows.

122. How can you stop the processing in the Other Event of the datawindow?

You need to set Message.Processed to TRUE.

123. How can you stop the retrieval process in a datawindow after some number of
rows?

You need to declare a variable, increment it by one in the script
for the RetrieveRow event and check its value like

il_count++
If il_count > 10 then Return 1 // Stop the retrieval

124. How do you process database errors in case of a Datawindow and Embedded
SQL?

In case of Embedded SQL you check the value of SQLCA.SQLCode and if it's
less than 0,
Get the error code and error message from SQLCA.SQLDBCode and SQLErrText.

In a Datawindow you get the errors in a DBError event. In PB5 you get error
code and text from the arguments of this event, in PB4 you use the functions
DBErrorCode() and DBErrorMessage().

125. How do you declare an external function if it does not return a value?

You need to start a declaration with a keyword SUBROUTINE.

126. What is a correlated subquery?

A correlated subquery is a subquery that references columns in the tables
of its containing query. For example:

Select name from student s1
where age < (select max(age) from students s2
where s1.grade = s2.grade)

127. Can you update database using views?

You can do this only if the view is based on a Select from a
single table. If the view has been built based on a table join you
cannot do an update.

128. How can you retrieve, say, first 10 rows only from the result set
using ISQL?

In ISQL you should type SET ROWCOUNT 10 and after this line
your select statement.

To return back to full result set processing, type SET ROWCOUNT 0.

129. How many pbl's do you need to add to your application if you want to use
PFC in your app.?

You need to add 8 pbls:
4 pbls with names starting with PFC which are PFC objects
and 4 with names starting with PFE which are Extension Layer objects.

130. When do you use a Sybase command DUMP TRAN WITH NO_LOG

We use it when transaction log is full to remove inactive transactions.

131. Where are Sybase's system procedures stored?

They are stored in the table SYSCOMMENTS in the database SYBSYSTEMPROCS.

132. Name the two ways you can access values in a DataWindow?

You can access data values in a Data Window by using SetItem() or
GetItem() functions or using the new syntax in version 5.0:

dw_1.Object.emp_lname[1] = 'Smith'

133. How do windows and menus communicate in PFC?

They communicate using special object Message Router.
In the clicked event of the menu you call function of_SendMessage()
and Message router will send this message to the window, active control
and the last active Datawindow.

134. What is the use of quick-start libraries in PFC?

They contain pre-coded extention level objects so you can start a new
application without typing scripts like declaration of gnv_app variable,
etc.
You just need to include a copy of these libraries in your application's
library list instead of standard extension libraries.

135. What application services do you know in PFC?

There are DataWIndow Caching, Debugging, Error, Security and
Transaction registration services.

136. What DataWindow services do you know in PFC?

I know such services as Linkage, Sort, Filter, SelectRow and others.

137. What is the difference between functions and events in Powerbuilder?

They are almost the same in version 5. The major difference is that object-
level functions could be private and protected while events are always public.
You can specify a function name in the script even if the specified object
does not have such a function. You just need to add the keywords FUNCTION DYNAMIC.
For example:

invo_mine.FUNCTION DYNAMIC of_calc_salary().

138. How can you dynamically create a datawindow object at runtime if
you have a Select statement?
You can do it using the functions SyntaxFromSQL() and Create().

139. Can you use datawindows inside of a non-visual object?

You cannot use datawindows but starting from version 5 you can use
datastores inside NVO which has most of datawindow's functionality.

140. How can you extend functionality of, say, Transaction Object in PowerBuilder?

You need to create an object of type Standard Class inherited from
Transaction objects and add some properties/methods to it.


141. How can you get data from a database table based on data in another
table?

You can use correlated subquery for that. For example:

Select last_name
from customer where not exists
(select * from cust_order
where customer.id = cust_order.cust_id)

142. What command do you use in Unix to change the permission on files?

You do it using chmod command. For example, to revoke read, write and
execute permissions on a file my_file from other users you do

chmod g -rwx my_file

143. Which functions should you not call in the ItemChanged event to avoid an
endless loop?

You should not call AcceptText(), SetColumn() and SetRow()
in an ItemChanged event.

144. How can you find out if a table has any indexes in Sybase?

Call system stored procedure sp_helpindex, i.e.

sp_helpindex customers

145. How to ensure that SQL query is optimized in a Sybase stored procedure?

Execute or create stored procedure with option RECOMPILE

146. How to find out what columns a Sybase table has?

Use system stored procedure sp_columns

147. How to change your password in Sybase SQL Server?

Use system stored procedure sp_password

148. How to check if there are any currently locked tables in Sybase?

Use system stored procedure sp_lock

149. What's the name of the transaction object that comes with PFC?

n_tr.

150. If you can access object level public variables from the other object scripts - what's
the difference between global variables and object level public variables?

Global variables are always available when the application is running but
object level variables are available only if the object is instantiated.

151. Let's say you have local and instance variables with the same name X
and you are assigning the value to the variable: X=25. Which variable will be
equal to 25 - local or instance?

Local.

152. Write a code to update two datawindows in one transaction.

SQLCA.AutoCommit = FALSE // This Begins the Transaction

IF dw_1.Update( ) > 0 THEN
IF dw_2.Update( ) > 0 THEN
// Both updates succeeded, COMMIT the changes.
COMMIT;
ELSE
// Update of dw_2 failed, ROLLBACK the changes;
ROLLBACK;
END IF
ELSE
// Update of dw_1 failed, ROLLBACK the changes;
ROLLBACK;
END IF

// Clean up AutoCommit after COMMIT/ROLLBACK to prevent another transaction from beginning.
SQLCA.AutoCommit = TRUE

You might want to call the Update function with arguments (True, False)
to prevent resetting of row statuses. In this case you need to call function ResetUpdate()
after successful updates.

153. What's the maximum size of data you can store in integer and string variables respectively?

Integer can store values up to 32kb and string variables can hold up to 60000 bytes.

154. What are the different ways to pass arguments to a function?

You can pass arguments by value, by reference and read_only (in PB5).
If you choose 'by value' - PowerBuilder passes the copy of an argument to a function and any
changes to this value in a function will not affect the original.
'By Reference' means that PowerBuilder passes a pointer to the passed variable and all changes
to this value will be applied to the original.
'Read Only' passes argument by value without creating a copy of the variable but you cannot
change this argument within the function.

155. What happens to a PowerBuilder object when you regenerate it?

Powerbuilder recompiles all scripts of this object to make sure that all referred objects exist.

156. Give an example of delegation in PFC.

All application related services are implemented in n_cst_appmanager object. In the Open event
of the application you instantiate this object and trigger pfc_open event to this object.
In other words, you delegate the processing of the Open event to another object.

157. Which of the following DataWindow object settings will give you the highest row locking
protection, in a multiuser environment?
- Key Columns
- Key and Updatable Columns -- Correct answer
- Key and Modified Columns


158. What types of joins do you know?

Equijoin, Non-equijoin, Outer join, Self join

159. If you are developing an NVO that has a private variable - what would you do
to allow other objects to change the value of this variable?

I would create a public function in this object, for example, of_set_value().
This function will set the value to the private variable.

160. What do you know about MDI_1 control?

Whenever you create a window of type MDI_frame or MDI_frame with MicroHelp,
PowerBuilder automatically creates a control 'MDI_1'. This control represents the client
area in the window. You can open sheets only in the client area, i.e., in the MDI_1 control
only. MDI_1 is not a reserved word, so you can name another control with this name, but,
it is not advisable.

161. Let's say, SQL UPDATE statement modified 10 rows on a Sybase table that had a trigger
for Update. How many times will the trigger be fired?

It'll be fired once - Sybase has statement level triggers only.

162. How do you usually test your application and move it into production?

Initially each team member does unit test of his code. When it's done,
we build the executable and pbd's and perform the system test.
If we find some bugs, we perform the unit test again. If everything is OK,
we optimize and regenerate libraries and copy executable and pbd's into
a special directory on a file server and users can run the application
from there to perform the user acceptance test.
If they find a bug, we start from unit test again and re-build the pbd(s)
with the problem fixed. If users like the application, our team leader copies
files into production directories.

163. Can you start SQL Server's transaction from PB script?

You can write in PB script Execute Immediate "BEGIN TRAN".

164. When do you need to use the AcceptText() function ?

If the user changes data in the edit control of the datawindow
and after that clicks on some other control on the Window, this
changed value will stay in the edit control and will not go through the
validation process. This means that the data will not be placed in the
datawindow buffer.
The AcceptText() function will enforce the validation process from the
script.

165. If you need to write a script that will sort the data in a datawindow
when the user clicks on the column's header, how would you do this?

I'd use the function GetObjectAtPointer() to find out what column the user
clicked on and whether he clicked on the header or not. After that I'd use
functions SetSort() and Sort().

166. What is the BCP utility in Sybase for?

This is a Bulk Copy Program and it's used by Database Administrators
to copy big amounts of data between tables or databases.

167. What do you know about 3-tier architecture?

It's when you place business logic in a separate application. In this
Case your application consists of the front end, middle tier and back end parts.
Sometimes multi-tier applications are called Distributed Systems.

168. What are API calls and can you do API calls from PowerBuilder?

API (Application Program Interface) is a library of useful functions.
For example, you can use MS Windows API calls from PowerBuilder by declaring
external functions from such DLL's as User32, Kernel32 or GDI32.

169. Let's say you have two database tables Tab1 and Tab2. Tab1 has 3 rows and
Tab2 has one row with the following values:
Tab1: id Tab2: id
a b
b
c

Write an SQL statement to produce the result set which will exclude
the row with the b value. Use both tables.

Method 1. Select Tab1.id
from Tab1
where not exists
(select *
from Tab2
where Tab2.id = Tab1.id)

Method 2. Select id
from tab1
where id not in (select id
from tab2)

170. What would you do to improve performance of a PB application?

On the back end: 1. Create indexes where needed.
2. Use stored procedures instead of embedded SQL.
3. Try to minimize use of database cursors.