• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • How to Create External Datawindow dynamically without any SQL

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (5)

Resolved How to Create External Datawindow dynamically without any SQL

  1. How-to
0
Votes
Undo
  1. DIPANJAN DUTTA
    DIPANJAN DUTTA
  2. PowerBuilder
  3. Thursday, 16 May 2019 08:11 AM UTC

Hi Experts,

In generally, to create a datawindow / datastore, I use SyntaxFromSQL function, but my current scenario is creating External DW dynamically.

I have already tried with the following SQL, SyntaxFromSQL PowerScript function & DW Create Method

SQL -> Select 0 as num, ''  as str from dual

In above case I can’t mention the width and other visible property of DW column and another point is my DW is external, it doesn’t require any SQL [Select 0 as num, ''  as str from dual]

Is there any way to Do it except SQL query [SyntaxFromSQL]?

Allow copy data from disabled control or theme Dis...
PowerBuilder Runtime Deployment
Who is viewing this page
Martin Kaltenböck
Accepted Answer
DIPANJAN DUTTA
DIPANJAN DUTTA Accepted Answer Pending Moderation
  1. Monday, 20 May 2019 04:49 AM UTC
  2. PowerBuilder
  3. # Permalink

Hi Guys,

I have created a small function to resolve Dynamic external datawindow issue.  In the argument of that function I have received table attribute = (column name, datetype & size) and return DW syntax as string.

Here is the Export syntax of structure where I Assign table attribute value and pass into function

global type str_tableattribute from structure

              string                   datatype

              string                   size

              string                   colname

end type

Declare instance: str_tableattribute       istr_TableAttribute

Then call the function

String                  ls_DWSyntax, ls_error_create, ls_resp

Integer               li_TotalIndex

//Assign table attribute into istr_TableAttribute

ls_DWSyntax = wf_CreateExtDWSyntax(istr_TableAttribute)

dw_bookdetails.Create(ls_DWSyntax, ls_error_create)

 If Len(ls_error_create) > 0 Then

              MessageBox("CreateError", ls_error_create)

              Return

End If

Here is my function to create External DW Syntax

String                  ls_DWSystax = "", ls_TableAttribute, ls_DW_TextHeader, ls_DW_Column

String                  ls_ColName, ls_DataType, ls_Size

Integer li_index, li_TotalIndex, li_MeasurementPerChar, li_ColPixelDiff

Long                    ll_X, ll_Width

 

li_TotalIndex = UpperBound(astr_tableattribute)

 

If li_TotalIndex > 0 Then

              ls_DW_TextHeader = ""

              ls_DW_Column = ""

              ll_X = 4

              ll_Width = 0

              li_MeasurementPerChar = 30

              li_ColPixelDiff = 12

             

              //Prepare Basic of DataWindow - Part 1

              ls_DWSystax = "release 12.5;" + "~n~r" + &

                             "datawindow(color=1073741824 processing=1 )" + "~n~r" + &

                             "header(height=80 color=" + '"536870912"' + " )" + "~n~r" + &

                             "detail(height=92 color=" + '"536870912"' + " )" + "~n~r"

             

              ls_TableAttribute = "table("

             

              For        li_index = 1 To li_TotalIndex

                             ls_ColName = astr_tableattribute[li_index].ColName

                             ls_DataType = astr_tableattribute[li_index].DataType

                             ls_Size = astr_tableattribute[li_index].Size

 

                             //Prepare DataWindow Table and column details - Part 2

                             ls_TableAttribute = ls_TableAttribute + "column=(type=" + ls_DataType

                             If Upper(ls_DataType) = 'CHAR' Or Upper(ls_DataType) = 'DECIMAL' Then

                                           ls_TableAttribute = ls_TableAttribute + "(" + astr_tableattribute[li_index].Size + ") "

                             End If

 

                             If Upper(ls_DataType) <> "CHAR" Then

                                           ls_Size = String(20)

                             End If

 

                             ls_TableAttribute = ls_TableAttribute + &

                                                                                                                   " name=" + ls_ColName + " " + &

                                                                                                                   "dbname=" + '"' + ls_ColName + '" )' + "~n~r"

                                                                                                                  

                             ll_Width = li_MeasurementPerChar * Integer(ls_Size)

                             If li_index > 1 Then

                                            ll_X = ll_X + li_ColPixelDiff + ll_Width

                             End if

 

                             //Prepare DataWindow Column Header details - Part 3

                             ls_DW_TextHeader = ls_DW_TextHeader + "text(band=header text=" + '"' + ls_ColName + '" color=' + '"33554432"' + &

                                           " y=" + '"4"' + " x=" +  '"' + String(ll_X) + '"' + " height=" + '"68"' + &

                                           " width=" + '"' + String(ll_Width) + '"' + " name=" + ls_ColName + "_t" + &

                                           " background.color=" + +  '"536870912"'   + " )" + "~n~r"

 

                             //Prepare DataWindow Column details - Part 4

                             ls_DW_Column = ls_DW_Column + "column(band=detail id=" + String(li_index) + " tabsequence= " + String(li_index * 10) + &

                                           " color=" +  '"33554432"' + " y=" + '"4"' + " x=" +  '"' + String(ll_X) + '"' + " height=" + '"76"' + &

                                           " width=" + '"' +String(ll_Width) + '"' + " name=" + ls_ColName + &

                                           " background.color=" + +  '"536870912"'   + " )" + "~n~r"

              Next

             

              //Marge All 4 part

              ls_TableAttribute = ls_TableAttribute + ")" + "~n~r"

              ls_DWSystax = ls_DWSystax + ls_TableAttribute + ls_DW_TextHeader + ls_DW_Column

