Recent

Author Topic: Manually generate sql statement using tparams?  (Read 2079 times)

snorkel

  • Hero Member
  • *****
  • Posts: 793
Manually generate sql statement using tparams?
« on: February 23, 2016, 01:16:41 am »
Hi,
I have a project where I need to send SQL over Tcp/ip using Indy to a app server that then executes the sql on the app server.  database is SQLite in WAL mode.

I saw that I can use tparms to parse the sql to generate the params and assign values, but I can't seem to figure out how to then apply the value to the SQL

basically what I want to to use use the params of tsqlquery but not connect to anything and then get the prepared statement and send it to my custom SQLite app server.

Any ideas?   I can use format but in this case I need to apply params and then change some of them based on use input.

***Snorkel***
If I forget, I always use the latest stable 32bit version of Lazarus and FPC. At the time of this signature that is Laz 1.8 and FPC 3.0.4
OS: Windows 10 64 bit

taazz

  • Hero Member
  • *****
  • Posts: 5363
Re: Manually generate sql statement using tparams?
« Reply #1 on: February 23, 2016, 02:11:02 am »
That is not how parameterized(wrong word too bored to look it up) sql statements work actually. In SQL commands with parameters the named parameters are converted to question marks and send to the server this way and the parameter values are send afterwards so there is no way to "get" the complete statement with the parameter values. Use the format function instead.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

LacaK

  • Hero Member
  • *****
  • Posts: 577
Re: Manually generate sql statement using tparams?
« Reply #2 on: February 23, 2016, 06:20:18 pm »
basically what I want to to use use the params of tsqlquery but not connect to anything and then get the prepared statement and send it to my custom SQLite app server.

You must create own TSQLConnection descendant, which will not connect to any database, but will send SQL text over TCP/IP.
(when Execute method will be invoked)
I use exactly this scenario.
It gives you possibility use transparently TSQLQuery (in my case once connects to real SQL database and in second case to app "sql proxy")

snorkel

  • Hero Member
  • *****
  • Posts: 793
Re: Manually generate sql statement using tparams?
« Reply #3 on: March 13, 2016, 11:01:07 pm »
In case anyone else is trying to do this:

What I did that completely solved the issue was to use component streaming to convert a zeos ZQuery for result sets and a ZSQLProcessor component to a string.
By doing this I could use the Zeos dataset components on my "Thin" client without a connection component.
i.e. I use them as normal, but instead of calling the components Open or ExecSQL, I instead use my own that do the streaming and send to the App Server.
I simply send the base64 encoded streamed components to my App server and then assign the connection after I stream it back to a component and then execute.
For updates, deletes, inserts I just send back boolean and a error string and for a result set I send back a Tbufdataset I saved to a stream after copying the result set from the ZQuery.   I use a Indy tcpserver on the app server side and tcpclient on the client side.
It works incredibly well, only thing to keep in mind the indy components enable Nagel by default and because the amount of data sent for a streamed component is small the Nagle if enabled will really slow it down.  With nagel disabled, you can't tell your executing all the SQLite SQL remotely on a LAN.

End result is a super easy to implement 3 tier client server application.  no database dlls are needed on the client side, and this could easily be adapted to other databases instead of SQLite.

I should also point out this allows your sqlite db to run in wal mode since all the users access it are on the server device the WAL shared memory is not affected,
and you get greatly increased concurrency.

I attached a screen shot of the App server in operation.

The app server portion is to manage a  "Mini" Managed File Transfer system  that uses the same SQLite DB and has a Indy FTP server and a SecureBridge SFTP server sharing the same user auth tables and rules.   The rules use GLOB pattern matching to do various things with the files after they have been uploaded i.e. Unix to Windows CRLF convert, move to other SMB UNC locations, forward to other SFTP or FTP servers etc .

« Last Edit: March 13, 2016, 11:13:07 pm by snorkel »
***Snorkel***
If I forget, I always use the latest stable 32bit version of Lazarus and FPC. At the time of this signature that is Laz 1.8 and FPC 3.0.4
OS: Windows 10 64 bit