Hi All,

I am trying to write a tcp client (redis) in free pascal, and found this function marked as deprecated:

Function Connect(Sock:longint;const addr:TInetSockAddr;var
SockIn,SockOut:text):Boolean; deprecated;

My question is, why it is deprecated, or, what is the replacement for this function?

Thanks

Hi,

I think that Connect was replaced by fpconnect that is more cross platform, so you should use fpconnect instead of Connect. The same happens with the other sockets functions like Accept, Bind, etc. You should use fpaccept, fpbind, etc.

See unit rtl/inc/socketsh.inc

Best Regards.

Yes, I read the source code and found that fpconnect + socktotext = connect, but just don't know why combining them together is a bad idea :-) thanks

2013/4/16 Victor Campillo

In our previous episode, Xiangrong Fang said:

It was deprecated for two reasons:

1. the format, returntype and parameter typing level and was never updated to 2.0
2. errorhandling is limited to non existent. It is mainly useful for small scripting stuff like logging on a local net. I wouldn't recommend doing anything serious with it.

There was some discussion, and IIRC Daniel wanted to have a look at it, and try to describe and improve errorhandling but that is still pending.

My question is, why it is deprecated, or, what is the replacement for this function?

It is not recommended for use currently.

In our previous episode, Victor Campillo said:

That is the general idea yes, but the non fp versions have already mostly been removed in 2.6.0 and +.

The "text" versions however were never updated because IMHO they were ill defined, and bugreports about them lingered forever.

So I deprecated them pending a review. Maybe it is wise to remove them in 2.8.0 if by then nothing has happened. I made a note about it.

As far as I can see, all errors are returned via inoutres/IOResult in the same way as happens with disk-based IO.

Jonas

In our previous episode, Jonas Maebe said:

Yes. An attempt is made to map some of the errors to I/O. Anyway, if sb wants to support and modernize them, I have no problem with that. But that hasn't happened since 2005-2007 or so.

That would also make it possible to directly map socketerror to getlastresult/fpgeterrno, instead of caching it in another threadvar.

What needs to be modernised about it? It's not like our disk i/o error code handles many more different error codes, nor are those errors much more specific. I remember the old discussion, but I never understood how the error reporting of the sockets code and the generic I/O code differed in a way that made the former worse than the latter.

That would also make it possible to directly map socketerror to getlastresult/fpgeterrno, instead of caching it in another threadvar.

IIRC the problem with removing socketerror would be that it could easily break existing code (given that socketerror is not overwritten by calls other than those from the sockets unit).

Jonas

using the socket like read/write file is very convenient. This is the first time I use fpc to write socket program, it is much easier than I used Indy or ICS in Delphi several years ago.

I hope to "modernize" or make it more stable, instead of deprecate it. Of course deprecate Connect and use fpConnect + Sock2Text manually is not a big deal.

2013/4/16 Jonas Maebe

You can get some hints from minilib
https://sourceforge.net/p/minilib
"socket" directory

Hi All,

I have an application build with Delphi7+ADO connecting to MSSQL database, and planning to migrate to FPC+SQLdb.

It has many stored procedures which have OUT parameters to retrieve value from database. I am trying to migrate by using TMSSQLConnection+TSQLQuery, but still I can not settle it. I can execute and pass values to stored procedures, but not the other way. The RETURN_VALUE (default result) can not be retrieved either.

I would appreciate any advice.

Toru

In our previous episode, Jonas Maebe said:

> sb > wants to support and modernize them, I have no problem with that. > But that > hasn't happened since 2005-2007 or so.

The modernize bit above also refers to the changes done during the 2.0 RTL update, the functions still have a 1.0.x signature:

- Name (fp-)
- parameter typing
- parameter returnvalue. IOW return a proper -1

Besides that the Unix socket unit has extra overload versions which are used in the examples, which means that the examples only work on *nix.

What needs to be modernised about it? It's not like our disk i/o error code handles many more different error codes, nor are those errors much more specific

There are specific disk related error codes defined. Not for sockets. IIRC there is no way to distinguish between a clean disconnect and an unexpected error. The information is lost, since the error is cached nowhere.

Attempts with non-blocking sockets will block unless fpread returns ewouldblock and ewouldblockeagain

The above is a bit grasping at straws from memory(the attempts below) and a quick look. The problem IMHO is more that nobody knows.

example with it (when Ales was starting out with lnet), and failed miserably. That is the only serious attempt at every doing something with these that I know.

If I'm honest, they were experimenting with non-blocking sockets too though, so that might have confused the picture though.

Btw the number of reactions to the deprecation has been mostly zero.

True. And that behaviour was never deprecated because "socketerror" was considered needed to abstract socket errors in an OS dependent manner when sockets also was expanded to be supported on Windows.

