Hey,

I am using the Firebird Classic-Server and I'm trying to learn accessing FB
with its C-API.

I created a Database with one Table TEST, which contains columns for the
different datapyes, one each.

Now I try to insert values into this table with the following Code:
<code>
/*
* main.c
*
* Created on: 25.05.2011
* Author: lars.liedtke
*/

#include <ibase.h>
#include <string.h>
#include <stdio.h>
#include <stdlib.h>
#include <time.h>
#include "putsqlvar.h"

int it = 5;
int si = 5;
float nu = 56.23;
double dp = 5.67;
float fl = 6.78;
char ch = 'e';
char vc[1] = "f";
int da_y = 2011;
int da_m = 5;
int da_d = 26;
int ti_h = 12;
int ti_m = 0;
int ti_s = 0;
int ts_y = 2011;
int ts_m = 5;
int ts_d = 26;
int ts_h = 12;
int ts_mi = 0;
int ts_s = 0;
int* blob_data;
float de = 56.23;

const unsigned short sql_client_dialect3 = 3;
const unsigned short sql_client_dialect1 = 1;
const int Dec31_1899 = 693960;
short indnull = 0;

// TODO Sollte irgendwann kein Globale Variable sein aber aus Einfachheit
machen wir das jetzt
XSQLDA *input;

void initDatabase(char password[256], char userName[256], ISC_STATUS_ARRAY
status, char databaseName[256], isc_db_handle *hDb)
{
//DPB-Puffer zum Uebergeben von Parametern an die Datenbank
char *pDPB;
// Zeiger um die einzelnen Stellen des DPB-Puffers ansprechen zu können
short DPB_length = 0;
// Laenge des DPB-Puffers (initialisiert mit 0)// Füllen des DPB mit
Benutzername und Passwort
char DPB_buffer[256];
char *pPassword = password;
//Setzen des Zeigers an den Anfang von passwordchar
char *pUserName = userName;
//Setzen des Zeigers an den Anfang von password
//Zählvariable
int i;
pDPB = DPB_buffer; // setzen des dpb Zeigers auf den anfang des dpb-Puffers
*pDPB++ = isc_dpb_version1;
*pDPB++ = isc_dpb_user_name;
*pDPB++ = (char)strlen(userName); // Laenge des Benutzernamens
// Uebertragen des Zeichen des Benutzernamens in den -PB Puffer
for(i = 0;i < strlen(userName);i++){
*pDPB++ = *pUserName++;
}
*pDPB++ = isc_dpb_password;
*pDPB++ = (char)strlen(password); // Laenge des Passworts
// Uebertragen der Zeichen des Passworts in den DPB-Puffer
for(i = 0;i < strlen(password);i++){
*pDPB++ = *pPassword++;
}
// Berechnen der Laenge des DPBs = Adresse des Zeigers - Adresse des
Anfang des DPB-Puffers
DPB_length = pDPB - DPB_buffer;
//printf("DPB-Length: %d\n", DPB_length);
// Das sollte stattdessen laut doc auch gehen, ist aber als DEPRECATED
markiert und scheint auch nicht zu gehen
//isc_expand_dpb(&dpb, (short*)&dpb_length, isc_dpb_version1,
isc_dpb_num_buffers, 1L, isc_dpb_user_name, userName, isc_dpb_password,
password, 0L);
// Aufbau der Datenbankverbindung
if(isc_attach_database(status, strlen(databaseName), databaseName, *&hDb,
DPB_length, DPB_buffer)){
isc_print_status(status);
exit(1);
}
}

void startTransaction(ISC_STATUS_ARRAY status, isc_tr_handle *hTrans,
isc_db_handle *hDb)
{
// TPB-Puffer
char TPB[] = {isc_tpb_version3, isc_tpb_write, isc_tpb_read_committed,
isc_tpb_no_rec_version, isc_tpb_wait};
if(isc_start_transaction(status, *&hTrans, 1, *&hDb, (unsigned short
)sizeof (TPB), TPB)){
isc_print_status(status);
exit(1);
}
}

void initXSQLDA(short nVariables)
{
// Allokieren von speicher fuer die XSQLDA-Struktur
input = (XSQLDA*)malloc(XSQLDA_LENGTH(nVariables));
input->sqln = nVariables;
input->sqld = nVariables;
input->version = SQLDA_VERSION1;
}

//TODO
char* makeVarchar(char* vc, char* varchar)
{
short length = strlen(vc);
char vc_tmp[length + 2];
varchar = malloc(length + 2);
char *pVc = vc_tmp;
sprintf(pVc, "%02d\0", length);
pVc++;
pVc++;
*pVc = *vc;
varchar = memcpy(varchar, vc_tmp, length + 3);
return varchar;
}

