My Visual Database — sql in code question

Re: sql in code question

null@example.com (timlitw) — Tue, 21 Feb 2017 20:23:28 +0000

wow - thanks!

Re: sql in code question

null@example.com (mathmathou) — Tue, 21 Feb 2017 07:16:27 +0000

Hello timlitw,

You are right, there is a way of getting multiple results from just one query, but this is not an array, this is a dataset.

IF and only IF you use the SAME query multiple times to get multiples results, then you can simplify like :

procedure .....
var
     MyResults : TDataSet;
     x : Integer;
     y, z : String;
begin
     SQLQuery('SELECT x, y, z FROM my_table WHERE my_conditions',MyResults);
     while not MyResults.Eof do
          begin
               x := MyResults.FieldByName('x').AsInteger;
               y := MyResults.FieldByName('y').AsString;
               z := MyResults.FieldByName('z').AsString;

               ..... your awesome code here to use the x, y and z variables you just got in one single query

               MyResults.Next;
          end;
     MyResults.Free;

end;

Don't forget the MyResults.Next or your application will hang and you'll get an error, and don't forget to Free the dataset at the end of the procedure or you might encounter memory leaks.

You can use this to get multiples results with one query but also multiple sets of multiple results if you , for example, specify a range in your conditions. The "while not MyResults.Eof" means as long as there is data, loop through them, so you can have multiple loops with just one query.

Test this by putting a memo on a form and writing a query that fetches all your company names (for example), and output these names to the memo in the loop. If you have 10 companies, you'll get 10 lines with 10 names in your memo. With just one query

have fun

Cheers

Mathias

Re: sql in code question

null@example.com (timlitw) — Mon, 20 Feb 2017 18:49:09 +0000

