quotes round string variable in SQL
lazarus / 16-Databases

----------------------------------------------------------------------
timcs · 2015-12-29 15:58

Hi

  This is one of those things that I have had working before and even
copied existing code but for some reason today it is failing. I am
trying to use a string variable called username as part of a SQL .
Below is the SQL but before you reply to this question please
understand that this has worked along with using quotedstr (see other
example below) before but today no, Lazarus has decided to report

unterminated string at or near <username>

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 'select con
nection.id,connection.type,connection.ip,connection.customer_id,connec
tion_access.date,'+       
 'connection_access.username,connection_access.id as conaccessid from
connection  inner join connection_access on
connection.id=connection_access.connection_id'+         ' where
date(connection_access.date)=current_date and connection_access.finish
isnull ' +          ' and connection_access.username =
'+''''+username+''''+' '
With Quotedstr

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 'select con
nection.id,connection.type,connection.ip,connection.customer_id,connec
tion_access.date,'+       
 'connection_access.username,connection_access.id as conaccessid from
connection  inner join connection_access on
connection.id=connection_access.connection_id'+         ' where
date(connection_access.date)=current_date and connection_access.finish
isnull and connection_access.username <> '''''''' '+         ' and
connection.id='+custid+' and connection_access.username <>
'+quotedstr('guest')+' and connection_access.username <>
'+quotedstr(username)+' '
Please help!

----------------------------------------------------------------------
balazsszekely · 2015-12-29 17:30

Try something like this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
SQLQuery1.SQL.Text :=  'select connection.id,connection.type,connectio
n.ip,connection.customer_id,connection_access.date, '+               
        'connection_access.username, connection_access.id as
conaccessid from connection inner join connection_access on
connection.id=connection_access.connection_id '+                     
  'where date(connection_access.date)=current_date and
connection_access.finish isnull ' +                         ' and
connection_access.username = :UserName'; 
SQLQuery1.Params.BeginUpdate; 
SQLQuery1.Params.ParamByName('UserName').AsString := 'testuser'; 
//SQLQuery1.Params.ParamByName('custid').AsInteger := 45; 
SQLQuery1.Params.EndUpdate;  SQLQuery1.Open;

----------------------------------------------------------------------
timcs · 2015-12-29 18:15

Thanks GetMem m I will try when this but it does not explain why my
original methods do not work when I am using them in another program
exactly the same way and that program compiles without an error.

The weird part is that it errors on the last part of the string not
the part with the ('guest') and on removing that part of the code
makes no difference either.

I know for a fact I have used the above without it being a problem so
I really do not know why it has decided to do this now

----------------------------------------------------------------------
taazz · 2015-12-29 22:43

Quote from: timcs on December 29, 2015, 04:58:25 pmCode: Pascal
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 'select con
nection.id,connection.type,connection.ip,connection.customer_id,connec
tion_access.date,'+       
 'connection_access.username,connection_access.id as conaccessid from
connection  inner join connection_access on
connection.id=connection_access.connection_id'+         ' where
date(connection_access.date)=current_date and connection_access.finish
isnull and connection_access.username <> '''''''' '+         ' and
connection.id='+custid+' and connection_access.username <>
'+quotedstr('guest')+' and connection_access.username <>
'+quotedstr(username)+' 'Excuse my curiosity but what sql server are
you using? isn't isnull a function on that server? I was under the
impression that the condition was

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i WHERE
DATE(connection_access.DATE)=CURRENT_DATE    AND
connection_access.finish IS NULL  instead of
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i WHERE
DATE(connection_access.DATE)=CURRENT_DATE    AND
connection_access.finish IsNull

----------------------------------------------------------------------
timcs · 2015-12-30 06:33

Hi taazz

 the isnull is not a problem as I run a version of this sql with
isnull through pgadmin and it works. This is postgresSQl server
version 9.x

----------------------------------------------------------------------
Zoran · 2015-12-30 07:29

Quote from: timcs on December 30, 2015, 07:33:25 amHi taazz

 the isnull is not a problem as I run a version of this sql with
isnull through pgadmin and it works. This is postgresSQl server
version 9.x

But does your query work when you change "IsNull" with "IS NULL"? Did
you try it? I do not have PostgreSQL, but this IsNull function with
its postfix usage looks very strange to me.
I tried to search Google with "postgresql isnull", but I do not find
this usage. I doubt that it can work this way.

----------------------------------------------------------------------
timcs · 2015-12-30 15:29

Quote from: Zoran on December 30, 2015, 08:29:53 amQuote from: timcs
on December 30, 2015, 07:33:25 amHi taazz

 the isnull is not a problem as I run a version of this sql with
isnull through pgadmin and it works. This is postgresSQl server
version 9.x

But does your query work when you change "IsNull" with "IS NULL"? Did
you try it? I do not have PostgreSQL, but this IsNull function with
its postfix usage looks very strange to me.
I tried to search Google with "postgresql isnull", but I do not find
this usage. I doubt that it can work this way.

Hi Zoran

  I cannot try and this point in time because this is all work based I
