Changing a Query at Runtime

[Baldur3635]

I'm back! My database holds several different things (don't blame me for the database design, someone else did that!). There are three distinct types of things. They all haver some of the fields, but some of the items, the fields are blank.

I'm trying to use a single CRDBGrid to display each different item.

I have a ListBox1 with the 3 types Outsourced, Local, Other.

procedure doDisplay;
var
TheType : integer;
begin
TheType := ListBox1.ItemIndex;
Case TheType of
0 : begin
MyQuery2.SQL.Clear;
MyQuery2.SQL.Text := ''SELECT it_type, itno, itname, itsup1, itsup2, itcst, itrate, itin FROM ItemList WHERE it_type = "Outsourced" ';
// Here I setup the Columns for the CRDBGrid
CRDBGrid1.Clear;
CRDBGrid1.Columns.Add.FieldNeme := 'it_type';
//etc for each column
MyQuery2.Open;
MyQuery2.Execute;
CRDBGrid1.Refresh;
end;
1 : begin
MyQuery2.SQL.Clear;
MyQuery2.SQL.Text := ''SELECT it_type, itno, itname, itcst, part1, part2, ithrs, itrate, itin FROM ItemList WHERE it_type = "Local" ';
// Here I setup the Columns for the CRDBGrid
CRDBGrid1.Clear;
CRDBGrid1.Columns.Add.FieldNeme := 'it_type';
//etc for each column
MyQuery2.Open;
MyQuery2.Execute;
CRDBGrid1.Refresh;
end;
end;

