PostgreSQL array data type

Request new PGNP funclionality and enhancements.

PostgreSQL array data type

Postby pgdude » Sun Apr 12, 2009 12:04 pm

What is the official status on arrays. How are they going to be implemented?
Code sample would help I guess. Just curios If it is worth waiting for version 1.3.
pgdude
 
Posts: 5
Joined: Sun Apr 12, 2009 11:49 am

Re: PostgreSQL array data type

Postby Moderator » Mon Apr 13, 2009 12:27 am

We have finished implementation of the multi-dimensional arrays in the Provider, version 1.3.

Since OLEDB does not support arrays, we have implemented them as VARIANT wrappers around SAFEARRAYs. We decided that an array lower index will be 1 (not 0).

The array elements accessed differently depending on the programming language. For example, let's PostgreSQL array declared as:
Code: Select all
CREATE TABLE test_arrays
(
  "array2D" smallint[][],
  ...
)

// Populate "array2D" as 5x7 matrix
INSERT INTO test_arrays("array2D",...)
VALUES('{{3,16,9,22,15,0,100},{20,8,21,14,2,0,100},{7,25,13,1,19,0,100},{24,12,5,18,6,0,100},{11,4,17,10,23,0,100}}',...)


The following C# sample demonstrates access to the 2D array:
Code: Select all
string sConnectionStr = "Provider=PGNP.1;...";
OleDbConnection conn = new OleDbConnection(sConnectionStr);
conn.Open();

OleDbCommand dbcmd = new OleDbCommand("SELECT \"array2D\" FROM test_arrays", conn);
OleDbDataReader dr = dbcmd.ExecuteReader();

while (dr.Read())
{
    object o = dr.GetValue(0);
    if (o is DBNull)
        Console.WriteLine("[NULL]");
    else
    {
        Array array = o as Array;
        switch (lang_req.Rank)
        {
        case 1:
              for (int i = lang_req.GetLowerBound(0); i < lang_req.GetLowerBound(0) + lang_req.GetLength(0); i++)
              {
                   object x = lang_req.GetValue(i);
                   Console.WriteLine(String.Format("{0}>{1}", i, x));
              }
              break;

        case 2:
            for (int j = lang_req.GetLowerBound(1); j < lang_req.GetLowerBound(1) + lang_req.GetLength(1); j++)
            {
                for (int i = lang_req.GetLowerBound(0); i < lang_req.GetLowerBound(0) + lang_req.GetLength(0); i++)
                {
                   object x = lang_req.GetValue(i, j);
                   Console.WriteLine(String.Format("({0},{1}) > {2}", i, j, x));
                }
            }
            break;

        case 3:
            for (int k = lang_req.GetLowerBound(2); k < lang_req.GetLowerBound(2) + lang_req.GetLength(2); k++)
            {
                 for (int j = lang_req.GetLowerBound(1); j < lang_req.GetLowerBound(1) + lang_req.GetLength(1); j++)
                 {
                     for (int i = lang_req.GetLowerBound(0); i < lang_req.GetLowerBound(0) + lang_req.GetLength(0); i++)
                     {
                         object x = lang_req.GetValue(i, j, k);
                         Console.WriteLine(String.Format("({0},{1},{2}) > {3}", i, j, k, x));
                     }
                 }
             }
             break;
        }
    }
}


If you compile and execute the C# sample you will notice that indexes are:
Code: Select all
(1,1) > 3
(2,1) > 16
(3,1) > 9
(4,1) > 22
(5,1) > 15
(6,1) > 0
(7,1) > 100
(1,2) > 20
(2,2) > 8
(3,2) > 21
(4,2) > 14
(5,2) > 2
(6,2) > 0
(7,2) > 100
(1,3) > 7
(2,3) > 25
(3,3) > 13
(4,3) > 1
(5,3) > 19
(6,3) > 0
(7,3) > 100
(1,4) > 24
(2,4) > 12
(3,4) > 5
(4,4) > 18
(5,4) > 6
(6,4) > 0
(7,4) > 100
(1,5) > 11
(2,5) > 4
(3,5) > 17
(4,5) > 10
(5,5) > 23
(6,5) > 0
(7,5) > 100


That corresponds to C# array:
Code: Select all
short array2D[7,5];      // it can be confusing because PostgreSQL array is 5x7!


However, C++ allows to access the array elements similarly to PostgreSQL declaration.
User avatar
Moderator
Site Admin
 
Posts: 305
Joined: Wed Oct 29, 2008 11:27 pm

Re: PostgreSQL array data type

Postby pgdude » Wed Apr 15, 2009 9:44 am

Thank you for the C# sample. Would you have a C++ code handy?
pgdude
 
Posts: 5
Joined: Sun Apr 12, 2009 11:49 am

Re: PostgreSQL array data type

Postby Moderator » Fri Apr 17, 2009 12:02 am

C++ sample demonstrating multidimentional arrays access via PGNP provider

Code: Select all
#include <stdio.h>
#include <tchar.h>
#include <iostream>
#include <string>
using namespace std;

#import "msado15.dll" no_namespace rename("EOF", "adoEOF")

void MyArrayPrint(const _variant_t& v);

