Conversion Guide¶

Datetimes in Dataflex 20.0¶

The eSQL interface used to pass and return Datetimes as strings. There were attributes related to the format that these were passed and returned. All of this is no longer supported. Datetimes are now passed as native Datetimes. There is one major side effect to be aware of due to this. DataFlex (as of version 20.0.7) has a bug when Datetimes are moved into variants that causes them to lose millisecond precision. This can be seen when looking at data returned via f2s_esql_get_row. The milliseconds are actually being returned, but DataFlex isn’t able to read them. There are two workarounds for this we’ve implemented. One is the f2s_ExactDT function. This function is passed a variant array and an index to the column that is a DateTime within that variant array. It returns a Datetime variable that has millisecond precision. It is used like this:

f2s_esql_get_row vRow
Showln "variant array DateTime value = " vRow[1]
Showln "Exact Datetime Value = " (f2s_ExactDT(1, vRow))

This code demonstrates how you can get the Datetime WITH millisecond precision. Note that since this is operating on a variant array, the index passed is zero based. There is another way as well using a new command: f2s_esql_get_element. This command returns the value in a column of the current row of the result set. Unlike f2s_ExactDT, this command is one based (which is to help maintain compatibility with existing code):

f2s_esql_get_row vRow
Showln "variant array DateTime value = " vRow[1]
Showln "Exact Datetime value = " (f2s_ExactDT(1, vRow))
f2s_esql_get_element 2 to dtResult
Showln "Also exact Datetime value = " dtResult

When passing a Datetime as a parameter to an eSQL call, it is best to avoid moving the DateTime to a variant as DataFlex will lose the millisecond precision. If you pass it as a Datetime data type it will work.

Stored procedure/stored function parameters¶

In previous version of our driver, special care had to be taken to define not only the total count of parameters to a SP/SF, but also the direction of those parameters. With this new version of our driver you no longer need to do this and in fact can’t do this. The driver will determine on it’s own the type, count and direction of the parameters on it’s own. Any code that was using out or RSET parameters will need to be modified in recognition of this. OUT and RSET parameters can’t be set using f2s_esql_set_proc_param. You can get the out value using f2s_esql_get_proc_param after executing the SP/SF.

The method to deal with RSET parameters in Oracle has been changed. Instead of getting the next resultset, you will need to use f2s_esql_get_proc_param which will return a resultset interface as a variant. This variant is then passed to the f2s_esql_set_resultset command which will make that interface the “current” resultset. From there the standard resultset related commands can be used.

Prepared statement re-execution¶

Very often, developers will need to call the same procedure over and over again in their code. The way this was done in prior version was to set the statement, set the parameters, and execute your eSQL, then get the results. This pattern still works, but because of our easier to use interface this pattern, if used in a loop can result in slower execution. This is because as part of the prepare process, we now analyze your eSQL to determine the number, type and order of parameters. This requires an additional roundtrip to the server to do. Although this slows things down somewhat, it enables another feature that can make your code much faster.

If instead of creating a new statement each time, you instead create a statement and prepare that statement using our new “with” syntax, you can then call that statement over and over again with different parameters. To do this, after executing, just change the parameter values and execute again. When your statement is re-executed, because everything is already configured for it in the driver already, you’ll end up with even faster execution times. In our testing, using this new pattern is over twice as fast as the old pattern was in prior driver versions! Note though that the old pattern, if left in place and used in a tight loop can be many times slower than in prior versions.

Cleaning up after yourself¶

Although you are still able to make eSQL calls in most places without worrying about closing that statement, there are some cases where this can cause problems now. As a general rule of thumb, it is best to close a statement after you’re done with it. If you don’t close it, there are certain points where the driver will close it for you. But if the driver is unable to determine if it is safe to close a statement, that statement will be left open. This can cause issues in certain transactions. One area to be especially cautious is where an eSQL call returns multiple resultsets in the Flex2SQL for MS SQL driver. This can cause transaction code to fail if the statement isn’t closed properly beforehand or during the transaction. A workaround for this is to use a server cursor for this statement which is isolated from the transaction.

Inverse key column support¶

The new unicode drivers no longer support inverse keys at all. Inverse keys served a few purposes. In older versions of Oracle and even Microsoft SQL Server there wasn’t support for descending or uppercase indexes. Inverse keys gave our drivers a way of supporting this often used feature of DataFlex DAT files. Additionally, they were used so that a primary key could be uppercase or descending and as a speed optimization.