I never considered the proper deprecation way (deprecating socketerror, introducing a new function) worth the trouble, specially since the number of users with such code are probably extremely low (since all pre 1.0.x functions that rely on it are removed or deprecated, oldlinux isn't compiled anymore etc)

I always assume these functions would either be removed or moved and updated anyway.

Marco van de Voort wrote

In our previous episode, Jonas Maebe said:

.
.

Correct - GetLastError is not supported on all platforms (OS/2, Netware, GO32v2, etc.) because the concept is simply not portable to platforms maintaining information about last error states at the level of different subsystems rather than having single place for everything ranging from basic I/O (console, disk devices) through (possibly different) GUI layer(s)/implementations and various network communication layers up to an e-shop implementation ( ;-) ) as soon as it becomes shipped and marketed as part of the operating system.

Tomas

Hello Toru,

Not sure if it works with SQLdb or ADO, but it has been working for some other tasks that I've been involved with (not FPC, but delphi based, though).

You can have the result as through the select statement. Have a TSQLQuery to run the following query:

SET NOCOUNT ON
declare @var1 int; -- whatever type of the out parameter you need. exec YourStoredProc @outvar = @var1 output
select @var1 as var1

then
var q: TSQLQuery;
 i:integer;
...
q.Active:=true;
if not q.eof then
  i:=q.FieldsByName('var1').asInteger;

Hope that helps.

thanks,
Dmitry

Hi Dmitry,

Thank you very much for your advice. I am sure your code should work, but unfortunately I can not modify the existing stored procedures.

I will explain what I have been trying so far.
Suppose you are using the following stored procedure,

----------------------------------------------
CREATE procedure [dbo].[IncValue]

@Param int OUTPUT

AS

SET NOCOUNT ON

set @Param = @Param + 1

return 100
----------------------------------------------

The original program uses TADOStoredProc in Delphi 7, which is very easy to handle stored procedure.
How to use it is like this:

var
   r,i: Integer;
   DBP: TADOStoredProc;
begin
...
    DBP.ProcedureName:='IncValue';
    DBP.Parameters.Refresh;
    DBP.Parameters.ParamValues['@Param']:=3; //set @Param as 3
    DBP.ExecProc;
    r:=DBP.Parameters.ParamValues['@RETURN_VALUE']; //got r=100, OK!
    i:=DBP.Parameters.ParamValues['@Param']; //got i=4, incremented OK!

If you don't have TADOStored, still you can do the equivalent thing by using TADOQuery. (a little tricky, though...)

var
   r,i: Integer;
   DBQ: TADOQuery;
begin
...
    DBQ.SQL.Text:='IncValue';
    DBQ.Parameters.CreateParameter('@RETURN_VALUE',ftInteger,pdReturnValue,0,0);
    DBQ.Parameters.CreateParameter('@Param',ftInteger,pdInputOutput,0,3); //set @Param as 3
    DBQ.Parameters.Command.CommandType:=cmdStoredProc;
    DBQ.ExecSQL;
    r:=DBQ.Parameters.ParamValues['@RETURN_VALUE']; //got r=100, OK!
    i:=DBQ.Parameters.ParamValues['@Param']; //got i=4, incremented OK!

With analogical thinking, I expected that the following might work for TSQLQuery in FPC:

var
   r,i: Integer;
   DBQ: TSQLQuery;
begin
...
    DBQ.SQL.Text:='IncValue ';
    DBQ.Params.CreateParam(ftInteger,'@RETURN_VALUE',ptResult);
    DBQ.Params.CreateParam(ftInteger,'@Param',ptInputOutput).AsInteger:=3;
    DBQ.ExecSQL;
    r:=DBQ.ParamByName('@RETURN_VALUE').AsInteger; //got r=0, NG
    i:=DBQ.ParamByName('@Param').AsInteger; //got i=3, unchanged NG

It does not work as I expected.

I read fpc db tutorials and googled, but I could not find the answer for this specific issue. Would anyone have an idea which of the following is the truth?

1. My program is wrong or incomplete.
2. TSQLQuery (or TMSSQLConnection) does not support OUTPUT parameter.
3. TSQLQuery should be able to handle OUTPUT parameter, but currently a bug exists.

Thank you very much for reading my long mail.

Toru

ExecSQL executes SQL and does not return a value.
Try DBQ.Open to return values. No idea if the mssqlconn connector supports output parameters in SPs though; best to just try.

I read fpc db tutorials and googled, but I could not find the answer for this specific issue. Would anyone have an idea which of the following is the truth?

Documentation:
http://www.freepascal.org/docs-html/fcl/sqldb/index.html

Hi Toru,

You can use SQLdb, MSSQL, StoredProcs and OUTPUT params, without problems... but you have to change a little bit your code. 1- Use a TSQLScript
2- Following your example above, use this SQL:
declare @param int = :param
exec IncValue @param output
select @param as value
3- Call Open instead of ExecSQL

You can try Greyhound[1] to abstract this. See the example:

procedure TForm1.ToolButton9Click(Sender: TObject);
var
  SC: TghSQLClient;
  DS: TDataSet;
