Search for a Text in Sybase and SQL Server Database

SQL Server Code:

select
S.name as [Schema],
o.name as [Object] ,
o.type_desc as [Object_Type],
C.text as [Object_Definition]
from sys.all_objects O inner join sys.schemas S on O.schema_id = S.schema_id
inner join sys.syscomments C on O.object_id = C.id
where S.schema_id not in (3,4) -- avoid searching in sys and INFORMATION_SCHEMA schemas
and C.text like '%%'
order by [Schema]


Sybase Code:

Select distinct sysobjects.name
, case
 when sysobjects.type = 'TR' then 'TRIGGER'
 when sysobjects.type = 'P' then 'PROCEDURE'
 when sysobjects.type = 'V' then 'VIEW'
 else 'UNKNOWN' end type
from sysobjects inner join syscomments
on sysobjects.id = syscomments.id
where syscomments.text like '%tbl_books%'

Datawindow Blinking Color Text

You will need to set the timer inteval of the datawindow to something, You can find the timer by double clicking on any blank part of the datawindow.

Next place a text field on the datawindow and In the visible section place this code:

if( mod( Integer( Mid( String( Now() ), 7, 2 ) ),2 ) =1,0,1)

Also possible to change colours by using color attribute:

if( mod( Integer( Mid( String( Now() ), 7, 2 ) ),2 ) =1,0,255)

Create Visual Objects Dynamically in PowerBuilder

Yes, We can create and open that Visual User Objects in PowerBuilder at run time. in the following example we are going to see how to create a Picture control at runtime and assigning the image for that Picture Control also.

Code:

 picture p_2 // declare the picture variable 
 p_2 = create picture // create the picture object 
  
 p_2.x = 100 // x position of the object 
 p_2.y = 100 // y position of the object 
 p_2.height = 350 // height value of the object 
 p_2.width = 450 // with value of the object 
 p_2.picturename = "D:\raj\profile.jpg" // name of the picture inc full path 

parent.openuserobject( p_2, "p_2", 100, 100) // ok, let's create the object

PowerBuilder 10.5 AnimateWindow Code.

Decalre the External Function:
Function boolean AnimateWindow( &
   long lhWnd, long lTm, long lFlags ) library 'user32'

The first parameter is the handle to your window,
the second is the time taken for the animation (larger values take longer),
the last parameter is the animation type.

To call the function add the following code to the open event of your about window:

// Animate the window from left to right
Constant Long AW_HOR_POSITIVE = 1
// Animate the window from right to left
Constant Long AW_HOR_NEGATIVE = 2
// Animate the window from top to bottom
Constant Long AW_VER_POSITIVE = 4
// Animate the window from bottom to
Constant Long AW_VER_NEGATIVE = 8
// Makes the window appear to collapse inward
Constant Long AW_CENTER = 16
// Hides the window
Constant Long AW_HIDE = 65536
// Activates the window
Constant Long AW_ACTIVATE = 131072
// Uses slide animation
Constant Long AW_SLIDE = 262144
// Uses a fade effect
Constant Long AW_BLEND = 524288

AnimateWindow( Handle( this ), 500, AW_VER_NEGATIVE )

How do I get the output parameters back from the server if my stored procedure is also returning a resultset?

There are a couple of different ways to get the RESULTSET and OUTPUT parameter from a stored procedure.

Code Sample 1: DECLARE, EXECUTE and FETCH ResultSet and Output parameters:

The first is to declare, execute and fetch the resultset and then when the sqlcode = 100 get out of loop and fetch the procedure a second time.  The code would look like this:

//declare local variable
LONG l_parm1
LONG l_out_parm

STRING s_message

CONNECT USING SQLCA;

//Initialize the input parameter - this could be hard coded.
l_parm1 = 35

DECLARE testproc PROCEDURE FOR dbo.testproc @Parm1 = :l_parm1, @OutParm = :l_out_parm OUTPUT USING SQLCA;

EXECUTE testproc;
//First, fetch the RESULTSET
do while sqlca.sqlcode = 0
FETCH testproc INTO :s_message;
if sqlca.sqlcode = 0 then
MessageBox( "s_message", s_message)
end if

loop
//Now fetch the OUTPUT PARM
FETCH testproc INTO :l_out_parm;
MessageBox( "l_out_parm", String(l_out_parm))
CLOSE testproc;
DISCONNECT USING SQLCA;

The second way this can be accomplished is by using the RPC method.  This is discussed in the "Application Techniques" manual.

