C-Functions using SPI - Missing Magic Block

On 02/07/10 21:26, Saitenheini@web.de wrote:

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

Why the conditional compilation of PG_MODULE_MAGIC?

ERROR: incompatible library "G:/PostgreSQL/8.3/lib/pgExampleSPI.dll":
missing magic block
TIP: Extension libraries are required to use the PG_MODULE_MAGIC macro.

--
Craig Ringer

On Sat, Jul 03, 2010 at 09:35:56AM +0800, Craig Ringer wrote:

On 02/07/10 21:26, Saitenheini@web.de wrote:

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

Why the conditional compilation of PG_MODULE_MAGIC?

That's the recommendation, so the module compiles on all versions of
Postgres.

ERROR: incompatible library "G:/PostgreSQL/8.3/lib/pgExampleSPI.dll":
missing magic block
TIP: Extension libraries are required to use the PG_MODULE_MAGIC macro.

My guess is that the installed server headers are not compatable with
th eversion of postgresql installed.

Have a nice day,

--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Hi people,

after a two days break:

I could compile the following code with Visual C++ Express 2010 under Windows Server 2003 R2:

/* Use 32-bit timer (provided header file uses 64-bit timer, not
* compatible with Windows postgreSQL versions */
#define _USE_32BIT_TIME_T
#define BUILDING_DLL 1
#include "postgres.h"
#include "fmgr.h" /* PG_MODULE_MAGIC */
#include "executor\spi.h" /* SPI - Server Programming Interface */

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(count_person);

__declspec(dllexport)
Datum count_person(PG_FUNCTION_ARGS) {
int ret;

SPI_connect();
ret = SPI_exec("SELECT count(*) FROM person", 0);
SPI_finish();

PG_RETURN_INT32(ret);
}

The steps I did:

- install GnuWin32 (GetText for Windows) and copy libintl.h to ...\PostgreSQL\8.3\include\server\port\win32

- edit "pg_config.h" and replace #define ENABLE_NLS 1 by #undef ENABLE_NLS

- create new empty DLL-Project and add new C++-File
- Rename file into "filename.c"

- in Visual C++ add:

PostgreSQL\8.3\include\server\port\win32

PostgreSQL\8.3\include\server\

PostgreSQL\8.3\bin

postgres.lib

compile as C-Code !!!

see also:

http://www.postgresql.org/docs/8.2/interactive/xfunc-c.html

http://www.postgresql.org/docs/8.0/interactive/xfunc-c.html

http://www.dbforums.com/postgresql/1626445-how-get-conn-ptr-c-lang-external-function.html

But I guess I still did something wrong, because no matter how many rows exist in my table "person" the result in always 5.

Could this be an data type problem? count(*) returns int8

int8 -> int (C-type) -> PG_RETURN_INT32 ??? Could this cause the problem?

Thanks for any advise, Max.
___________________________________________________________
WEB.DE DSL ab 19,99 Euro/Monat. Bis zu 150,- Euro Startguthaben und
50,- Euro Geldprämie inklusive! https://freundschaftswerbung.web.de

On Mon, Jul 05, 2010 at 10:20:30AM +0200, Saitenheini@web.de wrote:

Datum count_person(PG_FUNCTION_ARGS) {
SPI_connect();
int ret = SPI_exec("SELECT count(*) FROM person", 0);
SPI_finish();
PG_RETURN_INT32(ret);
}

But I guess I still did something wrong, because no matter how many
rows exist in my table "person" the result in always 5.

5 is actually the value you want to be getting back!

The problem is that you're treating the status value of SPI_exec as the
result of the query. You need to check the result to see if there was
an error and only if it's OK can you call something like SPI_getvalue to
actually get the count out. You could probably steal some code from:

http://developer.postgresql.org/pgdocs/postgres/spi-examples.html

--
Sam http://samason.me.uk/

HI,

El viernes, 2 de julio de 2010 10:26:43 UTC-3, (desconocido) escribió:

Hello,

I&#39;ve got a problem with Functions in C using SPI.

Using: PostgreSQL 8.3, Codeblocks, Windows Server 2003 R2

I compiled the file &quot;pgExampleSPI.c&quot; with the following code without any error:

/* Use 32-bit timer (provided header file uses 64-bit timer, not
* compatible with Windows postgreSQL versions */
#define _USE_32BIT_TIME_T

#include &quot;postgres.h&quot;
#include &quot;executor\spi.h&quot;

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

extern Datum count_person (PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(count_<WBR>person);

__declspec(dllexport) Datum count_person(PG_FUNCTION_ARGS) {
    int32    ret;
    SPI_connect();
    ret = SPI_exec(&quot;SELECT count(*) FROM person&quot;, 0);
    SPI_finish();
    PG_RETURN_INT32(ret);
}

- then I&#39;ve copied the resulting file &quot;pgExampleSPI.dll&quot; into the directory &quot;G:\PostgreSQL\8.3\lib&quot;
- I tried to load to function into PostgreSQL with the command:

CREATE FUNCTION count_person() RETURNS int
     AS &#39;G:/PostgreSQL/8.3/lib/<WBR>pgExampleSPI.dll&#39;, &#39;count_person&#39;
     LANGUAGE C STRICT;

- and received the following error description:

ERROR:  incompatible library &quot;G:/PostgreSQL/8.3/lib/<WBR>pgExampleSPI.dll&quot;: missing magic block
TIP:  Extension libraries are required to use the PG_MODULE_MAGIC macro.

After searching google for about 5 hours in couldn&#39;t find a way to solve this problem.

Can anybody help, please?

<span style="font-size:9pt"><span style="font-family:verdana,geneva"><span style="background-color:transparent"><span style="color:#000000"><span style="color:#000000"></span></span></span></span></span></div>  

<table cellpadding="0" cellspacing="0" border="0"><tr><td bgcolor="#000000"><img width="1" height="1" border="0" alt="" origsrc="https://img.web.de/p.gif&quot;&gt;&lt;/td&gt;&lt;/tr&gt;&lt;tr&gt;&lt;td style="font-family:verdana;font-size:12px;line-height:17px">GRATIS für alle <a href="http://WEB.DE&quot; target="_blank">WEB.DE</a> Nutzer: Die maxdome Movie-FLAT!   
Jetzt freischalten unter <a href="http://movieflat.web.de&quot; target="_blank">http://movieflat.web.de&lt;/a&gt;&lt;/td&gt;&lt;/tr&gt;&lt;/table&gt;
</div>

Hi, you must put "PG_MODULE_MAGIC" after include sentences, if you use include own put in firth .h of you call in .c code, remenber after include section