BorlandTalk.com

Alessandro · Guest

Hi. I need make my application independece database. I think, instead of
write a sql command in my application, create a componente where i fill some
properties and have a metodo (getSelect, for example) that return a command.
Then, when i change the database i need only change this metodo.
Example:

wl_Select := TcmdSQL.create(tablename)
wl_Select.fieldsname.add('field1')
wl_Select.fieldsname.add('field2')
wl_Select.fieldsname.add('field3')
wl_Select.fieldsname.add('field4')
wl_Select.criterios.add ....
wl_Select.joins.add....
....
.....
wl_sql := wl_Select.getSelect;
wl_Select.free;

then wl_sql is "select field1, field2, field3 from tablename ....."
BUT I'M THINK IS VERY WORK COMPAREESD WITH WRITE A COMMAND SQL. ANYBODY HAVE
A SOLUTION FOR THIS AND CAN SHARED WITH ME?

Thnaks
Alessandro

Morpheus · Guest

But then you's still ahve to change the application itself when you chnage
to a different database. The nuances of the SQL dialect between different
vendor platforms are subtle, but they are still there.
If you plan to write something that you can deploy against both MSSQL and
Interbase for example, you need to observer the ANSI SQL rules to a lrge
extent. This will ensure that most of your SQL code will run OK. Of course
there are differences in the way triggers and stored procs are written
between the tow, so much so that some stored procs written for Interbase
will fail in SQLSERVER ands vice versa.
Having said that, what we do is put all of our SQL code (queries, that is)
in a SQL table in the database. The only query component in the system that
has a hard coded query is the one which returns the content of this table,
and therefore each of the SQL queries required by the system.
We implement another table, called AllTables which lists all of the query
component names, together with a pointer to the Query table for the location
of the SQL code, as well as two flags to indicate the opening sequence of
the tables, and wheter or not the table must be opened at the time of
datamodule creation.
two loops then load the sql code into the various query components (we use
FindComponent in the datamodule to get a pointer to the query component
named in the AllTable table), then a second loop to opne the tables in the
correct sequence.
We include in all of our software a form which allows us to add new queries
to the Query table, and tables to the AllTable table. Obviously these are
not available to the normal user.
This way, we avoid having to recompile an EXE if one of the queries fail, we
simply send out an update to the query which the user loads via a custom
interface.
It also makes development easier, as if a query fails, you do not have to go
back to the IDE, you can update it in the running software, then re-open the
query component.
I don't know whether this is precisley what you want, but I do hope that it
helps to point you in the right direction.

Oh, BTW, this apprach facilitates on the fly component creation as well. We
have a tab in oneapplicatoin that must have a varying number of DBGrids on
it depending upon the entries in a table in the database. With a block of
about 240 lines of code, the enture tab is populated with dbgrids (around
seven at present) with custom titling of column headers, etc. as well as
editing facilities.

Hope this helps,
Regards,
Morpheus
In the OnCreate event of the datamodule, the
"Alessandro" <alessandrocamara (AT) hotmail (DOT) com> wrote

Wayne Niddery [TeamB] · Guest

Alessandro wrote:

Alessandro · Guest

Thank you, very much.
One question more. In your case, you put some update, insert, etc command
sql in application? This commands you put too in query table database?
Thanks
Alessandro

"Morpheus" <morpheusATredpointDOTcoDOTza> escreveu na mensagem
news:41011382 (AT) newsgroups (DOT) borland.com...

Bruno Lovatti · Guest

http://www.remobjects.com/page.asp?id={A1FEB4A5-CC12-4FFF-9C47-7116910DCBB8}

Morpheus · Guest

Hi Allesandro,
No, I am using ADO components to connect to the DB, so I simply process
updates, inserts and delete directly using the appropriate Methods of the
ADODataSet commponent. Remeber to set the "Unique Table" property of the
ADODataSet component if you delete a row from a query that returns a joined
result set. I got caught with this until a meber of Team B pointed out what
I was doing wrong.

If you want explicitly process INSERTs, UPDATEs and DELTE' you can certaint
put them into the query table. I find it best to use a TADOCommand for such
events, or you can use the Execute metohd of the TADOConnection component,
sometgin that I do quite frequently. Rember that you cannot use a
parameterised query if you choose to do this. You have to build the entire
SQL string before executing it in the Execute method.

Let me know if you need any further info.

Regards,
Morpheus

"Alessandro" <alessandrocamara (AT) hotmail (DOT) com> wrote

Alessandro · Guest

Thank you more one time Morpheus.
In many cases i have a block of commands, include database.starttransaction
and commits between this commands. In this cases, how can you do?

Thanks
Alessandro

"Morpheus" <morpheusATredpointDOTcoDOTza> escreveu na mensagem
news:41026bc6 (AT) newsgroups (DOT) borland.com...

Morpheus · Guest

Is this SQL code or Delphi code? If it is SQL code, then irecommend that you
use the BeginTrans, CommitTrans and RollBackTrans methods of your
TADOConnection to manage transactions. I do this and it works very well for
me.
My code would ook something like this...

try
MyQuery.Close;
MyQuery.CommandText := GetCommandText(MyQuery); //This is the line which
loads the SQL code from the Query Table, using the value in the Tag property
of the TADODataSet component
if not TADOConnection.InTransaction then
TADOConection.BeginTrans;
MyQuery.ExecSql;
TADOConnection.CommitTrans;
except
on E:Exception do begin
TADOCnnection.RollBackTrans;
MessageDlg('Error processing transaction. It has been rolled
back.',mtError,[mbOK],0);
end;
end;
Hope this helps.
Regards,
Morpheus

"Alessandro" <alessandrocamara (AT) hotmail (DOT) com> wrote