Type Outsourced is correct. If I select Local and Refresh Button, (which calls DoDisplay, the Columns change perfectly. but the information shown never changes. I've tested all the SQL SELECT statements. they work 100%. I've stepped through the code, everything seems OK, but I'm obviously missing something - Open and Execute don't seem to work. What have I missed?

 

[DjangMan]

I think that .Execute is typically reserved for running SQL commands that do not expect a result set (UPDATE, DELETE, INSERT).

So try commenting out the .Execute line.

And just to confirm that the CRDBGrid1 data source is connected to the query?

I wouldn't be surprised if there was a way to add all of the columns with one command.

 

[Baldur3635]

I tried that already, same result.

The query is definitely connected to the table and the CRDBGrid is definitely connected to the query. The columns change 100% but not the query.

I have stepped through the code. The query text is correct, It passes it to the Query, but for some reason it isn't getting executed.

There may be an easier way to setup the columns, but I need to get this very simple bit of code working. After that I can spend some time trying to improve it.

Neither Execute nor Post works. All the queries are correct. All are being sent to MyQuery2. There must be something I'm missing.

 

[DjangMan]

Are you closing the query before refreshing the data? (Maybe you don't have to.)

 

[Baldur3635]

Oh, I found the problem this morning.
MyQuery2.Open;
MyQuery2.Execute;
is unnecessary. Just replace with MyQuery2.ExecSQL Works like a dream.

A few more hours and I should be finished with this job. Waiting for the next one! My Delphi mindset is starting to come back. I didn't do a lot of work with databases (other than the NDS one) and that wasn't any known interface.

 

[DjangMan]

Well done!

I would have a look at this post if you're going to be doing database work:

http://www.tek-tips.com/viewthread.cfm?qid=1647984

I found that it made working with databases and queries much much easier and my code easier to write.

 

[Baldur3635]

DjangMan That's very useful indeed. I wish I hadn't sold all my books when I retired. There's a lot floating around on the web, but I can't remember which one covered databases well. To be truthful, I didn't work very much at all with SQL, most of the work that I did was for Novell eDirectory (NDS) and GroupWise.

There is one thing that I'm really struggling with. - Because of Typos, I'm trying to clean up this big table. Badly designed, monolithic, but that was someone else.

I can write Update queries easily with HeidiSQL. I did sort of try parameters, but couldný get it to work, but the worst part is, that because of all the typos I need to use LIKE

UPDATE stItems SET iName = 'xxx' WHERE bName LIKE "%Frxxxxx";
UPDATE stItems SET wloc = 'abcd' WHERE bName LIKE "%Grexxxxx%";
UPDATE stItems SET iName = 'xyxz' WHERE bName LIKE "- 40087%";

I thought, simple, just create a form with a couple of EditBoxes, Then fill in the correct values and use something like

UPDATE stItems SET iName = 'edtName.text' WHERE bName LIKE "'%'+edtCval.text+'%'";

There seems to be no way to use a LIKE statement in Delphi. I've searched the web for hours, but no mention of how to pass a LIKE statement to a database using Delphi.

 

[DjangMan]

What's the underlying database that you're working with? That should tell us what wildcard character to use, the syntax of LIKE, what string character to use and, if your database is case sensitive.

Looking at one of your update statements it looks like you want something like:

sql := 'UPDATE stItems SET iName=''' + edtName.text + ''' WHERE bName LIKE ''%' + edtCval.text + '%'' ';

 

[Baldur3635]

That is brilliant DjangMan, I'm still kicking myself, I tried just about everything except your LIKE. I tested it, and it works perfectly, except for two fields,

The most important thing is Description. This seems to have been cut and pasted from a catalog (not written by an English speaker it would seem) lots of spelling and typos. It is a LongText field.

In every case, it is just a single line in a TMemo box. I get an error "Unknown column memDescrip.lines in field list.

There's also a Float field with a max of 255.00 but it isn't money, it would be nice to know how to handle both of these as well.

Thanks for all your much valued help. It's starting to get easier with coding now, but still struggling a bit with SQL and how it works with Delphi.

Now I tried changing this to :
UPDATE stItems SET iDesc = ''' + memDesc.Lines.Text + ''' WHERE iName LIKE ''%' + edtCval.text + '%'' ';

Now I get text is too long for column probably because it's longtext and not text as a string of 255 max. How does one get around that?

 

[DjangMan]

You didn't mention what the underlying database is. Is it SQL Server or Postgres or MySql or another one?

Based on the error message your trying to search for a field called memDescrip.lines instead of the value of the variable/control.

You didn't include the whole line so I can't tell if you're missing an apostrophe and if you should be using quotes or apostrophes.

sql := 'UPDATE stItems SET iDesc = "' + memDesc.Lines.Text + '" WHERE iName LIKE "%' + edtCval.text + '%" ';

For floats you'll want to use something like BETWEEN eg: Where TheFloatField BETWEEN LowValue AND HighValue

If you know the maximum length of your iDesc field then you can use SubString to limit then length of text that you're sending to that field.

 

[Baldur3635]

Sorry, I'm using MySQL (actually MariaDB).

Here is the full Line :

MyQuery1.SQL.Text := ' UPDATE stItems SET iDesc = ''' + memDesc.Lines.Text + ''' WHERE iName like ''%' + edtCval.Text + '%'' ' ;

It seems that it doesn't like LongText. it's certainly a lot longer than 255 chars. I think MidText would have done the job, but hey, I didn't design this database! ALL the text, no matter how long, is a single line.

The text in this field can vary from a single line to over a page. It's great to have all the other bits working, but that's an awful lot of text to even contemplate re-writing.

Using HeidiSQL has a problem with apostrophes, so I would have to either encapsulate each one with more apostrophes or delete the apostrophes, which isn't improving the already poor English.

 

[DjangMan]

So, to keep it easy to read in the code as you dust off your Delphi, use a variable to hold the value that you want to search for.

var
  SearchValue: string;

begin
  SearchValue := Copy(memDesc.Lines.Text,1,30); //Or whatever the maximum length is.
  //QStr is a function that I made up that will return a string with the characters that MariaDB uses (which seems to be ' ), but not for like
  MyQuery1.SQL.Text := 'UPDATE stItems SET iDesc=' + QStr(SearchValue) + ' WHERE iName LIKE "%' + edtCVal.Text + '%" ';


end;

 

[Baldur3635]

I've been scanning through all the chunks of text. The biggest I've found is 1587 characters, some are as small as 145 characters.

Remember the definition SQL is LongText.

Let me test your suggestion but I have doubts that it will work because string us a max of 255 as I remember.

 

[DjangMan]

What version of Delphi are you running? It used to be that strings were limited to 255 characters but that was changed at some point. They can be very, very long now.

 

[Baldur3635]

Delphi XE7

 

[DjangMan]

Yes, your string data type will handle more characters than you need, by far.

I would be really tempted to have you make use of the query interface. It makes working with parameters easy and you don't have to worry about memory issues. It would look like this:

USAGE:
procedure DoSomeQuery;
var
Qry : IIADOQuery;
begin
Qry := TIADOQuery.Create(MyDBConnection); // MyDBConnection is your TADOConnection
// delete/insert/update statements
Qry.UpdateQuery('DELETE FROM DaTable');

// using parameters
Qry.ParseQuery('SELECT name FROM DaTable WHERE id=:id';
Qry.Parameter('id', 10);
Qry.SelectParsedQuery;
ShowMessage(Qry.AsString('name'));
end;

So your code would be something like:

Qry := TIADOQuery.Create(MyDBConnection);
Qry.ParseQuery('UPDATE stItems SET iDesc=:newDesc WHERE iName LIKE "%:itemName%");
Qry.Parameter('newDesc', memDesc.Lines.Text);
Qry.Parameter('itemName', edtCval.Text);
Qry.UpdateParsedQuery;

Using parameters is a little different than working directly with SQL statements but it is a better way of working with the SQL statements.

 

[Baldur3635]

For the moment, I don't want to make too many changes. Your method :
var
SearchValue: string;

begin
SearchValue := Copy(memDesc.Lines.Text,1,30); //Or whatever the maximum length is.
//QStr is a function that I made up that will return a string with the characters that MariaDB uses (which seems to be ' ), but not for like
MyQuery1.SQL.Text := 'UPDATE stItems SET iDesc=' + QStr(SearchValue) + ' WHERE iName LIKE "%' + edtCVal.Text + '%" ';

Works fine, but there's always a catch. The string can contain multiple apostrophes (such as there's), so all that I need to do is replace every occurrence of ' with ''.

I tried StringReplace and others, they all seem to work with any character EXCEPT quotation marks. I seem to recall that back in the day, there was a JEDI function to do this. I installed the newest JEDI, but nothing seems to do the job.

Seems that fast forward instead of making things easier, they just become more complicated.