Recent

Author Topic: Using string variables in TSQLScript  (Read 9265 times)

skinnyhate

  • New member
  • *
  • Posts: 8
Using string variables in TSQLScript
« on: March 10, 2013, 07:08:08 am »
Does anyone know if it is possible to use a string variable in TSQLScript? I can hard code an SQL query in there and execute it and get the result I want, but I want to make the search criteria a variable which is declared in source code somewhere.

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Using string variables in TSQLScript
« Reply #1 on: March 10, 2013, 07:57:26 am »
No idea, but won't an TSQLQuery do what you want? You can use parameters there.
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

goodname

  • Sr. Member
  • ****
  • Posts: 297
Re: Using string variables in TSQLScript
« Reply #2 on: March 10, 2013, 05:25:09 pm »
Think that you are looking for Filter or ServerFilter. This allows you to change the WHERE clause of the query without touching the other parts of the query.

Filter applies to the local dataset so it only filters fields in the select list. It does not handle advanced filter terms such as "IN" or "LIKE".

ServerFilter changes the WHERE clause sent to the server. As such it can handle any WHERE clause recognised by the database engine.

Just be aware to properly escape your string data.

BigChimp parameters are good but they don't handle varying search terms. For example in a people search you don't know at compile time if the user will search on first name, last name, sex, age, ... or any combination of these.

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Using string variables in TSQLScript
« Reply #3 on: March 10, 2013, 06:17:21 pm »
BigChimp parameters are good but they don't handle varying search terms. For example in a people search you don't know at compile time if the user will search on first name, last name, sex, age, ... or any combination of these.
Yawn... yes well, it's hard to know what the OP is asking for exactly since he's talking about TSQLScript which is completely different to TSQLQuery, isn't it?
In other cases, parameters are better (when running e.g. loops of insert statements)

I'm not going to guess all over the place and prefer the OP to spell out what he actually means.
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

goodname

  • Sr. Member
  • ****
  • Posts: 297
Re: Using string variables in TSQLScript
« Reply #4 on: March 10, 2013, 06:28:44 pm »
Sorry BigChimp misread the original question. Thought it said TSQLQuery and not TSQLScript. Now I can't make a guess as to what is being asked either.

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Using string variables in TSQLScript
« Reply #5 on: March 10, 2013, 06:51:46 pm »
No worries. I'm just a bit tired of guessing what people mean ;)
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

skinnyhate

  • New member
  • *
  • Posts: 8
Re: Using string variables in TSQLScript
« Reply #6 on: March 16, 2013, 05:05:24 am »
I am referring to the TSQLScript object in which you put SQL query text, such as:

Select *
from xyz
where state = 'Queensland'
;

The issue is that I do not want the state to be fixed as Queensland, I want the user to be able to select a state from a drop down menu, but I cannot see how to read variables with the TSQLScript object. I guess the best way for someone interested in helping to understand what I mean is try it yourself. Put the TSQLScript object on a blank form, select it and look at its properties. There is a field named Script and when you select it a little window opens where you can code in SQL and it stores the query. 

skinnyhate

  • New member
  • *
  • Posts: 8
Re: Using string variables in TSQLScript
« Reply #7 on: March 16, 2013, 05:09:28 am »
Hi Big Chimp,

Ah yes, I looked in the TSQLQuery object and it has the same functionality as the TSQLScript. I can enter SQL code in the InsertSQL field. BUT, same problem, I want to be able to read viariables defined in the main code and use them in the TSQLQuery object. How does one get variables in there??

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Using string variables in TSQLScript
« Reply #8 on: March 16, 2013, 07:34:07 am »
Hint: I don't think the INSERTSQL property is the right one.

Have a look here:
http://wiki.lazarus.freepascal.org/SQLdb_Tutorial1
http://wiki.lazarus.freepascal.org/SQLdb_Tutorial2
http://wiki.lazarus.freepascal.org/SQLdb_Tutorial3
and the official documentation at
http://www.freepascal.org/docs-html/fcl/sqldb/index.html

Hint: you're looking for parameterized queries/parameterised queries, e.g.: (air code, untested - in fact the property names may be wrong, please see the docs)
Code: [Select]
mysqlquery.sql.text:='select name,address,birthday from xyz where state = :stateparam';
mysqlquery.params.parambyname('stateparam').asstring:=tedit1.text;

