PostgreSQL array data type

More
10 years 4 months ago #14 by pgdude
pgdude created the topic: PostgreSQL array data type
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.

Please Log in or Create an account to join the conversation.

More
10 years 4 months ago #18 by Moderator
Moderator replied the topic: Re: PostgreSQL array data type
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:
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:
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:
(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:
short array2D&#91;7,5&#93;;      // it can be confusing because PostgreSQL array is 5x7!

However, C++ allows to access the array elements similarly to PostgreSQL declaration.

Please Log in or Create an account to join the conversation.

More
10 years 4 months ago #25 by pgdude
pgdude replied the topic: Re: PostgreSQL array data type
Thank you for the C# sample. Would you have a C++ code handy?

Please Log in or Create an account to join the conversation.

More
10 years 4 months ago #30 by Moderator
Moderator replied the topic: Re: PostgreSQL array data type
C++ sample demonstrating multidimentional arrays access via PGNP provider
#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&#91;&#93;)
{
	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)&#91;i&#93;; break;

	case VT_I4: case VT_UI4:	cout << ((int*)pvData)&#91;i&#93;; break;

	case VT_BSTR: { _bstr_t bstrTemp(((BSTR*)pvData)&#91;i&#93;, 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&#91;0&#93;.lLbound; i < v.parray->rgsabound&#91;0&#93;.lLbound + v.parray->rgsabound&#91;0&#93;.cElements; i++)
			{
				cout << i << ">";
				PrintArrayElement(vt, v.parray->pvData, i-v.parray->rgsabound&#91;0&#93;.lLbound);	// the implementation requires zero-based indexes
				cout << endl;
			}
			break;

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

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

Please Log in or Create an account to join the conversation.

Time to create page: 0.077 seconds
Powered by Kunena Forum