Wednesday, September 8, 2010

Bye Bye for SetRedraw( )

A common Classic PB code technique when modifying the displayed contents of window controls, is to turn off screen refresh by calling SetRedraw( false ), performing the update activity and then calling SetRedraw (true).

Setredraw( ) is one of those graphic object methods that falls by the wayside when migrating to .NET. It is no longer supported. Is this a bad thing that will give your users a migraine headache or a non-issue?

This post and the accompanying video will give you a refreshing understanding of the issue and help set your expectations when you migrate and see those unsupported feature warnings.

First some background:

In classic Windows UI and .NET WinForm, each window (and remember internally each control is a window) is constantly redrawing itself; when it's first created, when it's covered and re-exposed, and when something about its look and feel needs to change. When the application is told to paint itself (draw in PB terms), it works with an abstraction called a device context which is wrapped in a Graphics object. The context deals with the drawing surface as a field of colored dots which each of which it turns on in a color or off to match the request. The whole process is built upon the 1990's hardware and device driver technology that was current when MS Windows 3.x was designed Whew, I bet you're begin to get the feeling that constantly repainting a display during a dynamic refresh process is a lot of work for the application program!

Monday, August 30, 2010

Powerbuilder Interview question _ part 9

STRING FUNCTION

1. What does a string manipulation function Left( ) do?
Obtains a specified number of characters from the beginning of a string. Left(string, n)

2. What does a string manipulation function LeftTrim( ) do?
Removes spaces from the beginning of a string. LeftTrim(string)

3. What does a string manipulation function RightTrim( ) do?
Removes spaces from the end of a string. RightTrim(string)

4. What does a string manipulation function Mid( ) do?
Obtains a specified number of characters from a specified position in a string.
Mid(string, start{, length})

5. What does a string manipulation function Lower( ) do?
Converts all the characters in a string to lowercase. Lower(string)

6. What does a string manipulation function Right( ) do?
Obtains a specified number of characters from the end of a string.
Right( string )

7. What does a string manipulation function String( ) do?
Convert variables of other datatype into sting String ( data, { format } )

8. What does a string manipulation function Trim( ) do?
Removes leading and trailing spaces from a string Trim(string )

9. What is the function of SelectText( )?
Selects text in an editable control. We specify where the selection begins and how many characters to select: editname.SelectText ( start, length )

10. Write a function to find one string within another and return position of that sub-string?
String msg, putin
long NewPos, l_NewPos
msg = mle_text.text
putin = sle_find.text
NewPos = Pos(msg, putin, 1)
l_NewPos = Pos(msg, putin,NewPos)
SetFocus(mle_text)
mle_text.SelectText(l_NewPos,len(putin))

STRUCTURE
1. What is a Structure? What is Structure Data type?
The structure is a collection of one or more related variables of the same or different data type grouped under a single name. There are Global and Object level structure.

2. How do you use a Structure in your script ?
We define structures using the Structure painter. In scripts we must create instances of the structure in order to use them. When we refer to an individual variables within a structure, we need to prefix them with the name of the structure instance.

3. Which attribute of the Message Object is used to pass a structure between windows ?
PowerObjectParm attribute of the Message Object is used to pass a structure between windows as the parameter in the OpenSheetWithParm( ) function.

4. Show me how to send a structure to a function.
You can pass a structure as an argument in user-defined functions. Simply name the structure as the structure data type when defining the argument.

5. Can an array be an element of the Structure? Can a User Object be?
YES. An array and a User Object can be an aliment of the structure. Any object can be an element of the structure.
Example
S_struct s_str
for I = 1 to 10
s_str.array[i] ……..
next

6. How to reference structure variables in the script?
If the structure is Global, you have to declare the variables of this structure type and use dot notation to reference the structure variables. E.g.,
emp_data emp1
emp 1.emp_id =100
If the structure is object level and you want to use it in the script for the object itself declare a variable of this structure type and use dot notation. If you want to make this object level structure accessible outside the object: in the object that defines the structure, declare an instance variable of the structure type. In any script of the application reference a variable in the structure using syntax:
object .instance_variable .variable

7. Could you pass structure as argument in user defined function?
YES. We just have to name structure as a data type when we defining argument.

8. When do you use = operator?
It is used to compare one structure to another structure of the same type.

9. Can an array be an element of the Structure? Can User Object be?
Yes, an array can be in the structure, because it is also a variable that can hold multiple storage values. Unlike an array which has a single variable name and a range of numeric index values, a structure is composed of many variable names that are related in some way. These variables may be of different data types. How to process an array:
s_struct s_str.
For I = 1 to 10
s_struct.array[I] = …

Yes, a user object can be a part of a structure. How to process a user object:
s_str.ou_obj = Create u_obj
open s_structure.window

10. Can you assign a structure to a structure?
Yes, we can copy all elements of a structure to another structure with a single assignment statement. At first, we declare a structure as a class and then assign values of the first structure to another structure.

TARGET OBJECT

1. What are the Target Object events?
DragDrop
DragEnter
DragLeave
DragWithIn

2. What is a drag mode?
A Drag mode is the state of the object when the user has clicked a draggable control and is holding the left mouse button down.

3. In what case did you use Drag and Drop?
We want to create a trash can in our program to make it look more user friendly

4. What are automatic and manual drag modes?
In automatic drag mode, PowerBuilder automatically switches the application into drag mode when the control is clicked. or
DragAuto is a boolean indicating whether PowerBuilder puts the control into a drag mode automatically. DragAuto has these boolean values.
True – when the user clicks the control, PowerBuilder puts the control in drag mode.
When DragAuto is True, clicking a control triggers a DragDrop event, not a Clicked event.
False – when the user clicks the control, PowerBuilder does not put the control in drag mode; we have to call the Drag function to put the control manually into a drag mode.
With manual drag mode, PowerBuilder does not switch the application into drag mode when the control is clicked. We must control when the drag mode is initiated using command drag(Begin!), drag(Cancel!), drag(End!).

TRANSACTION OBJECT

1. When do you use AutoCommit?
1. If you set AUTO COMMIT to FALSE, all SQL commands are part of a transaction and you must issue COMMIT and ROLLBACK commands from time to time to complete your work.
If AUTO COMMIT = TRUE, there are no transactions and each SQL command is immediately committed. In this case explicit COMMIT and ROLLBACK commands in the script have no effect
2. When we need to create a temporary table.
3. When a window has a lot of display activity only.

2. What is a Transaction Object? What is the purpose of a transaction object?
A Transaction Object is an NVO which stores information required to connect to a specific DB.

3. Explain the difference between SetTrans and SetTransObject.
The SetTrans() function provides another way of managing the database connection. SetTrans, which sets transaction info in the internal transaction object for the DW control or DataStore, manages the connection automatically. You do not explicitly connect to the DB; the DataWindow connects and disconnects for each database transaction, which is less efficient but necessary in some situations.
SetTransObject causes a DW control or DataStore to use a programmer-specified transaction object.

