How to update a dynamic datawindow in PB 5

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


Rudi28Asked:
Who is Participating?
 
Vikas_DixitConnect With a Mentor Commented:
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
 
Rudi28Author Commented:
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
 
Vikas_DixitCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Rudi28Author Commented:
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
 
Vikas_DixitCommented:
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
 
namasi_navaretnamConnect With a Mentor Commented:
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
 
namasi_navaretnamCommented:
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
 
Rudi28Author Commented:
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
 
Vikas_DixitCommented:
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
 
sandeep_patelCommented:
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
 
Rudi28Author Commented:
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
 
sandeep_patelCommented:
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
 
Rudi28Author Commented:
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
 
sandeep_patelCommented:
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
 
sandeep_patelCommented:
which pb version u r using ?
0
 
Rudi28Author Commented:
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
 
sandeep_patelCommented:
oh no !!!!!

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

cheers,,,,

0
 
Vikas_DixitCommented:
Great !!!!!

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

Regards,
Vikas
0
 
sandeep_patelCommented:
well.....i don't have any objections.........:)

thanks,
-sandeep
0
 
Vikas_DixitCommented:
No Objections!!!!!!! :)
--Vikas
0
 
namasi_navaretnamCommented:
No objections. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.