com.smartgwt.client.docs

Interface SqlDataSource

public interface SqlDataSource

SQL DataSources

The Smart GWT Server supports comprehensive, codeless SQL connectivity for DataSources defined in XML. Our SQL connectivity is mature, feature-rich, protected against injection attacks and easily customizable to support user-written SQL and additional business logic of any complexity. This article compares the built-in SQL DataSource to other persistence approaches based on Javabeans.

To use the built-in SQL engine, declare a DataSource in XML format with DataSource.serverType set to "sql", and place it in the shared dataSources directory ([webroot]/shared/ds by default) in a file called "[dataSourceId].ds.xml".

You can then use the Admin Console to configure database access, as well as to automatically create and populate a database table based on your DataSource. By default, DataSources will use the "default database" from the admin console, however you can set DataSource.dbName to the name of a specific database configuration you have configured via the Admin Console.

The list of databases known to work with the the built-in SQL logic is as follows:

	HSQLDB 1.7.x, 1.8.x, 2.0.x, 2.2.x, 2.3.x, 2.4.x
	IBM DB2 8.x, 9.x, 10.x, 11.x
	IBM DB2 for i (formerly known as DB2 for i5/OS) V5R4M0 onwards
	Firebird 2.5.x
	Informix 11.5+, 12.1
	MS SQL Server 2000, 2005, 2008, 2008 R2, 2012, 2014, 2016, 2017
	MySQL 3.2.x, 4.0.x, 4.1.x, 5.0.x, 5.1.x, 5.5.x, 5.6.x, 5.7.x
	MariaDB 5.x, 10.x
	Oracle 8.0.5, 8i, 9i, 10g, 11g, 12c, 18c
	PostgreSQL 7.x, 8.x, 9.x, 10.x

We also support a generic SQL92 database connection which works for basic CRUD operations with any database product that supports standard SQL92 syntax and data types, plus a couple of widely-implemented features that are not actually part of the standard. Specifically, this means we do not support:

• Sequences
• Paging via SQL limit queries
• Automatic transaction management
• Long text values (there is no real definition of "long" here - we try to use a standard VARCHAR, but different databases will support different maximum values for this)
• Databases that do not implement the widely-supported LOWER() function
• Databases that do not support the ability to perform string-type operations on numeric columns - for example, myNumericColumn LIKE '%5%'

You will also need a JDBC driver for your specific database. Licensing restrictions prevent us including any JDBC driver other than the one for HSQLDB. However, you can download these drivers for free from the vendors' websites. If your specific database server or version is not listed above, please go to the Smart GWT forums for assistance.

You can also connect to an existing database table - the Visual Builder DataSource Wizard ("New" button in DataSources pane) allows you to pick an existing database table and have a DataSource generated from it. The Batch DataSource Generator tool will allow you to generate DataSources for any number of SQL DataSources, and customize the process so that organization-specific rules are applied (eg ignoring certain columns, or using naming conventions to generate more complete DataSources).

Once you have your SQL DataSource connected to a table, in a default SDK installation, DSRequests for your DataSource will be sent to the default actionURL, and hence handled automatically, without you having to write any Java code, by the IDACall servlet registered in web.xml. IDACall is a very simple servlet that just calls the server-side method dsRequest.execute() for all inbound requests. For more details on how DataSource requests are processed by Smart GWT Server, and how you can alter and add to this processing, see this description of server data integration.

See Also:

DataSourceField.getSequenceName(), DataSourceField.implicitSequence