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?
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 = @"
}
}";
}
}