USER OBJECT
1. What are Non-Visual User Objects?
Non-visual user object is an object that encapsulates attributes and functions but is not visible to the user. NVO is a collection of business user functions. They can be Custom Class and Standard Class. NVOs are useful because they allow you to encapsulate application operations into objects making them easier to develop, manage and maintain and also to encapsulate all security management for an application into a single object.

2. Explain the purpose of a non-visual user objects
The purpose of non-visual user objects is to define business rules and other processing (which are checked in different windows in your application) that act as a unit. We implement them in PowerBuilder using Class User Objects.

3. What types of a non-visual user object do you know?
There are two types of non-visual user objects :
Standard user object inherits its definition from one built-in, PB NVO such as the Trans-action object or Error object. We modify its definition to make the object specific to our application.
Custom user objects are objects of our own design that encapsulate properties and
functions but are not visible to the user.

Typically, we use Custom Class User Objects to define processing in our application that has no visual component. We typically use class user objects to define processing that acts as a unit; for example if our application calculates sales commissions, we might create a custom class UserObject to perform the commission calculation.

After we build and save a visual user object, we can use it as we use a PB control. We can place it in a window or in other user objects, and we can size it, change its style, and build scripts for its events.
After we build and save a class user object, we declare a variable of the UserObject type and create an inheritance of it using the CREATE statement in a script. Once we have instantiated the class UserObject, its attributes and functions are available within the scope of the variable declaration. After using the UserObject, we destroy it with the DESTROY statement to free up memory.
We can also build new user objects that inherit attributes, events, structures, and functions from an existing user object.

4. What are facilities of the User Objects ?
We define a component once, then reuse it many times as we need without any additional work.

5. Give me an example of Standard & Custom visual user objects you have created
and what was the purpose of each?
Standard – Any single control can be placed in a standard user object
Custom – Many controls can be placed in a custom user object

6. What does OpenUserObject( ) do?
OpenUserObject( ) adds a user object to the specified window and makes all its properties and controls available to scripts.
windowname.OpenUserObject ( userobjectvar, userobjecttype {, x, y } )
This function is used when you want dynamically, at runtime place some user objects on a window. This function displays this user object, makes all its attributes available to scripts. You must open a user object before you can access the attributes of the user object. If you access the user object’s attributes before you open it, it will cause an execution error.

7. How can you dynamically place an User Object on the window?
By using functions : OpenUserObject( ), OpenUserObjectWithParm( )

8. When would you use a Standard User Object?
We use a standard UserObject to extend the standard set of predefined attributes, events, and functions or to encapsulate common controls into an object that can be reused without any additional work.

9. Can a standard visual User Object contain another User Object? No.

10. In what situations would you use a custom visual UserObject?
We use a visual custom UserObject when we frequently group controls in a Window and use the controls to perform the same processing.

11. What Events does the Custom Class have?
Constructor and Destructor

12. What events are available to a standard class User Object?
Constructor and Destructor

13. What events are available to a standard visual User Object?
It has all events that standard PowerBuilder control has it.

14. How can you instantiate non-visual object (class) in memory?
First we need to declare a variable of our User Object data type. We have to create an instance of our object in memory using command CREATE. Now we have access to the attributes and functions encapsulated in our User Object. When we don’t need this object anymore, we should Destroy it before we close an application to avoid memory leaking.
First we need to declare a variable having our object’s type:
uo_business_rules my_object
CREATE my_object - This will create an instance of our object in memory.
When we don’t need this object anymore, you should Destroy it to avoid memory leaking:
DESROY my_object or
We need to create (instantiate) a variable of the class UserObject type when we use the class
We create custom transaction object MY_TRANS
Then you have to declare and create this UserObject
MY_TRANS l_my_trans
l_my_trans = create MY_TRANS
Now we have access to the attributes and functions encapsulated in the UserObject.
We have to destroy MY_TRANS before we close the application

15. Once a standard user object is placed on a window, can you override its attributes? Yes, we can.

16. How can you override User Object’s function?
Using polymorphism: In the User Object that we put on the window (a descendant of original UserObject) we can declare a function with the same name as the function in an ancestor we want to override.

17. How many custom user events can you define and what triggers them?
In PB 4 we could have not more then 75 custom event ID’s that we use for each object. In PB5 we may have as many as we need but with no mapping to PB custom events, however the events can work as a functions. We trigger them manually by using functions in our scripts. New syntax to trigger the custom event is
object.event ue_cancel( )
object.event post ue_cancel( )

18. How is a custom visual UserObject different from a standard UserObject?
A custom visual user object is an object that has several controls that work as a unit.
A standard visual user object inherits its definition from a standard control. We modify the definition to make the control specific to our applications.

19. What events do you know for custom visual user objects?
Constructor - occurs after window’s open event or when a user object is dynamically placed in the window
Destructor -occurs after window’s Close event or when a user object is dynamically removed from the window
DragDrop When a dragged object is dropped on the user object
DragEnter - when a user object enters the user object etc.
Drag within
Drag leave
Other
RightButtonDown

20. How can you communicate between the window and user object in the window?
When we placed user object into the window we can write the script s for User Object event (conctructor, dectructor) but we cannot write the scripts for controls inside the user object. But we can use user object level functions or user events.

VARIABLES
1. What is a variable?
Variables are storage locations that hold values. The values can be entered by user, retrieved from a Database, calculated by the application. Variables must be declared before they are referenced in a script.

2. What does the scope determine?
The scope of a variable determines which scripts can access the variable to use or change its value. The scope of a variable determines the scripts in which the variable can be referenced.

3. What types of variables do you know?
Local—defined within a script for a control and can be accessed only within that script.
Instance—defined for an object (e.g., window or User Object) and can be accessed only with in an object.
Shared—defined for an object. All descendants of that object can access the variable (i.e. there is one variable with one value for the object and all of its descendants). Shared has a scope very similar of the instance but it also will be known for all instances of the object. Shared variable retains its value when instances of the object are closed and opened again. If the value of the shared variable is changed in one instance and then accessed in another, the second instance gets the new value.
Global variables can be accessed by any object and are known through the scope.

4. What is a local variable?
A local variable is declared in the script for a control or object that can only be referenced in that script. We use local variables when the value only needs to be known while the particular script is executing.

5. When do you use reserve word PARENT?
We can use it in the script for different events of controls. In this case it refers to window this control belongs to. If we use it in a script for a control in a custom user object, it refers to the user object. When we use it in a script for MenuItem it refers to the MenuItem on the level above.

6. What does the pronoun THIS refer to.?
It refers to the object or control we are writing script for.

7. What does the pronoun PARENT WINDOW refer to?
The pronoun PARENT WINDOW refer to the window that menu is associated with at the execution time.

