[Lazarus] Accessing MS SQLServer database from Lazarus/FPC?

[Bo Berglund]

On Fri, 21 Jan 2011 17:30:57 +0100 (CET), michael.vancanneyt at wisa.be
wrote:

>
>
>
>While I will not comment on the general validity of your statement, for
>SQLDB it is certainly not correct. SQLDB was developed exactly to serve a
>wide range of programmer needs. Of course, the engines we use ourselves will
>be tested best, this is only natural.
>
>That said, SQLDB is under continuous development; The current case was not one
>which we were familiar with, so it is entirely possible that it doesn't
>work currently. That doesn't mean we're not open to fixing it, and 
>enhance SQLDB in the process.
>
>So, in that spirit:
>Unless I have missed it in the SQL syntax sidetrack debate, I still haven't 
>seen the original poster's error message, which I asked for in the very first 
>reply to his original post.
>
>Without the error message, we cannot begin to start looking for a solution.
>
>Michael.

I can now answer that after I have restarted the virtual machine where
I was investigating this:

The error message captured as the Exception.Message is:

'Cannot open a non-select statement'

The code I executed at this time is (mostly copied from the wiki
page):

[code]
procedure TForm1.DatabaseTest;
var
  S: String;
  conn: TODBCConnection;
  query: TSQLQuery;
  transaction: TSQLTransaction;
begin
  conn := TODBCCOnnection.Create(nil);
  query := TSQLQuery.Create(nil);
  transaction := TSQLTransaction.Create(nil);
  try
    try
      conn.HostName := '<name of our db server>';
      conn.DatabaseName := '<name of odbc data source>';
      conn.Transaction := transaction;
      conn.UserName:= '<user login name>';
      conn.Password:= '<password>';
      query.DataBase := conn;
      query.UsePrimaryKeyAsKey:=false;
      query.SQL.Text := 'EXEC SelectDongleData @SerialNo=2485';
      query.Open; <== Exception here!
      S := '';
      while not query.EOF do
      begin
        S := S + query.FieldByName('AppNo').AsString + #13#10;
        query.Next;
      end;
    finally
      query.Free;
      conn.Free;
    end;
  except
    on E: Exception do
    begin
      S := E.Message; <== Exception message text picked up here
      ShowMessage(S);
    end;
  end;
  Memo1.Text:= S;
end;

[code]

I might add that when I replace the SQL.Text entry with the actual
procedure code for the case I am testing here (the procedure makes
different processing depending on parameter values) then I get proper
database execution.
In this case the procedure executes a SELECT statement with an inner
join so I entered this into the SQL.Text property.
So the problem is entirely due to the inability of the component to
handle an EXEC statement that actually returns records.


I would certainly be very intereated in a fix to the SQLdb system for
this problem, rather than starting to investigate the innards of
ZEOSlib....


-- 
Bo Berglund
Developer in Sweden