PS: What is your Lazarus/FPC/version? See my first link in signature. This matters sometimes (e.g. mysql questions, mssql questions)
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

skinnyhate

  • New member
  • *
  • Posts: 8
Re: Using string variables in TSQLScript
« Reply #9 on: March 16, 2013, 11:56:11 am »
Hi Big Chimp,

Lazarus v 0.9.30.4 amd FPC v2.6.0

You are right, the InsertSQL field is not the right one and I have tried this using the SQL field instead. I have read though some of the documentation you linked to and it was helpful, got me going in the right direction, but the problem persists.

Here is the exact query I am putting into TSQLQuery.SQL field:

select distinct bsc_rnc_node
from mn_cell
where (cell_status = 'In Service' or cell_status = 'In Service - Not Accepted')
and bsc_rnc_node is not null
and state = :MY_STATE
order by bsc_rnc_node asc

Note the colon before the param MY_STATE... I have created a Param and named it MY_STATE.
In the source code, MY_STATE gets set from a text box as per the documentation, user entered. My understanding is that with ParseSQL = True, :MY_STATE should be replaced with whatever the user enters, but it isn't. In fact, I have been using a messagebox to display the SQLQuery.SQL.Text and it is just spitting out :MY_STATE in the query string. Can you advise further on this?

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Using string variables in TSQLScript
« Reply #10 on: March 16, 2013, 12:04:47 pm »
The SQLQuery.SQL.Text property is not changed; it keeps showing the parameter. The parameter value is replaced by sqldb when sending the data to the server.

You shouldn't have to mess with parsesql. If in doubt, check the official docs.

Without code it's hard to see what you're doing wrong; please post the snippet that fills the parameter (using code tags, the button with the # mark on the button toolbar)


Finally, you might consider upgrading Lazarus. 1.0.6 is out and has many fixes (including some for db controls).

Edit clarified first sentence
« Last Edit: March 16, 2013, 12:07:01 pm by BigChimp »
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

u2o

  • Jr. Member
  • **
  • Posts: 72
  • No message
Re: Using string variables in TSQLScript
« Reply #11 on: April 06, 2013, 09:15:13 pm »
Hi! Here is a real examples to understand how it works, with different data types. In Spanish, but I think you can understand it.

This code is for UPDATE, but modify for SELECT as you need.



Code: [Select]
class procedure TDatabases.MaterialRenovacionGuardar(nIdPrestamo: integer; dFecha_Renovacion: TDateTime; nNumero_Renovaciones: integer);
var
  SQLite3ConnectionP: TSQLite3Connection;
  SQLTransactionP: TSQLTransaction;
  DataSourceP: TDatasource;
  SQLQueryP: TSQLQuery;

begin
  SQLite3ConnectionP := TSQLite3Connection.Create(nil);
  SQLTransactionP := TSQLTransaction.Create(nil);
  DataSourceP := TDatasource.Create(nil);
  SQLQueryP := TSQLQuery.Create(nil);

  sDatabaseFilePath := TDatabases.DatabaseGetFilePath;

  TDatabases.DatabaseOpenOnly(SQLite3ConnectionP, SQLTransactionP, DataSourceP, SQLQueryP, sDatabaseFilePath);

  try
    SQLQueryP.Close;
    SQLQueryP.SQL.Text := 'UPDATE prestamos SET FECHA_RENOVACION=:A, NUMERO_RENOVACIONES=:B WHERE ID=:C';
    SQLQueryP.Params.ParamByName('A').AsDate := dFecha_Renovacion;
    SQLQueryP.Params.ParamByName('B').AsInteger := nNumero_Renovaciones;
    SQLQueryP.Params.ParamByName('C').AsInteger := nIdPrestamo;
    SQLQueryP.ExecSQL;
    SQLTransactionP.Commit;
  except
    on e: Exception do begin
      SQLTransactionP.Rollback;
      ShowMessage('Renovación a Material Especial, error al guardar renovación' + #13 + 'Error: ' + e.message);
    end;
  end;

  TDatabases.DatabaseClose(SQLite3ConnectionP, SQLTransactionP, DataSourceP, SQLQueryP);
  SQLite3ConnectionP.Free;
  SQLTransactionP.Free;
  DataSourceP.Free;
  SQLQueryP.Free;

end;

 

TinyPortal © 2005-2018