8. When do you use instance variable?
You use an instance variable when you have data that have to be accessible in more then one script within the object, but don’t need to be global.

9. What access level for instance variable do you know?
PUBLIC - accessible for all scripts
PRIVATE - accessible in the scripts for events in the object for which the variable was declared
PROTECTED - accessible in the scripts for events in the object for which the variable was declared and its descendants.

10. What the difference between instance and shared variable?
An instance variable is initialized when the object is opened. When you close the object, it ceases to exist. When you open the object again, the instance variable is initialized again. But the Shared variable continues to exist when you close the object and if you open the object again the shared variable will have the value it had when you closed the object.

11. What does the qualified reference mean?
A qualified reference is a variable (or object) name made specific by prefixing a window or object name to the variable name. We qualify references to refer to a variable in a child within its parent. E.g.,
w_customers.sle_address
Qualifying variables or column name in the SQL statement also eliminate ambiguity.

12. What is a shared variable? An Instance variable? How do you use them?
A shared variable is a variable that belongs to an object definition and exists across all instances of the object. Shares variables retain their value when an object is closed and opened again. Shared variables are always private. They are accessible only in scripts for the object and for controls associated with the object. Shared variables can belong to the application object, a window, a user object, or a menu.
An instance variable is a variable that belongs to an object and is associated with an instance of that object (you can think of it as a property of the object). Instance variables have access keywords that determine whether scripts of other objects can access them. Instance variables can belong to the application object, a window, a user object, or a menu. (Difference: they have different scope. The value of the shared variable is the same in each instance of the object. Instance variables are associated with one instance of an object such as a window and each instance can have different values in the instance variable.)

1. What is Check-in, Check-out options on a Library Painter?
It is a Version control.
Check- in, Check-out are options on a Library painter, which control an access to Library entries. For example, when more then one programmer are working on a project, we want to prevent them from modifying a library entry at the same time. To control the access to library entries we can use Check-in and Check-out. When you Check-out an entry, PB makes a copy of the entry, stores it in a specified library( a test or development library), and sets the status of the entry to check out. As long as the status of an entry is checked out, we can change only to the working copy. If we try to open the original copy, PB displays a warning message. When we finish working with an entry that you checked out , we can Check-in entries on to replace the entry in the original library with the working copy of the entry. Check-in clears the check-out status from the entry, and delete the working copy from the library in which it is saved.

2. What is an access control system for developers?
Check-in, Check-out.

3. Did you use any Version Control systems such as PVCS?
No, we have used PowerBuilder’s own Check-in/Check-out feature in the Library painter.

WINDOW OBJECT

1. For the following window types, describe typical uses or provide examples of how they are used in commercially available products:
Main is initial window of an application.
Characteristics: operates independently of all other windows.
Child is subordinated to its parent window.
Characteristics: can exist only within parent; thus, automatically closed when its parent window is closed;
is never the active window;
appears as an icon within the parent window when it is minimised.
cropped when a user attempts to move it beyond its parent;
moves with its parent, since its position is always relative to the parent;
Uses: the main window for a customer displays information in textual form and a child window displays it graphically.
Response is application modal
Characteristics: cannot be minimized;
the user cannot go to other windows in the same application;
the user cannot go to other Windows applications;
remains the active window until the user responds.
Uses: displaying error or warning messages to the user;
requesting a critical item of information from the user before work can begin
(Select Window in the Window painter)
Popup is
Characteristics: has a parent window;
is never hidden behind its parent;
can display outside the parent window;
appears as an icon outside the parent window, at the bottom of the screen, when minimized;
minimizes within its parent, when its parent is minimized.
Uses: online help.
MDI is a style of application used to manage multiple documents of the same or different types within one.
Characteristics: Each of the document types in its own window, but all of the windows appear framed within a foundation window for the application.
MDI with Microhelp has the same behaviour as an MDI frame window without Microhelp, but it also permits the display of microhelp information in the status bar.
Characteristics: provides the Microhelp facility to deal with the limited number of words allowed in a Menu. In the status bar at the bottom of the frame we can display additional information about a MenuItem to help the user to understand what the MenuItem does.

2. What are the Types of Windows and how is each one used?
Main is used as a top-level, foundation window( or as a sheet in an MDI application)
Child is used as a subordinate window.
Pop-up is used to provide subordinate information and it is never covered by the parent window.
Response is used to obtain information from and provide information to the user.
MDI and MDI with microhelp is a window frame in which users can open multiple document windows (sheets).

3. What is a control?
Controls are objects that we place in a Window to allow the user to interact with the Application.


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

STORED PROCEDURE questions

1. What is the stored procedure (SP)?
Stored procedures are blocks of SQL code which have been compiled and stored on the server. It may be called from client processes or within triggers.
Or
A Stored Procedure is a set of precompiled and preoptimized SQL statements that performs some database operation. Stored Procedures reside where the DataBase resides and you can access them as needed.

2. Why do we need Stored Procedures?
Stored Procedures make our process faster. One time we compile select and then just use it. They give us completely controls how information is stored and retrieved. Security reason. Defined business rule and Maintenance
or
the benefits of using stored Procedures :
n executes much faster, because the path is not created each time of execution of Stored Procedure
n can be shared between multiple users;
n it is much easier to manage them when they stored in DB, then embedded SQL.
or
Use stored procedures to improve:
security
consistency
data integrity
response time

2a. What benefits of Stored Procedures ?
n run faster than the same commands executed interactively as a batch
n reduce network traffic
n enforce consistency throughout the database
n help provide security
n reduce operator error

3. How do you declare a Cursor for the Stored Procedure?
Use Declare cursor statement:
DECLARE emp_pro procedure for GetName
@emp_name = :EditEmpName,:@emp_salary = 5000;

4. How to use Stored Procedure from another one?
Execute by name.

5. What is allowed in Stored Procedure:
n create view
n create default
n create trigger
n create procedure
n create table ? Nothing

6. What is a Remote Procedure?
Remote Procedure is a stored procedure which exists on a different server than the one you are currently connected to. Remote Procedure is when you execute procedures on another SQL server from your local SQL server. Once both servers are properly configured by System Administrator, System administrator does:
1.Both servers must be configured for remote Access
2.Our server must have the remote server in its list of known servers (sp_addserver)
3.The remote server name must be in the interfaces file
4.We Must have added appropriate remote login on the system being accessed
We can execute any procedure on the remote SQL Server using the server name as part of the identifier.
Exec.servername.dbo.procedurename
We can pass value as parameters to a remote procedure from the SQL batch or procedure that contains the EXECUTE statement for the remote procedure. The Result will appear on our local terminal.

7. What are : @@rowcount, @@error?
@@rowcount is a Global variable which stores the number of rows affected by the most recent data modification operation. It is used to check how many rows were affected by a previous @@error a section of code may be executed based on the value returned by error code.