int _tmain(int argc, _TCHAR* argv[])
{
   CoInitialize (NULL);

   _ConnectionPtr pConn;

   try   
   {
      HRESULT hr = pConn.CreateInstance (__uuidof (Connection));
      if (FAILED (hr))
      {
         cout << "Can't create Connection instance" << endl;
         return 1;
      }
      hr = pConn->Open (_bstr_t ("Provider=PGNP.1;Password=12345;User ID=postgres;Initial Catalog=postgres;Data Source=localhost;Extended Properties=\"\""), _bstr_t (""), _bstr_t (""), adModeUnknown);
      if (FAILED(hr))
      {
         cout << "Can't open datasource" << endl;
         return 2;
      }

      _CommandPtr pCommand;

      hr = pCommand.CreateInstance (__uuidof (Command));
      if (FAILED (hr))
      {
         cout << "Can't create an instance of Command" << endl;
         return 3;
      }

      pCommand->ActiveConnection = pConn;
      pCommand->CommandText = "select \"array2D\"from test_arrays";


      _RecordsetPtr pRecordset;

      hr = pRecordset.CreateInstance (__uuidof (Recordset));
      if (FAILED (hr))
      {
         cout << "Can't create an instance of Recordset" << endl;
         return 4;
      }

      pRecordset->CursorLocation = adUseClient;
      pRecordset->Open ((IDispatch *) pCommand, vtMissing, adOpenForwardOnly, adLockUnspecified, adCmdUnknown);

      if (!pRecordset->GetadoEOF ())
      {
         _variant_t v = pRecordset->Fields->GetItem("array2D")->GetValue();

         MyArrayPrint(v);
      }

      pRecordset->Close ();
   }
   catch ( _com_error &e )
   {
      cout << e.Description() << endl;
   }
   catch (...)
   {
      cout << "*** Unhandled Exception ***" << endl;
   }

   if ((pConn->State & adStateOpen) == adStateOpen)
      pConn->Close();

   CoUninitialize();

   return 0;
}

void PrintArrayElement(VARTYPE vt, LPVOID pvData, LONG i)
{
   switch (vt)
   {
   default: cout << "?unknown vartype"; break;

   case VT_I2: case VT_UI2:   cout << ((short*)pvData)[i]; break;

   case VT_I4: case VT_UI4:   cout << ((int*)pvData)[i]; break;

   case VT_BSTR: { _bstr_t bstrTemp(((BSTR*)pvData)[i], true); cout << LPCSTR(bstrTemp); } break;
   }
}

void MyArrayPrint(const _variant_t& v)
{
   if (0 == (v.vt & VT_ARRAY))
   {
      cout << "Not array, vt=" << v.vt;
      return;
   }

   VARTYPE vt = v.vt & ~VT_ARRAY;   // element type

   switch (v.parray->cDims)
   {
      case 1:
         for (LONG i = v.parray->rgsabound[0].lLbound; i < v.parray->rgsabound[0].lLbound + v.parray->rgsabound[0].cElements; i++)
         {
            cout << i << ">";
            PrintArrayElement(vt, v.parray->pvData, i-v.parray->rgsabound[0].lLbound);   // the implementation requires zero-based indexes
            cout << endl;
         }
         break;

      case 2:
         for (LONG i = v.parray->rgsabound[0].lLbound; i < v.parray->rgsabound[0].lLbound + v.parray->rgsabound[0].cElements; i++)
         {
            for (LONG j = v.parray->rgsabound[1].lLbound; j < v.parray->rgsabound[1].lLbound + v.parray->rgsabound[1].cElements; j++)
            {
               cout << "(" << i << "," << j << ")>";
               PrintArrayElement(vt, v.parray->pvData,
                  (i-v.parray->rgsabound[0].lLbound) * v.parray->rgsabound[1].cElements +
                  (j-v.parray->rgsabound[1].lLbound));   // the implementation requires zero-based indexes
               cout << endl;
            }
         }
         break;

      case 3:
         for (LONG i = v.parray->rgsabound[0].lLbound; i < v.parray->rgsabound[0].lLbound + v.parray->rgsabound[0].cElements; i++)
         {
            for (LONG j = v.parray->rgsabound[1].lLbound; j < v.parray->rgsabound[1].lLbound + v.parray->rgsabound[1].cElements; j++)
            {
               for (LONG k = v.parray->rgsabound[2].lLbound; k < v.parray->rgsabound[2].lLbound + v.parray->rgsabound[2].cElements; k++)
               {
                  cout << "(" << i << "," << j << "," << k << ")>";
                  PrintArrayElement(vt, v.parray->pvData,
                     ((i-v.parray->rgsabound[0].lLbound) * v.parray->rgsabound[1].cElements +
                      (j-v.parray->rgsabound[1].lLbound)) * v.parray->rgsabound[2].cElements +
                     (k-v.parray->rgsabound[2].lLbound));   // the implementation requires zero-based indexes
                  cout << endl;
               }
            }
         }
         break;
   }
}
User avatar
Moderator
Site Admin
 
Posts: 305
Joined: Wed Oct 29, 2008 11:27 pm


Return to Request Features

Who is online

Users browsing this forum: No registered users and 1 guest

cron