VARCHAR not recognized by Lazarus (MySQL) [SOLVED] lazarus / 16-Databases ---------------------------------------------------------------------- tee · 2012-09-28 09:33 Hi guys just wondering if you can help me out with this  I connected the table in lazarus and queried the database like so: Code: [Select] var Barcode := string; begin   dmoCD.SQLQuery1.Close;   Barcode := edtBarcode.Text;    dmoCD.SQLQuery1.SQL.Text:= 'SELECT * from cdinventory WHERE cdinventory.CDBarcode = ' + Barcode + ';';   dmoCD.SQLQuery1.Open;   edtBarcode.Clear; end;  The above works perfectly fine and finds the record by searching the CDBarcode field which is of type INT. However, when I try the same thing with the CDArtist field (contained in the same table) it doesn't work. Code: [Select] var Artist := string; begin   dmoCD.SQLQuery1.Close;   Artist := edtArtist.Text;    dmoCD.SQLQuery1.SQL.Text:= 'SELECT * from cdinventory WHERE cdinventory.CDArtist = ' + Artist + ';';   dmoCD.SQLQuery1.Open;   edtArtist.Clear; end;  I did a lot of testing to find what the problem is and what I found is that CDArtist is of type VARCHAR(20). When I try inputting an alpha value in the edtArtist component, the SQL Query statement doesn't execute and gives an error. I tried with a numeric value and the query works perfectly fine! However it does not work with Alpha characters... My conclusion is the problem lies with CDArtist field being VARCHAR. Is there any way to fix this? I tried changing it to TEXT and it still does not work. Oh btw I'm using a normal editbox and not a DBedit because eventually I want the user to enter either Artist OR Barcode in one editbox. DBedit only allows for one field connection. ---------------------------------------------------------------------- BigChimp · 2012-09-28 09:57 Welcome to the forum! First, descirbe what "doesn't work" mean. Does your computer disappear? Do hordes of screaming monkeys descend on the MySQL server? Do you see an error message? Also: Lazarus+FPC version + Operating system please - see signature. In this case, mysql version may be nice to know though probably not that important. Well, what happens when you run that query in mysql administrator or similar tools? Does it work then? I'm guessing cdartist for some reason contains numbers, is a foreign key and linked to an Artists table. Why somebody decided that had to be varchar is beyond me. This is just what my crystal ball is telling me though, I might be completely wrong. ---------------------------------------------------------------------- taazz · 2012-09-28 10:33 Quote from: tee on September 28, 2012, 11:33:35 amThe above works perfectly fine and finds the record by searching the CDBarcode field which is of type INT. However, when I try the same thing with the CDArtist field (contained in the same table) it doesn't work. Code: [Select] var Artist := string; //<--Compilation error probaly you mean Artist : string; begin   dmoCD.SQLQuery1.Close;   Artist := edtArtist.Text;  //<--- pretty normal so far.   //////dmoCD.SQLQuery1.SQL.Text:= 'SELECT * from cdinventory WHERE cdinventory.CDArtist = ' + Artist + ';';   // Yeah you are searching using the Artist value as a variable or column name quote it to make a value. eg.   dmoCD.SQLQuery1.SQL.Text:= 'SELECT * from cdinventory WHERE cdinventory.CDArtist = ' + QuotedStr(Artist) + ';';   dmoCD.SQLQuery1.Open; //<--- normal again.   edtArtist.Clear; //<----- argh!!!! Why? don't remove user data that took him some effort to put there. Let him do that.   // or add a clear button on the form to clear everything up for him. end;  have fun ---------------------------------------------------------------------- tee · 2012-09-28 11:09 Haha Sorry for not being so specific and some badly edited code... I wrote an entire long post in reply and it didnt post...automatic logout... Let me start again I'm using Lazarus 1.0, Windows Vista, MySQL 5.0 (using Workbench) My table is called cdinventory: CDBarcode  INT --PK, Not Null CDArtist  VARCHAR(20) --Not Null CDTitle  VARCHAR(25)  --Not Null CDTracks  INT --Not Null CDCategory  VARCHAR(20) --Not Null CDQtyInStock  INT  --Not Null CDQtySold  INT  --Not Null CDPrice  DECIMAL(19,2)--Not Null I have four other tables but they aren't relevant. One of them 'invoiceline' is linked to 'cdinventory' by CDBarcode. As in, 'cdinventory' is the parent table which links 'CDBarcode' to 'Barcode' in invoiceline. But for this dilemma I'm only working with one table: 'cdinventory'. When a sale is made I ultimately wanted the user to enter in either CDBarcode or CDArtist in one editbox and the relevant fields would show up for one CD. I know this is a bad method because one Artist can have multiple CD's but for simplicity's sake its a small school assignment so only one artist will have one CD title to their name in this database. I have an editbox on my form, a button, and a 3 DBTexts' (CDTitle, CDArtist, CDPrice). So the user enters in a CDBarcode/CDArtist in the edit box and the 3 DBTexts' will be populated with that record's relevant information. Before i wrote the code behind the OnClick event handler of the button, I first tested the editbox to take only CDBarcode. Code: [Select]procedure TfrmSales.btnFindRecClick(Sender: TObject); var Barcode : string; begin   dmoCD.SQLQuery1.Close;   Barcode := edtSearch.Text;   dmoCD.SQLQuery1.SQL.Text:= 'SELECT * from cdinventory WHERE cdinventory.CDBarcode = ' + Barcode + ';';   dmoCD.SQLQuery1.Open;   end;  When I ran this code it worked perfectly and when a barcode number was entered in, it found the relevant record and displayed the info in teh DBTexts'. After this I tested the editbox to take only CDArtist: Code: [Select]procedure TfrmSales.btnFindRecClick(Sender: TObject); var Artist : string; begin   dmoCD.SQLQuery1.Close;   Artist := edtSearch.Text;   dmoCD.SQLQuery1.SQL.Text:= 'SELECT * from cdinventory WHERE cdinventory.CDArtist = ' + Artist + ';';   dmoCD.SQLQuery1.Open;   end; This is when problems arose. When i ran this and entered an artists name in the editbox, an error came up: Debugger Exception Notification Project CDSaleP raised exception class 'EDatabaseError' with message: scoConnection: Error executing query: Unknown column 'Neil Young' in 'where clause' So I did a bunch of testing and changed my records' CDArtist field by entering only numbers instead of artist's names (ex: replaced Neil Young with '456'). I then ran the program and it worked perfectly when I entered a number for the artist and it pulled up the designated record. But when I use alphabetical characters for the CDArtist field in my records, it does not pull up the record, it gives the error message shown above. ---------------------------------------------------------------------- BigChimp · 2012-09-28 11:48 Ah ok. Thanks for the clear post. If you are developing serious db apps, I wouldn't use the 'select bla from bla where bla2='++'; ' taazz' suggestion of using quotedstr helps a bit, but is not usable across dbs (e.g. if you ever port it to a better supported db like postgresql or firebird). I'd suggest parametrized queries: see e.g. this example http://wiki.lazarus.freepascal.org/SQLdb_Tutorial2#Filtering_data Code: [Select]SQLQuery1.SQL.Text:= 'select * from CUSTOMER where COUNTRY = :COUNTRY'; But that doesn't help you with your current problem. You may actually hit a bug with the FPC sqldb parser - it may think your data is a column. You could try with another version of FPC. I would suggest downloading a lazarus snapshot with an FPC 2.6.1 compiler; they're quite stable lately. Either/or you could zip up your project, including the script to create the MySQL db, attach it to a post and I'll have a look with a different fpc version (current trunk, as well as 2.6.1 - fixes 2.6). Thanks, BigChimp ---------------------------------------------------------------------- ludob · 2012-09-28 11:50 QuotescoConnection: Error executing query: Unknown column 'Neil Young' in 'where clause'Taazz already mentioned it before: you need to put quotes around string literals in MySQL. Code: [Select]dmoCD.SQLQuery1.SQL.Text:= 'SELECT * from cdinventory WHERE cdinventory.CDArtist = ' + QuotedStr(Artist) + ';'; EDIT: Or even better, use parameters as BigChimp suggests. You don't have to worry about quotes any more. ---------------------------------------------------------------------- tee · 2012-09-28 12:01 Thanks guys! I tried again..didn't work. But I'll use those suggestions from now on ---------------------------------------------------------------------- BigChimp · 2012-09-28 12:01 You could throw in Code: [Select]dmoCD.SQLQuery1.SQL.Text:= 'SELECT * from cdinventory WHERE cdinventory.CDArtist = ' + QuotedStr(Artist) + ';'; edtSearch.Text:=dmoCD.SQLQuery1.SQL.Text ... and copy the query from edtSearch... then run that in a query tool to see if it executes there. Ludo is nearly always right, but I suppose I don't see the quote thing here... unless you entered 'Neil Young' instead of Neil Young.... ---------------------------------------------------------------------- BigChimp · 2012-09-28 12:01 Quote from: tee on September 28, 2012, 02:01:11 pmThanks guys! I tried again..didn't work. But I'll use those suggestions from now on Suggest you post the project then... ---------------------------------------------------------------------- ludob · 2012-09-28 12:11 Quote I suppose I don't see the quote thing here... unless you entered 'Neil Young' instead of Neil Young....Original code was Code: [Select]dmoCD.SQLQuery1.SQL.Text:= 'SELECT * from cdinventory WHERE cdinventory.CDArtist = ' + Artist + ';';When Artist = Neil Young the resulting query is Code: [Select]SELECT * from cdinventory WHERE cdinventory.CDArtist = Neil Young ;. MySQL interpretes Neil Young as a column name. Hence the error message. Literal strings have to be quoted. ---------------------------------------------------------------------- tee · 2012-09-28 12:17 Thank you!!!! Finally got it! And yes that was my mistake...I just VERY stupidly quoted it wrong when you asked me to try. Thanks so much guys! Saved me a headache and a half! ---------------------------------------------------------------------- BigChimp · 2012-09-28 13:24 Duh. Should have seen that. @tee: now quickly please use at least QuotedStr otherwise prepare for horrible results with artist Sinead O'Connor... ---------------------------------------------------------------------- tee · 2012-09-28 13:41 Oh yes yes I used QuotedStr() haha too many single quotes for my blind eyes to handle! Lol after this charrade I've learned my lesson! Quoting Sinead O' Conner would be hell without QuoteStr() ---------------------------------------------------------------------- goodname · 2012-09-28 14:19 For such simple queries I would suggest parameters queries. If your going to use QuotedStr with MySQL make sure backslash escapes are not used.   A   \'   in a string run through QuotedStr will result in a database error if it uses backslash escapes. http://dev.mysql.com/doc/refman/5.0/en/server-sql- mode.html#sqlmode_no_backslash_escapes