Converting SQLDA from DB2 to PostgreSQL

Question

I have the following code that currently works against DB2.

There's the SQLDA declaration...

extern struct sqlca sqlca;
struct{struct sqlda daNM;
       struct sqlvar vaNM[6];
       } C_NM={"SQLDA  ",280,6,6,
                 492,   8,(char*)&NMTBL.namn,                0,4,"NAMN",
                 452,   1,(char*)&NMTBL.ssncd,               0,5,"SSNCD",
                 497,   4,(char*)&NMTBL.ssn,    &NMTBL.Fssn   ,3,"SSN",
                 448,  65,(char*)&NMTBL.lna,                 0,3,"LNA",
                 449,  46,(char*)&NMTBL.fna,    &NMTBL.Ffna   ,3,"FNA",
                 449,  80,(char*)&NMTBL.nm2ln,  &NMTBL.Fnm2ln ,5,"NM2LN"};

struct sqlda *pNM = (struct sqlda*)&C_NM;

Then there's the cursor declaration...

EXEC SQL DECLARE C_NM_# CURSOR FOR SELECT NAMN, SSNCD, SSN, LNA, FNA, NM2LN FROM ASL.NMTBL
 WHERE NAMN = :Q_namn FOR FETCH ONLY;

And finally a method that opens, fetches and closes the cursor.

 void fetch_name_nd(__int64 namn,__int32 commit)
 {  /** fetch name with no display **/
 struct sqlca ret_sqlca;

 Q_namn = namn;
 EXEC SQL OPEN C_NM_#;
 if (sqlca.sqlcode != 0)
    show_error("Error in opening nametable in 'REA_LIB'",1);
 memset(&NMTBL,0,sizeof(NMTBL));
 EXEC SQL FETCH C_NM_# USING DESCRIPTOR :*pNM;
 if (sqlca.sqlcode != 0)
    if (sqlca.sqlcode == 100)
       show_error("Couldn't find the correct nam# in NMTBL",1);
 else show_error("Error in Fetching from the NMTBL",1);
 ret_sqlca = sqlca;
 EXEC SQL CLOSE C_NM_#;
 if (sqlca.sqlcode != 0)
    show_error("Error in closing table after reading name",1);
 if (commit)
    {
    EXEC SQL COMMIT;
    if (sqlca.sqlcode != 0)
       show_error("Error commiting after fetching name from Database",1);
    }
 sqlca = ret_sqlca; /** copy value from fetch **/
 }

At the end of that method I have the database values available in a struct to use wherever I'd like, NMTBL.lna, etc.

I've read through https://www.postgresql.org/docs/current/static/ecpg.html and https://www.postgresql.org/docs/current/static/ecpg-variables.html, it all seems very similar to DB2.

However https://www.postgresql.org/docs/9.1/static/ecpg-descriptors.html loses me when it comes to my SQLDA definitions and usage.

Is there anyone that uses similar SQLDA descriptors to query data in embedded SQL C/C++ programs against a PostgreSQL database that might be able to advise?

Answer 1

It appears ecpg does not allow you to pre-define where the cursor results will go. In the end, rather than pre-defining an SQLDA structure that would place the cursor results in my desired struct, I wrote a function to fill the desired struct from the dynamic sqlda that ecpg filled.

For example, now I just define an sqlda pointer

 sqlda_t *sqlda_ap;

Then I fetch my cursor into that

EXEC SQL DECLARE c_ap CURSOR FOR SELECT COL1, COL2 FROM SCHEMA.TABLE WHERE PK=1234;
EXEC SQL OPEN c_ap; 
EXEC SQL FETCH c_ap INTO DESCRIPTOR sqlda_ap; 
EXEC SQL CLOSE c_ap;

Then I call my function to pull the results out of the sqlda pointer and fill my struct.

processSqlda_APbuf(sqlda_ap);

This walks through each of the members of the sqlvar[] and pulls out the corresponding values.

void processSqlda_APbuf(sqlda_t* sqlda)
{
    if (sqlda == NULL)
    {
        printf("can't continue - sqlda is null");
        return;
    }
    for (int i = 0; i < sqlda->sqld; i++)
    {
        if (memcmp(sqlda->sqlvar[i].sqlname.data, "codes", 5) == 0)
        {
            memcpy(APbuf.codes, sqlda->sqlvar[i].sqldata, 4);
        }
        if (memcmp(sqlda->sqlvar[i].sqlname.data, "adrn", 5) == 0)
        {
            APbuf.adrn = *(long long int*)sqlda->sqlvar[i].sqldata;
            APbuf.Fadrn = *sqlda->sqlvar[i].sqlind;
        }
        if (memcmp(sqlda->sqlvar[i].sqlname.data, "nam", 4) == 0)
        {
            APbuf.nam[0] = *(long long int*)sqlda->sqlvar[i].sqldata;
            APbuf.Fnam[0] = *sqlda->sqlvar[i].sqlind;
        }
        if (memcmp(sqlda->sqlvar[i].sqlname.data, "aslkey", 6) == 0)
        {
            memcpy(APbuf.aslkey, sqlda->sqlvar[i].sqldata, 6);
        }
    }
}