8. What happens if you rename the object referenced by a Stored Procedure?
We must drop and recreate a procedure if we rename any of the objects it references. A stored procedure that references a table or view whose name has been changed may seem to work fine until SQLServer recompiles it. Recompilation takes place for many reasons and without notification to the user.

9. What is sp_depends?
sp_depends is system a procedure that allows us to get a report (list) of the objects referenced by a procedure. We usually use it before when we need to drop or rename object.

10. How do we create a Stored Procedure? Delete it?
Create procedure “name”
@- specify parameters (variable)(if we need any)
as
select . . . .
If we use more than one SQL statement we have to use begin and end keyword
delete storedprocedure - Drop procedure_name

11. Name some of the Sybase system Stored Procedures you made use of. What did you use them for?
The master DB contains useful stored procedures called “system procedures” that access the system tables. System procedures begin with “sp_” and can be run from any DB. The master DB contains a library of useful stored procedures which access the system tables.
Sp_help - prints all database objects and data types
sp_rename renames an object
sp_helpindex indexes information on the table
sp_depends displays dependency information
sp_who displays currently logged on users
sp_helptext prints text of store procedure, trigger, view, default of rule
sp_lock displays information about locks
sp_adduser sp_dropgroup
sp_addgroup sp_droplogin
sp_addlogin sp_dropuser
sp_changegroup sp_lockloginsp
sp_help sp_password
sp_helpdb sp_user_id
sp_helpgroup sp_user_name
sp_helpuser

12. How can you find out who you are?
sp_who
sp_who_am_i

13. Let’s say you have Stored Procedure B that is being called from Stored 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 a nested Stored Procedure changes are committed only after successful commit in the outermost Stored Procedure. You will not be allowed to put commit statement in nested stored procedure on syntax level.

14. How can you return user-defined error message from a Stored Procedure?
By using SQL function RaiseError( ). The number of errors must be greater than 2000. You should not use transaction statements in triggers. A trigger is a special kind of stored procedure that takes effect when you issue a statement such as INSERT, DELETE, or UPDATE on a specified table or column. Triggers can be used to enforce referential integrity. For example, assume that a certain condition within a trigger is not met and you want to execute a ROLLBACK. Instead of coding the ROLLBACK directly in the trigger, you should use RaisError and test for that particular return code in the DBMS-specific return code (SQLDBCode) property within the referenced transaction object.

15. What utility do you use to create and place Stored Procedure into Sybase DB?
Usually, I use DBA painter. But sometimes I use LAN Workplace and ISQL utility.
You can use ISQL (DOS), WISQL, SQL Object Manager, etc.

16. How can you use a Stored Procedure in PowerBuilder?
We can use them as a Data Source for the DataWindows or we just execute them using EXEC command in PowerBuilder. If we just execute it and the Stored Procedure returns result-set, we have to:
1. Declare variable for a Stored Procedure,for example,
Declare emp_pro Procedure for
pr_GetName @emp_name = :EditEmpName,
@emp_salary = 500
2. Execute (e.g. execute emp_pro)
3. Fetch in a Loop
4. Close Procedure.(close emp_pro) (optional)

17. Why do you use a Stored procedure instead of interactive SQL?
Stored procedures work faster than the same SQL commands executed interactively, because first time a SP is run it is optimized, compiled and stored in cache memory.

18. How do you call stored procedures from the script?
By name: execute sp_name;

19. How do you pass an argument from your script into a stored procedure?
Declare variable with @ sign.
Example
DECLARE Emp_proc procedure for GetName
@emp_name = :Emp_name_var,
@emp_salary = :Emp_sal_var ;
execute Emp_proc;

20. What system Stored procedures do you use working with SP?
SP_helptext - to view the text of store procedure
SP_rename - to rename a store procedure

21. How can be specify parameters for SP?
The parameters can be specified by position or by name.

22. What is a reason to use the default values for parameters in SP?
If we defined default value for parameters in SP we can check if user provided values for parameters when the executed SP.

23. How can we use SP in PB
We can execute SP from the script or use it as a source for DW object.

24. How can we pass value from SP to the user?
We have to define parameters in SP with keyword OUTPUT

25. What statement do you use if you want to execute SP from the script?
DECLARE - to declare SP
EXECUTE - TO EXECUTE SP
FETCH - TO FETCH the result set into the variable (usually in the LOOP)
CLOSE - is necessary if SP returns the RESULT SET

26. If SP is a source for DW object how do you retrieve data?
With PowerScript RETRIEVE() and if SP has parameters we have to provide these parameters in RETRIEVE()

27. How do you call stored procedures from the script?
Declare the PROCEDURE variable. The EXECUTE statement calls the procedure. The FETCH statement returns the result row, copying its values into two variables. SP is an SQL code that resides in the DB rather than in a client application and is executed by the DB server rather than a client workstation. First, call the procedure to generate the result set. Then issue FETCH commands in a loop to retrieve the results one row at a time. The last thing you do—CLOSE the procedure.

SQL Server Fundamentals.

1. What is SQLCA, SQLSA, SQLDA?
They are built-in global objects that are predefined in all applications.
SQLCA - default PB Transaction Object, SQL communication area. Used to communicate with our Database.
SQLSA is Dynamic Staging Area. We use it when we need to use a Cursor dynamically.
SQLDA is Dynamic Description Area, used in dynamic SQL.

2. When and why do we need dynamic SQL?
Dynamic SQL is a structure of SQL statements which can be changed dynamically in your
program. Used when at design time it is undetermined what the user is going to input (ad hoc Query).

3. How many formats of Dynamic SQL do you know and what are they?
1. InPut, NoResultSet
2. InPut + ResultSet (through a dynamic Cursor or a dynamic Stored Procedure)
3. InPut and ResultSet unknown until runtime.

Consider the following code (it is the basis for Questions 1 - 8 ) :
CREATE table my_table( a int not NULL, b int default 10 NULL)
GO
CREATE rule b_rule as b_val between 0 and 100
GO
sp_bindrule b_rule, “my_table.b”
GO
CREATE view sample_view as SELECT * from my_table
GO
alter table my_table ADD c varchar(20) NULL
GO
alter table my_table ADD constraint b_check
check(b between -10 and 10)
GO.

4. What value will be inserted into the table for column b by the following insert statement : INSERT sample_tab(a, b) values (1, NULL)
Explicitly providing NULL is essentially a value (no value) and the default will not be used.

5. If there were 50 rows already in the table when the default was bound to column b and 20 of the rows had no values for column b, what value will those rows have for column b after the default is bound?
Defaults, rules apply only to future rows added to the table. Existing rows are unaffected.

6. If a file containing 20 rows is bulk copied into a table and 5 of the rows do not have values for column b, what value will those rows have for column b after the bcp is completed?
10 Defaults are recognized during bcp.

