Descartes ShipRush Web uses ADO to connect to databases. ADO has been part of Windows for several years and is the successor to ODBC. ADO is the perfect method to connect to SQL Server, Access, Oracle, MySQL, and nearly all other contemporary database systems. ADO can also be used to access ODBC data sources that accept regular SQL syntax (e.g. SELECT / UPDATE / INSERT / DELETE queries).
A confusing fact is that ADO is not called ADO in Windows. If you have ever seen this screen, you were actually configuring an ADO data source:
Fetching Data from Your Database
The SQL Webstore metaphor is simple: Return fields with the names expected by Descartes ShipRush Web. For example, you have a field in your database named 'Company'. Descartes ShipRush Web wants this field termed 'CompanyName', Therefore, your SQL reads:
select Company as CompanyName from ...
The SQL Webstore will only 'see' data that conforms to its expected field names. The corresponding fields in the database can have any name.
Posting Back Data to Your Database
Writing to the database is more flexible because multiple SQL statements can be used. When multiple SQL statements are used, each statement must be separated by the 'GO' command on its own line. This mechanism is supported on all database systems (not just SQL Server.)
SQL Webstore Merge Codes
SQL Webstore 'merge codes' are placed in your SQL statements, allowing simple SQL to do real work. For example, if a new GUID is needed when inserting a new record, use the %GUID% merge code. This causes the SQL Webstore to generate a new GUID and merge it into the SQL statement at run time. For example, this posts back the tracking number:
update orders set ShippingTrackingNumber = '%TRACKINGNUMBER%'where ordernumber = %RecordID%
Descartes ShipRush Web then performs merging and passes something similar to this to the database:
uppdate orders set ShippingTrackingNumber = '76938493898’where ordernumber = 1001
In a Microsoft SQL Server environment, you can also use Transact-SQL scripts in the write-history SQL. Click here for script examples.
Note : See also How the SQL Webstore Gets Data.