Solved

How to update a dynamic datawindow in PB 5

Posted on 2004-03-24
23
816 Views
Last Modified: 2013-12-26
Hi,

  I have a table that stores the column names and titles of the respective columns that are used for a survey. I then proceeded to build a tabular datawindow that retrieves these column names and their titles. So far, o.k.

  Now I have a function that gets the names of the  columns and their titles from the datawindow that I build and proceeds to construct a SQL to create the Dynamic Datawindow. I create the Dynamic Datawindow without problems. However, I can't update data in the Dynamic Datawindow. I noticed that the dbnames assigned by PB were different from the real ones (compute_0005, etc.) so I did a modify to assign the real ones. I did modifies for column.update=yes. Also, I even try to make the dynamic dw (dw_1) updatable using dw_1.of_SetUpdatable (TRUE). I can trace it going through the pfc_update but even if everything appears o.k. it doesn't save.

  Any help is appreciated. Thanks.

Rudi28


0
Comment
Question by:Rudi28
  • 6
  • 6
  • 6
  • +1
23 Comments
 
LVL 8

Accepted Solution

by:
Vikas_Dixit earned 25 total points
Comment Utility
Can you post the SQL that you are using to create the dynamic datawindow......
PB will create column names like COMPUTE_XXX if you are using function like ltrim/rtrim or any other function in the select query..

Regards,
Vikas D
0
 

Author Comment

by:Rudi28
Comment Utility
Hi Vikas,

  The sql is build dynamically linking columns that I get from another table. It looks something like this:
  select part_code "Part", survey_code "Survey", survey_date "Date", survey_data_ordinal "Ordinal" , family_code "Family Code" , subfamily_code "Subfamily Code"  from d_survey_data
where survey_code = 'SIRS'
and survey_date = '01-JUN-03'
and part_code = 'A510'

  Nope. PB will create compute_XXXX even without trim functions. I saw the SQL generated by PB and has these type of names even thought I just do a regular select sql. I think what causes this is that I get the columns from another table and then concatenate them to create the dynamic sql string.

  The db names for the columns were changed to the right ones using a modify so that's not the problem.

Regards,

Rudi28

0
 
LVL 8

Expert Comment

by:Vikas_Dixit
Comment Utility
Hi,
Can you Just try with simple sql, no aliases for column names :

select part_code, survey_code, survey_date, survey_data_ordinal, family_code , subfamily_code from d_survey_data
where survey_code = 'SIRS'
and survey_date = '01-JUN-03'
and part_code = 'A510'

And check if you still get the column names as compute _XXX.

Regards,
Vikas
0
 

Author Comment

by:Rudi28
Comment Utility
Hi Vikas,

  The aliases do not cause the compute_xxxx in this case. I need the aliases. However, there're four columns that I put directly in the sql and are not obtained from the other table. These four columns also have aliases and they appear with the table name '.' alias name as the db name which I change to the db name that should be the correct one. It seems that PB doesn't pick up the field name but the alias and assigns that to dbname for columns that are hardcoded. For columns obtained from another table (with a getItemString on another dw) PB then gives the compute_xxxx. Either way, PB assigns the wrong name which I then correct with the modify.

  I need the aliases for these columns since the users want to see different names as titles. I guess business names that make sense to them.

Thanks,

R.

0
 
LVL 8

Expert Comment

by:Vikas_Dixit
Comment Utility
Hi rudi,
Aliases do cause the DB column name to be a computed column...
Can you try once removing the aliases ?
You can easily change what the user see as titles by modifying the text of the items in the header band, or by just replacing the values in the syntax before you create your dw... e.g col part_code will have the title as "Part Code" so just search this in the syntax and replace it with "Part", be fore you call the Create ().

Regards,
Vikas
0
 
LVL 15

Assisted Solution

by:namasi_navaretnam
namasi_navaretnam earned 25 total points
Comment Utility
also check sqlpreview event of the datawindow to see if the correct update statement is generated.

In sqlpreview event code as follows to debug.
MessageBox("debug", sqlsyntax)

If using alias names, do not place " double quotes around them.

regards-
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
Comment Utility
Also try something like this:

ll_cols = Long(idw_requestor.Object.DataWindow.Column.Count)

for ll_col = 1 to ll_cols
    <DW Control Name>.Modify("#" +string(ll_col) + ".Update=<Yes or No>")
next
0
 

Author Comment

by:Rudi28
Comment Utility
Hi Vikas,

  I took the aliases out. Still get the compute_xxxx. Yes, aliases cause the compute_xxxx. I didn't say they didn't. However, getting the column values from another table and using a getItemString to get the names and concatenate them to use in the sql for a dynamic dw also causes PB to create the compute_xxxx. Since the names are stored in another table and I need to read that table to get the column names, I still have to face having the compute. That's not a problem since I can use the modify to change the dbname. No problem there.

  The sqlpreview doesn't fire for the update even though the pfc_update event works and it looks like it goes thorugh it fine and issues a commit. The datawindow is Updatable. I already define the columns as updatable and also the keys in my code with a modify. In addition, I also defined the table to update.

  By the way, can  have aliases without the double quotes like you suggested above ? I don't think that is allowed since sql needs the aliases in double quotes.