//Funktion, um Testdaten in eine Datenbank zu schrieben
void saveValuesInDB()
{
// Statusarray
ISC_STATUS_ARRAY status;

// Datenbank Handle
isc_db_handle hDb = 0L;

//Datenbankname
char databaseName [256];
strcpy(databaseName, "/localhome/liedtke/fbCATest.fdb");
// Benutzername
char userName[256];
strcpy(userName, "SYSDBA");
// Kennwort
char password[256];
strcpy(password, "feuervogel");

char insert_string[] = "insert into
TEST(id,it,si,nu,dp,fl,ch,vc,da,ti,ts,bl,de) Values(?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?)\0";

int numberOfVariables = 13;

initDatabase(password, userName, status, databaseName, &hDb);

// Transaktionshandle
isc_tr_handle hTrans = 0L;

startTransaction(status, &hTrans, &hDb);

initXSQLDA(numberOfVariables);

int n = 0;
int id = 0;
input->sqlvar[n].sqldata = (char*)&id;
printf("sqldata %d\n", *(int*)input->sqlvar[n].sqldata);
input->sqlvar[n].sqltype = SQL_SHORT + 1;
input->sqlvar[n].sqllen = sizeof(int);
input->sqlvar[n].sqlind = &indnull;
n++;
input->sqlvar[n].sqldata = (char*)⁢
printf("sqldata %d\n", *(int*)input->sqlvar[n].sqldata);
input->sqlvar[n].sqltype = SQL_SHORT + 1;
input->sqlvar[n].sqllen = sizeof(int);
input->sqlvar[n].sqlind = &indnull;
n++;
input->sqlvar[n].sqldata = (char*)&si;
printf("sqldata %d\n", *(short*)input->sqlvar[n].sqldata);
input->sqlvar[n].sqltype = SQL_SHORT + 1;
input->sqlvar[n].sqllen = sizeof(int);
input->sqlvar[n].sqlind = &indnull;
n++;
input->sqlvar[n].sqldata = (char*)ν
printf("sqldata %f\n", *(float*)input->sqlvar[n].sqldata);
input->sqlvar[n].sqltype = SQL_FLOAT + 1;
input->sqlvar[n].sqllen = sizeof(float);
input->sqlvar[n].sqlind = &indnull;
n++;
input->sqlvar[n].sqldata = (char*)&dp;
printf("sqldata %f\n", *(double*)input->sqlvar[n].sqldata);
input->sqlvar[n].sqltype = SQL_DOUBLE;
input->sqlvar[n].sqllen = sizeof(double);
input->sqlvar[n].sqlind = &indnull;
n++;
input->sqlvar[n].sqldata = (char*)&fl;
printf("sqldata %f\n", *(float*)input->sqlvar[n].sqldata);
input->sqlvar[n].sqltype = SQL_FLOAT;
input->sqlvar[n].sqllen = sizeof(float);
input->sqlvar[n].sqlind = &indnull;
n++;
input->sqlvar[n].sqldata = &ch;
printf("sqldata %c\n", *input->sqlvar[n].sqldata);
input->sqlvar[n].sqltype = SQL_TEXT;
input->sqlvar[n].sqllen = sizeof(char);
input->sqlvar[n].sqlind = &indnull;
n++;
short indnull = 0;
char* varchar = makeVarchar(vc, varchar);
input->sqlvar[n].sqldata = varchar;
printf("sqldata %s\n", input->sqlvar[n].sqldata);
input->sqlvar[n].sqltype = SQL_VARYING; // geht auch mit SQL_TEXT
und ohne den Aufruf von makeVarchar
input->sqlvar[n].sqllen = strlen(vc);
input->sqlvar[n].sqlind = &indnull;
n++;
struct tm c_date;
c_date.tm_year = da_y;
c_date.tm_mon = da_m;
c_date.tm_mday = da_d;

ISC_DATE da;
isc_encode_sql_date(&c_date, &da);

ISC_SCHAR test;

da = da - Dec31_1899;

input->sqlvar[n].sqldata = (char*)&da;
// input->sqlvar[n].sqldata = malloc (sizeof(ISC_DATE) + 1);
// input->sqlvar[n].sqldata = memcpy(input->sqlvar[n].sqldata,
&da,
sizeof(ISC_DATE));
printf("sqldata %d\n", *(int*)input->sqlvar[n].sqldata);
input->sqlvar[n].sqltype = SQL_TYPE_DATE;
input->sqlvar[n].sqllen = sizeof(ISC_DATE);
input->sqlvar[n].sqlind = &indnull;
n++;
struct tm c_time;
c_time.tm_hour = ti_h;
c_time.tm_min = ti_m;
c_time.tm_sec = ti_s;
ISC_TIME ti;
isc_encode_sql_time(&c_time, &ti);

