Thread: C-Functions using SPI - Missing Magic Block
<body bgcolor=3D"#ffffff" background=3D"https://img.web.de/v/p.gif" class= =3D"bgRepeatYes" style=3D"background-repeat: repeat; ; background-color: rg= b(255, 255, 255); color: rgb(0, 0, 0); font-family: verdana,geneva; font-si= ze: 9pt; padding-left: 0px;"><div style=3D"min-height: 200px; background-im= age: url(https://img.web.de/v/p.gif); background-repeat: repeat; background= -color: #ffffff; font-family: verdana,geneva; font-size: 9pt; padding-left:= 0px;">Hello,I've got a problem with Functions in C using SPI.<= br />Using: PostgreSQL 8.3, Codeblocks, Windows Server 2003 R2<= br />I compiled the file "pgExampleSPI.c" with the following code without a= ny error:/* Use 32-bit timer (provided header file uses 64-bit = timer, not* compatible with Windows postgreSQL versions */#defi= ne _USE_32BIT_TIME_T#include "postgres.h"#include "execut= or\spi.h"#ifdef PG_MODULE_MAGICPG_MODULE_MAGIC;#end= ifextern Datum count_person (PG_FUNCTION_ARGS);PG_F= UNCTION_INFO_V1(count_person);__declspec(dllexport) Datum count= _person(PG_FUNCTION_ARGS) { int32 = ;ret; SPI_connect(); ret = =3D SPI_exec("SELECT count(*) FROM person", 0); SPI= _finish(); PG_RETURN_INT32(ret);}= - then I've copied the resulting file "pgExampleSPI.dll" into the directory= "G:\PostgreSQL\8.3\lib"- I tried to load to function into PostgreSQL= with the command:CREATE FUNCTION count_person() RETURNS int<br= /> AS 'G:/PostgreSQL/8.3/lib/pgExampleSPI.dll', 'c= ount_person' LANGUAGE C STRICT;- = and received the following error description:ERROR: incom= patible library "G:/PostgreSQL/8.3/lib/pgExampleSPI.dll": missing magic blo= ckTIP: Extension libraries are required to use the PG_MODULE_MA= GIC macro.After searching google for about 5 hours in cou= ldn't find a way to solve this problem.Can anybody help, please= ?<span style=3D"font-family: ve= rdana,geneva;"><span style= =3D"color: #000000;"><= /span> <table cellpadding=3D"0" cellspacing= =3D"0" border=3D"0"><img src=3D"https://img.web= .de/p.gif" width=3D"1" height=3D"1" border=3D"0" alt=3D"" /><= td style=3D"font-family:verdana; font-size:12px; line-height:17px;">GRATIS = für alle WEB.DE Nutzer: Die maxdome Movie-FLAT! J= etzt freischalten unter http://movieflat.web.de
;){kind=link}
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/ > Patriotism is when love of your own people comes first; nationalism, > when hate for people other than your own comes first. > - Charles de Gaulle
Attachment
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
always5.
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've got a problem with Functions in C using SPI.
>
> Using: PostgreSQL 8.3, Codeblocks, Windows Server 2003 R2
>
> I compiled the file "pgExampleSPI.c" 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 "postgres.h"
> #include "executor\spi.h"
>
> #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("SELECT count(*) FROM person", 0);
> SPI_finish();
> PG_RETURN_INT32(ret);
> }
>
> - then I've copied the resulting file "pgExampleSPI.dll" into the directory
"G:\PostgreSQL\8.3\lib"
> - I tried to load to function into PostgreSQL with the command:
>
> CREATE FUNCTION count_person() RETURNS int
> AS 'G:/PostgreSQL/8.3/lib/<WBR>pgExampleSPI.dll', 'count_person'
> LANGUAGE C STRICT;
>
> - and received the following error description:
>
> ERROR: incompatible library "G:/PostgreSQL/8.3/lib/<WBR>pgExampleSPI.dll": missing magic block
> TIP: Extension libraries are required to use the PG_MODULE_MAGIC macro.
>
>
> After searching google for about 5 hours in couldn'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"><spanstyle="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"></td></tr><tr><td
style="font-family:verdana;font-size:12px;line-height:17px">GRATISfür alle <a href="http://WEB.DE"
target="_blank">WEB.DE</a>Nutzer: Die maxdome Movie-FLAT!
> Jetzt freischalten unter <a href="http://movieflat.web.de"
target="_blank">http://movieflat.web.de</a></td></tr></table>
> </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
