Board index » delphi » ISQL-StoredPros-UDF-IBAPI doesn't work?

ISQL-StoredPros-UDF-IBAPI doesn't work?

Hi all.

I can't understand why my IB6 server restarts with message:
Abnormal termination...
Server started...

in the next situation:

In ISQL I execute:
SELECT LI_RC FROM BR_CC_CREATE_USER ('USEROCHEK', 'PASSIK', 'SNLWS', ' ');

where BR_CC_CREATE_USER is a stored procedure:
create procedure BR_CC_CREATE_USER (
    S_USNAME varchar(32) character set WIN1251,
    S_USPSW varchar(9) character set WIN1251,
    S_SERVER varchar(32) character set WIN1251,
    S_RESERVED varchar(32) character set WIN1251)
returns (
    LI_RC smallint)
as
BEGIN
     LI_RC = br_createuser(S_SERVER, S_USNAME, S_USPSW);
END

where br_createuser is a UDF declared as:
DECLARE EXTERNAL FUNCTION BR_CREATEUSER
CSTRING(32) CHARACTER SET WIN1251, CSTRING(32) CHARACTER SET WIN1251,
CSTRING(9) CHARACTER SET WIN1251
RETURNS SMALLINT
ENTRY_POINT 'br_createuser' MODULE_NAME 'br_utils';

br_createuser has the next realization, which was taken from documentation:
int br_createuser (char *DBServer, char *UName, char *UPSW)
{
 ISC_STATUS  isc_status[20];
 USER_SEC_DATA user_data;
 int    li_rc;

 user_data.server = DBServer;
 user_data.user_name = UName;
 user_data.password = UPSW;
 user_data.protocol = sec_protocol_tcpip;
 user_data.dba_user_name = "SYSDBA";
 user_data.dba_password = "masterkey"; /* Don't hardcode this */
 user_data.sec_flags = sec_server_spec
      | sec_password_spec
      | sec_dba_user_name_spec
      | sec_dba_password_spec;

 isc_add_user(isc_status, &user_data);

 if(isc_status[0] == 1 && isc_status[1])
  {
  switch (isc_status[1])
   {
   case isc_usrname_too_long:
    // The user name passed in is greater than 31 bytes
    li_rc = -1;
    break;
...<here I cut the text to shorten its length>
   default:
    // Unknown error
    li_rc = -100;
    break;
   }
  }
 else
  {
  li_rc = 1;
  }
 return li_rc;

Quote
}

As I said server restarts, ISQL looses connection, but I can see that new
user is created.

By the way, UDF br_createuser has been successfully executed from C++ (not
trough stored procedure).
I suspect that the matter is in the declaration of UDF, but I don't know
where exactly.
Does anybody have any idea about it?

Please help me.

Nickolay S.

 

Re:ISQL-StoredPros-UDF-IBAPI doesn't work?


In article <3d11fecd$1_2@dnews>, nickolay...@chat.ru says...

Quote
> By the way, UDF br_createuser has been successfully executed from C++ (not
> trough stored procedure).

        Try executing the UDF in normal SQL, e.g.:

SELECT
  br_createuser( ... )
FROM
  RDB$DATABASE

        ...and see if you get the same problem (i.e., eliminate the proc
as a possible cause).  I'm betting you do.

Quote
> I suspect that the matter is in the declaration of UDF, but I don't know
> where exactly.

        My guess would be an unhandled exception -- or perhaps it's not
thread-safe.  

        HTH,

        -Craig

--
 Craig Stuntz (TeamB) Vertex Systems Corp. Columbus, OH
     Delphi/InterBase WebLog: http://delphi.weblogs.com
     InterBase PLANalyzer (Free IB optimization tool):
          http://delphi.weblogs.com/IBPLANalyzer

Re:ISQL-StoredPros-UDF-IBAPI doesn't work?


Thanks a lot.

Unfortunately I've got the same result.

Nickolay S.

"Craig Stuntz [TeamB]" <cstu...@nospamplease.vertexsoftware.com> wrote in
message news:MPG.177bd4b2380e49dc9897ad@newsgroups.borland.com...

Quote
> In article <3d11fecd$1_2@dnews>, nickolay...@chat.ru says...
> > By the way, UDF br_createuser has been successfully executed from C++
(not
> > trough stored procedure).

> Try executing the UDF in normal SQL, e.g.:

> SELECT
>   br_createuser( ... )
> FROM
>   RDB$DATABASE

> ...and see if you get the same problem (i.e., eliminate the proc
> as a possible cause).  I'm betting you do.

> > I suspect that the matter is in the declaration of UDF, but I don't know
> > where exactly.

> My guess would be an unhandled exception -- or perhaps it's not
> thread-safe.

> HTH,

> -Craig

> --
>  Craig Stuntz (TeamB) Vertex Systems Corp. Columbus, OH
>      Delphi/InterBase WebLog: http://delphi.weblogs.com
>      InterBase PLANalyzer (Free IB optimization tool):
>           http://delphi.weblogs.com/IBPLANalyzer

Re:ISQL-StoredPros-UDF-IBAPI doesn't work?


Hi, Craig.

Do you know how I could create user from my application. I don't us C[++],
but PowerBuilder.
I had hoped to use the way I described.

Thanks in advance.
Nickolay S.

Re:ISQL-StoredPros-UDF-IBAPI doesn't work?


Try

Quote
> DECLARE EXTERNAL FUNCTION BR_CREATEUSER
> CSTRING(32) CHARACTER SET WIN1251, CSTRING(32) CHARACTER SET WIN1251,
> CSTRING(9) CHARACTER SET WIN1251

  RETURNS INTEGER BY VALUE

Quote
> ENTRY_POINT 'br_createuser' MODULE_NAME 'br_utils';

