1. You can get a SQL prompt directly at the Magik prompt...
MagikSF> sql(gis_program_manager.cached_dataset(:gis))
$
SQL> SELECT name, address1 FROM hotel ORDER BY address1
$
Name Address 1
Acorn Guest House 154 Chesterton Road
Hamilton Lodge 156 Chesterton Road
De Freville House 166 Chesterton Road
Rosswill 17 Chesterton Road
Cam Guest House 17 Elizabeth Way
Kirkwood House 172 Chesterton Road
All Seasons 219 Chesterton Road
Lyngamore 35 Chesterton Road
Suffolk House 69 Milton Road
Southampton House 7 Elizabeth Way
Ashley Hotel 74 Chesterton Road
Arundel House Chesterton Road
Gonville Hotel Gonville Place
Garden House Granta Place
University Arms Regent Street
15 record(s) selected
SQL> SQL> select count(*) from min_road
$
COUNT(*)
833
1 record(s) selected
SQL> SQL>
NOTE: the sql() procedure is defined in module :sql2. You can load that module if your image doesn't have the sql() procedure loaded.
2. You can also use the sql2_parser object directly in your Magik code.
You can find out more by searching the Smallworld documentation for "SQL Support". To be clear, this is not the same as Smallworld's SQL Server image code. This is strictly related to writing SQL in Magik to query Smallworld tables.
I have never used this in code, but I suppose it could come in handy if you were more comfortable writing a SQL statement compared to writing a selection predicate. I'm curious to hear if anyone else uses this SQL functionality within Smallworld.
Posts
8 comments:
I've found SQL useful in cases when I need to see UNIQUE values of a not enumerated field(s) :
MagikSF> sql(gis_program_manager.cached_dataset(:gis))
$
SQL> select DISTINCT street from address
$
SQL> select DISTINCT zip_code, street from address
$
This way I can see the result quickly without writing _loop/_if statements.
I'm thinking about using SQL for an on-demand report, but first wanted to try it out at the console. I can get the SQL> prompt by using the sql() procedure, but how do I 'exit' out of SQL> SQL> and get back to the MagikSF> prompt?
Thanks,
Peter Reale
Hi Peter,
Just click return/enter a couple of times and you will exit the SQL prompt.
Alfred
Just by pressing the 'Enter' at the 'SQL>' prompt
Thanks for the quick responses; it worked.
I am now trying to use the sql2_parser in Magik code, to get a list of Circuits by Complex_Area, but the SQL Select stmt we have already coded, uses Oracle tables that are not 'reverse engineered' into Smallworld. Does anyone know of a way to map Complex_Areas to Circuits within PowerOn VMDS?
Thanks!
Thanks for the quick responses; it worked.
Now I am trying to use the sql2_parser to run an existing SQL Select stmt we have that gets Circuits for a specified Complex_Area. But some of the Oracle tables used are not found; I'm guessing they are not 'reverse engineered' into Smallworld VMDS?
Does anyone know of a way to get Circuits for a given PO_Complex_Area in Smallworld PowerOn, using objects in the Electric datastore? (here's where a Smallworld object model diagram, showing properties, methods and relationships would come in handy)
Thanks!
here is a code snippet that you can use to do what you want. change complex area name and name of circuit table and geometry accordingly -
_block
circs << rope.new()
_for sa _over po!complex_area.table.select(predicate.eq(:name,"COMPLEX AREA NAME")).an_element().simple_areas.elements()
_loop
circs.add_all_last(evc[:CIRCUIT_TABLE].select(predicate_spatial.new(:CIRCUIT_GEOMETRY, :inside, {sa.rwo.area})))
_endloop
_endblock
I've run a simple query using IN operator both in sql2_parser and sql() procedure. But it takes too long time. Anyone can help me how to tune up sql2_parser so that can run faster? I've already created the index for all fields to be called in the query.
MagikSF> p << sql2_parser.new_with_properties(:soc, gis_program_manager.spatial_object_controller(:gis), :dataset, gis_program_manager.cached_dataset(:water))
$
p.parse_sql("select * from premise_table where premise_id in ('000049') ")
$
res << p.process_sql()
$
Post a Comment