Bug in SQL-Params

Comments

• MrSpock

  October 2006 edited 12:56PM

  Hello kaju74,
  
  I cannot reproduce this error, it worked with a table that I have in my database. Sometimes problems like that happen if the column names are the keywords of the database. Which database are you using?
• MSDTrinity

  November 2006 edited 12:56PM

  Kaju74/MrSpock,
  
  I think the exception error that is coming up is in the following case:
  
  Using the ADOQuery object:
  
  <span style='color:blue'>SELECT ID, GUID, Name FROM XYZ WHERE GUID = :GUID</span>
  
  The Parameter gets created as type GUID because the column in the server is set for uniqueidentifier (in SQL Server anyway).
  
  When you try to apply any text to the parameter it will display an error message:
  
  <span style='color:red'>The following error(s) have occurred:
  aqryXYZ: aqryXYZ: Error in expression: '{SOME-GUID-TEXT}': Expression expected
  aqryXYZ: Error in expression '{SOME-GUID-TEXT}': Expression expected.</span>
  
  At least this is the exception that gets raised when I try to perform this operation.
  
  I am actually trying to query my table by passing in a GUID and it raises this error. If I try and set the parameter itself to string - it tells me the parameter is incompatible and will not allow me to change that property.
  
  In regular sql, I just drop the braces '{' and quote the str to query the uniqueidentifier. I am doing that every where using a regular TADOQuery component throughout my application but I can't seem to trick FR to do it.
  
  If this is the same problem, I'd like to hear the solution...
  
  Thanks in advance,
  -mark
• kaju74

  November 2006 edited 12:56PM

  Yes...this sounds like the same problem as I have...
  
  @FR-Team: Any idea how to solve this...?
  
  Regards
• gordk St.Catherines On. Canada.

  November 2006 edited 12:56PM

  Try using a different name for parameter and variable
  SELECT ID, GUID, Name FROM XYZ WHERE GUID = :p1
  in query param editor set the parameter to correct value type and in value box select
  the variable you created in the categorized variable list.
  when passing value in from delphi to variable if a string use:= ''''+'strval'+''''
  if numeric use := value.
  
• MSDTrinity

  November 2006 edited 12:56PM

  Gordk,
  
  This is the actual query that does NOT work:
  

  SELECT
   CS.CylinderID,
   CS.SalesOrderNumber,
   CS.ShipIncident,
   CS.Pressure
  
  FROM CylinderShipments CS 
  WHERE CS.ShipIncident = :AShipIncident AND CS.ShipIncidentLine = :AShipIncidentLine
  

  
  <span style='color:blue'>
  CylinderShipments = SQL Table as:
  ShipIncident = Integer
  ShipIncidentLine = uniqueidentifier
  SalesOrderNumber = String
  Pressure = String
  </span>
  
  
  I have 2 variables in the report definition:
  
  ShipIncident
  ShipIncidentLine
  
  In code for the report, I am setting the values as follows:
  
  ShipIncident = someinteger
  ShipIncidentLine = QuotedStr(GUID) *This is how I pass to SQL statements. The curly braces are stripped out {}.
  
  I have tried this different ways but the common error I get is when I try to define the parameters for the query object (by setting the GUID field) to either UNKNOWN or STRING, I receive the error:
  
  <span style='color:red'>
  Arguments are of the wrong type, are out of acceptable range, or are in conflict with another.
  </span>
  
  What I do to get around the entire issue is a complete workaround and I don't like it but I have to get this report working:
  
  I created a SQL View where I cast the GUID column as a varchar(40). This makes it as text and then applying the parameters and variable it works like a charm but it creates an overhead in this situation.
  
  We use uniqueidentifiers quite often in our database design and this would definitely hinder the use of them if I can't use them in their native format.
  
  Any ideas?
  
  Thanks,
  -mark