I've had a search for the solution but can't find it:-

We have a client who has developed 200+ scripts in their DBISAM-based system which simply execute in a TDBISAMQuery and as a by-product of the last statement being a SELECT, get a result set back via a connected TDatasource into a connected DB grid. Now we want to move them to our EDB-based system, we find that while we can use TEDBScript and ConvertSQL to deal with the scripts, we can't get at the results of the final SELECT. As far as we can see, we would have to rewrite all the scripts to create and return a cursor, and hence make the result set in the TEDBScript component accessible to Delphi code.

Or is there another way? [He says hopefully....]

Charles,

Sounds about right, if I understood it correctly.
You will have to use a TEDBScript (or TEDBStoredProcedure), convert the SQL to Dynamic SQL and return a dataset to the application using a CURSOR WITH RETURN.

Just to clarify, those scripts are loaded by the application into a TDBISAMQuery in runtime when needed?
 
--
Fernando Dias
[Team Elevate]

Fernando Dias wrote:

Charles,

Sounds about right, if I understood it correctly.
You will have to use a TEDBScript (or TEDBStoredProcedure), convert the SQL to Dynamic SQL and return a dataset to the application using a CURSOR WITH RETURN.

CHB: Sounds like a lot of work.

Just to clarify, those scripts are loaded by the application into a TDBISAMQuery in runtime when needed?

CHB: That's right, a number of SQL statements terminated with semi-colon
 
--
Fernando Dias
[Team Elevate]

CHB: We have a solution. I've grabbed the code from TEDBScript.ConvertSQL and modified it to return a list of SQL statements (removing any line breaks) in a TStrings. We'll then execute each line one after another in a TEDBQuery. We have a few places we'll need to update our Delphi code to do that, but we would have had to mod code anyway with TEDBScript.

Charles


You will have the code for ConvertSQL (look for edbcomps.pas) and if you look at it all its doing is stuffing an EXECUTE IMMEDIATE in front of each statement which, depending on yopur client's scripts,  may mean that that its not producing what you want anyway.

Can you post an example of the scripts that your client has written (preferably something complex that has to return a result) because depending on just what is needed it may be possible to subclass EDBScript to deliver the result you want. If there are intermediate results it may get more difficult.

Roy Lambert

Thanks all, I have a solution up and running. I sub-classed TEDBQuery and internally, using code pulled from ConvertSQL, feed the query a statement at a time via a new ExecScript member procedure.

Charles


Sounds about what I would have done - actually I think I did do something like that way back when I first switched to ElevateDB. hmmm must go and check.


Roy Lambert