Custom Queries are defined and a sample is provided starting on page 28 of the documentation. If you have not yet looked there:
How To: Custom Queries
Custom Query sources provides the ability to select data for your points from external sources. The base requirement for this is the simple fact that your external source will provide its own Longitude and Latitude. Please refer to the Map_Points table which defines the require columns. Your external source query MUST result in the required data structure. Additionally, with Custom Query you may add any number of Query Variables to be consumed within the Query. This provides the ability to create a robust and interactive search mechanism or direct lookup and plotting of your records based on incoming parameters from your user.
To use an existing source for your data that will not be managed by the Map Points table, or to provide some extended functionality of the incoming forms and parameters for your pages (like custom filtering), you can create a Custom Query data source.
First – in the Data Sources page, select Custom Query. This can only be specified when the user is a Super User, otherwise, you cannot manipulate or gain access to the SQL interface.
Next, specify the Query. The only requirement of the query is that it return a table structure that contains all of the existing columns expected from the Map_Points table. You may include other columns, but the default columns are required specifically. Any incoming variables you will need can be setup with the Query Editor region. It will use a standard String Replacement technique for using your variables, so make sure that the Target of the variable and the value of your source are setup properly.
If you data source is external, you may use any ODBC or OleDB connection string to connect to that external source.
The provided list below is a breakdown of example Connection Strings from well known Database Management Systems.
Access
· Default
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\path\dabasename.mdb;User Id=admin;Password=;
· Password Protected
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\path\dabasename.mdb;Jet OLEDB:Database Password=passwordValue;
Active Directory
· Provider=ADSDSOObject;User Id=Username;Password=PasswordValue;
AS/400 (iSeries)
· PROVIDER=IBMDA400; DATA SOURCE=SOURCE_SYSTEM;USER ID=Username;PASSWORD=PasswordValue;
DBF / FoxPro
· Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\path;Extended Properties=dBASE IV;User ID=Admin;Password=OptionalPasswordValue;
DSN
· Default
DNS=DsnName;Uid=Username;Pwd=OptionalPasswordValue;
· File DSN
FILEDSN=C:\Path\File.dsn;Uid=Username;Pwd=OptionalPasswordValue;
Excel
· Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Path\FileName.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1
Firebird
· User=Username;Password=PasswordValue;Database=DatabaseFileName.fdb;DataSource=ServerName;Port=3050;Dialect=3;Charset=NONE;Role=;Connection lifetime=15;Pooling=true;MinPoolSize=0;MaxPoolSize=50;Packet Size=8192;ServerType=0;
IBM DB2
· Provider=DB2OLEDB;Network Transport Library=TCPIP;Network Address=IP ADDRESS;Initial Catalog=SourceCatalog;Package Collection=SourceCollection;Default Schema=Schema;User ID=Username;Password=OptionalPasswordValue;
Informix
· Provider=Ifxoledbc.2;password=OptionalPasswordValue;User ID=Username;Data Source=DatabaserName@Server_Name;Persist Security Info=true;
Ingres
· Provider=MSDASQL.1;DRIVER=Ingres;SRVR=SERVER;DB=DATABASE;Persist Security Info=False; uid=UserName; pwd=OptionalPasswordValue; SELECTLOOPS=N; Extended Properties="SERVER=SERVERNAME; DATABASE=DATABASENAME; SERVERTYPE=INGRES"
Interbase
· provider=sibprovider;location=server:;data source=c:\path\dabasename.gdb;user id=UserName;password=OptionalPasswordValue
MySQL
· Provider=MySQLProv;Data Source=DatabaseName;User Id=Username;Password=OptionalPasswordValue;
Oracle
· Default (Microsoft)
Provider=msdaora;Data Source=OracelDatabaseName;User Id=Username; Password=OptionalPasswordValue;
· Default (Oracle)
Provider=OraOLEDB.Oracle;Data Source=OracelDatabaseName;User Id=Username; Password=OptionalPasswordValue;
· Trusted Connection
Provider=OraOLEDB.Oracle;Data Source=OracelDatabaseName;OSAuthent=1;
Paradox
· Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\DatabaseName;Extended Properties=Paradox 5.x;
Pervasive
· Provider=PervasiveOLEDB;Data Source=C:\Path
SQL Server
· Provider=sqloledb;Data Source=DataSourceName;Initial Catalog=DatabaseName;User Id=Username;Password=OptionalPasswordValue;
· Provider=sqloledb;Data Source=IPADDRESS,1433;Network Library=DBMSSOCN; Initial Catalog=DATABASENAME; User ID=Username; Password=OptionalPasswordValue;
Sybase
· Adaptive Server Anywhere (ASA)
Provider=ASAProv;Data source=ASADataSourceName
· Adaptive Server Enterprise (ASE)
Provider=Sybase.ASEOLEDBProvider;Srvr=SERVERNAME,5000; Catalog=DATABASENAME;User Id=Username; Password=OptionalPasswordValue
Text
· Delimited Columns
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Path\;Extended Properties="text; HDR=Yes; FMT=Delimited”
· Fixed Columns
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Path\;Extended Properties="text; HDR=Yes; FMT=Fixed"
Visual FoxPro
· Database
Provider=vfpoledb.1;Data Source=C:\Path\FileName.dbc;Collating Sequence=machine
· Table Directory
Provider=vfpoledb.1;Data Source=C:\Path\;Collating Sequence=general
· ODBC DSN
Provider=vfpoledb.1;DSN=DSNNAME
How to use the Custom Query
As provided in the above screen, a simple example of the use of the Custom Query functionality is presented. Two Query Variables were created that persist the Latitude and Longitude of the current Map view to display points relative to that area.
Type - The following types of variables can be utilized:
Session – values contained in the session. The Source consists of the key value f the session variable (Case Sensitive).
Querystring – values contained in the incoming request url. The Source consists of the key value from the query string parameter (Case Sensitive).
form – values contained in the incoming form request. The Source consists of the NAME value of the form element from the request. Be sure that NAME is the source of your element and not the ID value.
TabID / ModuleID / PortalID / UserID – parameters provided directly by DotNetNuke. Source is ignored for these attributes.
Latitude / Longitude – the coordinates on the interface that are currently the center of the map.
Target – the value that will be replaced within your query with the value bound to your Type/Source. The default use of this is via SQL-like syntax. For example: @Latitude.
Left / Right – If you variable source contains a value – the output of that value will be placed between the Left and Right values. For example – single quotes for non numeric values. When the value is empty, the left and right are not consumed.
Empty Value – when your source contains NO value, the output is provided via the Empty Value.
Security – this enforces the check for SQL Injection. It is recommended that this is always used. The end behavior of this is that all variables replace single quotes with escaped single quotes.