--
Ivan
http://www.volny.cz/iprenosil/interbase

Re:ISQL-StoredPros-UDF-IBAPI doesn't work?


In article <3d12cfb1_1@dnews>, nickolay...@chat.ru says...
Quote
> Unfortunately I've got the same result.

        That means the bug is in the UDF, not in the proc.

        -Craig

--
 Craig Stuntz (TeamB) Vertex Systems Corp. Columbus, OH
     Delphi/InterBase WebLog: http://delphi.weblogs.com
     InterBase PLANalyzer (Free IB optimization tool):
          http://delphi.weblogs.com/IBPLANalyzer

Re:ISQL-StoredPros-UDF-IBAPI doesn't work?


In article <3d12d16f_2@dnews>, nickolay...@chat.ru says...
Quote
> Do you know how I could create user from my application. I don't us C[++],
> but PowerBuilder.

        The best way would be to use the services API.  You can call a DLL
from PowerBuilder, right?

http://community.borland.com/article/0,1410,27002,00.html

        Note that this only works with IB 6 SuperServer.  If you're using
IB pre-V6 or IB6 classic architecture, you need to use the deprecated
isc_add_user function.

        Neither the services API nor the isc_add_user function can be
called from a proc -- you call it from your application instead.

        HTH,

        -Craig

--
 Craig Stuntz (TeamB) Vertex Systems Corp. Columbus, OH
     Delphi/InterBase WebLog: http://delphi.weblogs.com
     InterBase PLANalyzer (Free IB optimization tool):
          http://delphi.weblogs.com/IBPLANalyzer

Re:ISQL-StoredPros-UDF-IBAPI doesn't work?


Thanks, Craig

Quote
> The best way would be to use the services API.  You can call a DLL
> from PowerBuilder, right?

Yes, I've done that. Alright. Unfortunately I didn't manage to call
isc_add_user directly from PowerBuilder. I had to insert my dll between PB
and isc_add_user.

Quote
> Neither the services API nor the isc_add_user function can be
> called from a proc -- you call it from your application instead.

I suppose, you're not right. I'm successfully using service API functions in
UDFs which are called from stored procedure to get IB Server users, to get
connected users (isc_service_start, isc_service_attach, isc_service_query,
isc_service_detach, isc_attach_database, isc_database_info).

Good luck.
Nickolay S.

Re:ISQL-StoredPros-UDF-IBAPI doesn't work?


It really works!!!

Thanks a lot Ivan.

Nickolay S.

Quote
"Ivan Prenosil" <preno...@ms.anet.cz> wrote in message

news:3d130d13_2@dnews...
Quote
> Try

> > DECLARE EXTERNAL FUNCTION BR_CREATEUSER
> > CSTRING(32) CHARACTER SET WIN1251, CSTRING(32) CHARACTER SET WIN1251,
> > CSTRING(9) CHARACTER SET WIN1251
>   RETURNS INTEGER BY VALUE
> > ENTRY_POINT 'br_createuser' MODULE_NAME 'br_utils';

> --
> Ivan
> http://www.volny.cz/iprenosil/interbase

Re:ISQL-StoredPros-UDF-IBAPI doesn't work?


In article <3d1454f5_1@dnews>, nickolay...@chat.ru says...

Quote
> > Neither the services API nor the isc_add_user function can be
> > called from a proc -- you call it from your application instead.

> I suppose, you're not right.

        I didn't say from a UDF, I said from a proc.

Quote
> I'm successfully using service API functions in
> UDFs which are called from stored procedure to get IB Server users, to get
> connected users (isc_service_start, isc_service_attach, isc_service_query,
> isc_service_detach, isc_attach_database, isc_database_info).

        If this is working for you then why did you post?

        FWIW, I don't recommend doing this from a UDF/proc.  The services
API is meant to be called from a client.  UDFs are supposed to be fast
and simple, and adding users is neither.

        -Craig

--
 Craig Stuntz (TeamB) Vertex Systems Corp. Columbus, OH
     Delphi/InterBase WebLog: http://delphi.weblogs.com
     InterBase PLANalyzer (Free IB optimization tool):
          http://delphi.weblogs.com/IBPLANalyzer

Re:ISQL-StoredPros-UDF-IBAPI doesn't work?


I think it's impossible to call API function from stored procs because API
functions have __stdcall specification, but UDFs, which you can call from
procs should have __cdecl specification. So you have to write additional
__cdecl functions (UDFs) to call __stdcall API functions.

Nickolay S.

"Craig Stuntz [TeamB]" <cstu...@nospamplease.vertexsoftware.com> wrote in
message news:MPG.1780eb2859282c0b9897c3@newsgroups.borland.com...

Quote
> In article <3d1454f5_1@dnews>, nickolay...@chat.ru says...
> > > Neither the services API nor the isc_add_user function can be
> > > called from a proc -- you call it from your application instead.

> > I suppose, you're not right.

> I didn't say from a UDF, I said from a proc.

> > I'm successfully using service API functions in
> > UDFs which are called from stored procedure to get IB Server users, to
get
> > connected users (isc_service_start, isc_service_attach,
isc_service_query,
> > isc_service_detach, isc_attach_database, isc_database_info).

> If this is working for you then why did you post?

> FWIW, I don't recommend doing this from a UDF/proc.  The services
> API is meant to be called from a client.  UDFs are supposed to be fast
> and simple, and adding users is neither.

> -Craig

> --
>  Craig Stuntz (TeamB) Vertex Systems Corp. Columbus, OH
>      Delphi/InterBase WebLog: http://delphi.weblogs.com
>      InterBase PLANalyzer (Free IB optimization tool):
>           http://delphi.weblogs.com/IBPLANalyzer

Other Threads