Parameterized Queries

When you’re working with data-bound fields, the SLX Provider handles names or other data with apostrophes—like “O’Reilly”—or fields with Null values in them, without you needing to worry about it.

If you’re doing explicit string SQL Inserts or Updates, however—or using fields that could have apostrophes/nulls in them in your WHERE clauses—you’re probably used to passing the field values through Replace functions, and either testing for Nulls or kludg-ily concatenating empty strings onto the end of each field.

If you use parameterized queries instead of SQL strings, you can go back to not worrying about stuff like that.

Sage actually uses exactly that method in the OTB “System:SLX Lead Support” script, but it’s full of a lot of other unrelated code. In bare-bones terms, parameterized queries in VBScript look like this:

Dim strCommand
Dim objParams
Dim objCmd
Dim objRs

...

Set objCmd = CreateObject("ADODB.Command") 'DNL
Set objCmd.ActiveConnection = Application.GetNewConnection

objCmd.CommandType = adCmdText
objCmd.Prepared = True
strCommand = "SELECT Lastname FROM Contact " _
     & "WHERE AccountID = ? AND IsPrimary = ?"
objCmd.CommandText = strCommand

objCmd.Parameters.Refresh
Set objParams = objCmd.Parameters

objParams.Item(0).Value = strAccountID
objParams.Item(1).Value = blnIsPrimary

Set objRs = objCmd.Execute

...

Set objRs  = Nothing
Set objParams = Nothing
Set objCmd = Nothing

It works exactly the same way for Inserts and Updates, just (obviously) without the objRs recordset.

If you’re getting weird errors, the Very First Thing to check is that the number of question-marks in the command string is equal to the number of parameters you’ve added.

And if you’ve ever wondered, the “DNL” that you see all over the place in SalesLogix OTB code stands for “Do Not Localize”—i.e., for strings which are just used internally and cannot be translated into other languages without having hugely detrimental effects on the program, as opposed to UI strings which can/should be localized into the user’s preferred language.

So it’s not just some developer’s initials. 🙂

SQL for Migrations

If you don’t have a fancy tool like Scribe lying around, or if you need to work around its shortcomings/bugs/features, or if you simply need to import into a single table and need more flexibility than the Import Wizard gives you, you can do that in straight SQL, through WG Admin.

First, in Database Manager, set the primary key of the table you’ll be importing into, to be auto-increment. (Right-click on the field, select Properties…, and check the Field is Auto Increment checkbox.)

Then, use SQL like the following to build a result set that contains the SQL INSERT strings (this is for transferring all of the Account and Contact Address records from one SLX database to another, but the concept would obviously be the same for any other sources or tables):

SELECT 'INSERT INTO Address (ENTITYID, TYPE, DESCRIPTION, ADDRESS1, ADDRESS2, CITY, STATE, POSTALCODE, COUNTY, COUNTRY, ISPRIMARY, ISMAILING, SALUTATION, CREATEDATE, CREATEUSER, MODIFYDATE, MODIFYUSER, ROUTING, ADDRESS3, ADDRESS4, TIMEZONE, OLDADDRESSID) VALUES ('
+ '''' + CASE WHEN SUBSTRING(ENTITYID, 1, 1) = 'C' THEN ISNULL((SELECT TargetKey FROM sysdba.XREFCONTACT WHERE SourceKey = sysdba.Address.EntityID), '') WHEN SUBSTRING(ENTITYID, 1, 1) = 'A' THEN ISNULL((SELECT TargetKey FROM sysdba.XREFACCOUNT WHERE SourceKey = sysdba.Address.EntityID), '') END + ''', '
+ CASE WHEN TYPE IS NULL THEN 'NULL' ELSE '''' + REPLACE(TYPE, '''', '''''') + '''' END + ', '
+ CASE WHEN DESCRIPTION IS NULL THEN 'NULL' ELSE '''' + REPLACE(DESCRIPTION, '''', '''''') + '''' END + ', '
+ CASE WHEN ADDRESS1 IS NULL THEN 'NULL' ELSE '''' + REPLACE(ADDRESS1, '''', '''''') + '''' END + ', '
+ CASE WHEN ADDRESS2 IS NULL THEN 'NULL' ELSE '''' + REPLACE(ADDRESS2, '''', '''''') + '''' END + ', '
+ CASE WHEN CITY IS NULL THEN 'NULL' ELSE '''' + REPLACE(CITY, '''', '''''') + '''' END + ', '
+ CASE WHEN STATE IS NULL THEN 'NULL' ELSE '''' + REPLACE(STATE, '''', '''''') + '''' END + ', '
+ CASE WHEN POSTALCODE IS NULL THEN 'NULL' ELSE '''' + REPLACE(POSTALCODE, '''', '''''') + '''' END + ', '
+ CASE WHEN COUNTY IS NULL THEN 'NULL' ELSE '''' + REPLACE(COUNTY, '''', '''''') + '''' END + ', '
+ CASE WHEN COUNTRY IS NULL THEN 'NULL' ELSE '''' + REPLACE(COUNTRY, '''', '''''') + '''' END + ', '
+ CASE WHEN ISPRIMARY IS NULL THEN 'NULL' ELSE '''' + REPLACE(ISPRIMARY, '''', '''''') + '''' END + ', '
+ CASE WHEN ISMAILING IS NULL THEN 'NULL' ELSE '''' + REPLACE(ISMAILING, '''', '''''') + '''' END + ', '
+ CASE WHEN SALUTATION IS NULL THEN 'NULL' ELSE '''' + REPLACE(SALUTATION, '''', '''''') + '''' END + ', '
+ CASE WHEN CREATEDATE IS NULL THEN 'NULL' ELSE '''' + CONVERT(varchar(20), CREATEDATE) + '''' END + ', '
+ CASE WHEN CREATEUSER IS NULL THEN 'NULL' ELSE '''' + 'ADMIN' + '''' END + ', '
+ CASE WHEN MODIFYDATE IS NULL THEN 'NULL' ELSE '''' + CONVERT(varchar(20), MODIFYDATE) + '''' END + ', '
+ CASE WHEN MODIFYUSER IS NULL THEN 'NULL' ELSE '''' + 'ADMIN' + '''' END + ', '
+ CASE WHEN ROUTING IS NULL THEN 'NULL' ELSE '''' + REPLACE(ROUTING, '''', '''''') + '''' END + ', '
+ CASE WHEN ADDRESS3 IS NULL THEN 'NULL' ELSE '''' + REPLACE(ADDRESS3, '''', '''''') + '''' END + ', '
+ CASE WHEN ADDRESS4 IS NULL THEN 'NULL' ELSE '''' + REPLACE(ADDRESS4, '''', '''''') + '''' END + ', '
+ CASE WHEN TIMEZONE IS NULL THEN 'NULL' ELSE '''' + REPLACE(TIMEZONE, '''', '''''') + '''' END + ', '
+ '''' + ADDRESSID + ''');'
FROM sysdba.Address
WHERE ENTITYID IN (SELECT AccountID FROM sysdba.Account)
OR ENTITYID IN (SELECT ContactID FROM sysdba.Contact)

