Database: assign variables as fields of the table

Volgar · **Joined:** March 4th, 2007, 7:17 pm **Posts:** 132

Can`t to find in Opusscript manual assign variables as fields of the table in databases.
Using action - it's easy, but how by the script?

And how view list of all data from the fields in order? Array variable or loops?

Volgar · **Joined:** March 4th, 2007, 7:17 pm **Posts:** 132

Well, i write the script:

Code:

var db = new Database("FILEDSN=bsff.dsn"); 
var records = null; 
sk = new Array(); 

var sql = "SELECT aspect FROM table1"; 
records = db.ExecuteSQL(sql); 
    
var num_records = records.GetNumberOfRecords(); 
for (num=0; num<num_records; num++) { 
   sk[num] = records; 
   records.NextRecord(); 
}

Then create an array variable and set <sk[0]> in expression in text object, but displayed "Database RecordSet Object"/
How I can draw all fields table1 or selectively desired fields?

Sentosa · **Posted:** March 17th, 2008, 12:43 pm

Volgar

You just might get better responses if you continue this thread in the Advanced section of the Forum

Volgar · **Joined:** March 4th, 2007, 7:17 pm **Posts:** 132

OK
Maybe moderators move my topic in the advanced?

Volgar · **Joined:** March 4th, 2007, 7:17 pm **Posts:** 132

It`s progress!

Code:

var db = new Database("FILEDSN=bsff.dsn"); 
sk = new Array(); 

var records = db.ExecuteSQL("SELECT aspect FROM table1"); 
    
var num_records = records.GetNumberOfRecords(); 
for (num=0; num<= num_records; num++) { 
   sk[num] = records.aspect; 
   
   records.NextRecord(); 
}

I create a object listbox and insert in it expression: <sk[0]>, <sk[1]>, etc. All data derived from field "aspect"! Looks wonderful.

But when I have in listbox properties "Store selected text" in a new variable and then in new textbox insert this variable, it not shows "aspect", but only parts "<sk[0", "]>", etc.

Volgar · **Joined:** March 4th, 2007, 7:17 pm **Posts:** 132

Yes, I did it!

1. In listbox option "Store index of selected item" - for example: variable Y
2. "Store selected list" - Empty
3. Insert button near to the listbox with action "Set to a variable": Z to sk [Y]
4. Insert variable Z in any textbox.

COOOOOL. Now databases really helpful.

Lar_123 · **Posted:** March 17th, 2008, 11:31 pm

Volgar,

Congrats on working through to success.

I think I can speak for most people here --- Thanks for 'closing the loop' and posting your Results. It is much appreciated to know the outcomes and it helps us all to learn (especially seeing the intermediate steps so well articulated).

Cheers.

Volgar · **Joined:** March 4th, 2007, 7:17 pm **Posts:** 132

New problem.
How to set WHERE option in the SQL query?

Quote:

var records = db.ExecuteSQL("SELECT aspect FROM table1 WHERE prob==sp");

"prob" - field of the table, and "sp" - the string variable.

Lar_123 · **Posted:** April 3rd, 2008, 11:45 pm

Looks like some syntax issue.

From the script Help files:
- - - - - - - -
Database Functions - Introduction

Below is a simple OpusScript example ...

var db = new Database("DSN=Products;")
var RecSet = db.ExecuteSQL("SELECT Price, Qty FROM Customer_Order WHERE OrderID=1;")
- - - - - - - -
I don't know if any issue with page variable in that statement.

Volgar · **Joined:** March 4th, 2007, 7:17 pm **Posts:** 132

One problem is not solved so far.

I created a query from DB that only includes records where field "pr" is equals a variable pr_load.

Code:

var records = db.ExecuteSQL("SELECT * FROM `table1`WHERE `pr` = pr_load ORDER BY `id` DESC"); 

But I get a message:

Code:

07002: fffff43e: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. 

What`s wrong syntax?

Lar_123 · **Posted:** May 10th, 2008, 8:46 pm

Something to 'try'.

Change the * to a column name or two. I don't remember clearly, but I may have had some difficulty with the * .

Let us know.

Volgar · **Joined:** March 4th, 2007, 7:17 pm **Posts:** 132

Thank you, Lar_123.
But "Change the * to a column name or two" does not help.
If I delete WHERE, then everything is OK. I forgot to say that "pr_load" - is variable.

In general I need to solve this problem:
1. There is a "houses" and each "house" live a few "residents".
2. I create the list of "houses" - table 2, the list of "residents" - table 1.
3. If you choose "house", it displays a list of all residents who live in it.

I have no other way but to make query with the parameter "pr_load". Maybe there are other ways?

Lar_123 · **Posted:** May 11th, 2008, 7:03 am

Hard to say.

Try to eliminate different places where error could come in.

1. use a fixed value, as a test, instead of pr_load. Or just before this, force pr_load to be a known good value..,.
then you know to work on that or not

2. check your SQL syntax. sometimes 'spaces' make a difference.
`table1`WHERE

3. as an alternative, test your SQL statement from some other program.

Good luck.

Volgar · **Joined:** March 4th, 2007, 7:17 pm **Posts:** 132

Thanks for your quick reply Lar_123

If I paste instead "pr_load" any data (name of the house), then everything works. Moreover, it works if I use the standard action - Database Query. But as the script object is not working. I need script object, because I use listbox to display data of array variable - it is convenient.

Lar_123 · **Posted:** May 11th, 2008, 9:37 am

Volgar,

Okay then, it may be simple.

What is telling the Script Object to execute?

Digital Workshop

Database: assign variables as fields of the table

Who is online