Forum Discussion

lynx_rousse's avatar
lynx_rousse
New Contributor
11 years ago

Using a variable in SQL query in DelphyScrypt

What I need to do: I have an Excel file with a list of barcode. I need to take them one by one and then using an SQL query serch an Oracle Database for relevant information. To do this, I take a barcode from file, assign it to a variable and then enter the variable into the query. But TestComplete doesn't seem to understand what I want from it. here is the code:




 procedure Main;

 var AConnection, RecSet, Phone, Type, Type_name, stringconn, var1, ddtExcel;

 begin

 try

 ddtExcel := DDT.ExcelDriver('C:/Barcode.xls', 'Sheet 1', true);

 while not ddtExcel.EOF do

 begin

 var1 := ddtExcel.Value['barcode'];

 // Create a Connection object

 AConnection := ADO.CreateADOConnection;

 stringconn:='a correct string that works';

 // Suppress the login dialog box;

 // Specify the connection string

 AConnection.ConnectionString := stringconn;

 AConnection.LoginPrompt := False;

 AConnection.Open();

 // Execute a query

 RecSet := AConnection.Execute_

   ('select i.tel_number, i.sms_type from sms.sms_item t join sms.sms_info i on t.ID_SMS_ITEM=i.ID_SMS_ITEM where t.BARCODE='+ StrToInt(var1));

 // Iterate through query results and insert data into the test log

 RecSet.MoveFirst;

 while not aqConvert.VarToBool(RecSet.EOF) do

 begin

 Log.AppendFolder(var1);

 Log.AppendFolder(RecSet.Fields('TEL_NUMBER').Value);

 Log.Message(RecSet.Fields('SMS_TYPE').Value);

 RecSet.MoveNext;

 Log.PopLogFolder;

 end;

 AConnection.Close;

 end;

 ddtExcel.Next();

 end;

 DDT.CloseDriver(ddtExcel.Name);

 except

 AConnection.Close;

 Log.Error('Exception', ExceptionMessage)

 end;

 end;


 



I believe it will turn out as a simple syntax error, but I can't find a proper example of a syntax for such a case. Here on the forum I saw only examples with other languages...
  • AlexKaras's avatar
    AlexKaras
    Icon for Champion Level 3 rankChampion Level 3
    11 years ago
    Hi Vera,



    >  if I replace the var with one of the barcodes from file, all works. But it doesn't work with the var.



    In this case it looks like there is some problem with the value of the var1 variable.

    I would recommend to add some variable (e.g. with the strSQL name) and use it to build the SQL string. E.g.:

    strSQL := 'select i.tel_number, i.sms_type from sms.sms_item t join sms.sms_info i on t.ID_SMS_ITEM=i.ID_SMS_ITEM where t.BARCODE='+ StrToInt(var1);

    RecSet := AConnection.Execute(strSQL);



    Put a breakpoint on the RecSet := ... line and examine the value of the strSQL variable. Is it valid? Can the query be executed if you copy the value of the strSQL variable and execute it from the Oracle development tool?



    As a side note: your code is pretty ineffective. The problem is that you are creating ADO object, open connection to DB, etc. on each cycle iteration. This is very resource consuming and I would recommend to create the ADO object, open the connection and create an SQL string outside of the data loop. For the cases like yours, it is not recommended to build SQL string like you do, but introduce a parameter for the barcode value (see MSDN and Delphi documentation for more details and code samples). Then, within the loop, you will need just to assign the value to the parameter and execute the query. This is much more reliable and effective.
  • lynx_rousse's avatar
    lynx_rousse
    New Contributor
    11 years ago
    Thank you all for your answers, it turned out that using CreateADOQuery is a lot easier in this case, so I re-wrote the whole thing and it works.
  • TanyaYatskovska's avatar
    TanyaYatskovska
    Icon for Alumni rankAlumni
    11 years ago
    Hi Vera,

      


     What exactly doesn't work? Was TestComplete able to successfully read data from the Excel file?


     Try debugging your code to figure out what is going on.


      


     BTW, I'm not sure if you need to use the StrToInt function. You convert the barcode string to the integer number and add it to the connection string. It makes sense using the aqConvert.VarToStr method instead.


      

  • lynx_rousse's avatar
    lynx_rousse
    New Contributor
    11 years ago
    Hi, Tanya!

    Yes, the file is read, and is read correctly - I checked. Also the query itself is correct - if I replace the var with one of the barcodes from file, all works. But it doesn't work with the var. I tryed it without the STRToInt, no difference.

    TestComplete just posts an "exception" error in the log with no details about it.