Native connections in SQLWindows, is there a way to send NULL instead of blank spaces in SQL without extra variable?

Question

Due to many inconveniences caused by the ODBC driver, regardless the database management system (SQL Server 2000 in my case), I want to change my application connection type from ODBC to native.

SQLWindows offers this feature, but with a huge gap. Using ODBC driver, when a field in the form is blank, it sends NULL to be recorded in the database. Thanks to the configuration parameter setzerolengthstringstonull=on, in the sql.ini file. However, using native connections, it sends a blank string ' ' instead, and that, of course, causes a variety of inconsistencies and errors depending on which table or foreign keys the column is related. I don't know how to reproduce the parameter setzerolengthstringstonull in a UDL file or change it internally.

Is there a way to configure that correctly in the application, or even intercept the SQL command before running (in a generic way, not before each SQL) so I can manually change blank values to NULLs?

I know that SQLWindows documentation suggests me don't send fields directly in SQLs, but create extra variables, check if those fields are blank or not, set the variables to either the values in the fields or STRING_Null, and send the variables in SQL. That is impossible in my situation due to the immense corporative size of my application. That would require simply rewrite almost everything.

---

I wrote two minimal examples of how things are different between them:

This works in OBDC, it inserts an user in table tblUser, supposing that column UsrEmail looks for emails in a tblEmail. And dfUsr[something] are controls in MyForm.

INSERT INTO tblUser (
    UsrName,
    UsrEmail
)
SELECT
    :MyForm.dfUsrName,
    :MyForm.dfUsrEmail

To do the same in native connections, I have to create a String variable named sUsrEmail (or whatever), and execute the following code before the specific SQL below

if SalIsNull(MyForm.dfUsrEmail)
    sUsrEmail = STRING_Null
else
    sUsrEmail = MyForm.dfUsrEmail

Even the query is different:

INSERT INTO tblUser (
    UsrName,
    UsrEmail
)
SELECT
    :MyForm.dfUsrName,
    :sUsrEmail

Answer 1

In Sql.ini you can use the substitute= setting. This setting is used by the native connection driver provided by Gupta for every DB-Client communication.

The setting must be placed within the appropriate section in sql.ini. I.e. when using an Oracle DBMS, the setting has to be inside [oragtwy] The setting has been made for substituting DBMS specific commands.

Example:

[oragtwy] substitute=@UPPER,UPPER

In the example, mentioned above the setting is being considered by the native connector when connected to an oracle DBMS and every time it finds a Statement having "@UPPER" inside (which is SqlBase specific), it substitutes that string with UPPER (which is used in oracle)

So in your case, the setting should look like this:

substitute='',

I am using it constantly when working with oracle databases.

Answer 2

I dont know how to change global, but in instruction you can use:

INSERT INTO tblUser (
    UsrName,
    UsrEmail
)
SELECT
    :MyForm.dfUsrName,
    NULLIF(:MyForm.dfUsrEmail, '')