Digital Workshop

Firstly let me apologise for posting this in the wrong forum I hope I am in the right place now.

How to read multiple ROWS of data from an access database back into an opus publication?

I have read an answer, given by FISHBOX, to a similar question, but it is a PHP/Mysql example. Despite the authors extra help regarding reading back to Opus, I still can't unravel it. It would be the last hurdle to my learning curve. Can anyone help?

Opus already reads the entire dataset (rows) when you perform a query. You then use either the Opus actions or script commands to navigate the dataset.

Mack

I think Bizzy wants to display multiple records (rows) at the same time.

Mack, if there is a simple way to display the whole recordset (using Opus actions and Access DB) at one time, I'd certainly like to learn that shortcut.

Bizzy, I know some ways to manipulate and display DB records using Opusscript -- but there is a learning-curve and some time investment involved. For now, I will try to outline some things you can do using Opus standard actions. DW/Opus provides a sample Pub with the Access DB example, so I have used that to illustrate multiple rows.

Normally, we would use an Action Loop to run through the DB recordset and accumulate the values in a variable. However in the steps I outline, I show this manually --- each time I press the Next Record button as in the modified example, one more record (Row) is added to the displayed records. What was needed, what is needed... is to add a newline character after each single record is displayed -- so the next record displays on a new line.

So I created a couple Opus variables to hold the equivalent of a Tab keystroke and a Newline keystroke.
Note also, the var I created, DBrows, is used to accumulate row after row. You simply insert that variable (DBrows, DB_Dataset or whatever variable name you choose to use) into a Text box/object.

Try to set up the following actions for yourself. See screenshot (attached) of Action tree. Once you do that, we can help you set up a LOOP action.

[Lar EDIT -- I corrected a few typos, errors in my explanation ]

Ah, linguistics - the ancient study of using many words to make sense not me thinks



The return from the ExecuteSQL is an Object containing the properties and methods necessary to navigate the dataset. If you looking for something like a simple .flatten method, I don't think one exists but then all it would do is loop through the database and build an output string - something that could be done with a few lines of code anyway.

Personally, if I need to have multiple records displayed simultaneously, I read the record as a new Object into an array because this gives me a lot more flexibility.

Mack

Ah yes, and me thinks there were FEWER misunderstandings in the time before mankind had language. Of course there were fewer people then too.

Thanks to you, and others here, I have gotten comfortable with the looping exercise running through a DB set.

But the reason I posed the question/curiosity here is that I interpreted that the OP may be using Opus standard action for the DB Query -- and I wondered if that Query1's recordset you alluded to was directly accessible. In a small test I did, Query1 in std actions is not recognized as an object or as anything else AFAICT over in Opusscript.


I think for the OP, if using std actions, he'll have to use the Loop Action.
As for me, I may try to learn more about JS methods on objects and arrays --- re: .flatten. I think however it may 'implode' on me, and 'reduce' me to rubble. (puns intended).

Cheers.

Okay,

Using scripting to access the results of an action query

I'm not going to say it cannot be done, but I would be inclined to ask why

And if any of us are smart enough to work it out, I wager good money, that we'd be smart enough to use the two lines of code necessary to just connect the DB and run the query in script to start with


Mack

I think I was thinking in terms of helping someone who got stuck after making some progress with DB access via standard actions.

It would be easy to write a script function that could actually illustrate what was happening with their database --- e.g., move the 'pointer' to next record using a loop, and displaying the accumulation as each row is added to a text object. With a small wait(), they could see things unfold. (I am not inclined to spend a lot of time in Std Actions trying to construct a loop solution)

But you are right (two lines of code), learn opusscript and do it all there.

So Bizzybee, where are you with progress on your Pub?

Many thanks for all the help I have had. I followed the graphic and got everything to work. It led me on to a bit of scripting and I have had some modest success. However Is it possible to sync text boxes with the scroll bar of a Listbox? I can get a text box with a scroll bar to sync, but not a Listbox. The advantage for me would be the ability to select a record with a mouse click.

Bizzy,

Good you are getting along. Thanks for the update.

As for the scrolling, 'yes'. It can certainly be done in scripting. I do not remember there being useful actions for this in standard actions. The opusscript commands for this are themselves easy to understand.
Look in the Script Help files for something like:
GetScrollPosition() e.g., var myMasterScroll = Listbox04.GetScrollPosition() //you typically do this once
SetScrollPosition() becomes: Textobj22.SetScrollPosition( myMasterScroll ) //You may apply the SetScrollPosition() command to multiple objs (best on separate lines)

I may not have the syntax exactly right there, but if you follow closely the examples in the mentioned helpfile, you can easily adapt it.

Just to make your life easier (you can use your own naming instead of what I show here) :
1) On your Listbox object, create a Std Trigger... probably Mouse-Over (but you can try others)
Create an Action. Under the 'Programming' Tab, add a script (here is it known as an action script)
Copy or modify the code below and put it in that script edit space. Click Apply or OK.
2) Back on your Opus Page, click the toolbar to create a Script Object (or you may use an existing one if you have one)
Type in or paste-in the following code. Note, I have indicated where you place those scroll_position commands.
That's it. Save your work. And Preview it.

Hi Lar_123

I cannot get this to work. All the script references I have found seem to imply, that text box to text box is OK, but I cannot find reference for Listbox to text box

The first example works fine:
e.g. var scrollPos = list_textbox_1.GetScrollInfo()
list_textbox_2.SetScrollPosition(scrollPos.vpos,scrollPos.hpos)

but, the following does not work.

e.g. var scrollPos = Listbox_1.GetScrollInfo()
list_textbox_2.SetScrollPosition(scrollPos.vpos,scrollPos.hpos)

I must be missing something, but I don't know what.

Hi Lar_123

Quick rewind;

I find, that I can control a Listbox from a textbox. This solves my problem, although not in the way I expected.

Once again, thank you for all your help.

Hi Lar

Things have gone well and I have most everything working. I notice however, that after exiting the publication, that the database is showing as still open. I have read about end query on the forum and have added three actions (to close three querys) before exiting, but it does not seem to make any difference. I'm not sure where else to put the actions. Does it matter? The publication continues to work and reopen.

Regards to all

Hi bizz, I don't know an answer for this. [Edit: You might try close each query before starting the next query on the same DB.]

Did you happen to see/find the following post on a similar issue? viewtopic.php?f=4&t=2773 releasing a database.
It mentions that the lock file is released ("only") after exiting the Opus Pub. I am not sure why your project is behaving differently (compared to what other people have reported). That is a long thread, you might skip down to July 7 2007 to get a quick idea of how the discussion concluded. Do note that many people there used Opusscript, whereas earlier comments may have touched on Standard Actions for DB.

One thing you should try is to open the Sample Pub provided with Opus (has Access DB). Modify something there, and do a Save As. Then see if that modified Pub is leaving its database open.

I am surprised the Listbox scroll does not work over on a Text object (as slave scrolling). I may play with that if/when I get some time. Do you have the same number of lines in a text box that you have in the Listbox?

BTW, up in the reply where I gave you a screenshot of Actions -- I want to explain something in case you want to refine or perfect that setup. The way I constructed it was the most direct way, and kept it 'simple'. You should examine the result, and will see one (minor?) problem. Those actions add a newline after every database record. This means your Listbox will contain one extra empty line after all the records. Let me know if you want to work on that -- 20 minutes work.

Hi Lar

Thanks for you advice. I am still trying to find a solution to the locked file (might be a win7 thing). I am interested in learning how to handle the extra line.

Is it possible to generate a word doc from the publication?

Regards