ODBC LSX Connections in Dialog Box - Help/Advice Required

HCL Domino Domino Forum
1

I am working on an application which basically steps through creation of a document via a series of dialogboxes in a wizard type format. A large number of fields (dialog lists) retrieve their data from a SQL Server database. However, we are experiencing horrendous performance issues. This seems to be down to the fact that in the 3 screens presented to the user there are a total of 37 @DbCommands connecting to SQL Server (I didn’t write this!). I have been tasked with improving the solution.

My initial thoughts were to use LEI to have the data in a Notes db and retrieved from there but this has been rejected by the IT Manager - he wants to retain the direct connections. He has asked me to re-engineer it using the LS ODBCLSX.

This is fine but I am having problems determining which event to use to connect to SQL Server. For example the first 3 fields are all dialog lists from SQL Server but each dependent on the previous. I thought of using the form Postrecal event but it is not populating correctly. Is the use of LS events on the form the correct approach or is even using LS connections for this going to give the type of performance increase we would expect?

Suggestions/advice greatly appreciated.

Thanks.

2

Subject: ODBC LSX Connections in Dialog Box - Help/Advice Required

I think you mean a single dialogbox with a whole bunch of listboxes. The dialogbox form has a big table, with one row representing page 1, and another page 2, right? And buttons for moving between pages? Correct me if I make a mistake but all those @DbCommands are executing each time the form refreshes, right? That must be pretty bad.

He has asked me to re-engineer it using the LS ODBCLSX.

Wrong answer. Absolutely not. You should certainly use the LCLSX. It’s easier to use, a lot more flexible and faster. For example if you are on Windows and you are connecting to SQL Server you can skip the dsn and connect using OLE DB.

You can fix all this with a little Lotusscript. It sounds complex but let me assure you – and I’ve done this many times – it is not. Quite the opposite in fact.

I would write a class to represent the dialogbox, with a public property Page (default value of 1) to represent what page of the wizard you are on. You have the option of connecting to SQL server when the dialog opens or waiting until the user does something. Likewise you can populate all the lists up front or, if the values of list B depend on the user’s selection in List A, you can populate each as you go (when the Page value changes).

You will want to use a $Table field to control which row gets displayed. The dialog probably already does this, just not with Lotusscript. Simplest way to handle this field is to set it from your Page property:

public property set Page as integer

’ possibly fetch some values from SQL Server here, depending on how you handle that part

with me.uidoc

call .document.replaceitemvalue(“$Table”,Page)

call .refresh ’ may or may not be necessary depending on what else is going on on the form

end with

end property

Your Previous and Next buttons just increment or decrement the class’s Page property:

Previous TheDialogObject.Page = TheDialogObject.Page-1

Next TheDialogObject.Page = TheDialogObject.Page+1

You should put in hidden multivalue fields to store the values for your various listboxes. For example right now you may have a listbox FOO. Field FOO has some @DbCommand or other to fetch its value-list. You should create instead a hidden editable mutlivalue field FOO_VALUES, and populate this field using script. Use FOO_VALUES as the formula for FOO. This arragement is more intuitive - I think it is anyway – and simplifies debugging.

It’s not a difficult task if you know Lotusscript pretty well, and remember that you can switch on the debugger and preview the form, and then display it as a dialog when it’s ready.

You will be left with a dialogbox that is fast, easy to debug and easy to modify.