Dynamic Datawindows based on a stored procedure 1

[tdhny]

Hello All,

I am using PB 11.5 and Oracle 11g, my requirement is to create dynamic datawindows based on a oracle stored procedure ( i know it can be created by using syntaxfromsql by passing sql select statement, but to make it SQl injection proof I have to use SP).

If any one has any ideas will be greatly appreciated.

 

[Topwiz]

You can build the DataWindow syntax in a string and use the Create function of the DataWindow or DataStore objects. To get an example of what the string would look like, create a DataWindow in the painter and then delete all visual controls. After saving, open it in source mode. The second line that starts with "datawindow(" only needs the value "processing", all the others have defaults.

 

[tdhny]

Thanks for your solution, but I figured it how to build a dw based on SP.

One more question for you?

What would be the limit on length of a string ( it can be a string or comma separated values) we can pass from PB to Oracle Stored Procedure. I know from PL/SQL it is 32K, but would want to know from PB side.

I appreciate your suggestions.

 

[Topwiz]

I don't use Oracle so can't help with that question.

 

[tdhny]

My issues is I am trying to create a datawindow dynamically based on a SP with parameters. I could able to create a datawindow dynamically from a SP without parameters, but with parms I am getting this error - "Can not get parameters of procedure"

Any suggestions please?

My code:


sql_syntax = "execute starsdba.SP_PROVIDER_LIST; as_Sql= From Users"

presentation_str = "style(type=grid)"

presentation_str = &
"style( type=Grid &
Horizontal_spread = 25 &
Header_bottom_margin = 15 &
Header_top_margin = 15 ) &
datawindow( units=2 &
Color= 67108864) &
column( Font.Face='system' &
Font.Height=-10 &
Font.Weight=700) &
text( Font.Face='system' &
Font.Height=-10 &
Font.Weight=700 &
Border=6)"

dwsyntax_str = SQLCA.SyntaxFromSQL(sql_syntax, &
presentation_str, ERRORS)

IF Len(ERRORS) > 0 THEN
MessageBox("Caution", &
"SyntaxFromSQL caused these errors: " + ERRORS)
RETURN

END IF

 

[mbalent]

Have you created a dw with the stored proc and then looked at the export for the SQL part?

Matt

"Nature forges everything on the anvil of time"

http://www.anvil-of-time.com

 

[tdhny]

I figured it out.

Thanks for your suggestions though.

 

[mbalent]

Care to share your solution?

Matt

"Nature forges everything on the anvil of time"

http://www.anvil-of-time.com

 

[tdhny]

Here it is:

string ls_sql = "'From Users'" /* String should be passed in a quote */ This was the key and the rest was same.


sql_syntax = "execute starsdba.SP_PROVIDER_LIST; as_Sql=" + ls_sql


presentation_str = &
"style( type=Grid &
Horizontal_spread = 25 &
Header_bottom_margin = 15 &
Header_top_margin = 15 ) &
datawindow( units=2 &
Color= 67108864) &
column( Font.Face='system' &
Font.Height=-10 &
Font.Weight=700) &
text( Font.Face='system' &
Font.Height=-10 &
Font.Weight=700 &
Border=6)"

dwsyntax_str = SQLCA.SyntaxFromSQL(sql_syntax, &
presentation_str, ERRORS)

IF Len(ERRORS) > 0 THEN
MessageBox("Caution", &
"SyntaxFromSQL caused these errors: " + ERRORS)
RETURN

END IF


HTH.