Does Iron Speed Designer (ISD) use username or domain\username in the user table?

When using Windows Authentication with Iron Speed Designer (ISD) you can use a user table to specify who can access your application. I often forget what the username column in Iron Speed Designer (ISD) is supposed to contain.

The confusion in my mind is that Windows authentication requires a domain to be specified. This may not be apparent if using IE, but with other browsers such as Firefox you will be prompted for your credentials and required to put the username in the format: domain\username. In the case of ISD, it appears that Windows authentication is still handled by IIS / Windows / Active Directory as it is in any other ASP.NET application that uses Windows Authentication. Once the user is authenticated though, ISD queries the database tables that you specify. In these queries it uses just the username portion of the domain\username you typed (or IE passed on for you). I don't think this would work very well if your network has multiple domains, but I have not confirmed this.

So, to put it simply, store just the username (no domain) in the column in the user table you specify.

Oracle APEX Review

Overview

Oracle APEX = Oracle Application Express

Been around since 2004 under various names

Browser Based development and deployment

Think of it as a replacement for MS Access, but on the web and multi-user with an Oracle backend.

RAD tool for the Oracle Database; think Forms over data

Declaratively build web 2.0 applications

Leverage SQL and PL/SQL skills.

Multi-tenant Hosting

Departmental Solutions is focus

The Oracle store is built with APEX

Lots of options for Authentication or even custom

Built-in support to prevent URL tampering

20 canned themes or can create your own, including one for starting mobile.

Wizards to create forms including master detail, etc, but has basic layout, functionality, etc.

Create web services with wizard. A web service can be created from a region also.

Can add regions to a page and add most anything to the region including forms, etc.

Cost

Fully supported by Oracle

Free with Oracle Database (include Oracle XE)

Skill Set Required

PL/SQL

SQL

Skill Set Required for Customization

HTML

JavaScript

CSS

Advantages

Robust migration path for Oracle Forms application to APEX.

Very easy to generate data-driven CRUD data entry style applications that include simple reporting.

Migrating MS Access, or Excel files to a multi-user web environment

Similar to SharePoint lists such that you can create tables, and UI based on an Excel spreadsheet.

Free with Oracle database license

RAD or demo or POC

Built-in themes

Customization done using standards such as JavaScript, HTML, and CSS.

Disadvantages

Debugging can be painful. No breakpoints,

Business Logic written in PL/SQL. No layers such as a business layer and data access layer. SQL or stored procedures are referenced directly.

Web based development environment  feels a bit clumsy and slow.

Tightly bound to PL/SQL and Oracle.

You must work within the paradigm that is defined by APEX. If the application doesn't fit the paradigm the effort drastically increases. For instance javascript and html/css become the way of working.  This is a much different skillset. Alternatively, plug-ins can be created to extend APEX.

Migration path from MS Access to APEX is limited and works on with simple

The time you save in development time can be quickly lost in debugging, support, and customization.

No version control 

Architecture

• Web Browser
• Apache with mod_plsql/EPG web listener
• Application Express
• Meta Data

KEY FEATURES according to a Oracle APEX Specialist

FRAMEWORK

The APEX framework uses SQL and PL/SQL on the back-end, and HTML, CSS, and JavaScript for the user interface. SQL and PL/SQL are solid and proven languages and they allow APEX developers to leverage the features of the Oracle database. HTML, CSS, and JavaScript are industry standard components for building web applications.

SUPPORT FOR MULTIPLE AUTHENTICATION SCHEMES

APEX supports various authentication schemes such as LDAP, database, Single Sign on, Oracle Access Manager, custom, etc.  This makes it convenient to integrate with any existing applications and authentication systems.

DECLARATIVE MOBILE APPLICATION DEVELOPMENT

APEX is bundled with the popular jQuery mobile libraries and provides declarative support for building mobile applications. A special mobile theme provides APEX mobile applications with typical mobile features such as page transitions and gestures including swipe, pinch, and tap. Custom mobile themes can easily be created with the jQuery mobile theme roller.

RESPONSIVE DESIGN FOR OPTIMAL VIEWING ON DESKTOP, TABLET AND SMARTPHONES