input->sqlvar[n].sqldata = (char*)&ti;
printf("sqldata %d\n", *(int*)input->sqlvar[n].sqldata);
input->sqlvar[n].sqltype = SQL_TYPE_TIME;
input->sqlvar[n].sqllen = sizeof(ISC_TIME);
input->sqlvar[n].sqlind = &indnull;
n++;
struct tm c_timestamp;
c_timestamp.tm_year = ts_y;
c_timestamp.tm_mon = ts_m;
c_timestamp.tm_mday = ts_d;
c_timestamp.tm_hour = ts_h;
c_timestamp.tm_min = ts_mi;
c_timestamp.tm_sec = ts_s;

ISC_TIMESTAMP ts;
isc_encode_timestamp(&c_timestamp, &ts);

ts.timestamp_date = ts.timestamp_date - Dec31_1899;

input->sqlvar[n].sqldata = (char*)&ts;
// printf("sqldata %f\n", *(float*)input->sqlvar[n].sqldata);
printf("sqldata \n");
input->sqlvar[n].sqltype = SQL_TIMESTAMP;
input->sqlvar[n].sqllen = sizeof(ISC_TIMESTAMP);
input->sqlvar[n].sqlind = &indnull;
n++;
blob_data = malloc(sizeof(int));
*blob_data = 5;
isc_blob_handle hBlob;
hBlob = 0L;
ISC_QUAD blob_id;


if(isc_create_blob2(status, &hDb, &hTrans, &hBlob, &blob_id,
0,
NULL))
{
isc_print_status(status);
exit(1);
}

if(isc_put_segment(status, &hBlob, sizeof(int),
(char*)blob_data))
{
isc_print_status(status);
exit(1);
}

if(isc_close_blob(status, &hBlob))
{
isc_print_status(status);
exit(1);
}

input->sqlvar[n].sqldata = (char*)&blob_id;
printf("sqldata %lld\n", *(long long*)input-

>sqlvar[n].sqldata);

input->sqlvar[n].sqltype = SQL_BLOB + 1;
input->sqlvar[n].sqllen = sizeof(ISC_QUAD);
input->sqlvar[n].sqlind = &indnull;
free(blob_data);
n++;
input->sqlvar[n].sqldata = (char*)&de;
printf("sqldata %f\n", *(float*)input->sqlvar[n].sqldata);
input->sqlvar[n].sqltype = SQL_FLOAT + 1;
input->sqlvar[n].sqllen = sizeof(float);

if(isc_dsql_execute_immediate(status, &hDb, &hTrans, 0, insert_string,
sql_client_dialect3, input))
{
isc_print_status(status);
isc_rollback_transaction(status, &hTrans);
exit(1);
}
free(input);

// Transaktion comitten
if(isc_commit_transaction(status, &hTrans))
{
isc_print_status(status);
isc_rollback_transaction(status, &hTrans);
exit(1);
}

//Beenden der Datenbankverbindung
if(isc_detach_database(status, &hDb))
{
isc_print_status(status);
exit(1);
}

printf("Seems to work!!!\n");
}

int main()
{
saveValuesInDB();
getValuesFromDB();
return 0;
}

</code>

the result I get is the Mail's Subject error: "Dynamic SQL Error
-SQL error code = -804
-Incorrect values within SQLDA structure"

I tried this all with a Database where I have one Table per datatype and
single insert. There it works. Now when I trying it with the mentioned code
it
doesn't. I think I tried nearly everything I could imagine.

Note: the Id I set up within FB as "Auto-Increment" with a Generator.

I would be very pleased if you could help me.

Thank you very much.

Regards Lars
--
Karlsruher Institut für Technologie (KIT)
Institut für Angewandte Informatik

Lars Liedtke
Student der DHBW
Hermann-von-Helmholtz-Platz 1
Gebäude 445
76344 Eggenstein-Leopoldshafen

Telefon: +49 7247 82-3804
Fax: +49 7247 82-
E-Mail: Lars.Liedtke@...
Web: http://www.kit.edu/


KIT – Universität des Landes Baden-Württemberg und
nationales Forschungszentrum in der Helmholtz-Gemeinschaft


[Non-text portions of this message have been removed]