End If

 

Return ls_DWSystax

Comment
Sivaprakash BKR
  1. Sivaprakash BKR
  2. Wednesday, 9 June 2021 11:38 AM UTC
DIPANJAN DUTTA



Can u tell us what pass by, argument type and argument value for the function ?

function wf_CreateExtDWSyntax ( Value structure astr_tableattribute)



Here structure as argument type is not working. What argument type to be used ?



  1. Helpful
Sivaprakash BKR
  1. Sivaprakash BKR
  2. Wednesday, 9 June 2021 11:54 AM UTC
Got it. sr_table_attribute should be the argument type.



  1. Helpful
There are no comments made yet.
Responses (5)
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 9 June 2021 18:01 PM UTC
  2. PowerBuilder
  3. # 1

Hi Dipanjin;

   FYI: I use a much simpler way ... well at least I think so (LOL).   ;-)

   Its based on the old Distributed PowerBuilder product but this technique still works today. Its another way to create Dynamic DWO (internal or external) using the "CreateFrom" command, as follows:

https://docs.appeon.com/pb2019/datawindow_reference/ch09s42.html

Food for thought for the next time.  ;-)

Regards ... Chris

Comment
There are no comments made yet.
Olan Knight
Olan Knight Accepted Answer Pending Moderation
  1. Thursday, 16 May 2019 23:19 PM UTC
  2. PowerBuilder
  3. # 2

I've done this by creating the DWO with I need and saving it. Use two columns in the DWO.

Export that dwo and you have the syntax you need. Save the syntax as text.

Use that text file as your template. Divide it into three segments: before columns, columns, and after columns.

Now in your script you can copy the "Before Columns", use the "Columns section as a guid on dynamically creating the columns you need, then pasting the "After Columns" syntax at the end, after the column definitions have been established.

Having said that, if you have access to the database I like Chris's solution better.


Olan

Comment
DIPANJAN DUTTA
  1. DIPANJAN DUTTA
  2. Friday, 17 May 2019 05:04 AM UTC
Thanks for your your reply.........:)
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 16 May 2019 14:07 PM UTC
  2. PowerBuilder
  3. # 3

Hi Dipanjan;

    I find that the DW Source code generation to be a bit laborious script wise for complex column generation. So here is what I do ...

1) Create a TEMP table using the "Execute Immediate" PB command that also includes the columns & data properties you need for that processing thread.

2) Create a dynamic DW using the SyntaxFromSQL() command on the TEMP table

3) Drop the TEMP table using the "Execute Immediate" PB command

Tip: If your using ASE or MS-SQLServer, use the #Temp feature to create the TEMP table for much better performance. nAlso, using a #TempTable route in ASE or SS, you do not need to drop the table. The DBMS does this for you on Disconnect. 

Note: This approach is PB version proof.  Food for thought.  ;-)

HTH

Regards ... Chris

Comment
DIPANJAN DUTTA
  1. DIPANJAN DUTTA
  2. Friday, 17 May 2019 05:08 AM UTC
Hi Chris.......Thanks for your approach...........I will try this.
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Friday, 17 May 2019 14:33 PM UTC
Super .. let us know how you make out with that approach!
  1. Helpful
There are no comments made yet.
René Ullrich
René Ullrich Accepted Answer Pending Moderation
  1. Thursday, 16 May 2019 12:00 PM UTC
  2. PowerBuilder
  3. # 4

Hi,

SyntaxFromSQL is only to help you create datawindow syntax. You can also create the syntax without using this function. You can build the string or import it from a library, file or database.

Here is a very very simply to create a datawindow with one column "dummy" of type specified in ls_type and without any controls and other settings:

dw_1.Create('release 5; datawindow() table(column=(type=' + &
                ls_type + ' name=dummy dbname="dummy" ))')

 

Comment
DIPANJAN DUTTA
  1. DIPANJAN DUTTA
  2. Friday, 17 May 2019 05:20 AM UTC
I have tried this with pb 12.5 with following systax



String ls_type = 'number', ls_err

dw_bookdetails.Create('release 12.5; datawindow() table(column=(type=' + &

ls_type + ' name=dummy dbname="dummy" ))', ls_err)

If Len(ls_err) > 0 Then

MessageBox("DW Create Error", ls_err)

End if

dw_bookdetails.SetTransObject(g_connect)

dw_bookdetails.InsertRow(0)



Where DW is crated, but it was not displaying any column......
  1. Helpful
René Ullrich
  1. René Ullrich
  2. Friday, 17 May 2019 05:44 AM UTC
Yes, I said it is a very simple example without any controls.

If you want to show columns you have to add more syntax as you ca see in an exported datawindow.

Add more definitions in "datawindow(...)" part of the string, add definitions of the bands before the table() part and add the controls definitions (columns, texts ...) after the table() part.
  1. Helpful
DIPANJAN DUTTA
  1. DIPANJAN DUTTA
  2. Friday, 17 May 2019 06:08 AM UTC
Thanks a lot.........:)

I will try your approach........
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.