7. Will file containing rows that have negative values for column b be added during bulk copying?
Yes. Rules, triggers and constrains are not recognized during bulk copy operations.

8. What columns will the user see if he runs the following SELECT statement :
SELECT * from sample_view.
Columns a and b. Although the view executes a select *, * is expanded during view compilation. Column c does not exist at this point.

9. What values are allowed in column b for inserts/updates?
0 to 10. The rule is checked first (between 0 and 100), then the constraint will be checked (-10 to 10). The valid range is the intersection.

10. What methods can be used to enforce a business rule requiring a value in column b to be greater than a value in column a for an insert/update?
A table-level constraint or an insert/update trigger.

11. What command would you use to change the default value in column b to 5?
Alter my_table replace b default 5.

12. What system table contains information about objects, such as tables, defaults, and triggers within a Database?
Sysobjects.

13. How many pages are allocated when a table is created?
An extent of 8 pages.

14. How is clustered index different from a non-clustered index?
Clustered indexes dictate the physical order of data. The leaf level of the clustered index is the data. A non-clustered index has a row in the leaf level of the index for every row in the table.

15. Can a clustered index be placed on a separate device from a table?
No.

16. Can a column be deleted from a table?
No. (Not officially, although a student once asked me this question. I answered, with authority “Never”. “I just did it!” he said. There is an undocumented System 10 feature : alter table t1 drop c1 from table t1. The implementation is interesting : the column is undefined, but all the data remains untouched until the rows are modified. As each row is modified, the column is nullified.

17. What command allows you to add columns to a table?
Alter table

18. How many columns with the identity property can be created in a table?
One or Zero.

19. What system datatype must be used for identity columns?
Numeric, with a definition of (n, 0) where n is the number of digits.

20. What global variable can you query to determine the last identity value inserted into a table?
@@identity

21. Explain the difference between char and varchar datatypes?
Char is fixed-length datatype whose trailing spaces are stored. Varchar is a variable-length datatype. Nullable char is stored like varchar because it is variable length – either no length or the declared length.

22. How is the string “Bob” in a char (10) not-null column stored differently than the same string in a varchar(10) not-null column?
This string would store 10 bytes in the char field (the string is padded with spaces) and 3 bytes in the varchar field (plus 1 byte overhead).

23. Define what is meant by NULL?
The absence of a value.

24. How are null values handled by aggregate functions?
NULLs are not considered in aggregates.

25. What Sybase function can substitute a value for NULL in a query?
IsNull (expression, value)

26. What is the restriction on updating base tables through a view, if the view is defined on multiple tables?
The columns being updated must exist in only one of the tables.

27. What is the restriction on inserting data into a base table through a view if the view contains only a subset of the columns in the base table?
The columns that are not part of the view must be Nullable or have a default.


28. What is the restriction on deleting rows through a multitable view?
Deletes are not allowed in multitable views.

29. What is the maximum triggers that can be created on a table?
Three triggers : insert, update, and delete triggers. (Note that there is a column called seltrig in Sysobjects, but it is not currently used.)

30. If update on table_A fires a trigger that updates table_B, and table_A has an update trigger defined on it, will the trigger on table_B be executed?
Yes, in the default server configuration. If the nested triggers configuration value is set to 0, the trigger on table_B would not fire.

31. If an update of a row in table_A fires a trigger that updates another row in table_A, will the trigger on table_A execute again?
No, by default. If nesting is allowed and set self_recursion on has been executed, the trigger would fire.

32. What are the names of the tables that are accessible only from within a trigger when a trigger is executed?
Inserted and deleted.

33. What is the structure of the tables referred to in the previous question?
The exact structure of the table on which the trigger is created.

34. Where are tables mentioned in the previous two questions located?
In memory. (Alternative answer : They actually are special views of syslogs)

35. When is the Update(column_name) function used, and when it is true?
Update(column_name) is used in a trigger to determine whether the value of a column has been modified. In an insert trigger, the function is true if a non-null value was inserted; in an update trigger, the function is true if a column was named in the update statement set clause.

36. An insert trigger is created on a table that validates whether one of its column values exists in another table before allowing the insert to succeed. A file that contains invalid values for that column is bulk copied into the table. Will the rows be inserted by the bulk copy?
Yes, bcp bypasses triggers, rules and constraints (but not defaults)

37. An update trigger exists on the titles table which contains 1,000,000 rows.
If you issue the following statement, how often does the trigger fire?
UPDATE titles
set price = price + $2
where type = “mod_cook”
One time. A trigger fires once per modification statement whether the statement modifies zero, one or many rows in a table.

38. If a table is created with a unique constraint on a column, will that column
allow NULL values?
Yes, but only once.

39. What if the column is created with a primary key constraint?
No. The primary key constraint requires the column to be declared as not NULL.

40. If a view is created on table “my_table” and that table is renamed to “new_table” will the view still work?
Yes. During compilation, the server converts an object name to an object ID. The rename changes only the name.

41. If a column is added to a table after a view has been defined on the table, what must be done to make the column accessible through the view?
Drop and re-create the view.

42. Under which of the following circumstances will a stored procedure be automatically recompiled:
a. Creating an index on a table referenced in the stored procedure?
b. Dropping an index on a table referenced in the stored procedure which is being used to satisfy the query?
c. Renaming a table referenced in the stored procedure?
d. Using the same table to drop and re-create a table referenced in a stored procedure?
b and d.

43. What three methods can be used to force a stored procedure to recompile a new query plan?
The following three methods :
sp_recompile table_referenced_in_proc
CREATE procedure proc_name ……. with recompile
EXEC proc_name with recompile

44. What system table contains information on system and user-defined datatypes?
Systypes

45. What stored procedure lists database user-defined types?
Sp_help

46. Describe how the @@trancount global variable is incremented and decremented.
Each begin tran(tran_name) increments by 1, commit tran decrements by one, and rollback tran rolls the transaction back and returns the value zero.

47. What type of the lock is held on a page during a select? A shared lock.

48. What type of the lock is held on a page during an insert? An exclusive lock.

49. How many page locks must be accessed by a single data modification statement before upgraded to a table lock? 200.

50. What global variable contains the number of rows affected by your last executed SQL statement? Which SQL statements affect that variable?
@@rowcount; all SQL statements except declare change the value of @@rowcount (many change its value to zero).

51. What is difference between server cursors and language cursors?
A server cursor is declared and processed in a stored procedure.
A language cursor is declared and executed using SQL, but not Open Client calls.

52. What is the proper sequence of statements to use and process a cursor?
The following sequence of statements :
DECLARE cursor_name cursor
OPEN cursor_name
FETCH cursor_name ….
CLOSE cursor_name
DEALLOCATE cursor cursor_name

53. What statement is used to delete the currently fetched row in an updatable cursor?
DELETE table_name where current of cursor_name

54. What type of lock does an updatable cursor obtain by default as it fetches rows? What type of lock does a read-only cursor obtain?
An updatable cursor obtains update locks. A read-only cursor obtains shared locks.

55. What is the difference between the following two select statements?
SELECT title, type, price from titles
ORDER BY type
COMPUTE avg(price) by type;

SELECT title, type, price from titles
ORDER BY type
COMPUTE avg(price);
The first select statement will report the average price for a type after listing its detail rows. The second select statement returns all detail rows followed by the average price for all non-null prices.

56. Assume the following five users have each executed the following set of statements in different Databases (assume all users are not running in chained mode) :
User1: begin tran User2: begin tran
delete my_tab delete my_tab
commit tran checkpoint
go go

User3: begin tran User4: begin tran
delete my_tab delete my_tab
commit tran go
go
checkpoint
go

User5: delete my_tab
go
If the SQL Server were to crash at this point, which of these transactions would be rolled back during recovery? Which would be rolled forward? Which would require no recovery?
Assuming an internal checkpoint was not activated by the server immediately prior to the crash, User1 and User5 are rolled forward; User2 is rolled back; User3 and User4 do not require recovery.

57. If a user executes the following set of statements, what rows will be displayed by a SELECT * from sample_tab statement?
CREATE table test_table (a int)
go
begin tran my_tran
INSERT sample_tab (8) values (1)
save tran my_savept
INSERT sample_tab (a) values (2)
rollback tran my_savept
INSERT sample_tab(a) values (3)
commit tran

The following will be displayed :
a
..
1
3
58. What global variable can you check to determine the current status of a transaction?
@@transtate

59. Describe a deadlock scenario.
User1 locks page A, User2 locks page B. User1 requests page B(process will be blocked waiting for the lock on page B to be released). If User2 requests page A, a deadlock has occurred.

60. What SQL statement is notorious for causing deadlock situations?
SELECT ….. holdlock

61. What steps can you take to avoid deadlocks?
You can access tables in the same order, decrease the length of transactions, avoid the use of holdlock, and spread data across data pages.

62. What is the definition of a transaction?
A transaction is a logical unit of work.

63. Describe the differences between chained transaction mode and unchained
transaction mode. When does a transaction begin and end in chained mode?
In unchained mode?
In chained mode, the server begins a transaction before the first SQL statement(insert, update, delete, or select) Only commit transaction must be specified. In unchained mode, begin and commit tran statements are required to define a logical unit of work; each SQL statement includes an implied begin tran and commit tran statement.

64. What global variable can you query to determine whether you are running in chained or unchained transaction mode?
@@tranchained

65. Based on following SQL statements : begin tran my_tran
print “Updating titles”
UPDATE titles set price = price * $1.25
if (SELECT avg(price) from titles ) > $50
print “Avg price exceeds $50”
rollback tran my_tran
Print “Update rolled back”
return
commit tran
print “Update successful”
go
a) What will be displayed if the average price after update is greater than $50?
“Updating titles”, “Avg price exceeds $50”, and “Update rolled back”.
b)What will be displayed if the average price after update is less than $50?
“Updating titles”, and “Update rolled back”.