Run that SQL statement in Management Studio. When the result set appears, right-click, and Select All. Then right-click again, and Copy.

Open Notepad, and Paste from the clipboard into a blank file.

Save the file with ANSI encoding (not Unicode!). Note: If you instead right-click on the SSMS result set and select “Save Results As…”, it will save them as Unicode; so if you’re going that route, you’ll still need to open the file in Notepad, and re-save it as ANSI encoding.

In WG Admin, go to Tools -> Execute SQL…, then right-click over the SQL statement box, and Load Script. (If it shows up as just a few character of gibberish, you saved the Notepad file as Unicode.)

Scroll down to the bottom of the grid, and delete the final (blank) row. Then Test/Execute the SQL.

Clear the auto-increment flag on the PK field.

Note: If any of the fields you’re bringing across have semicolons anywhere in their data, that will crash the transfer: WG Admin automatically starts a new row whenever it’s loading a file and encounters a semicolon. To work around that, before you save the Notepad file, search and replace all of the “);” characters in the file into “):” strings; then manually replace each of the remaining semicolons with another string that doesn’t occur anywhere else in the file (e.g., “.,.”); then change the “):” strings back into “);” strings. After the import, Update the field in question, changing its “.,.” strings back into “;”.

Of course, if your valid data has the “);” string anywhere inside it, you’ll need to work around that, too.

😉

Tips and Tricks

Below, I’ve gathered some Miscellaneous Tips and Tricks for customizing SLX (some of which are borrowed from Stephen Redmond’s DevLogix books):

• When using a DateTimeEdit control, bind the DateTime property to the database, but use the Text property (<> “”) to check for whether a value has been entered
• You can only Enable Field-Level Security when you’re initially creating a table; there’s no supported way to turn it on later
• You can’t directly show a Memo field in a DataGrid … but if you assign the grid’s SQL in a string, in code, you can use the SQL CONVERT function on the field, to cause it to display in the grid, e.g.,

SELECT CONVERT(varchar(4000), MyMemoField) As A1.MyField, ...

• To prevent a Data Form from closing when its OK button is pressed, in the AXFormValidate event, set AXFormValidate = False
• To load a custom form for entering search criteria for a lookup, create the form (with data-bound fields) and assign it to the lookup’s View Name property
• To iterate through the selected records in a multi-select grid:

For i = 0 to DataGrid1.Selection.Count - 1
     ... DataGrid1.Selection.Item(i) ...
Next

• To show/hide columns in a grid, either format works:

dgContact.Columns(2).Visible = False
dgContact.Columns("Workphone").Visible = True

• To execute the OTB “stored procedures” in SLX, pass them to the CommandText property of an ADODB.Command object:

Set cn = Application.GetNewConnection
Set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = cn
cmd.CommandText = "slx_DBIDs('ACCOUNT', 10)"
cmd.CommandType = adCmdText
Set rs = cmd.Execute
...

• To use dates in SQL statements:

strSQL = "SELECT Field1, Field2 FROM MyTable
WHERE StartDate > '" & Application.DateToISO(Now() - 30) & "'"

• Redmond: “It is good practice to add a hidden edit box containing the key field that joins the data table [e.g., the grid on a tab] to the main view…. Without this value on the form, the record is not created automatically when you save the Add/Edit form…. Some people have suggested that this field needs to be the first control created (you can control creation order, if you add the field later, by right-clicking on the form and select ‘Creation Order…’ from the menu).”
• To set (and show) the current ID for a Main View:

' Loads default Main View for Contact table:
Application.MainViews.GetViewForRecord strContactID, "CONTACT"
Application.MainViews.AddEx "System:Contact Details", _
    1, True, 1, ContactID, ""

Or:

Set MV = Application.MainViews.Add("System:Account Details", 1, True)
MV.CurrentID = strAccountID
MV.Show

To get the ID:

strID = Application.MainViews.ActiveView.CurrentID