Using Opus with a Database

Pete Smithies · **Posted:** July 26th, 2012, 4:18 am

Hi Mack
It seems that the imp that you mention in the post is no longer available for further scrutiny - so I have not been able to make any progress.
The question on that thread however, is exactly what I am trying to achieve.

viewtopic.php?f=4&t=2801&hilit=arrays+array+arrays

Regards

Pete

Lar_123 · **Posted:** July 27th, 2012, 5:23 pm

Pete Smithies wrote:

Hi Lar,
Currently I am using a reworking of the code that Mack provided me with in combination with the SQL Wizard (see db2.zip attachment to Macks last post)
It is working really well - locating a name and using the next and previous buttons to go forward and back to list the "Browns", but I am not sure how to achieve a "list" of all the Browns together on one page.
Hope that makes sense?

Hey Pete,
Do a Search on the forum for: populate listbox
This link should carry out that search: http://tinyurl.com/cjun3qw

The basic principle is this:
You execute a LOOP -- the number of iterations through the loop is equal to the number of records returned.
Inside the Loop, you take individual current record and add it to a variable (as a string). The "trick" is to append a LINE RETURN each time you accumulate the next individual record.

For example:

Code:

var accum = "" ;
//then inside the Loop
accum += Last_Name_returned_record   + "\n" ;

The result is that the variable accum when displayed (in a ListBox or in a Text Object) will appear as a list column.
That's the principle. There's one little problem here in that you will have one extra blank line at the end of that list. Most of the examples in those Posts or Threads show you how to handle that. You can problably look for it and find it now in the code Mackavi has provided.

Cheers.
ps. If you want to observe exactly how it works, add a wait(0.7) to your code in the Loop, and on your Opus Page just Insert the Variable accum in a tall text box (you may have to use a Page variable, or global variable).

Pete Smithies · **Posted:** July 31st, 2012, 1:25 am

Hi Lar,

Sorry but I am just not getting this.
Using a reworking of the code that Mack has provided - I can now call all the "Browns" and display them one at a time on a page via the next and previous records.
But what I am not able to achieve is to get those records listed in the list box on one page.

I have a listbox on a page with the variable accum in there - I dont understand how to code the loop in the script box
I copied and pasted your code - But I am assuming I need to add the loop code where you wrote //then inside the Loop

This currently just returns the surname of one person into the list box - not the complete list of people.

Sorry for being so dense

Pete

Pete Smithies · **Posted:** August 1st, 2012, 11:58 pm

I have now located the Cricket example and am trying to utilise that - but the first issue I have is:

I am trying to connect to my TEST database but I get errors when I use " and \

e.g

var db = new Database("FILEDSN=mydb.dsn")
as in the example from the forum is fine
But when I put
var db = new Database ("FILEDSN=C:\Users\user\Documents\TESTDatabase.dsn")
I just get a load of Unrecognised characters and Syntax Errors.

Any help would be really appreciated.

Regards

Pete

Lar_123 · **Posted:** August 2nd, 2012, 5:39 pm

Quote:

But I am assuming I need to add the loop code where you wrote //then inside the Loop

Yes. I was just racing through the basics.
If you need more specific code for this, just let me know and I'll find something. (I have not included code because my scripting is for SQLite, and I do not use Opusscript commands for 'next record' and such. But the iteration structure is basically the same.)

Quote:

I am trying to connect to my TEST database but I get errors when I use " and \
e.g
var db = new Database("FILEDSN=mydb.dsn")
as in the example from the forum is fine
But when I put
var db = new Database ("FILEDSN=C:\Users\user\Documents\TESTDatabase.dsn")
I just get a load of Unrecognised characters and Syntax Errors.

For the DW/Forum provided example, see Help File example for scripting this. Note the use of double-backslash for Windows directory path.
But when it comes to your own Pub, if you are using Standard Actions, just use the Database actions (in lieu of scripting it).
OpusScript Help File:

Quote:

Database Functions - Overview
Opus Pro provides the ability to connect to a database. Please note that it is intended to provide an easy route to display the contents of individual records and not huge lists of records. Nor should it be seen as a replacement for dedicated tools for more sophisticated database programming requirements.
Note:
A full description of databases is provided in the main Opus Help file. To open the help, click on Contents & Index option in the Help menu at the top of the Opus Editor. Search for the word databases in the Index tab of the help file for more information.