66. What changes would you recommend for the code in Question 63?
Add begin after the if statement, and add end after the return statement.

67. How are local variables defined in SQL? How are global variables defined?
Local variables are defined by declare # @variable_name datatype.
Global variables are defined by system.

68. How are values assigned to local variables?
The select statement (SELECT @var_name = value)

69. How are values assigned to global variables?
Values for global variable are read-only : only the server may update globals.

70. What is the definition of local variables?
Local variables exist for the duration of a stored procedure or for the duration of a batch.

71. What are the two ways to change the displayed column heading for a select statement?
The two ways are as follows :
SELECT “Heading” = col_name ……
SELECT col_name Heading …….

72. What statement (other than CREATE TABLE) can be used to create a table in SQL Server?
SELECT into

73. What will be the effect using the following statement :
SELECT * into new_titles from titles where 1= 2
A new table will be created having the same structure with no rows

74. What data values would the following WHERE clause match :
WHERE name like “%[Cc]omputer”
Any string that ends in the word Computer or computer.

75. What function is used to display the value of a datetime column in the format dd/mm/yyyy?
Convert.

76. What security requirements must be met before a table can be referenced by a foreign key reference constraint?
Either both tables must be owned by the same user or the references permission must be granted.

77. Consider the following table definition :
CREATE table titles (title_id char(8) not NULL,
title varchar (68) not null,
pub_id char (4) not null
references publishers (pub_id)
total_sales int null,
pub_date datetime not null)
What happens if you attempt to delete a row from publishers, where there is an existing title in the titles table for that pub_id?
The delete fails, but processing continues. You must check @@error and rollback manually.

78. Considering the code in Question 77, what if you attempt to update a pub_id for a publisher who has related rows in the titles table?
The update fails.

79. Considering the code in Question 77, what if you attempt to insert a new publisher table? The row is inserted.

80. Considering the code in question 77, what happens if you attempt to insert a row into the titles table? It will check the pub_id value to see if it exists in the publishers table. If not, the insert fails.

81. Considering the code in Question 77, what happens if you update a pub_id in the titles table?
It checks to see if the new value is in the publisher table. If not, the update fails.

82. Considering the code in Question 75, what if you delete a row the titles table?
The row is deleted.

83. Describe the difference between a table-level and a column-level constraint.
A constraint is considered table level if it is defined after the column definitions in the CREATE table statement or is defined using alter table.

84. When must you define a constraint as a table-level constraint?
When the constraint references two different columns.

85. What value will be displayed for @var when the following statements are executed:
CREATE proc my_proc (@parm1 int output)
as
SELECT @parm1 = @parm1 + 50
go

DECLARE @var int
SELECT @var = 50
EXEC my_proc @var output
SELECT @var
go
100.

86. What data values can be returned by the return statement from within a stored procedure? Any integer value.

87. What data values are reserved for use by Sybase?
-1 through -99

88. What statement is used to generate system -like error messages?
RaisError

89. What is the minimum error number that can be used?
20 001

90. If a rule is bound to a user-defined datatype, what happens to any existing columns that are defined with that user-defined datatype?
The rule will also be bound to those columns.

91. What if those columns in the previous question already had a rule that was bound explicitly to the column?
An explicit bind to a column overrides a bind to a datatype. The bind to the datatype would have no effect.

92. If a column has a rule bound to it and you attempt to bind another rule to that column, what happens?
The new rule will replace the old one (the existing rule does not have to be unbound).

93. How are database objects fully qualified?
dbname.username.objectname

