Loading ...
Sorry, an error occurred while loading the content.

Array, the last Samurai or Mission impossible ?

Expand Messages
  • stoneoldrock
    Hi, all (and Vlad) I was here to ask help about BLOB, and it helped a lot. Now I have developed a function which can load multiple (different) files into
    Message 1 of 2 , May 19 1:13 AM
    • 0 Attachment
      Hi, all (and Vlad)

      I was here to ask help about BLOB, and it helped a lot.

      Now I have developed a function which can load multiple (different) files into multiple blob columns by INSERT or UPDATE with a single function call, after that help, thanks.

      Encouraged by this I am going to explore the array, an abundant and lonely field seldom attended by anyone( but still useful).

      The following codes are NOT working, would anyone like to point out where is wrong?

      INSERT/UPDATE ONE array as ?

      function (char *sqlcmd,
      char *table_name,
      char *column_name,
      char *array_buffer,
      int buffer_len))
      {
      ISC_STATUS status_vector[32];

      ISC_ARRAY_DESC desc;
      memset(&desc,0,sizeof( ISC_ARRAY_DESC));

      ISC_QUAD array_id;
      memset(&array_id,0,sizeof(ISC_QUAD));

      printf("sql :%s \n", sqlcmd);

      int *nullid = 0;
      int fberr=0;

      XSQLDA *in_sqlda = (XSQLDA *)malloc(XSQLDA_LENGTH(1));
      memset ((void*)(qi->in_sqlda->sqlvar), 0,
      ( 1 * sizeof(XSQLVAR)));

      in_sqlda->version = SQLDA_VERSION1;
      in_sqlda->sqln = 1;
      in_sqlda->sqld = 1;

      in_sqlda->sqlvar[0].sqldata = (char*) &array_id;
      in_sqlda->sqlvar[0].sqltype = SQL_ARRAY+1;
      in_sqlda->sqlvar[0].sqllen = sizeof( ISC_QUAD) ;
      in_sqlda->sqlvar[0].sqlind = &nullid;

      // getting connected to database...
      / then.....

      isc_start_transaction( &status_vector[0], &tr, 1, &db, 0, NULL);
      if (status_vector[0] == 1 && status_vector[1]) {
      printf("start main transaction ERROR!\n");
      isc_print_status( status_vector);
      return;
      }else { printf("start transaction OK\n"); }


      isc_array_lookup_bounds( status_vector[0], //status_vector
      &db, //db_handle,
      &tr, //&trans,
      table_name,/* table name */
      column_name,/* array column name */
      &desc);

      if (status_vector[0] == 1 && status_vector[1]) {
      printf("get array descriptor ERROR!\n");
      isc_print_status( status_vector);
      return;
      }else { printf("get array descriptor OK\n"); }

      int lob = desc.array_desc_bounds[0].array_bound_lower;
      int upb = desc.array_desc_bounds[0].array_bound_upper;
      short slice_len = desc.array_desc_length;

      printf("lower bound: %d\n", lob);
      printf("upper bound: %d\n", upb);
      printf("slice length %d\n", slice_len);
      printf("buffer length: %d\n", buffer_len);

      isc_array_put_slice( &status_vector[0], //status_vector,
      &db, //&db_handle,
      &tr, //&trans,
      &array_id,/* array ID (NULL, or existing array's array ID) */
      &desc, /* array descriptor describing where to write data */
      array_buffer,/*array buffer containing data to write to array*/
      &buffer_len /* length of array buffer */
      );

      if (status_vector[0] == 1 && status_vector[1]) {
      printf("put array slices ERROR!\n");
      isc_print_status( status_vector);
      return;
      }else { printf("put array slices OK\n"); }


      isc_dsql_execute_immediate(status_vector[0],
      &db, // DB handle
      &tr, // transaction
      0, // indicates string to execute is null-terminated
      sqlcmd, // UPDATE statement string to be executed
      1, // XSQLDA version number
      in_sqlda // XSQLDA supplying parameters to UPDATE statement
      );

      if (status_vector[0] == 1 && status_vector[1]) {
      printf("execute immediate ERROR!\n");
      isc_print_status(status_vector);
      printf("\n");
      return;
      }else {
      printf("execute main immediate OK\n");
      printf("SQL: %s\n",sqlcmd);

      }

      }
      //---------------------------------------------------

      the table with array column is as:

      table name: TBL_D
      column name: FLD_ARRA CHAR(20)[5]

      SQL:UPDATE TBL_D SET FLD_ARRA = ? where FLD_SML = 100

      char *arr_buf[] = { "AAA", "AAA", "AAA", "AAA", "AAA" };

      // firebird array (lower bound) starts from 1
      // but that should not be the case here.
      //arr_buf[0] = "AAA";
      //arr_buf[1] = "AAA";
      //arr_buf[2] = "AAA";
      //arr_buf[3] = "AAA";
      //arr_buf[4] = "AAA";

      int buf_len = sizeof (arr_buf); //= 20, why, how ?

      function ("UPDATE TBL_D SET FLD_ARRA = ? where FLD_SML = 100 ",
      "TBL_D",
      "FLD_ARRA",
      arr_buf, // is it like this OK?
      buf_len))

      It runs on Linux Fedora 14 with FB superserver 2.1.3
      whole OS on UTF-8

      running results:

      sql:UPDATE TBL_D SET FLD_ARRA = ? where FLD_SML = 100

      start transaction OK
      get array descriptor OK
      lower bound: 1
      upper bound: 5
      slice length 80
      buffer length: 20
      put array slices ERROR!
      subscript out of bounds //here comes the rub!!

      if increase the buf_len, it will be:
      Mal forward string length...

      Ho, array, the last samurai, or really mission impossible?

      This is the question, Who can answer ?
      thanks in advance

      yours
      o'rock
    • hvlad
      ... For CHAR(20) data type you must supply buffer of 20 bytes per item. Or you could leave this C-data as is but modify data type at array descriptor returned
      Message 2 of 2 , May 24 2:27 AM
      • 0 Attachment
        --- In firebird-support@yahoogroups.com, "stoneoldrock" wrote:
        >
        > Hi, all (and Vlad)
        >
        > I was here to ask help about BLOB, and it helped a lot.
        >
        > Now I have developed a function which can load multiple (different) files into multiple blob columns by INSERT or UPDATE with a single function call, after that help, thanks.
        >
        > Encouraged by this I am going to explore the array, an abundant and lonely field seldom attended by anyone( but still useful).
        >
        > The following codes are NOT working, would anyone like to point out where is wrong?
        ...
        > the table with array column is as:
        >
        > table name: TBL_D
        > column name: FLD_ARRA CHAR(20)[5]
        >
        > SQL:UPDATE TBL_D SET FLD_ARRA = ? where FLD_SML = 100
        >
        > char *arr_buf[] = { "AAA", "AAA", "AAA", "AAA", "AAA" };

        For CHAR(20) data type you must supply buffer of 20 bytes per item.
        Or you could leave this C-data as is but modify data type at array
        descriptor returned by Firebird - try to set it to the blr_cstring
        (it should be blr_text when you retrieve it from engine). I don't know
        if this trick will work. If not - supply correct CHAR(20) data.

        Hope this helps,
        Vlad
      Your message has been successfully submitted and would be delivered to recipients shortly.