Board index » cppbuilder » writing own query-component

writing own query-component


2003-07-03 03:24:25 PM
cppbuilder2
Hi,
I have 2 questions concerning writing a query-component:
1)
I would like to write my own Query, for using SQLite into Builder.
My component should be able to work with TParams as a TQuery. I am
able to do this, and internally, the SQL statement is changed like
in this example (this is the same way as TQuery works I think):
the statement entered in the SQL property:
select * from table where column1 = :column1 and column2 = :column2
internally this is changed like this
select * from table where column1 = ? and column2 = ?
Now is my question: before executing, the question marks should be
replaced off course. But in case of an integer, this should be replaced
by the number converted to a string, and in case of a string, this shoud be
replaced by the string between quotes. So depending on the datatype,
a different 'translation' should happen.
Is there allready code provided for doing so, or do I need to write this
code completely myself? . In both cases, if somebody could help me a bit, I
would by very gratefull.
2)
I just found out that if the datatype of a parameter is for example an
Integer, and then you execute following code:
Query->ParamByName("theparam")->AsString = 12345;
then the DataType is changed from Integer to String. If you will execute
the query, will the parameter be replaced by 12345 or by '12345'?
regards,
Koen
 
 

Re:writing own query-component

Koen wrote:
Quote
the statement entered in the SQL property:
Are all the TStrings of the SQL property considered to be ONE query ?
Quote
select * from table where column1 = :column1 and column2 = :column2
Never used this component and I don't understand your syntax ':colomn1'
and how that would be changed to a '?'
But if I had:
int Age = 23;
AnsiString Query
= "SELECT Name,Street,Place FROM Addresses WHERE Age = "
+ IntToStr ( Age );
And if I had:
AnsiString Place = "Amsterdam";
AnsiString Query
= "SELECT Name,Street,Place FROM Addresses WHERE Place = '"
+ Place + "'";
And assign the Query like:
Query1->SQL->Clear();
Query1->SQL->Add ( Query );
Hans.
 

Re:writing own query-component

Hans Galema wrote:
Quote
Never used this component and I don't understand your syntax ':colomn1'
and how that would be changed to a '?'

Hi,
Thanks for your answer. I'll try to explain the use of ':column1'
If you have a query that needs to be executed a lot,
but the parameter is different, then You can use TParams;
for example, there is a table 'employees', with a field (amongst others)
emp_name, and you want to get all info of the records with a specific
name.
in the BDE-component TQuery, u can set the SQL statement as follows,
"select * from employees where emp_name = :emp_name"
When u want to open the query, u then can use the following code:
Query1->ParamByName("emp_name")->AsString = "thename";
Query1->Open ();
When executing this code, :emp_name is replaced by "thename", and
executed.
This way of working has the advantage that you don't need to re-adjust
the SQL when u want to search for another name.
Internally, this is the way of working (i think):
Firstly, all the parameters in the SQL-statement (those words
starting with ':' ), are replaced by a ? by using the command:
TParams::ParseSQL(AnsiString SQL, bool DoCreate);
So in the example the statement would become
"select * from employees where emp_name = ?"
Secondly, the question marks need off course be replaced by the
correct value.
"select * from employees where emp_name = 'thename'"
And now I want to have the same way of working in my component.
I allready am able to do the first step, but now I want to know
if I can execute step 2 with an existing function, or if I have
to replace the question marks manually by f.e. using StringReplace.
If I have to do this manually, I have to first check wich DataType
the parameter has, because in case of a string the question mark
needs to be replaced by "'" + Value + "'", in case of an integer it
needs to be replaced by IntToStr(Value), ...
If there would allready be a function that does this, I would be
stupid to write this code again. So that's what I want to know :)
Hopefully you now understand what I mean.
regards,
Koen
 

{smallsort}

Re:writing own query-component

Hans Galema wrote:
Quote
Koen wrote:

>
>Firstly, all the parameters in the SQL-statement (those words
>starting with ':' ), are replaced by a ? by using the command:

>Secondly, the question marks need off course be replaced by the
>correct value.

I consider that a very bad approch as you can then have many
questionmarks. Who would tell where every questionmark would
stand for? Why not a one step process ?

Because the parameters are created in the correct order. I don't
think you really understand. If you use a Query from BDE, Interbase,
dbExpress,... you can do the same thing, and they work all like
the way I explained (Check the help from Builder: See the property
Params from the query). I just want to have the same thing in my
own created component, but I don't really know how to do this exactly.
I appreciate you trying to help me!
greetz,
Koen
ps: I should start speaking in Dutch, since you are the only one
answering my questions, and I'm from the Flemish side of Belgium :p
 

Re:writing own query-component

"Koen" schreef>
Quote
Because the parameters are created in the correct order. I don't
think you really understand. If you use a Query from BDE, Interbase,
dbExpress,... you can do the same thing, and they work all like
the way I explained (Check the help from Builder: See the property
Params from the query). I just want to have the same thing in my
own created component, but I don't really know how to do this exactly.

Why don't you look it up in the VCL source? It's done there many times in a
correct way I suppose.
Quote

greetz,

Koen

ps: I should start speaking in Dutch, since you are the only one
answering my questions, and I'm from the Flemish side of Belgium :p
Kijk dan ook me{*word*249} naar de Prepare functie. Preparing en Unpreparing zijn
belangrijke functies bij het uitvoeren van queries volgens de help.
Peter
 

Re:writing own query-component

Peter Agricola wrote:
Quote
Why don't you look it up in the VCL source? It's done there many times
in a correct way I suppose.

Well, It seems that I only have the source of the Interbase components
(I thought, however, that the source of all VCL-components was available),
and I tried to look into it, but it's done with a lot of inheritance, and
I couldn't see the forest through the trees :( And that's why I thought of
asking it here.
Quote
Kijk dan ook me{*word*249} naar de Prepare functie. Preparing en Unpreparing
zijn belangrijke functies bij het uitvoeren van queries volgens de
help.

ok, thanks for the tip! I'll look at it for sure.
regards,
Koen
 

Re:writing own query-component

"Koen" schreef in bericht
Quote
Well, It seems that I only have the source of the Interbase components
(I thought, however, that the source of all VCL-components was available),
and I tried to look into it, but it's done with a lot of inheritance, and
I couldn't see the forest through the trees :( And that's why I thought of
asking it here.

IIRC it's an installation option. It should be in $(BCB)/Source/vcl
directory. If it's not just install it or copy it from the installation cd.
I pressume you use at least the professional version because you have
db-aware controls. Personal version does not have the source included.
Peter
 

Re:writing own query-component

Peter Agricola wrote:
Quote
IIRC it's an installation option. It should be in $(BCB)/Source/vcl
directory. If it's not just install it or copy it from the
installation cd. I pressume you use at least the professional version
because you have db-aware controls. Personal version does not have the
source included.


ok, thanks for the info, Peter!
regards,
Koen