94. What is the purpose of the syskeys table?
The syskeys table contains a row for every primary, foreign or common key in a Database.(These rows are added through sp_primarykey, sp_foreignkey, or sp_coomonkey). Keys are stored for documentation and reference only; the server does not enforce keys as part of referential integrity. Use constraints instead. SQL Server Administration.

95. What command do use to configure the SQL Server?
sp_configure

96. What command do you use to start SQL Server?
StartServer

97. How are remote procedures invoked?
By fully qualifying the procedure name to include the server name .The format is
servername.dbname.owner.procedure_name.

98. What needs to be set up or configured on the local server to implement remote procedure calls?
Remote access needs to be installed and the names of the local and remote servers needs to be added with sp_addserver

99. What needs to be set up or configured on the remote server to implement remote procedure calls?
Remote access needs to be installed; a remote login method needs to be selected and remote logins added; and the names of the local and remote servers need to be added with sp_addserver

100. What is normalization? What do you mean by Under- and Overnormalization?
Normalization means that you eliminated redundancy. Normalization is a theory of a logical Database design which refer to minimum data redundancy and maximum data integrity.
1. Eliminate arrays.
2. Eliminate repeating data across the rows.
3. Make sure all columns within a row depend on the primary key column.
4. Eliminate situation when two or more columns have mutual exclusive values.
Undernormalization contains a lot of redundant data
Overnormalization contains a lot of small tables which call a lot of joins.

101. Why its not such a good idea to use joining more then certain number of tables?
Because joining more then five tables in Sybase database will decrease performance.

102. What does OPEN CURSOR mean?
OPEN CURSOR causes the SELECT specified when the cursor was declared to be executed. The USING Transaction Object clause is not allowed with OPEN; the transaction object was specified in the statement that declared the cursor.

103. What is the purpose of Fetch statement?
We use Fetch to retrieve data from the buffer, line by line, and place it into a variable.

powerbuilder interview question _ part 8

POLYMORPHISM

1. What is polymorphism ?
Polymorphism is functions with the same name that can do different things for different objects and give different results . For example, 1 + 1 = 2 mathematics plus
“John” + “Smith” Concatenation plus
Polymorphism is a feature of an object-oriented language which defines same methods for different objects. Example: Let’s say, you are going to have several windows in your program, all of them with DataWindows and Retrieve buttons. We want to be generic and put code for the retrieve event in the function of the ancestor window. But some of these DataWindows are with external data source and have to be handled differently than the rest. Our solution is to create a generic function for retrieve in the window-ancestor and for the window with special handling create their own functions with the same name as in the ancestor. The function in a descendant will overload the ancestor’s function. We can define a Print function for a different kinds of objects. Each has its own definition of what print does.

2. How do you understand using Overloading functions in Polymorphism?
Overloading is functions with the same names with different parameters that can be used in the same object. Away in which polymorphism works in PowerBuilder. Means that when several functions the same name exist in ancestors and descendants call to another (with different function body) will overload (change) function’s body in the memory.

3. What is overloading?
When the function of the object has the same name but different arguments.

4. What is polymorphism?
Polymorphism is when functions have the same name but behavior varies with the type of the object to which it is applied.

5. Give examples of polymorphic functions in PowerBuilder.
For example, 1 +1 = 2 mathematics plus
“John” + “Smith” Concatenation plus
Another example: When you implement polymorphism, it’s common to create an ancestor object that has a method in it but no code. E.g., suppose, that you have a custom class called u_Invoice and one called U-check. They are both descendants of the custom class called u_Document. U_Document has a Print method but it has no code in it. Both u_invoice and u_Check also have a Print method, each with different code. Why create this structure? Because then an application that uses your objects can refer to any document using variables of type u_Document. And when they want to print a document, they can just call the Print method.
Uo_document doc

//NextDocument() may return a u_Invoice or u_Check document
doc = NextDocument()
doc.Print()
This is called dynamic binding. The specific function you are calling isn’t actually resolved until runtime. It just depends on whether the variable doc is holding a u_Invoice or u_Check object.

6. What is polymorphism?
Polymorphism is an object-oriented feature. Different object classes can have functions with the same name which can be used more than once to do different things for different objects. There are situations in which you want to send the same kind of message to different kinds of objects and have each object respond to that message in its own unique way.


1. What is Regenerate option in the Library painter? When is it most useful?
It compiles all codes of an object(s). Regeneration is important during the development of our PB application. Regeneration is useful:
n when we have an error message with no reason for it;
n When we upgrade to a new version of PB, the new version should regenerate all its source code to update it;
n When we make a change to an ancestor window that has many descendants, the changes can be immediately rippled throughout all child window through regeneration.
Regenerating could be called “recompiling”. When you regenerate an entry, PowerBuilder recompiles the source form stored in the library and replaces the existing compiled form with the recompiled form.

2. You are working with inherited windows, and your application does not work or produces ambiguous errors. You are tracing the program though the DEBUGGER, and still - nothing. What can you do next? REGENERATE



SCRIPT

1. How is a script associated with an object ?
When an event occurs, PowerBuilder executes the script for that event.

2. With which objects can a script be associated ? Any.

3. Code the broad outlines of the code necessary to check the value in the integer variable Count against the following ranges of values :
n less than or equal to zero(0)
n equal to 1, 2, or 3
n between 4 and 10 (including 4 and 10)
n over 10.

4. What is a script ?
A script defines the processing that take place when an event occurs for an object or control. When an event occurs, PowerBuilder executes the script for that event. Types of statements in a script are : variable declaration
calls to function
assignment statement
flow of control statement (IF and CHOOSE statements)
embedded SQL statement.

5. Under what circumstances do you need to declare multiple instances of a window class?

6. Assume that when the instance w_cust_maint1 of the window w_cust_maint is displayed, you want its title to read “Referenceable Customer”. Given the following declaration, code the statements to display the window appropriately.
w_cust_maint w_cust_maint1
w_cust_maint1.Title = “Referenceable Customer”

6. What is a focus ?
A focus identifies where on the Window the next action will take place.

7. How do you pass an array as an argument?
To pass an array we should define it as an argument in the window function with brackets. In a script we have to assign an array to a veritable in order to use it.

8. What is an array?
An array is an indexed massive of variables of the same Data Type.
EX. Prise[ ] or Prise[50]
9. Why are static arrays preferable over dynamic arrays?
Static arrays tell us how many elements an array will have by specifying number in brackets. Static arrays are faster.
Dynamic arrays grow dynamically what is more convenient .
Because the memory needed for a static array is located immediately on creation, so there is no reason to reallocate and copy anything and also because boundaries of the array are known when the array is accessed, so there is no processing overhead while checking the validity of the array subscript.

10. What is Array Data type?
Array is a massive of variables of the same Data Type

11. You need to process an array in a loop. Which function should you use if you don’t know array’s size (i.e., number of array elements)?
UPPERBOUND(Array)
int i
FOR i =1 TO UPPERBOUND(Array)
statementblock
NEXT