Over the years we’ve worked to remove support for inverse keys because their implementation was complex and confusing for outside developers that needed to access the tables containing these keys. Triggers had to be used to update the inverse key column to make things work with DataFlex correctly and the values in the inverse key column were often unintelligible.

With the release of the unicode driver, we’ve decided to completely remove this feature. The way they were implemented required the driver to have knowledge of the collation sequence so as to reverse it. This isn’t practical in Unicode because the collation is so large and is not fixed. Unicode characters can have modifiers that can change their sort order. There are rules for this, but implementing a system that tracks the differing rules for each collation on each server and each server version is really not possible.

The impact of this is that if your application was using inverse key columns, you’ll need to modify your table structure and possibly application code to remove this requirement. In general a standard index can now do what inverse key columns used to do, and they can do it quickly. There are a few places that are more complicated. For instance, if a primary key is based on an inverse key column, you’ll need to fix this. Oracle doesn’t require applications to have primary keys and you can use a function-based index instead.

If you have foreign key relationships based on inverse key columns, you may need to go a bit deeper to fix things. Oracle doesn’t support foreign key constraints on function-based indexes. Although there is a workaround for this using the virtual column from the function-based index, implementing this in the driver is impractical and you will be better served by fixing this relation to be based on a surrogate key.

You can identify the inverse keys in your INT files by the column names and the token INVERSE_KEY. An inverse key column will always end in _INVD (Descending), _INVC (UpperCase), or _INVB (Descending and Uppercase).

f2s_esql.pkg¶

This package provides a new object oriented interface for esql use. For now this is NOT the primary interface we advise using, but rather something you can take a look at. The interface consists of a number of classes for interacting with our new esql interface: cESQLCommand, cESQLParam, cESQLParams, cESQLRecordSet, cESQLColumn, and cESQLColumns. The basic usage is very simple allowing a code pattern such as:

Handle hCmd hRS
Variant[][] vRows

    Get f2sNewCommand \_f2s_ActiveDriver to hCmd
    Set f2sCommandText of hCmd to "select * from TestTable where someUCData = ?"
    Send f2sPrepare of hCmd
    Set Param of hCmd 1 to "🌿🍁🌱"
    Send f2sExecute of hCmd
    Get NextRecordSet of hCmd to hRS
    Get Rows of hRS 10 to vRows

See the online documentation for more details on how to use this new optional interface.

cMertechImageParser.pkg¶

This package is meant to make using old-style Dataflex Images more useful for recent versions of Dataflex. At the end of this package, an object is created automatically that is available in the global variable mdsImageParser. The primary function you’ll use for this object is ImageToString. This function takes an image name and returns a string containing all of the text of this image. This can be a very useful way to access large embedded SQL statements without having to use line continuations. For example consider this old style embedded SQL statement:

Move ( "SELECT TOP 10 "                                                                 + ;
"B.GUID, B.LTYPE, B.BRANCH_NO, B.REF_NUMBER, B.DEPART_DATE, B.FROMCITY, N.NAME "        + ;
"FROM BILLTO AS B "                                                                     + ;
"JOIN NEWCUST AS N ON B.ACTUAL_NO = N.CUSTOMER_NO AND B.ACTUAL_SITE = N.CUSTOMER_SITE " + ;
"WHERE B.REF_NUMBER >= ? AND B.BRANCH_NO >= ? ORDER BY B.REF_NUMBER , B.BRANCH_NO" ) to sSQL

In comparison, you can have a much more readable section of code loading this same SQL statement into a variable using this new package:

/SomeSQL
SELECT TOP 10 B.GUID, B.LTYPE, B.BRANCH_NO, B.REF_NUMBER, B.DEPART_DATE, B.FROMCITY, N.NAME
FROM BILLTO AS B
JOIN NEWCUST AS N ON B.ACTUAL_NO = N.CUSTOMER_NO AND B.ACTUAL_SITE = N.CUSTOMER_SITE
WHERE B.REF_NUMBER >= ? AND B.BRANCH_NO >= ? ORDER BY B.REF_NUMBER , B.BRANCH_NO
/*

Move (ImageToString(mdsImageParser, "SomeSQL")) to sSQL

This package isn’t limited to use with just our driver, but is available for your use anywhere within your application. See the online documentation for more details on using this new optional helper package.

cMertechStructAnalyzer.pkg¶

This is an updated version of the package we released in 2014 for working with structs in DF. At the end of this package, an object is created automatically that is available in the global variable mdsStructAnalyzer. This new version has three new functions that can prove very useful for simplifying your code: LoadStructFromArray, LoadStructFromBuffer, and LoadStructFromDD. When working with our new eSQL interface, the LoadStructFromArray is very useful as it allows you to take a row from a result set and load an array. For this to work, the member elements of the structure must map to the array elements in order. As an example:

Struct tCustomer
    String CustName
    String Addr
    String City
    String State
    String Zip
    Number Balance
End_Struct

Procedure TryIt
    Variant vRow
    tCustomer cust

    f2s_esql_create_stmt "select name,address,city,state,zip,balance from customer where balance > ?"
    f2s_esql_prepare_stmt
    f2s_esql_set_param 1 to 1000
    f2s_esql_execute_stmt
    If (not(err)) Begin
        f2s_esql_get_row vRow
        Get LoadStructFromArray of mdsStructAnalyzer cust vRow to cust
        ..do something with the struct..
    End
End_Procedure

As you can see here, it is trivial to load a customer row from the result set into the structure. The only unusual requirement is that you pass a structure into the function as well so that the function knows what type of structure it’s loading. The easiest way to do this is to pass the same structure as a parameter that is used as the return value as well (they must be of the same type).

The LoadStructFromDD and LoadStructFromBuffer functions work in a similar manner, although both of these functions are smart enough to match the names of the columns and element names in the structure instead of requiring data be in some specific order. See the online documentation for more details on using this new optional helper package.

Changing your collation & migrating your data¶

For MS SQL, your database has a default collation, and columns can use this default collation or they can override the collation on a column-by-column basis. When you change the collation of your database, it DOES NOT change the collation of existing tables. Only new tables will inherit the new collation. This means that you need to change your database collation AND convert your existing tables to use the new collation. This can be done in multiple ways, including in-place conversion, recreating your tables and copying data, or copying data to an entirely new database. See this page for more information.

Choosing a collation¶

SQL Server has well over 1000 collations available. In general collations are thought of as a way of sorting data. But collations actually define more than just the order in which data is sorted. They also (in the case of non-unicode data) determine how binary values are rendered as characters, and in the case of unicode they can also define what is treated as a character and if data should be treated as UTF-8 or ANSI. Various collations have different settings related to how characters are weighted based on things like language, accent sensitivity, supplemental character support, case sensitivity, Kanatype sensitivity (for Japanese), width sensitivity, binary order and more. Choosing which collation to use for your database is not much different than it used to be. It was a complex topic before, and it’s a complex topic now.

In the USA, the collation you’ll probably want is SQL_Latin1_General_CP1_CI_AI. In fact, there’s a good chance you are already using this collation. This collation uses codepage 1252 for non-unicode columns and by being accent insensitive, it makes words like “café” findable using the search term “cafe”. You might notice that this collation doesn’t have the “_SC” indicating support for supplementary characters. This doesn’t mean you can’t store these characters; it just means that from a sorting perspective they’ll be treated as two separate byte-pairs and sorted accordingly (which means the sorting might not make sense for users). If you need these supplementary characters to sort in a manner that a user might expect (think about the keyboard on iOS for emoji for instance), then you probably want the collation Latin1_General_100_CI_AI_SC (at least in the USA). Your scenario could be much more complicated than this though. Customer requirements might mean you need to use the accent sensitive version of either of those collations (replacing the “_AI” with “_AS”).

The key to remember is that collations apply across unicode aware and non-unicode columns. What you probably DON’T want is a binary sort collation as your default. Although this sort often makes sense to developers, it is rarely what you actually want since it doesn’t take into account the reality of sorting in most countries.

Also remember that columns can have their own collation different from the database collation and that how you build your SQL statements affects the collation as well.

Note that we haven’t advised using a “_UTF8” based collation. These collations are only available in SQL Server 2019 and newer. These allow for storing UTF-8 based data in varchar columns. Mertech’s drivers don’t currently support this.

Converting char/varchar to nchar/nvarchar¶

As can be seen from the preceding information, the conversion process is not cut and dry. In general, converting a column can be done with a simple alter table to alter the column type, but internally this is doubling the allocated space for that column. This can create a large log file and can use quite a bit of storage. Alternatively if your clustered index contains a column that is being converted, using the rebuild option on the alter table statement can actually end up being faster overall. If you don’t use the rebuild option, consider doing a cleantable afterwards.

The process you use to convert to unicode depends on the size of your database, the uptime requirements, the amount of storage available for the conversion, and the amount of data actually being converted. The approach you take might even vary from customer to customer for an ISV.

Modifying your SQL Statements¶

The documentation for DataFlex addresses this topic already. But one area to emphasize is how things can vary based on collation. You’re able to specify a collation to use in a query statement, and you’re also able to indicate if a string for a query statement should be treated as unicode or ANSI. This combination of column collation, string format, and query collation can cause unexpected results in some cases. In general you’ll best be suited by taking an all or nothing approach. Leaving some columns as varchar that could store ANSI characters above character 127 (in other words, code page specific characters) can cause a lot of headaches (barring the example given above of a column like zipcode that). Try not to do this.

Choosing a Collation¶

Choosing the right collation is imperative to keep your application working as expected when you move to Unicode. Prior to switching to Unicode your collation was probably set to either a binary collation or a country specific collation. In a binary collation, your data was sorted based on the binary value of the character code. For English speaking setups this was often “good enough” but was unlikely to be used in other countries where the binary order would sort letters in their alphabets incorrectly. Some older applications might have even had dependencies on binary sorting to help find the highest and lowest records. If your application made use of this logic, you should update your application to fix this if possible.

The collation you choose can vary greatly depending on where and how your application is used. If your application is primarily used in one country, then a monolingual sort might be ok. If your application is international, then a multilingual sort is probably best. Additionally there are all the classic considerations for collations as well, such as case sensitivity and accent sensitivity. You might consider a monolingual collation (such as FRENCH or FRENCH_CI) over a multilingual collation (such as GERMAN_M or GERMAN_M_CI) because there is a small speed boost provided by the simpler sorting algorithm employed in a monolingual collation. If you’re only ever operating in one country and don’t care about things like canonical equivalence and supplementary character sorting, then a monolingual sort might be best. For the United States, a binary sort (BINARY_CI_AI or just BINARY) is actually the default and best sort to use for a monolingual setup. In the US, if you want the benefits provided by a multilingual sorting system, then choosing a UCA collation might be best.

UCA (Unicode Collation Algorithm) is a special set of collations that attempts to provide decent sorting for nearly all countries. It is a little slower than the monolingual and multilingual sorts due to its complexity. It supports supplementary characters and there are a few language specific variants available in recent versions of Oracle Database (such as UCA0700_CFRENCH for Canadian French). Not every language has its own variant of the UCA collation as the root sort works fine for most languages. In the US on Oracle 12c, you might choose UCA0700_ORAROOT_CI_AI (the base collation is case sensitive and accent sensitive, this one would remove those sensitivities). Note that you SHOULD NOT choose UCA0700_ROOT or UCA0700_DUCET as both of these can cause an error in rare circumstances related to key expansion. In Oracle 21c there is a newer revision of the UCA available that you should use: UCA1210_ORAROOT. The UCA collations support a limited set of collation parameters.

Converting to a Unicode Database Character Set and Collation¶

Oracle does not allow you to change the character set of an existing database easily. This means that you normally have to migrate your database to a unicode character set by recreating it. Oracle has a utility called the Database Migration Assistant for Unicode that can help you do this. It is beyond the scope of this guide to go into the use of this utility. Oracle has a guide on database globalization that goes into a ton of detail on all aspects of this topic.

Client NLS_LANG Setting¶

It is very easy to set NLS_LANG on the client to something that will result in incorrect data being stored on the server. For instance, if NLS_LANG is set the same on the client and server, then no conversion is done by the database. If the client doesn’t actually send data using the code page specified in NLS_LANG, then invalid data will be stored in your database. It will look fine to you, but from other places it will be wrong and it can make the conversion to unicode difficult. The Database Migration Assistant for Unicode has a CSREPAIR script to help you fix this situation if you have it.

But for your new AL32UTF8 database, the question is, what should your client be set to? NLS_LANG can define three pieces of information: [NLS_LANGUAGE]_[NLS_TERRITORY].[NLS_CHARACTERSET]. So in the United States you probably want to set NLS_LANG to AMERICAN_AMERICA.AL32UTF8.

If you are still using non-unicode Dataflex applications against an AL32UTF8 database, then you’ll need to change the NLS_LANG for those applications to specify the code page on the client, which in the United States is very likely AMERICAN_AMERICA.WE8MSWIN1252. This will allow the non-unicode application to work against a unicode database, but data loss could occur if the non-unicode software modifies rows that contain unicode data as that data cannot be read or written by the client. In that case, those unicode characters will be replaced with question marks when returned to the client and then if that same data is written back to the database it will have those question marks (which are now ACTUAL question mark characters) instead.

Locale¶

Your database locale controls a variety of preferences about your database including the alphabet used, sorting, number formatting, etc. The locale is by default read from the operating system itself. If your server OS has a locale set correctly to your requirements at the time your database is created, then your database will have the same locale. An example of a locale is Portuguese as spoken in Portugal (pt_PT) or Portuguese as spoken in Brazil (pt-BR). In the United States we commonly see the locale “en-US” (English as spoken in the United States). Your OS must support the locale you choose.

To complicate matters, PostgreSQL supports locale providers. Most locales in recent versions of PostgreSQL use the ICU provider which is a good thing (especially since our driver and Dataflex also use ICU). There are still some that use the libc provider (such as POSIX and C). Unless you know exactly what you’re doing, there is no reason to adjust any of this.

Sometimes you may need to specify a different setting from what the locale has defined. Thats where we get into the specific settings and what they do.

Encoding¶

The encoding of characters in your database is normally picked up from the operating system. This is why Mertech has advised changing the default character encoding when your database is created. Most modern OSs and PostgreSQL version will automatically choose a UTF-8 encoding which in the past was incompatible with our driver. Now for the Unicode drivers you need to be UTF-8 encoding. Although there are many database encodings that support multi-byte characters, just choose UTF-8. There is really very few reasons on a new database you would want anything else.

Collation¶

Collation is very important as it determines how Postgresql sorts data. This of course affects your indexes and possibly the expectations of your users. Although a database will use the collation preference by default, if you have the need to collate a certain index differently for some reason, you are able to do so in PostgreSQL. Doing this can cause some weird interactions with our driver though that might cause rows at the beginning or end of your data set to be missed.

In general, the collation preference that is associated with a locale will work well for you unless you know that this isn’t the case for some specific reason. For instance, if you had software for a call center in Australia that primarily serviced the USA (that’s a stretch, but bear with us), the collation might be en-AU (English as spoken in Australia). This is a VERY similar collation to en-US but has (among other small differences) the “Þ” character sorted after the letters “T” and “t”, while in en-US it is sorted as the last character. If an application had logic to find the last record based on finding less than a string of Þ’s it would miss a good amount of records when the collation was set to en-AU while finding everything as expected when using en-US.

CType¶

CType is a preference that shouldn’t matter all too often. It relates to character classification (what is a letter and does that letter have an upper or lowercase equivalent, is it a number, a control character, a blank space, etc). It is closely related to the locale in practice and althought it can be changed independently of the locale, there is very little reason to do so. Having a CType different from the locale could cause unexpected search behaviour and has not been tested.

Data types¶

Unlike Oracle or MS SQL Server, PostgreSQL has very few restrictions related to their character data types. There are no special names to indicate they store unicode and no special restrictions related to the storage size. The size of a character(n) column is n characters. That value might consume n+1 bytes or it might consume (n*4)+4 bytes. As a developer you needn’t worry about this aspect of your database at all. It is worth nothing that PostgreSQL supports a varchar() and text data type, neither of which have a specified size. These columns can hold long strings with an upper limit of about 1 GB.

Converting to a Unicode Database¶

The advice you may have received from Mertech to use the SQL_ASCII encoding with PostgreSQL made things very easy at the time. You were able to get your system up and running very quickly. Unfortunately, using SQL_ASCII is a very bad thing as you move to Unicode. SQL_ASCII encoding is a special encoding on PostgreSQL that basically means “no encoding”.

Whatever is sent via the client to the server is stored as-is. When data is returned nothing is done to compare the client encoding to the server encoding, the data is just sent. This worked well in the early days of the driver where clients might be sending OEM or maybe ANSI encoded using some specific windows codepage. If your PostgreSQL server was *nix based you didn’t have to figure out what the equivalent encoding was (if there was one). If you were in the United States or parts of Europe where almost all databases use the basic Latin character set, everything “just worked”.

The downside to a SQL_ASCII encoding is that the server has no idea what is actually in your database though, which means that the normal way you’d use to convert your database to Unicode (pg_upgrade) won’t work. Additionally, over many years of putting data into your SQL_ASCII encoded database, there is a decent chance that data with multiple encodings has snuck in there. If this is the case for you, a SQL_ASCII encoded PostgreSQL database, it will require some manual data massaging. This blog <https://www.endpointdev.com/blog/2017/07/postgres-migrating-sqlascii-to-utf-8/> post outlines the process.

Locale¶

The locale in MySQL is not used for anything unicode related. It is only used for time and dates. The system variable lc_time_names controls this. The FORMAT() function doesn’t even use the locale and instead takes a locale as an optional third parameter. The default locale no matter what your operating system is set to is en_US.

Encoding¶

MySQL has nchar and nvarchar columns. DO NOT USE THESE! These columns have an encoding of utf8 which is internally utf8mb3 which is deprecated and does not support supplementary characters. Instead use the normal char/varchar columns you have always used and an encoding of utf8mb4 which is now the default. Although MySQL supports utf16 and utf32, our unicode driver is tested against utf8mb4 only.

Collation¶

Collation is very important as it determines how MySQL sorts data. This of course affects your indexes and possibly the expectations of your users. In MySQL, the collation can be specified (much like the encoding can also) at the server, database, table or column level. The more specific object for which it is specified will take precedence. So by default the server collation will be used unless a database collation is specified which overrides the server collation. This same pattern continues on all the way down to the column level.

The collation defines not only the sort order of characters, but also whether upper/lower case characters are treated equally or if one has a higher precidence. Also, the collation will determine if accented characters affect the sort order or if they are treated the same as unaccented characters. The collation you choose is one spot where MySQL and MariaDB differ.

In MySQL utf8mb4_… collations should be used. Note that you most likely want to avoid the collations ending in bin as these don’t follow language based rules and you probably want to follow the collating rules of SOME language. Also the utf8mb4_general_ci has some oddities you’ll want to avoid. For the US we’ve found the utf8mb4_0900_as_cs (or the case insensitive/accent insensitive version thereof) works best. The collations with 0900 in the name are faster and based on UCA 9.0.0. All UCA 9.0 based collations all have a PAD SPACE attribute of NO PAD which means that in these collations the spaces at the end of strings are NOT taken into account for data collation.

In MariaDB (and to a lesser degree MySQL) there isn’t access to the same level of UCA support as other databases. This has a couple of consequences. First, there isn’t proper sorting (as of v10.9.3) for sorting of Supplementary characters (those which take 4 bytes to encode). This means there are some strings that are totally different which could cause a duplicate record error if they contain completely different supplementary characters as their only attribute on which they can be differentiated by the MariaDB collator. So for instance ‘😀😅🙃’ is weighted the same as ‘𝐀𝐁𝐂’ (These are called Mathematical Alphanumeric Symbols and only LOOK like “ABC”) and could cause a duplicate record error if these were values placed into a column with a unique constraint.

Data types¶

There are no special names to indicate a column can store unicode and no special restrictions for unicode related to the storage size. The size of a CHAR(n) column is n characters. That value might consume n+1 bytes or it might consume (n*4)+2 bytes. As a developer you needn’t worry about this aspect of your database at all.

Converting to a Unicode Database¶

For MySQL and MariaDB you’ll need to do a full database dump (forcing the dump to utf8). Beyond that, you’ll also have to modify the dump file to define all the tables/columns/defaults as utf8 as well (even if you specify utf8 on the commandline, mysqldump will still output the character set settings that are in place for any tables/columns). After that you’ll have to reload the entire database. The details of this process could be significantly more complex if you have data that happens to be in multiple encodings. It is beyond the scope of this document to detail every situation and the conversion to Unicode should be down carefully and thoughtfully based on your specific circumstances.