Code sample 2: Dynamic SQL Format 4 declaring a stored procedure

The sample in our help file illustrates ways to use format 4 using a declared cursor.  This script uses Format 4 embedded SQL statements and a declared stored procedure. This example assumes you know that there will be only one output descriptor and that it will be an integer. You can expand this example to support any number of output descriptors and any data type by wrapping the CHOOSE CASE statement in a loop and expanding the CASE statements.

string ls_procname, ls_sql, ls_Temp
int li_job_id, li_Ctr, li_Temp
int li_rtn

li_job_id = dw_emp.getitemNumber(1, "job_id")
setNull(li_job_id)

ls_procname = 'pr_405237'
ls_sql = 'execute ' + ls_procname + ' @job_id=' + '?'

PREPARE SQLSA FROM :ls_sql using sqlca;
DESCRIBE SQLSA INTO SQLDA ;

DECLARE my_procudure DYNAMIC PROCEDURE FOR SQLSA ;
li_rtn = SQLDA.SetDynamicParm(1, li_job_id)
sle_1.Text = String(li_rtn)
EXECUTE DYNAMIC my_procudure USING DESCRIPTOR SQLDA ;
FETCH my_procudure USING DESCRIPTOR SQLDA ;
If Sqlca.Sqlcode <> 0 then
    Messagebox("Error ", String(Sqlca.Sqlcode) + sqlca.sqlerrtext)
 else
       for li_Ctr = 1 to sqlda.NumOutputs
          CHOOSE CASE SQLDA.OutParmType[li_Ctr]
            CASE TypeString!
              ls_Temp = GetDynamicString(SQLDA, li_Ctr)
            CASE TypeInteger!
              li_Temp = GetDynamicNumber(SQLDA, li_Ctr)
          END CHOOSE
       next
  end if

CLOSE my_procedure ;

----------------------------------------------------------------------
Et Voila
Pushparaj

Prevent Acceidental close at Powerbuilder and save the datawindow contents.

Hi

Today am going to share about how to prevent the window closing when the data are not saved in the datawindow.
Usually when you did any changes to your data window if you not saved that datawindow and if you clos that datawindow  no data will be saved.
So to avoid that and your application users must save the datawindow while before closing the window use the following code to protect the accidental close.

Code follows

Write this code on your windows Close Query Event. Or if you want you can write at base window.

Integer li_count,li_ret

window aw_window
aw_window=this

FOR li_count = 1 TO UpperBound(control[])
    IF TypeOf(aw_window.control[li_count])=Datawindow! Then
        Datawindow dw
        dw=control[li_count]
        IF dw.AcceptText() = 1 AND dw.ModifiedCount() > 0  THEN
            li_ret=Messagebox('Alert','Changes Not Saved, Do you Want to Save it?',Question!,YesNo!,1)
            If li_ret=1 Then Return 1
        End if
    End if        
NEXT

this code is common to all windows you can write this code at your base objects close query event.

Explanation :
 aw.control[] this code is used to get the all controls places on the window.
Using the control we are verifying the type using the TypeOf. If the type is datawindow then we are checking is there any modifications happened using the Acceptext() function.
After this we are checking for modified count. Whether the data is changed or not. If so then we are giving the messagebox to ask the user to close or not.

You can also code the datawindow to save automatically when the user click Yes button. Write the update code on the return value loop statement.
dw.update()

thats it.

Regards
Raj

How to Change the Child datawindow object dynamically at run time in Powerbuilder

Hi Every one

Hope all fine. Today am going to discuss with how to change the child datawindow name dynamically based on some value or some other thing.

Initially we will setup the child datawindow with the dataobject and will choose the display column and data column.
But what happen if we want to change that child datawindow itself based on the used input. Here is the code.
Here the name Datawindow represents your datawindow name and the column_name represents your column name . And the dd_child_new represents the data object you want to set. Code follows

//Change datawindow child object
Datawindow.Modify("column_name.DDDW.Name=dd_child_new")

// Setting display column
Datawindow.Modify("column_name.DDDW.DisplayColumn='Short_name'")

// Setting data column
Datawindow.Modify("column_name.DDDW.DataColumn='Number'")

DataWindowChild ldwc_child
Datawindow.Getchild("column_name",ldwc_child)
ldwc_child.Settransobject(SQLCA)
ldwc_child.Retrieve()
ldwc_child.Reset()


hope this will help you a bit

Regards
Raj~