The Database OpusScript functions are unique to Database objects created in a script. In other words, you must create a new Database object in your script that references a DSN, before you can use the OpusScript Database functions.

Below are three examples, showing you how you can create a new Database object:

For a file DSN on drive d:
var myDB = new Database("FILEDSN=d:\\catalogue.dsn;")

For a DSN in default path of Opus:
var myDB = new Database("FILEDSN=catalogue.dsn;")

For Machine DSN’s:
var myDB = new Database("DSN=catalogue;")

Minor problem. I am thinking in terms of ALL of the database operations being done in OpusScript. I see from your later post that you are mostly handling this with Standard Actions.

So maybe SKIP what I said / recommended ir earlier post, and keep it all in Std Actions.
- - - - - - - - - - - - - -
Now back to your current obstacles... and the screenshot of your standard actions:
At this point, I would not inject my suggested scripting into your FLOW of Standard Actions.
1) Remove the Script-Action (positioned after the Get Number of Records)
2) Create a Loop using Standard Actions (exit the loop when you have iterated through the number of records)
3) Inside the loop, insert a Standard Action to add string values to that accum variable

I do not use std actions for data handling, so cannot walk you through it in detail. But some Hints...
Create a Page variable called newline1. Set its default value to a text string: "\n"
(I hope it works the way I think it will -- that is to insert a line return)
Next, if you have a Page variable called accum --- in your Standard Actions (inside the loop) insert a "Variable action". You want to take accum, and add the current record value and also add newline1 to it. When the Loop is finished, the variable accum will hold a columnized list of record values (eg, people's Names).

Pete Smithies · **Posted:** August 2nd, 2012, 11:03 pm

Hi Lar

Many thanks for your response.
I have trawled through the forum as well as the database help documentation included with the software.
But I seem to be banging my head against a wall...

Using the standard actions
- I can connect to the database and set up the search successfully
Using Mack's help - I can get all the Browns and use the next/previous to show the results on a page.
BUT - I just dont seem to be able to achieve putting all these results in a single list box - nor can I work out how to effect your advice below.

Quote:

2) Create a Loop using Standard Actions (exit the loop when you have iterated through the number of records)
3) Inside the loop, insert a Standard Action to add string values to that accum variable

