Arcview can access external data sources through the SQL Connect dialog. This is a pretty easy thing to do, go to the File menu on the Project GUI, then to SQL Connect.

After you select the connection you might have to browse to a database or you might be asked to login. The tables’ list will reflect ONLY the tables that you have permission to access. Double click on the table name and it will move to the from: text line, then select, double click, the columns you want to include in the table. At this point you could click the Query button and you will create a table. Or you could add some logical expressions in the where: text box, like the example above.

If I were to write the above in a straight SQL statement it would look like this:

Select * from Master_Accident_Dbase where Day = ‘Wed’

The * means all fields. If I wanted specific fields it would look like this:

Select CaseNum, Location, Injuries from Master_Accident_Dbase where Day = ‘Wed’

Connecting to ODBC data sources using Avenue:

The following examples are connecting to Oracle & MSAccess.

The first thing to do is login/connect to the ODBC data source.

Access:

conSQLmdb = SQLcon.Find("MS Access 97 Database")

conSQLmdb.Login("DBQ=\\caribbean2\st.thomas\0traffic\accident\2000\2000 accident tracker.mdb")

Oracle:

SQLConnection = SQLCon.Find("TM_PERMIT")

SQLConnection.Login("SRVR=ppln;UID=avuser;PWD=jack")

TM_Permit is the name of an ODBC driver, in the connection string ppln is the name of the database I am connecting to.

After you connect/login you can use the Find request to reacquire that connection without having to re-login. So it is probably a good practice to put the connection/login code in a Startup script.

There are two other Avenue requests to become familiar with,

SQLCon.ExecuteSQL and Vtab.MakeSQL

SQLCon.ExecuteSQL will immediately execute an SQL statement on the database.

You can add, delete or insert records into a data table. Also it is possible to create new tables. See the attached scripts for some examples.

Vtab.MakeSQL will return selected records from the database into an ArcView virtual table object. After these records are returned you can join them to a shape file, geocode or if you have x,y’s , use that to map the information.

An SQL statement in Avenue is just a big string object. Get familiar with the string class and how to create complex, appended strings. Here are two examples of SQL statements generated to return records.

Sample:

 strTableSQL = “Select * from”++strTableName

vtbTable = VTab.MakeSQL(SQlConnection, strTableSQL)

Very complex:

strDateSQL = "select a.csa_assigned_to, a.action_description, b.bld_permit_type, d.vlv_desc, b.bld_develp_type,"++

"c.csm_st_nmbr||' '||c.csm_st_name address, a.csa_time2, a.csa_id, a.csm_caseno,"++

"c.prc_parcel_no"++

"from case_action a, case_bld b, casemain c, validation_values d"++

"where a.csm_caseno = b.csm_caseno(+)"++

"and b.csm_caseno = c.csm_caseno(+)"++

"and b.bld_permit_type = d.valid_value(+)"++

"and a.csa_date2 = To_Date('"+strDate+" 00:00:00', 'RRMMDD HH24:MI:SS')"++

"and a.csa_calendar_tag = 'INSP'"++

"and a.csa_date3 is null"++

"and a.csm_caseno Like 'BLD%'"++

"and a.csa_report_tag <> 'ENGS'"++

"and a.actn_code <> ANY ('FEN', 'ESO', 'ELV', 'PUB', 'FRM', 'MGD', 'MGL', 'MGT', 'SEWD', 'SEW', 'SEWI')"++

"and d.valid_key = 'Permit Type'"

vtbInspect = VTab.MakeSQL(SQlConnection, strDateSQL)

In the above statement the (+) symbol means OuterJoin the two tables.

Syntaxes used in SQL differ between databases. For example, in Access you need to put # signs around any date. Make sure you are familiar with the nuances of SQL in the databases you are using.

Here is an example of creating a list of unique values from a field in a table, this connects to an MSAccess database.

conSQLmdb = SQLcon.Find("MS Access 97 Database")

'conSQLmdb.Login("DBQ=\\caribbean2\st.thomas\0traffic\accident\2000\2000 accident tracker.mdb")

 strAcTypeSQL = "SELECT * FROM AccidentType"

 vtbAcType = Vtab.MakeSQL(conSQLmdb, strAcTypeSQL)

 lstAcTypes = {}

 for each rec in vtbAcType

   strAcType = vtbAcType.ReturnValueString(vtbAcType.FindField("AccidentType"), rec)

   lstAcTypes.Add(strAcType)

end

lstAcTypes.RemoveDuplicates

lstActypes.Sort(true)

Well, I hope this document helps you get started with Database connections and SQL in ArcView. If you have any questions you can contact me at:

By:

Robert Meier, GIS Analyst

City of Overland Park, Kansas

8500 Santa Fe Dr

Overland Park, KS  66062

913/895-6203

rmeier@opkansas.org