APEX applications can be rendered on desktops, tablets, and smartphones by choosing a theme based on a responsive design. APEX includes a set of modern themes that are based on CSS3 and HTML5, and supports HTML5 charts, and HTML5 item types such as sliders and toggles.

PACKAGED APPLICATIONS – A FULL DEVELOPMENT SUITE

APEX is bundled with a suite of business productivity applications that can be freely used to assist with the management and control of projects. The suite contains applications for bug tracking, issue logs, checklist management, meeting minutes, group calendar, decision management, document management, and project management.

RESTFUL WEB SERVICES

APEX has built-in support for RESTful web services and allows applications to access data and services over the internet or intranet using standard web APIs. Database web services that implement SQL or PL/SQL can also be created. RESTful web services in APEX requires the APEX Listener, a J2EE based alternative for the Oracle HTTP server and mod_plsql.

DATABASE INTEGRATION

APEX is a component of the Oracle database and applications build with APEX can utilize or benefit from any feature of the database such as advanced security, RAC, Spatial, Analytics, Multimedia, XML DB, Job Scheduler, utility packages, etc.  APEX uses SQL and PLSQL to interact with the Oracle host database.

GLOBALIZATION SUPPORT

Applications build in APEX can run concurrently in different languages. Applications are developed in a primary language and can be mapped to a supported target language. Strings in the primary application are exported to a XML Localization Interchange File (XLIFF) where they are translated and imported and automatically used by the translated application.

TEAM DEVELOPMENT

Team development is a built-in feature that allows a group of developers, working on a single application, manage new features, to dos, bugs, and milestones. Users of an application can provide instant feedback which can then be classified as feature, to do, or bug.

EXTENSIBLE

The APEX development framework supports plugins that allow developers to extend the functionality of their applications with reusable custom or third-party extensions. APEX applications can also be extended with custom HTML, CSS, or JavaScript.

OTHER FEATURES

APEX is also stacked with other utilities and features that greatly improve developer productivity throughout the life of a project.

Version Control Support – Applications can be automatically exported into SQL script files where they can be included in a version control system. APEX provides an application exporter utility as well as an application splitter utility for splitting application into individual page scripts.

Error Handling – Developers can create a single error handling function that handles exception consistently across all pages in an application.

Oracle Forms Migration Tool – If Java/ADF is not for you, APEX is a viable option for converting Oracle Forms applications. APEX includes a forms migration tool to assist with the migration of Oracle Forms applications.

Accessibility – APEX applications have automatic built-in accessibility support. Applications can be rendered in high contrast mode or screen reader mode in order to meet accessibility requirements.       

Utility Reports – APEX has a comprehensive list of reports that provide real-time information on applications. Some of the more useful reports are:

• Change History – List of changes made by developers
• Advisor – Quality control review of an application
• Database Object Dependencies – List of database objects used by an application
• Debug Messages – List of debug messages generated by an application
• Recently Updated Pages – List of pages that were recently updated

Features

Reports

Forms

Charts

Calendar

Templates

Navigation

Validations

Processes

Computations

Branches

Web Services

Email Services

Translation Services

Conditional Processing

Authentication

Authorization

Session State Management

Logging & Monitoring

Interactive Reports

Integrating with Other Tools / Services

SQL

PL/SQL

RAC

Spatial

OLAP

Flashback

Web Services

Text

Multimedia

Analytic Functions

Globalization

XML DB

eBusiness Suite

Migration to APEX

Direct Excel Conversion

MS Access Conversion Support

Oracle Forms Conversion Support

Security

Popular authentication supported as well as custom

Can hide columns based on user access level

Screenshots of the development environment

Built-in User Management

List of applications in the workspace

List of all pages in an application

Design a page

Options for creating a new application

Add a page

Create a Form

Sample User Interface

of the application created from a Spreadsheet

This is an interactive report. We can add charts, group by, add aggregate columns, filter, highlight, export to CSV, re-order columns, hide columns, save report, etc.

Conclusion

Oracle APEX may be a good choice depending on your needs. Here are some key criteria for this to be a good choice:

• Oracle database is your database of choice
• You know SQL and even better you know PL/SQL
• Your application is essentially a CRUD or forms over data applications or data-centric application
• You want to do RAD
• You don't want to write lots of code
• For customization you will need JavaScript and HTML/CSS knowledge
• For writing Business logic and customizing the application you will need PL/SQL knowledge
• To get the largest savings in time you will want to use the predefined forms and wizards, but this is not required.

It also means you are willing to give up some of the more common development practices

• Having separate tiers for business logic and data access. Instead you must want your business logic to be in the database in packages.
• IDE that runs on your pc
• Robust debugger
• Unit tests
• Version control (Yes you can export files and them to version control, but it isn't quite the same thing in my opinion)
• If you want to do OOP or MVC this is
• Drag n drop files from your desktop into the IDE
• Less control over files in your project

If the above criteria is okay with you then Oracle APEX is worth looking at. You get a lot of functionality. Similar to what you would get with SharePoint, MS Access, or meta-data tools, but with different strengths and weaknesses as noted above.

References:

Introduction to Oracle Application Express (APEX) Video

Oracle APEX - A Feature-Rich Development Platform you should be using

I keep getting the error  has not been registered" when I try to use the Import Data functionality in SQL Server Management Studio (SSMS) with a .xlsx Excel file as the data source.

The OLE DB provider Microsoft.ACE.OLEDB.12.0 has not been registered.

Here is my environment:
Windows 7 (64-bit)
SQL Server 2008 R2 (64-bit)
MS Access 2010 (64-bit)
MS Office Excel 2010 (64-bit)

I do NOT get the above error when I use the import wizard in MS Access 2010 to import my Excel (.xlsx). It only happens with SSMS. I tried installing the different Data Connectivity Components. Some would say it was already installed, other would complain that it wouldn't work with the 64-bit software I was using. In the end the only one that would install that helped was the following:

2007 Office System Driver: Data Connectivity Components

So frustrating that I had to waste time on something that should just work after installing SQL Server 2008 in my opinion.

Removing duplicate rows in SQL Server

If you are using SQL Server 2005 or greater (we need the CTE (Common Table Expression) functionality) you can in one statement delete duplicate rows from a table. You can look at all columns in the table or you can look at a subset of the columns. The example below uses just one column (EmailAddress), but you can replace that one column with a comma separated list of the columns you want to consider.

Before we go off and start running delete scripts on our data, it is always a good idea to make sure you have a backup of the table so you can check you work after or restore if something goes wrong.

Let's first look at the data

-- see what rows have duplicates and which ones don't
SELECT  EmailAddress
      , row_number() OVER ( PARTITION BY EmailAddress ORDER BY ID) AS NumInstances
FROM    Person

-- show only the rows that are duplicates
with myTable as (
SELECT  EmailAddress
      , row_number() OVER ( PARTITION BY EmailAddress ORDER BY ID) AS NumInstances
FROM    Person
)
select * FROM myTable
    WHERE   NumInstances > 1

The actual delete statement

-- do the actual deleting of the duplicate rows
with myTable as (
SELECT  EmailAddress
      , row_number() OVER ( PARTITION BY EmailAddress ORDER BY ID) AS NumInstances
FROM    Person
)
DELETE  FROM myTable
    WHERE   NumInstances > 1

To modify the code to fit your situation you will typically just need to replace the items in red with the comma separated list of columns you want to consider. Next change the table name in blue to the table you are trying to remove the duplicates from. Finally, change the column in orange to be one or more columns (separated by comma). In this case, I have a primary key so, I used it. You could use all the columns you are considering as well.

For more info on how it works and pre-SQL 2005 solutions see here.

GEOTAB review

I recently had the pleasure of reviewing GEOTAB GPS System. They have given a lot of thought into making their platform accessible via their API and their Architecture is very nice. 

Recommendation / Summary

After reviewing their website in detail and attending a demo for GEOTAB, I would technically recommend this product.  The ability to integrate with their product is strong, scalable, and well designed. Their proprietary hardware has one of the lowest failure rates in the industry (or so they say) and provides additional information that adds value to the data. To get the best features, you should use their proprietary hardware. The system is extensible from a hardware and software perspective. You can readily integrate with their GEOTAB. 

Hardware

GEOTAB has proprietary hardware, but also works with third party hardware. Their proprietary hardware appears to be superior to the competition because it uses accelerometers as well as GPS. This allows it to accurately be able to identify drivers that hard brake at low speeds, identify drivers that are distracted, etc. It has a high compatibility with most every vehicle. Installation is very easy (3 minutes they say) to install the device by plugging it into the data port of the vehicle. The hardware can be extended to interface with additional sensors, driver identification via RFID, and Garmin devices. With the Garmin devices two communication be done.

Reports/Monitoring/Dashboards

They have 30 reports, dashboards and pride themselves on productivity monitoring / reporting. Most vendors appear to record data at a periodic interval, but they do not. The reason is that some key data can be missed. This is actually a big benefit in recording the accuracy of the data.

API

The API is available in C# and JavaScript, but can also be called by any language that can make a HTTP GET or POST request via HTTPS (and most any language can). The API appears to be robust and they have a web farm so it should be scalable as well. The API is well documented and has many examples. There is a data integration Windows application they provide the source code for that extracts data from GEOTAB. It could easily be extended to import the data into your databases. You should be able to use this API to integrate with GateHouse GPS Portal if you want to or store the data in a database or file system. You can programmatically add users, devices, etc via the API as well.

GEOTAB Software Extensibility

Their web application UI can be extended using JavaScript to add calls to your own systems, or tweak their pages. The pages are unique urls so linking to their pages should be relatively easy.

Email notifications can easily be customized by users, but notifications can also be sent as texts. Their UI can also be embedded in your systems as well.

MADP changes from 2013 to 2014 according to Gartner

In the world of MADP (Mobile Application Develop) things change very fast. In fact, in April 2012 Gartner said, "Designers have to make complex trade-offs between native, hybrid and Web-oriented mobile architectures. From 2003 through 2009, Gartner observed that the majority of high-value mobile applications were written as native, but that began to change around 2009 as techniques for wrapping Web technologies emerged. These techniques create hybrid mobile applications (wrapped applications, where the container is native code, but the experience leverages the WebView capability of the OS) or as mobile Web applications. In addition, some Web-oriented applications began to offer HTML5 features, such as advanced rendering and local storage, that previously were only available within the native style.

This caused a rapid shift of focus for many enterprises toward Web-oriented techniques — so much so that, based on our surveys conducted in 2011, 40% of enterprise application developers were still targeting native first for a variety of reasons (such as performance and disconnected mode). This migration will continue for two to three more years, and we predict that, by 2015, 80% of all mobile applications developed will be hybrid or mobile-Web-oriented."

After reading about MADPs, I decided it might be useful to visually see how the players have changed from April 2012 to August 2013. So, I took the Magic Quadrant charts from 2012 and 2013 and superimposed them into one chart, added arrows from 2012 to 2013 and consolidated the labels to produce the diagram below.

NOTE: The ones in red are new in 2013. The ones in gray disappeared in 2013 from the rankings.
Some observations:

Antenna - didn't see much change in score, but is a pretty strong Leader.
Kony and Adobe - are neck and neck and both moved from a strong Visionary to Leader.
Appcelerator - They have the strongest vision out of all the companies. They need a little work on executing before they will be a Leader though.
Salesforce.com - was Niche and has moved into a strong Challenger; nearly a leader with a tad bit more vision.
jQuery - moved from fairly weak Visionary to a Leader because of their improved Execution. They still seem to be a bit light on the vision though.
IBM - moved from Niche player to a strong Leader. They lead in second on both execution and vision. They could be a good choice overall.
SAP - stayed in the Leader circle, but has gained some execution, but at the expense of losing some vision.
Google - stayed in the Niche player square, but has significantly increased their vision.
Blackberry - stayed in Niche player square, and really just lost its ability to execute. Not much hope on this one I think.
Microsoft - stayed in the Niche player square, and gained a little vision, but at the expense of execution.
Apple - stayed in the Niche player square, and gained noticeable amount of vision, but again at the cost of execution.

I am very curious what the 2014 Gartner report will bring.

Thank you to all for your support

Just geeks has reached over 1 million hits since I started tracking hits. This is very exciting! Thank you all for visiting my blog.