Subject: Re: structured host variable arrays within ecpg
From: Matthew Vanecek <mevanecek@yahoo.com>
To: Christoph Haller <ch@rodos.fzk.de>
Cc: Postgresql Interfaces List <pgsql-interfaces@postgresql.org>
In-Reply-To: <3E54D8FB.DE09FD3C@rodos.fzk.de>
References: <3E54D8FB.DE09FD3C@rodos.fzk.de>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-8PqZnVpoQkLxTRmnd0Gm"
Organization: 
Message-Id: <1045797266.27169.58.camel@reliant.home.pri>
Mime-Version: 1.0
Date: 20 Feb 2003 21:14:26 -0600

--=-8PqZnVpoQkLxTRmnd0Gm
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

On Thu, 2003-02-20 at 07:32, Christoph Haller wrote:
> I am porting a large C-coded application from ALLBASE SQL
> (the HP-UX DBMS) to PostgreSQL. The code makes frequent use
> of embedded SQL. My intention was to skip all the static stuff
> and replace it by dynamic calls to libpq. So far so good, it
> went pretty well and the application is running with postgres now.
> But I have to face a massive lack of performance, especially on
> INSERTs. I know there is lot of overhead done, if you are going
> to process let say 500 insert commands. Using COPY is not really
> an option. So I was trying to re-use embedded statements, but
> I had to face the fact that ecpg cannot deal with structured
> host variable arrays the way I expected.
> Example:
> EXEC SQL  INSERT INTO ASimple_Values (
>   Primary_Key,
>   List_Pointer,
>   Parameter_Name,
>   Parameter_Code,
>   Parameter_Value,
>   Source_Type
> )
> VALUES (
>   :ASimple_Values[SQL_ii].primary_key,
>   :ASimple_Values[SQL_ii].value_list_ptr,
>   :ASimple_Values[SQL_ii].parameter_name,
>   :ASimple_Values[SQL_ii].parameter_code,
>   :ASimple_Values[SQL_ii].value,
>   :ASimple_Values[SQL_ii].source_type
> );
> ERROR: parse error, unexpected '[', expecting ')' or ',' at or near "["
>=20

ecpg won't parse the above structure, as you so evidently have found
out.  It requires a simple struct.  Perhaps a future version will
support arrays?  One can hope...

What you can try to do, to circumvent this, is creative use of
pointers.  Something like:

	EXEC SQL BEGIN DECLARE SECTION;
	struct s_stuff {
	    int x;
	    char field[20];
	} sqlStuff;
	EXEC SQL END DECLARE SECTION;
	typedef struct s_stuff MyStuff;

	void update(MyStuff *stuff)
	{
	    sqlStuff =3D *stuff;

	    EXEC SQL
	        INSERT INTO TABLE=20
		VALUES (sqlStuff.x, sqlStuff.field);
=09
	    /* Check sqlca, etc */
	}

That's just a off-the-cuff thing.  What you want to do is take your
input struct and populate an sql declared struct, and insert using that.
vi and sed are really good at mass replacements, if it comes to that. ;)

ecpg handles structures fairly well, if you declare the structure inside
a DECLARE SECTION block, and use that declared struct for the SQL
operations.  There's nothing saying you can't move data around.

Hope this helps a little.  It's just from my observations working with
ecpg.  I do, however, really like embedded sql. :)

--=20
Matthew Vanecek
perl -e 'print $i=3Dpack(c5,(41*2),sqrt(7056),(unpack(c,H)-2),oct(115),10);'
***************************************************************************=
*****
For 93 million miles, there is nothing between the sun and my shadow except=
 me.
I'm always getting in the way of something...

--=-8PqZnVpoQkLxTRmnd0Gm
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQA+VZmSi/CNzDSN0RIRAs9gAJsGiRkqabIFVC+SSgkhy9uJWG5fIQCgn9JU
Nh+BMrf0L+Lk364x2QfEXkc=
=5gSZ
-----END PGP SIGNATURE-----

--=-8PqZnVpoQkLxTRmnd0Gm--