12. What is enumerated data type? How is it represented and what is its purpose ?
Some functions which were written in some other languages and build into PowerBuilder and PowerBuilder recognises them by the exclamation point at the end.

13. Can you always use CHOOSE…..CASE instead of IF-THEN-ELSE ?
Yes. We can. CHOOSE…..CASE More readable

14. What is the difference between DO… WHILE and DO… UNTIL clauses?
DO… WHILE will perform an action only while the condition is TRUE:
int A = 1, B = 1
DO WHILE condition DO WHILE A <= 15
statementblock BEEP(A)
LOOP A = (A + 1) * B
LOOP
DO… UNTIL will perform an action only until the condition is TRUE:
DO UNTIL condition int A = 1, B = 1
statementblock DO UNTIL A > 15
LOOP BEEP(A)
A = (A + 1) * B
LOOP

15. What is the difference between DO WHILE ….LOOP and DO….LOOP WHILE ?
DO WHILE ….LOOP performs an action only while the condition is TRUE:
DO WHILE condition
statementblock
LOOP
DO….LOOP WHILE performs an action at least once and repeat while the condition is TRUE
DO
statementblock
LOOP WHILE condition

16. What do you use to exit the loop before the condition in the loop is met?
What are the 2 choices?
We use EXIT inside the loop if we want to terminate the loop. EXIT jumps to the first
statement after the loop. Continue skips the rest of the statement inside the loop and returns to the beginning of loop to start the next iteration.

17. What is ANY Data Type ? How is it used?
Any is a chameleon data type — it takes the data type of the value assigned to it.

18. What is a BLOB data type?
BLOB is Unbound data type that stands for binary large object. Usually used to hold pictures, window’s wave sound file or very large text files.
19. How would you display a toolbar on a left side of screen, floating?
Syntax:
window_name .ToolbarAlignament = AlignLeft! (AlignAtTop!,
.... AlignRight!,
.... AlignAtBottom!
.... Floating!)
enumerated datatype

20. How do you put the time in the status bar?
On the open event of a window call Timer(1). On the timer event Time = Now( )
SetMicroHelp(Time)

21. Can Script#1 share a local variable declared in Script#2?
No, it cannot. Local variables are accessible only in the script in which they are declared.

22. What data types do you know?
There are following data types in PowerBuilder :
1. Standard (string,…..)
2. Enumerated data types its a some functions which were made in other languages and pb recognise it by exclamation point at the end
3. Array is a massive of variables of the same Data Type
4. Structure is a collection of one or more related variables of different or the same data types grouped under one name.


23. Where would you put a code to make sure that everything has been saved on the window in case if a user is closing the window?
I would write this code on CloseQuery event of the window. A CloseQuery event occurs every time when the window is to about to be closed. At this moment PowerBuilder checks the value of the attribute. If after performing some processing we don’t want to allow a user to close the window, set Return 1. When we close a window, PowerBuilder triggers the CloseQuery event and inspects the value of Message.ReturnValue. If the Message Return Value 1, the window cannot be closed. Note: Closing any window causes PowerBuilder to close all Child and Pop-up windows that is opened, and closing an MDI frame window causes PowerBuilder to close all sheet windows within it. Any window thus being closed can set Message.ReturnValue to cancel the close operation.

24. Where can external functions be declared?
External functions can be declared in the Application Object, User Object or Window Object. To declare an external functions, select External Functions on the Declare Menu of the current object painter or the PowerScript Painter.

25. What is the maximum number of characters that you can enter into a control?
The maximum size is a little bit more then 32KB


26. What can you do if you want to leave the PowerScript Painter but the script won’t compile?
I’ll put comments(//) on my code. When we are leaving the PowerScript Painter, it automatically compiles our script. We can have a script containing the errors, To save the script without correcting the errors, comment out any lines in errors.

27. What statement evaluates one variable?
Statement Choose Case evaluates one variable.

28. 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)?
By using GroupBox control. If the GroupBox contains RadioButtons, the user can select only one RadioButton in the GroupBox at a time.

29. How to update a ListBox without using a DataWindows?
AddItem( )

30. How to pass control as parameter(for ex. DataWindow)?
Specify as an argument in a function a chosen type of this control

31. How many Windows can be in the application?(maximum)
No more then 60 but we have more then 100.

32. Could you invoke DOS editor from PowerBuilder?
Yes pressing Shift-F6 anywhere.
33. How could you invoke help about specific function or reserved word from Power Script painter?
To highlight this function or reserved word and press shift-F1

34. How do we specify processing that takes place in our application?
By writing a script for different events that occurs. PB applications are event-driven.

35. How do you refer to the attribute inside the scripts?
Using syntax calling dot notation:.

36. How can you use enumerated data types?
As an arguments in functions or to specify the attributes of an object or control.

37. What is the array?
An array is an indexed collection of elements of a single data type. Could be fixed or variable size. Fixed-size arrays could be multidimentional.

38. What kind s of iteration(repeated) statements do you know?
For . . .NEXT uses to execute statementblock a specified number of times;
4 formats of DO. . LOOP
DO UNTIL . . . LOOP executes statementblock until condition is TRUE. If condition is TRUE in the first statement statementblock does not execute.
DO WHILE . . . LOOP executes statementblock while condition is true. If condition is FALSE on the first evaluation statementblock does not execute.
DO. . . LOOP UNTIL The same as DO. . . UNTIL LOOP, but statement always executes at least once.
DO. . . LOOP WHILE the same as DO WHILE . . LOOP, but statementblock always executes at least once.

39. What will be the outcome of the following code:
int I = 5
string name = “fox”
If int I = 5 then
if name = “rabbit” then
if I < 10 then
MessageBox(“Status”, “one”)
else
MessageBox(“Status”, “two”)
end if
else
if I <10 then
MessageBox(“Status”, “three”)
elseif I = 5 then
MessageBox(“Status”, “four”)
else
MessageBox(“Status”, “five”)
end if
MessageBox(“Status”, “five”)
end if
elseif I > = 5 then
MessageBox(“Status”, “six”)
end if
MessageBox(“Status”, “three”) and MessageBox(“Status”, “five”)

40. What’s wrong with the logic in the following code:
if years_of_service > 10 then
vacation = 4
bonus_factor = 1.5
elseif years_of_service > 6 and years_of_service < 10 then
vacation = 3
bonus_factor = 1.2
else
vacation = 2
bonus_factor = 1.0
end if
There is nothing said about years_of_service = 10

41. Can we prevent window from closing?
On the close query event we have to write script:
IF MessageBox("Closing window", "Are you sure?", Question!, YesNo!) = 2 THEN
RETURN 1
ELSE
RETURN 0
END IF
0 - Allow the window to be closed
1 - Prevent the window from closing

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.
Custom Search