1. Falguni Patel
  2. PowerBuilder
  3. Monday, 13 March 2023 07:00 AM UTC

Hi,

 

I am writing a select query to create a datastore. I want to create a datastore where all columns must be selected which starts with 'IS_' from a specific table abc.

Kindly note that the columns 'IS_' are added dynamically to the table. Hence, I need to select all columns those starts with 'IS_'

Your suggestion and solution will be greatly appreciated.

 

 

 

Accepted Answer
Sivaprakash BKR Accepted Answer Pending Moderation
  1. Monday, 13 March 2023 14:43 PM UTC
  2. PowerBuilder
  3. # Permalink
Declare c_cur Cursor For
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'abc' ;

ls_sql  = " Select "
ll_inc  = 1
Open c_cur ;
Do While True
	Fetch c_cur Into :ls_column;
	If Sqlca.Sqlcode <> 0 Then Exit
        If ls_column = 'IS_' Then
           If ll_inc = 1 Then
              ls_sql += ls_column
           Else
              ls_sql += ", " + ls_column
           End If
        End If
	ll_inc ++
Loop
Close c_cur ;

ls_sql += ' From abc ';

// Create Data store
Datastore    ds_store
ds_store  = Create datastore

ls_present_str = "style(type=tabular)"
ls_syntax_str  = SQLCA.SyntaxFromSQL(ls_sql, ls_present_str, ls_msg)
If ls_syntax_str = "" Then
	Return -1	
End If

ds_store.Create(ls_syntax_str, ls_msg)
ds_store.SetTransObject(Sqlca)
ds_store.Retrieve()

I'vent checked the above code...  I expect it to work.

HTH

Happiness Always
BKR Sivaprakash

 

 

Comment
  1. Sivaprakash BKR
  2. Tuesday, 14 March 2023 13:52 PM UTC
Falgui,

To set the tab order, use

dw_1.SetTabOrder(fieldname, 1)

If you don't know the fieldname(s), store the field names, in an array, while creating the SQL (ls_sql) and use that array to set the tab order.



If you want to convert an edit field to check box,

1. Must know the field name [ I don't have any idea to do that without that information]

2. To study the dw syntax through dw_1.describe('datawindow.syntax') and have a list of modifications that need to be done in the syntax. After modifying again re-create the dw using dw_1.Create(ls_modified_dw_syntax, ls_error_msg)

May not be simple as you think. Need to spend few hours (days) depending on what you want to do.

  1. Helpful 1
  1. Miguel Leeuwe
  2. Tuesday, 14 March 2023 14:07 PM UTC
Being a datastore, it doesn't make much sense to set taborders either.
  1. Helpful
  1. Sivaprakash BKR
  2. Tuesday, 14 March 2023 14:17 PM UTC
Miguel

OP has updated his query in his comments.. Now he want to allow editing facility to be included... so switching to dw.

  1. Helpful 1
There are no comments made yet.
John Raghanti Accepted Answer Pending Moderation
  1. Monday, 13 March 2023 11:28 AM UTC
  2. PowerBuilder
  3. # 1

Hi. If you're using SQL Server, this will start getting you there:

-- Looking for a column in tables
Select * From INFORMATION_SCHEMA.COLUMNS
Where column_name like 'is_%'
order by 3

Comment
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Monday, 13 March 2023 12:28 PM UTC
  2. PowerBuilder
  3. # 2

Hi Falgui;

  In any DBMS System, the "System Tables" will contain your DB Schema "Meta Data". If you query these in your PB App, you can dynamically get any table / column data dynamically. Check with your DBMS Vendor or their support forums on the needed SQL to run to get that column Meta Data you need.  HTH

Regards .... Chris 

Comment
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.