It has changed a bit but is working - I found out the price was by the product category so I had to add categories to the products and fill the category field by the product selected. but can I make this more efficient by running the SQL query only once to get both values? If so does it come back as an array? I guess I need to study arrays

    flatrate := SQLExecute('SELECT FlatRate FROM FreightRateChart '+
        'LEFT OUTER JOIN TruckCompanies ON FreightRateChart.id_TruckCompanies = TruckCompanies.id '+
        'LEFT OUTER JOIN ProductsType ON FreightRateChart.id_ProductsType = ProductsType.id '+
        'LEFT OUTER JOIN LoadOrigin ON FreightRateChart.id_LoadOrigin = LoadOrigin.id '+
        'LEFT OUTER JOIN LoadDestination ON FreightRateChart.id_LoadDestination = LoadDestination.id '+
        'WHERE (TruckCompanies.Name = '''+CalcShipping.truckCombo.Text+''') '+
        'AND (ProductsType.Name='''+CalcShipping.prodtypecombo.Text+''') '+
        'AND (LoadOrigin.Name='''+CalcShipping.originCombo.Text+''') '+
        'AND (LoadDestination.Name='''+CalcShipping.destinationCombo.Text+''') '+
        'ORDER BY FreightRateChart.id DESC LIMIT 1;');
    surcharge := SQLExecute('SELECT Surcharge FROM FreightRateChart '+
        'LEFT OUTER JOIN TruckCompanies ON FreightRateChart.id_TruckCompanies = TruckCompanies.id '+
        'LEFT OUTER JOIN ProductsType ON FreightRateChart.id_ProductsType = ProductsType.id '+
        'LEFT OUTER JOIN LoadOrigin ON FreightRateChart.id_LoadOrigin = LoadOrigin.id '+
        'LEFT OUTER JOIN LoadDestination ON FreightRateChart.id_LoadDestination = LoadDestination.id '+
        'WHERE (TruckCompanies.Name = '''+CalcShipping.truckCombo.Text+''') '+
        'AND (ProductsType.Name='''+CalcShipping.prodtypecombo.Text+''') '+
        'AND (LoadOrigin.Name='''+CalcShipping.originCombo.Text+''') '+
        'AND (LoadDestination.Name='''+CalcShipping.destinationCombo.Text+''') '+
        'ORDER BY FreightRateChart.id DESC LIMIT 1;');

    CalcShipping.PriceTon.Text := flatrate;
    CalcShipping.SurchargePercent.Text := surcharge;

Re: sql in code question

null@example.com (timlitw) — Mon, 20 Feb 2017 18:36:44 +0000

Sorry - I was away from project for a few days. Thaks for your help!

Re: sql in code question

null@example.com (mathmathou) — Tue, 14 Feb 2017 20:43:27 +0000

Hello again

OK, I've just checked and this works :

flatrate := SQLExecute('SELECT FlatRate FROM FreightRateChart '+
                            'LEFT OUTER JOIN TruckCompanies ON FreightRateChart.id_TruckCompanies = TruckCompanies.id '+
                            'LEFT OUTER JOIN Products ON FreightRateChart.id_Products = Products.id '+
                            'LEFT OUTER JOIN LoadOrigin ON FreightRateChart.id_LoadOrigin = LoadOrigin.id '+
                            'LEFT OUTER JOIN LoadDestination ON FreightRateChart.id_LoadDestination = LoadDestination.id '+
                            'WHERE TruckCompanies.Name LIKE ''' +Form1.truckCombo.Text+ ''' '+
                            'AND Products.Name LIKE ''' +Form1.productCombo.Text+ ''' '+
                            'AND LoadOrigin.Name LIKE ''' +Form1.originCombo.Text+ ''' '+
                            'AND LoadDestination.Name LIKE ''' +Form1.destinationCombo.Text+ ''' '+
                            'ORDER BY FreightRateChart.id DESC LIMIT 1; ');

I tested it with "LIKE " and "=" and both works. Since you don't use the % in your query, you can use = instead of LIKE.

Also, the concatenation sign + helps making your query easier to read on multiple lines like in sqlstudio.

I added a

     ShowMessage(flatrate);

just to make sure there was a value returned.

The result I get with value :
- Trucking Co4
- Gas
- Origin1
- Dest1

is 46,72.

Hope this helps

Cheers

Mathias

PS : using v 3.2

Re: sql in code question

null@example.com (mathmathou) — Tue, 14 Feb 2017 19:33:29 +0000

Hello timlitw,

Just a wild guess, it's early in the morning and I'm having my first coffee

I don't see the logical operators in your second query. Seems you forgot the "LIKE" in the statement.

The ORDER BY / LIMIT command is ok if you assume that the latest value saved in database is the highest. But what if this is not the case ?
You could also try something different with the statement "HAVING MAX(X)" if you are dealing with a integer (an ID for example or a number) but you'll have to had a "GROUP BY" command to use it.

If you are dealing with other variables that numbers, a number stored as text for example, you can still use this value for sorting with the command CAST. This instruction will transtype (convert) a value from a type to another and help you sorting.
For example, if a number is stored as text, you can convert it and then use it for sorting with :
ORDER BY CAST(X AS INTEGER) ASC

Finally, is you query included in the script or embedded in a SQL Query button ? Because the way you pass on the variables will be different in both cases.

I'll have a look at your project when I reach my office. I know nothing better than a bit of code with another coffee to start a good day of work

Cheers

Math

Re: sql in code question

null@example.com (timlitw) — Tue, 14 Feb 2017 14:41:01 +0000

if I run this in sqlitestudio I get the correct answer

SELECT FreightRateChart.FlatRate 
FROM FreightRateChart 
LEFT OUTER JOIN TruckCompanies ON FreightRateChart.id_TruckCompanies = TruckCompanies.id 
LEFT OUTER JOIN Products ON FreightRateChart.id_Products = Products.id 
LEFT OUTER JOIN LoadOrigin ON FreightRateChart.id_LoadOrigin = LoadOrigin.id 
LEFT OUTER JOIN LoadDestination ON FreightRateChart.id_LoadDestination = LoadDestination.id 

WHERE TruckCompanies.Name LIKE 'Trucking Co4'
  AND Products.Name LIKE 'Gas' 
  AND LoadOrigin.Name LIKE 'Origin1' 
  AND LoadDestination.Name LIKE 'Dest1'

ORDER BY FreightRateChart.id DESC LIMIT 1;

but when I try in mvd with

flatrate := SQLExecute('SELECT FlatRate FROM FreightRateChart LEFT OUTER JOIN TruckCompanies ON FreightRateChart.id_TruckCompanies = TruckCompanies.id LEFT OUTER JOIN Products ON FreightRateChart.id_Products = Products.id LEFT OUTER JOIN LoadOrigin ON FreightRateChart.id_LoadOrigin = LoadOrigin.id LEFT OUTER JOIN LoadDestination ON FreightRateChart.id_LoadDestination = LoadDestination.id WHERE TruckCompanies.Name ''' +Form1.truckCombo.Text+ ''' AND Products.Name ''' +Form1.productCombo.Text+ ''' AND LoadOrigin.Name ''' +Form1.originCombo.Text+ ''' AND LoadDestination.Name ''' +Form1.destinationCombo.Text+ ''' ORDER BY FreightRateChart.id DESC LIMIT 1; ');

flatrate never gets set to a value

I don't think I am understanding the method of getting the variables into the sqlquery properly.

Re: sql in code question

null@example.com (timlitw) — Tue, 14 Feb 2017 12:45:29 +0000

I had figured out the limit 1,
I am struggling with how to take that long sql and wrap it into a SQLExecute with the 4 variables needed to get the two results.

Also can I get them both with one call to the database or do I need to run it twice.

here is the project the freight table shows the sql working to filter the list for editing etc.

Re: sql in code question

null@example.com (DriveSoft) — Tue, 14 Feb 2017 11:27:14 +0000

Hello.

Try to add keyword LIMIT 1

....
WHERE TruckCompanies.Name LIKE '%{TruckSearch}%'
  AND Products.Name LIKE '%{ProdSearch}%'
  AND LoadOrigin.Name LIKE '%{OriginSearch}%'
  AND LoadDestination.Name LIKE '%{DestSearch}%'

ORDER BY FreightRateChart.id DESC

LIMIT 1;

sql in code question

null@example.com (timlitw) — Mon, 13 Feb 2017 21:52:36 +0000

this sql query wroks to fill and filter a table grid - now in a form I need to
choose Truck company, Products, Origin and Destination and then get the Flat rate and Surcharge from the database - if there are multiple that match I just need the newest one.

SELECT FreightRateChart.id,
       TruckCompanies.Name,
       Products.Name,
       LoadOrigin.Name,
       LoadDestination.Name,
       FreightRateChart.FlatRate,
       FreightRateChart.Surcharge,
       strftime ( '%m/%d/%Y',FreightRateChart.Updated)
From FreightRateChart

LEFT OUTER JOIN TruckCompanies ON FreightRateChart.id_TruckCompanies = TruckCompanies.id
LEFT OUTER JOIN Products ON FreightRateChart.id_Products = Products.id
LEFT OUTER JOIN LoadOrigin ON FreightRateChart.id_LoadOrigin = LoadOrigin.id
LEFT OUTER JOIN LoadDestination ON FreightRateChart.id_LoadDestination = LoadDestination.id

WHERE TruckCompanies.Name LIKE '%{TruckSearch}%'
  AND Products.Name LIKE '%{ProdSearch}%'
  AND LoadOrigin.Name LIKE '%{OriginSearch}%'
  AND LoadDestination.Name LIKE '%{DestSearch}%'

ORDER BY FreightRateChart.id DESC;