array elements in Database query

FishBox · **Posted:** January 8th, 2005, 5:48 pm

Is it possible to load a field from a database query object into an publication array element?

For example, I have a query object that says:
SELECT * FROM `firstedition`
WHERE `cardname` = 11202

on the list of fields below I have:

Field Variable
cardname sk_hand_cardname[sk_counter1]

This doesn't seem to work
sk_hand_cardname is a publication array variable I created
sk_counter1 is a counting variable I'm using in a loop

I have a text object looking at sk_hand_cardname[0] but nothing ever shows up. Is this beyond the scope of the standard action objects? Do i need to do this in Opuscript? If so, can someone post a crude example of using script to iterate through a pub array variable and have the results available in text objects on the page?

Thank you!

Robin Garrett · **Posted:** January 10th, 2005, 12:36 pm

Hi,

Thank you for your enquiry.

This should be possible. Please try adding the following code to a script object on the page:

Code:

var db = new Database("FILEDSN=mydb.dsn");
var records = null;
sk_hand_cardname = new Array();

var sql = "SELECT * FROM firstedition WHERE cardname = 11202;";
records = db.ExecuteSQL(sql);
   
var num_records = records.GetNumberOfRecords();
for (num=0; num<num_records; num++) {
   sk_hand_cardname[num] = records.cardname;
   records.NextRecord();
}

You may need to change the filename in the first line to match your DSN.

Now simply add a series of text objects to the page and insert the various indexes of the array (for example, sk_hand_cardname[0], sk_hand_cardname[1] and so forth).

Now preview the publication and the text objects should display the required records.

Of course, you may not want the records to appear immediately, but only when a button has been clicked. If so, simply edit the script object to read the folllowing:

Code:

function GetRecords() {
var db = new Database("FILEDSN=mydb.dsn");
var records = null;
sk_hand_cardname = new Array();

var sql = "SELECT * FROM firstedition WHERE cardname = 11202;";
records = db.ExecuteSQL(sql);
   
var num_records = records.GetNumberOfRecords();
for (num=0; num<num_records; num++) {
   sk_hand_cardname[num] = records.cardname;
   records.NextRecord();
}
}

You can now add a script action to a button to call this GetRecords() function at the required time.

I hope this helps. Please do not hesitate to contact me if you have any further queries.

Kind regards,[/code]

FishBox · **Posted:** January 10th, 2005, 11:17 pm

Thank you greatly, Robin. That is exactly what I was trying in my script!

I think the issue is connecting to my local database with the FILEDSN path. When I set that path, is it the same path as the one I use for a query object which i my case would be C:\\database\\sk\\carddb.dsn or should it change to C:<SYSTEM_PUBLICATION_DIR>\\carddb.dsn ?

Thank You!

Robin Garrett · **Posted:** January 11th, 2005, 11:05 am

Hi,

Thank you for your enquiry.

The FILEDSN should use the full path to the .dsn file and not utilise variables, so if your .dsn file is located at C:\database\sk\carddb.dsn, the script object should contain:

Code:

var db = new Database("FILEDSN=C:\\database\\sk\\carddb.dsn");

Please note that when you publish your project to a standalone executable, the DSN is embedded into the executable and will look for the database in the publication directory. You will therefore need to ensure that the original Access database (or whatever type of database you are using) is copied to the publication directory before writing the contents of the folder to CD.

I hope this helps. Please do not hesitate to contact me if you have any further queries.

Kind regards,

Digital Workshop

array elements in Database query

Who is online