Regards,

Rudi28

0
 
LVL 8

Expert Comment

by:Vikas_Dixit
Comment Utility
Hi,

I am assuming you are using oracle as the database.. if yes, aliases without double quotes are allowed... ( Even MS Access allows that)

Can you please post your code where you prepare the SQL? ( without the aliases). Getting the column names from anoter table should not be a problem.... after all it's a string that you will pass to the syntaxfromsql ... you can verify passing the hardcoded sql statement to the syntaxfrom sql and see what happens...

Another point I will suggest that first you try to update the datawindow as a non-pfc dw, and check if any update statements are fired .. of if you could debug through the  pfc code and check the return vaue of the dw.update() call...

Aslo check you actually have something to update .. ( ModifiedCount() +DeletedCount() > 0)

Regards,
Vikas
0
 
LVL 14

Expert Comment

by:sandeep_patel
Comment Utility
hi,
following are the major things u have to take care for the datawindow to be updateable
1) Define datawindow as udpateable
2) Define table name for update
3) Define updateable columns
4) Define Key columns
5) Define where clause for delete and update

as u described i think u have done all this stuffs using modify function....

But have u checked the return values after all these modify functions ? b'coz modify will not raise error, it will return the value and u will not come to know unless and until u check the return value...generally we don't have habit to check that....:)

second, if possible just create datawindow with one or two columns and check for update with simple update function...not with pfc_update...and what's the return value of update ??

Third, check why sqlpreview is not being executed? it must be executed...keep in mind that, messagebox will not appear if argument is null...so check with simple messagebox('preview','ok') and then check for syntax in the sqlpreview.....
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:Rudi28
Comment Utility
Hi,

  Thank you all for your responses. Maybe I'm not explaining my problem well.

  Sandeep, the steps that you described I already have. The datawindow was created dynamically already.

  I found the steps before at:
http://www.sybase.com.cn/cn/content/support/exp_jszc_pb_datawin_00004.htm

  Vikas, the "aliases" were causing the compute_xxxx. Without the aliases, I don't get that problem with the column names. Yes, it's Oracle and it allows aliases. However, PB seems to change the names of the real columns based on the aliases and I didn't want that. I can set the column titles later with a modify. That's the way I'm going with that.

  I just need to make it "updatable". I think I did and I also made the columns Updatable with modify's, but the sql preview shows "select" instead of "update".

Thanks both for your time and help, :)

Rudi






Thanks,

Rudi




0
 
LVL 14

Expert Comment

by:sandeep_patel
Comment Utility
hi,
we understood yr problem well...
and it should not be select in sqlpreview....

can u send me '.srw' file for the window where u have written all the script ? my id is tsandip@hotmail.com

-sandeep
0
 

Author Comment

by:Rudi28
Comment Utility
Hi,

  The sqlpreview executes fine as I said and has the right (update) statement. I was displaying the wrong statement (sorry about that).

Rudi


0
 
LVL 14

Expert Comment

by:sandeep_patel
Comment Utility
it's strage !!!!
if sqlpreview is also displaying the right statement...then what's the wrong it must update to the database

just make sure that u have written the commit statement after udpate() function and nowhere rollback is there before commit.......
0
 
LVL 14

Expert Comment

by:sandeep_patel
Comment Utility
which pb version u r using ?
0
 

Author Comment

by:Rudi28
Comment Utility
Hi Sandeep, Vikas,

  Thank you both for trying to help. I found the problem. It has to do with a trigger that I wasn't aware of. I browse the db objects and realized that this trigger was interfering with the update. Now things work fine.

  I'll tell the moderators to close the question but will recommend that some points be given to both of you.

Thanks,

Rudi28


0
 
LVL 14

Expert Comment

by:sandeep_patel
Comment Utility
oh no !!!!!

anyway u solved your problem...nice...

cheers,,,,

0
 
LVL 8

Expert Comment

by:Vikas_Dixit
Comment Utility
Great !!!!!

This problem was a difficult one..... good it's resolved now...

Regards,
Vikas
0
 
LVL 14

Expert Comment

by:sandeep_patel
Comment Utility
well.....i don't have any objections.........:)

thanks,
-sandeep
0
 
LVL 8

Expert Comment

by:Vikas_Dixit
Comment Utility
No Objections!!!!!!! :)
--Vikas
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
Comment Utility
No objections. :)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Programmer's Notepad is, one of the best free text editing tools available, simply because the developers appear to have second-guessed every weird problem or issue a programmer is likely to run into. One of these problems is selecting and deleti…
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now