begin
  DS := nil;
  SC := TghSQLClient.Create(DM.coRemote);
  try
    SC.Script.Text := 'declare @param int = :param '#13
                    + 'exec IncValue @param output '#13
                    + 'select @param as value';
    SC.Params['param'].AsInteger := 3;
    SC.IsBatch := True;
    SC.Open(DS);
    FOwnerForm.MsgInfo(DS.FieldByName('value').AsString);
  finally
    DS.Free;
    SC.Free;
  end;
end;

[1] https://github.com/mdbs99/Greyhound
(please, see the dev-0.1.6 branch for new features and changes)

Marcos Douglas

Sorry, I did not explained these:
DM.coRemote = DM is a DataModule and coRemote is a instance of TghSQLConnector class.
FOwnerForm.MsgInfo = change this for ShowMessage function.

Best regards,
Marcos Douglas

It appears that SQLdb doesn't support (MS) SQL variables.

thanks,
Dmitry

Hi Reinier,

I changed the SQL statement to 'EXEC IncValue ' to avoid error, and tried DBQ.Open, but the result was the same.

I suppose OUTPUT parameter is not "result set" but something like reference parameter (not exactly though). Actually I use both ExecSQL/Open according to whether the stored procedure return result set (include select statement) or not.

Anyway, thank you for your suggestion and docs link.

Toru

(snip)

Hi Marcos,

As to TSQLScript, it does not seem to have "Open" method. It may be for execute multiple SQLs which does not return result set.

And I checked your library. It looks simple and nice. But your suggestion is based on the same idea shown by Dimitry in previous mail in this thread, which require modification of existing stored procedures. Unfortunately, I can't modify them.

One more thing is that some procedures return both result set and value through OUTPUT parameter. In this case, if I would retrieve OUTPUT parameter as a result set, I must handle multiple result sets.

Thank you and all lists,

Toru

Thanks for the update, Toru, will have a look if I have time....

You right, sorry...

And I checked your library. It looks simple and nice. But your suggestion is based on the same idea shown by Dimitry in previous mail in this thread, which require modification of existing stored procedures. Unfortunately, I can't modify them.

(Thank you)
Not so fast. I used your SP, without changes!
The tip:
https://github.com/mdbs99/Greyhound/blob/master/src/gh_sqldblib.pas#L115

For MSSQL, the TStatementType need to be [stExecProcedure]. The StrToStatementType method is protected so, you need to code a new class... or use Greyhound.

Did you test the code as I sent before?
Take this example:
https://github.com/mdbs99/Greyhound/blob/master/examples/mssql/t1.pas ...and modify it to use the code I sent before.

One more thing is that some procedures return both result set and value through OUTPUT parameter. In this case, if I would retrieve OUTPUT parameter as a result set, I must handle multiple result sets.

I never tried to use a result set as output parameter before. But you can use another SP to call the "real SP" just for the application. ;-)

Regards,
Marcos Douglas

Thank you again for your advice. Now I understand what you mean. Actually I made a descendant class of TSQLQuery, which override StrToStatementType to force TStatementType on stExecPrcedure, but it did not work then. Your suggestion is that in addition to it, I should create a small sub-query to get OUTPUT parameter as result set.

I will try to proceed. On the other hand, I'm wondering if TSQLQuery (or TMSSQLConnection) would natively support OUTPUT parameter and RETURN_VALUE...

Toru

You need to override StrToStatementType method of TMSSQLConnection, not TSQLQuery.
The problem is the connection is global to all TSQLQuery instances so, you need to 'save' the status before call StrToStatementType to restore after.
The idea to create a SP to call the real SP that have OUTPUT parameters is to facilitate the integration with MSSQL... but I never needed to do this before.

I will try to proceed. On the other hand, I'm wondering if TSQLQuery (or TMSSQLConnection) would natively support OUTPUT parameter and RETURN_VALUE...

Well, even using Delphi (4~7) I never used RETURN_VALUE. I always used MSSQL but I never needed this feature.

Did you try Greyhound?

Marcos Douglas

Sorry, my mistake.

Thank you for your hint.

All my stored procedures use RETURN_VALUE in order to return error code. I think it useful, but I also know everyone has his/her own programming style.

Did you try Greyhound?

I haven't dig into it yet, but I will try later.

Thank you!

Toru

Ok, I will do some test to know if SQLdb handles the RETURN_VALUE parameter.

Ok

Thank you!

Regards,
Marcos Douglas

Toru Takubo wrote / napísal(a):

Your program is ok from your point of view ;-))

2. TSQLQuery (or TMSSQLConnection) does not support OUTPUT parameter.

TMSSQLConnection does not support handling of return status and output parameters of stored procedures
Theoretically it can be added, but it will complicate things ;-)

3. TSQLQuery should be able to handle OUTPUT parameter, but currently a bug exists.

it is not a bug in TSQLQuery, but a missing feature in TMSSQLConnection

-Laco.

Thanks Laco, updated
http://wiki.lazarus.freepascal.org/mssqlconn
for reference

Hi Laco,

Thank you for your clarification on the above issue.

TMSSQLConnection is newly supported on FPC 2.6 so I don't expect everything at this moment, but I would appreciate if this feature could be supported at some future time.

Toru