Applies To:
  • CitectSCADA 6.XX
  • CitectHMI 6.XX
Summary:
After using DSN-less connection strings I am getting an error once I run a query. This query seemed to work when using DSN connections.

After a detailed analysis I have discovered that once I run a select query to choose a Field, and once I try to execute another query based on that returned data I get an error that states that this field does not exist.

See example below:

/* Make a connection to an SQL server and select the name field
from each record in the employee database. */

FUNCTION
ListNames()

    INT hSQL;
    STRING sName;
    INT Status;

    hSQL = SQLConnect("Driver={Microsoft Access Driver (*.mdb)};Dbq=\\SomeServer\MyDataBase.mdb");

    IF hSQL <> -1 THEN
        Status = SQLExec(hSQL, "SELECT NAME FROM EMPLOYEE");
        IF Status = 0 THEN
            WHILE SQLNext(hSQL) = 0 DO
                sName = SQLGetField(hSQL, "NAME");
                . . . 
            END
            SQLEnd(hSQL);
        ELSE
            Message("Error", SQLErrMsg(), 48);
        END
    SQLDisconnect(hSQL);
    ELSE
        Message("Error", SQLErrMsg(), 48);
    END
END

In the code above after the connection is made, and the field NAME has been selected, when Citect tries to execute the SQLGetField function indicating the NAME field, an error returns that the field ("NAME") does not exist in the table

 

Solution:
When using the DSN-less connection string;
The select query returns the NAME field data in what is a somewhat virtual field..

The data can still be read or written to.  After you have run the select query it will be necessary to change the referenced field (“NAME”) to “FIELD1” in the SQLGetField function the code worked.

So the example above would be changed to:

hSQL = SQLConnect("Driver={Microsoft Access Driver (*.mdb)};Dbq=\\SomeServer\MyDataBase.mdb");
IF hSQL <> -1 THEN
    Status = SQLExec(hSQL, "SELECT NAME FROM EMPLOYEE");
    IF Status = 0 THEN
        WHILE SQLNext(hSQL) = 0 DO
            sName = SQLGetField(hSQL, "FIELD1");

Making this change should eradicate your problem.

 
Keywords:
 

Attachments