That lets me access the values from the struct as I did originally, so the rest of the program remains unchanged. If that happens to save someone else some time down the road you're welcome. :) Chances are there really aren't that many people migrating from DB2's embedded sql to PostgreSQL's ecpg embedded sql...but oh well.

Update: Here's a bit of code to take an old SQLDA struct (DB2 style) and generate a function to read a dynamic SQLDA and fill the originally-used struct. You just need a window with two text boxes, txtOld and txtNew.

    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }

        private void txtOld_TextChanged(object sender, TextChangedEventArgs e)
        {
            var old = txtOld.Text;
            var n = new StringBuilder();
            var structName = "bogus";

            foreach (var line in old.Split(new[] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries))
            {
                var words = line.Split(',');
                if (words.Length != 6 && words.Length != 7)
                    continue;

                var dataType = int.Parse(words[0]);
                var dataLen = int.Parse(words[1]);
                var dest = words[2].Replace("(", "").Replace("char", "").Replace(" ", "").Replace("*", "").Replace(")", "").Replace("&", "");//UGLY!
                var nullIndicator = words[3].Replace("&", "").Trim();
                var colLen = words[4];
                var colName = words[5].ToLower();

                if (structName == "bogus")
                    structName = dest.Split('.')[0];

                switch (dataType)
                {
                    case 384://date
                    case 388://time
                    case 448://non-null varchar
                    case 449://nullable varchar
                        addOpening(n, colLen, colName);
                        n.AppendLine($"          memcpy({dest}, sqlda->sqlvar[i].sqldata, {dataLen});");
                        if (dataType % 2 == 1)
                            addNullIndicator(n, nullIndicator);
                        addClosing(n);
                        break;
                    case 452://non-null char
                    case 453://nullable char
                        addOpening(n, colLen, colName);
                        if (dataLen == 1)
                            n.AppendLine($"          {dest} = *sqlda->sqlvar[i].sqldata;");
                        else
                            n.AppendLine($"          memcpy({dest}, sqlda->sqlvar[i].sqldata, {dataLen});");
                        if (dataType % 2 == 1)
                            addNullIndicator(n, nullIndicator);
                        addClosing(n);
                        break;
                    case 480://non-null double
                    case 481://nullable double
                        addOpening(n, colLen, colName);
                        n.AppendLine($"          {dest} = *(double*)sqlda->sqlvar[i].sqldata;");
                        if (dataType % 2 == 1)
                            addNullIndicator(n, nullIndicator);
                        addClosing(n);
                        break;
                    case 492://non-null bigint
                    case 493://nullable bigint
                        addOpening(n, colLen, colName);
                        n.AppendLine($"          {dest} = *(long long int*)sqlda->sqlvar[i].sqldata;");
                        if (dataType % 2 == 1)
                            addNullIndicator(n, nullIndicator);
                        addClosing(n);
                        break;
                    case 496://non-null int
                    case 497://nullable int
                        addOpening(n, colLen, colName);
                        n.AppendLine($"          {dest} = *(long int*)sqlda->sqlvar[i].sqldata;");
                        if (dataType % 2 == 1)
                            addNullIndicator(n, nullIndicator);
                        addClosing(n);
                        break;
                    case 500://non-null short
                    case 501://nullable short
                        addOpening(n, colLen, colName);
                        n.AppendLine($"          {dest} = *(short*)sqlda->sqlvar[i].sqldata;");
                        if (dataType % 2 == 1)
                            addNullIndicator(n, nullIndicator);
                        addClosing(n);
                        break;
                    default:
                        throw new ArgumentOutOfRangeException($"what is type {dataType}?!");
                }
            }

            n.Append(funcEnd);
            txtNew.Text = funcBegin(structName) + n;
        }

        private static void addClosing(StringBuilder n)
        {
            n.AppendLine("       }");
        }

        private static void addOpening(StringBuilder n, string colLen, string colName)
        {
            n.AppendLine($"       if(memcmp(sqlda->sqlvar[i].sqlname.data, {colName}, {colLen}) == 0)");
            n.AppendLine("       {");
        }

        private static void addNullIndicator(StringBuilder n, string nullIndicator)
        {
            n.AppendLine($"          {nullIndicator} = *sqlda->sqlvar[i].sqlind;");
        }

        string funcBegin(string structName)
        {
            return @"
void processSqlda_{{structName}}(sqlda_t* sqlda)
{
    if (sqlda == NULL)
    {
        printf(""can't continue - sqlda is null"");
        return;
    }
    for (int i = 0; i<sqlda->sqld; i++)
    {
".Replace("{{structName}}", structName);
        }

        string funcEnd = @"
    }
}";
    }
}