I do not use std actions for data handling, so cannot walk you through it in detail. But some Hints...
Create a Page variable called newline1. Set its default value to a text string: "\n"
(I hope it works the way I think it will -- that is to insert a line return)
Next, if you have a Page variable called accum --- in your Standard Actions (inside the loop) insert a "Variable action". You want to take accum, and add the current record value and also add newline1 to it. When the Loop is finished, the variable accum will hold a columnized list of record values (eg, people's Names).

Using OpusScript
I am not able to connect to the database in the first instance - where it resides in a sub folder - (but I suppose I could copy and paste it into the root folder of my D drive?) because it throws up all those errors I mentioned re characters and syntax.

I have tried that just now - using Ronnie Dakus's script - but tried to modify it for my use

Code:

var db = new Database("FILEDSN=g:\\CWGCDatabase.dsn;");
var records = null;
var num_records = 0;

//////////////////////////////////////////////////////////////////////////////
//
// NOTE: Start_Record_Num is the record number for the first of the 5 records
//       and it is a Page Variable
//

function SearchDB()
{
   var sql = "SELECT * FROM casualties WHERE SURNAME = FEEDBACK ORDER BY SURNAME;";
   // Debug.trace(sql+"\n");
   records = db.ExecuteSQL(sql);
   num_records = records.GetNumberOfRecords();
   numberrecords = num_records;
   ShowRecords();
}

function ShowRecords()
{
   if (records == null) {
      return;  // need to call Search First to get records
   }
   var list_box = Listbox_1;
   list_box.SetSelection(0, -1);   // Select all the old text
   list_box.ReplaceSelection("");   // Remove all the old text
   
   if (records.FirstRecord()) {
      list_box.ReplaceSelection(records.SURNAME+"\t "+records.FORENAME);
      records.NextRecord();
      for (i=1; i<num_records; i++) {
         list_box.ReplaceSelection("\n"+records.SURNAME+"\t "+records.FORENAME);
         records.NextRecord();
      }
   }
}

When I perform the search and navigate to the page where the "results" should be listed in the list box - I just get a blank list box

The access table that I am searching is called casualties and FEEDBACK is a variable that contains the surname that the user has input via a touch screen keypad.

The listbox is called Listbox 1 and has nothing in it - ie. no variables or text.

SO Either way I am not moving forward.

Pete

Lar_123 · **Posted:** August 5th, 2012, 12:48 am

Pete,

I think you are near an 'Aha moment'. But it is a few steps away.

Quote:

Using the standard actions
- I can connect to the database and set up the search successfully
Using Mack's help - I can get all the Browns and use the next/previous to show the results on a page.

If you've gotten that far, you are close to getting what you want.

I have an idea how I may help you. It is the weekend here, so be patient... and I'll try to come up with some Standard Actions for a record-reading loop. (not my forte, but what the hell)

Lar_123 · **Posted:** August 7th, 2012, 8:23 am

Hey Pete,

Here's a simple demo of the actions you want. NOTE: I used DW's Sample Pub for Access Database as a starting point.

The Attached Opus Pub simply contains one Page_2, with some DB Actions and a LOOP. This Page will not work by itself.
There is a ListBox object that will display the list of multiple records.

You will need to Browse the Sample Pubs provided by DW -- Open this Sample: Database Using Access.
Now simply take this tweaked Page_2 and “Copy Into” the above Sample Pub.* And Save As… (do not change the directory -- becasue the Access DB is there).

I am really unskilled in Standard Actions. So while I think I have something for you that works, it is not pretty. Could be much simpler way to accomplish the same thing.

Lar

BTW, I noticed that DW used Pub level variables for the database fields in the Sample Pub.... e.g., Name, Image, etc.

sandyn · **Posted:** August 9th, 2012, 8:07 am

Have you got this working yet? I have a database application where I search for a tested part from an Excel database. I use standard actions/scripts, but I create a report file, then create an array from the report file and use the array to populate the listbox.
The listbox contains a list of all the results containing the searched part The user can then click on the listbox and the test report and standards are pulled in for the selected entry.

Pete Smithies · **Posted:** August 18th, 2012, 9:25 pm

Hi Sandyn,
Thanks for your reply.

At present I have not achieved what I want and I am still working on it - but due to other commitments have had to "park" the project for a week or two. Hoping to get back on to it next week.
I (and probably others) would be keen to see your solution if you are happy to make it available.

Warm regards

Pete

sandyn · **Posted:** August 20th, 2012, 8:24 am

Hi Pete,

I avoid using Access for my database applications, because I find Excel spreadsheet/workbook seems to work with fewer problems, (still get same serial date as access!) but I usually create the data as well, so I can choose to use Excel.
I use a mixture of scripting and actions for the database queries, whichever is easier. In this application I use the report file action because I want to create two report files for each query. There are 5 built in report files, so I can populate the files with variables seperated by '%' as a delimiter. I then use Excel (text to columns) and pivot tables to analyse the data in the report files.

Attachment:

Action.jpg

The chunk of script shown below is in the Script action at the end of the list of actions

Code:

// This populates an array from the log file, then uses the array to populate the listbox.

MyLogFile = ReportGetFilename(1)/////////////////////  gets the name of the report file
var LoopCount2=0
MyText = OpenFile(MyLogFile)///////// opens the report file
MyText.GotoFirstLine()
MyText.ReadLine(false)/////////////////this effectively skips the first line which is the header

wait(0)/////////////////////////////////can't remember if I actually need these.....probably not. It refreshes the screen
Listbox1.SetSelection(0,-1)
Listbox1.ReplaceSelection("")
wait(0)
MyArray=new Array(NumRecords) ////////////////////creates an array sized by the number of records in the current query. I get the Number of records using an action. 

for (LoopCount2=1;LoopCount2<=NumRecords;LoopCount2++)
  {   
   MyArray[LoopCount2]=MyText.ReadLine(false)////////////////////////populates the array from the report file 
   Listbox1.ReplaceSelection(MyArray[LoopCount2]+"\n")//////////////populates the listbox from the array.
   wait(0)
  }
MyText.Close()//////////////closes the text file

Attachment:

tool.jpg

it seems to work well, apart from an indexing bug, which I have never bothered to fix. This application is just a tool for me, so no one else uses it...I'll fix it one day!!

Sandy

Pete Smithies · **Posted:** August 20th, 2012, 9:48 pm

Hi Sandyn,

Again, many thanks for your reply.
I will look at it more closely once I am "back on deck" with this project.
I am forced to use the access for this project however - but at first glance I think your principles will apply.

Thanks again

Regards

Pete

Digital Workshop

Using Opus with a Database

Who is online