PDA

View Full Version : SQL to x-basic


ABC123

craigdakkan
04-22-2008, 01:06 PM
(d'oh, just posted this to the wrong board! Second time lucky...)

Hi. New to Alpha (you can tell now can't you!), so forgive me if I ask a dumb question,

I'm used to working in SQL but not x-basic and unfortunately, after having trouble linking to my SQL tables, I got fed up and imported the data instead.

Now I'm trying to update a field on a table based on numerous parameters and variables. I know how to do it with SQL script but not in x-basic.

Is it something done in the field rules, via a function or an operation (what's the difference!?)?

Can anyone suggest a good reference to get a grounding in x-basic scripting?

any help appreciated!

Tony Rutherford
04-22-2008, 03:25 PM
Emily,

Peter Wayne's book "Xbasic for Everyone" is very good. You can download it for $19.99 from Alpha's store at https://www.alphasoftware.com/shop/books/

Tony

Stan Mathews
04-22-2008, 04:06 PM
Now I'm trying to update a field on a table based on numerous parameters and variables. I know how to do it with SQL script but not in x-basic.
This may be as simple as using an update operation depending on the complexity of your parameters and variables and where they reside.


Is it something done in the field rules, via a function or an operation (what's the difference!?)?A field rule is defined at the table level and covers many possibilities such as case conversion and default values. A function could be used in an expression in an update operation. In that usage it is mostly a defined calculation such as ceiling(), floor(), etc.

Have you found the webhelp (http://support.alphasoftware.com/alphafivehelpv8/index.htm)?

(P.S. De Wayne's book is great.)

Tom Cone Jr
04-22-2008, 04:56 PM
Emily, to supplement the good advice already posted, I'll add:

1) generally a saved operation is most useful when processing groups or batches of records.

2) the Alpha Five data manipulation language for native DBF tables resembles the dml of dBase itself (Foxpro, Clipper, etc.). These were pre SQL databases. There are two typical methods for changing an existing record using an xbasic script. (a) running a query against the table to find the record, and then change it or (b) do an indexed find by key to find the record and change it. "Change it" means using a script to establish a pointer to the table and then the <tbl>.change_begin() and <tbl>.change_end() methods.

Check the xbasic reference for <tbl>.query_create() and <tbl>.fetch_find().

To process an indexed collection of records you'd use a While ... end while loop, like this:


tbl.fetch_first()
While .not. tbl.fetch_eof() 'change all lastname fields to "Jones"
tbl.change_begin()
tbl.lastname = "Jones"
tbl.change_end(.t.)
tbl.fetch_next()
end while

This kind of loop can run against the whole table, or against a queried subset of the records in the table.

-- tom

alanc
04-24-2008, 11:06 PM
See, this is what happens when you educate women - they start asking questions you don't understand. And finding people who do...

Dad
(this board is great...brings families together!)