2

I have some SQL Command that contains a parameter such:(Note that myID has "int" type in SQL)

vSqlString :='Select * From myTable Where myID= :paramID';

and Use ParseSQL Command to execute this command:

myADOQuery.Parameters.ParseSQL(vSqlString , True);

Now myADOQuery.Parameters.ParamByName('paramID').DataType is smallint Type and it can't accept negative integer values.

I can exactly show to compiler that my Parameter[0].DataType is ftIneteger and it works properly, but what is a good solution for this problem?

Mohammad Gohari
  • 241
  • 3
  • 12
  • Have you tried changing the datatype: `myADOQuery.Parameters.ParamByName('paramID').DataType := ftInteger;` – Keith Miller Dec 28 '13 at 10:59
  • Alternatively you can set up the parameters manually rather than by calling ParseSQL. – Keith Miller Dec 28 '13 at 11:00
  • @Keith Miller: yes. i do and it works properly. but if i change my parameters or t-sql objects, i must change this line and it seems not good. i want a solution to tell me how can i don't define my parameters type and complier recognize them – Mohammad Gohari Dec 28 '13 at 11:12
  • 1
    what is your DB backend? Smallint type is rather weird for an identity column? – whosrdaddy Dec 28 '13 at 11:17
  • @whosrdaddy : this Question is a sample of parameters and paramID might be non identity column. in this sample myID has "int" type in SQL and i get small int for it! I want to pass a negative value to this parameter. – Mohammad Gohari Dec 28 '13 at 11:29
  • 4
    @MohammadGohari I think that is the best solution. The compiler cannot query the DB to see what the column datatypes are so you have to tell it. Personally I don't use ParseSQL, I set up the parameters in code so I have full control. – Keith Miller Dec 29 '13 at 09:36
  • How did you conclude that the parameter type is smallint? If you place ShowMessage ( inttostr ( ord ( myADOQuery.Parameters.ParamByName('paramID').DataType ) ) ) after the call to ParseSQL what is the result? My hypothesis is that it is zero indicating ftUnknown. – David Dubois Jan 20 '14 at 13:40
  • You don't say what the DB is. Looking at your query I would guess that that it is Oracle by the ':' in the parameter name. In SQL Server it should be '@'. – simon at rcl Jan 20 '14 at 18:08
  • 2
    see this article [ADOQuery.ParseSql][1] [1]: http://stackoverflow.com/questions/21194122/tadoquery-parsesql-do-not-work-in-xe4?noredirect=1#comment31943496_21194122 – imanShadabi Jan 21 '14 at 05:47

2 Answers2

3

My question is asked by Mr. imanShadabi Here: Using TAdoQuery.ParseSql and has resolved by user1008646

Community
  • 1
  • 1
Mohammad Gohari
  • 241
  • 3
  • 12
0

Hope this will help.

If you want to create in runtime parameters, you can use something like this: 

ADOQuery1.Close;
ADOQuery1.SQL.Text := vSqlString;
ADOQuery1.Parameters.Clear;
ADOQuery1.Parameters.CreateParameter('paramID', ftInteger, pdInput, 10, vIntegerValue);
ADOQuery1.Open;

Or you can concatenate values to the query. For example: 

//For Integer values:
vSqlString: = 'Select * From myTable Where myID =' + IntToStr (vIntegerValue); 

//For String values:
vSqlString: = 'Select * From myTable Where myID =' + QuotedStr (vStringValue); 

//For float values: 
//Be careful with this, usually in a query, the comma is separator values, 
//so make sure that the decimal separator is '.'
vDS := DecimalSeparator; //I keep the value it had 
DecimalSeparator := '.'; 
try
  ADOQuery1.close;
  ADOQuery1.SQL.Text := 'Select * From myTable Where myID='+FloatToStr(vFloatValue);
  ADOQuery1.Open;
finally
  DecimalSeparator := vDS; //Restore the value that had
end;

The third option is to set the parameters at design time. But I think this is not what you want.

pariasdev
  • 571
  • 5
  • 4