am not in the office until next week. I do know that this SQL works
with the other program I have which also runs at work so this is why
it is puzzling me at the moment.

----------------------------------------------------------------------
Leledumbo · 2015-12-30 16:54

Quote from: Zoran on December 30, 2015, 08:29:53 amBut does your query
work when you change "IsNull" with "IS NULL"? Did you try it? I do not
have PostgreSQL, but this IsNull function with its postfix usage looks
very strange to me.
I tried to search Google with "postgresql isnull", but I do not find
this usage. I doubt that it can work this way.
I can confirm that postgresql does NOT have isnull, whether as keyword
or aggregate function. It does have is null as defined by SQL
standards.

----------------------------------------------------------------------
shobits1 · 2015-12-30 21:03

Quote from: Leledumbo on December 30, 2015, 05:54:46 pmI can confirm
that postgresql does NOT have isnull, whether as keyword or aggregate
function. It does have is null as defined by SQL standards.
I can confirm that postgreSQL does indeed have ISNULL  (since v7.2
according to the documentation)and it is equivalent to IS NULL.
take a look at :
http://www.postgresql.org/docs/current/static/functions-
comparison.html

----------------------------------------------------------------------
Jurassic Pork · 2015-12-31 07:16

hello,
i have tried this SQL command :
Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i select *
from actor where first_name isnull;and also :
Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i select *
from actor where first_name is null;Both are OK for a postgresql
database.

actor is a table of my postgresql 9.3 database dvdrental :
Definition of actor table :
Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TABLE actor(  actor_id integer NOT NULL DEFAULT
nextval('actor_actor_id_seq'::regclass),  first_name character
varying(45),  last_name character varying(45) NOT NULL,  last_update
timestamp without time zone NOT NULL DEFAULT now(),  CONSTRAINT
actor_pkey PRIMARY KEY (actor_id))
Lazarus 1.6RC1 32 bits windows 
Components :
Quoteobject SQLQuery1: TSQLQuery
  Database = PQConnection1
  Transaction = SQLTransaction1
  SQL.Strings = (
    'select * from actor where first_name isnull;'
  )

object SQLTransaction1: TSQLTransaction

object PQConnection1: TPQConnection
  Connected = True
  DatabaseName = 'dvdrental'
  Transaction = SQLTransaction1
  UserName = 'openpg'
  HostName = 'localhost'
 
object DataSource1: TDataSource
  DataSet = SQLQuery1
 
object DBGrid1: TDBGrid
  DataSource = DataSource1

----------------------------------------------------------------------
taazz · 2015-12-31 07:38

thank you for info consider my curiosity served. Let me add at this
point that you should never use a database specific construct when you
have one common to all databases. for example porting your application
from postgresql to firebird will force you to hunt down all your
queries to change the sql command from  isnull to "is null". In any
case the string is correct on all cases I do not see any problems on
the template you are using the only other possible case is the value
of the username variable which should be nullified by the QuotedStr
function so try to post the complete sql command after all values
where expanded correctly.

----------------------------------------------------------------------
timcs · 2015-12-31 10:04

Quote from: taazz on December 31, 2015, 08:38:44 amthank you for info
consider my curiosity served. Let me add at this point that you should
never use a database specific construct when you have one common to
all databases. for example porting your application from postgresql to
firebird will force you to hunt down all your queries to change the
sql command from  isnull to "is null". In any case the string is
correct on all cases I do not see any problems on the template you are
using the only other possible case is the value of the username
variable which should be nullified by the QuotedStr function so try to
post the complete sql command after all values where expanded
correctly.

Point taken - however no porting involved in my programs - these are
all in house based and used only in our offices and we have been using
postgresSQL servers for some years now and to change this would mean
re-writing all of the applications we do. I had posted the complete
SQL (if you are referring to myself) in question that I was trying to
run. Furthermore (Cannot provide this now as it is held at work) I run
a similar SQL within PGadmin and that works.

I suspect that GetMems reply to resolve this should work anyway but
until I am back at work I cannot confirm this

----------------------------------------------------------------------
avra · 2015-12-31 11:38

Quote from: shobits1 on December 30, 2015, 10:03:36 pmI can confirm
that postgreSQL does indeed have ISNULL (since v7.2 according to the
documentation)Maybe server where query is not working is less then
7.2?

----------------------------------------------------------------------
goodname · 2016-01-05 18:20

A word of warning QuotedStr can be risky with PostgreSQL as it can
allow backslash escapes if standard escapes is turned off. QuotedStr
only handles quote escapes. This can lead to SQL Injection if the
server is allowing non-standard SQL string escapes. It is generally
easier to use prepared queries.

A good way of debugging this kind of query is to do a showmessage or
use the debugger to get the final query string then copy and paste
into PGAdmin and let it tell you what is wrong with the query.

----------------------------------------------------------------------
timcs · 2016-01-06 08:30

Just an update I did a quick test with using the method that GetMem
suggested and that appears to work now thanks.

Just got to do more work with this program to get it to edit the
selected record as it is coming up with dataset is readonly

Thanks

TimCS