Delphi TmysqlQuery

[question]

Hi,
I am using Tmysqlquery and TmySqlUpdateSQL to update(insert/delete/modify) the database concring user input, but i get the error "you have an error in your sql syntax near 'OC2000000A000000201' EmySqldatabase error"

i have checked several times the query and database connection, everything is fine but i dont have any idea why i get this error
do you have any idea?

[mkinzler]

Without the complete Query it's very hard to impossible to help you.

[DonManfred]

Zitat:

Zitat von question (Beitrag 1227068)

Hi,
I am using Tmysqlquery and TmySqlUpdateSQL to update(insert/delete/modify) the database concring user input, but i get the error "you have an error in your sql syntax near 'OC2000000A000000201' EmySqldatabase error"

The problem is not the databaseconnection. The problem is the SQL-Query you are using.

LOG it for debugging purposes. Or maybe post the query here...

[question]

as i have used tmysqlquery instead TQuery ,therefore I have one idea, it can happend becasue of tmysqlquery component?

here is the code for insert (it is from dfm)

Code:

insert into TableAdd
  (Name, Address, Phone, Emial, Date)
values
  (:Name, :Address, :Phone, :Emial, :Date)

modify

Code:

update TableAdd
set
  Name= :Name,
  Address= :Address,
  Phone= :Phone,
  Emial= :Emial,
  Date= :Date
where
  ID = :OLD_ID

delete

Code:

delete from TableAdd
where
  ID = :ID

[DeddyH]

What happens when you quote the fieldnames?

Code:

insert into TableAdd
  (`Name`, `Address`, `Phone`, `Emial`, `Date`)
values
  (:Name, :Address, :Phone, :Emial, :Date)

At least "Date" is quite surely a reserved word because there is a corresponding data type.

[question]

I have tried as you suggested even i have also tried by changing the name of the date field

Code:

insert into TableAdd
  (`Name`, `Address`, `Phone`, `Emial`, `Date`)
values
  (:Name, :Address, :Phone, :Emial, :Date)

but still have the same error, by the way,how can i check System.DateTime value and MySql.Data.Types.MySqlDate, maybe the datetime of the system and the mysqlDate is not matching, its just my idea,what do you think?

[DeddyH]

SQL-Parameters are usually parsed automatically. Maybe your field is a timestamp instead of a Date/DateTime? It is hard to find a solution when you don' t know either the table structure nor the Delphi-code used to execute your queries.

[question]

the field 'Date' is a DateTime field,

[Lemmy]

Hi,

please try this one:

Delphi-Quellcode:

  xxx.ParamByName('Date').AsString := FormatDateTim('yyyy-mm-dd', YourDateValue);

cu

[question]

the querystring is done by using dfm with field and parameter
how can i use

Code:

xxx.ParamByName('Date').AsString := FormatDateTim('yyyy-mm-dd', YourDateValue);

[DeddyH]

You have to fill the parameters with values before you execute your statement. Didn' t you do this?

[baumina]

Please check while debugging Tmysqlquery.Text. This is the SQL statement passed to MySQL.

[question]

Hi,
i have debug the application and i have found that, the system date format is: 2013-09-04(yy-mm-dd) but the database query dateformat shows the: 04-09-2013(dd-mm-yy)
how to make it similar formate?

[DeddyH]

You can try using FormatDateTime.

[question]

i have tried the follwoing ,but still have the same problem

Code:

QueryAdd1.ParamByName('DateNew').AsString:= FormatDateTim('yyyy-mm-dd', DateNew);

[mkinzler]

Why not

Delphi-Quellcode:

QueryAdd1.ParamByName('DateNew').Value := DateNew;

or

Delphi-Quellcode:

QueryAdd1.ParamByName('DateNew').AsDate := DateNew;

What type has the field in the DB?

[question]

in DB it is DateTime field

[mkinzler]

Then use .Value

[question]

i have used .value but its the same :(

[baumina]

Is your error still ? :

Zitat:

Zitat von question (Beitrag 1227068)

"you have an error in your sql syntax near 'OC2000000A000000201' EmySqldatabase error"

The string 'OC2000000A000000201' dont looks like a dateformat-problem.

[question]

how can i convert dateformate from 05.09.2013 to 2013.09.05 17:00:00 using following query

Code:

query1.parambyname('newdate') := newdate //here is the  05.09.2013 17:00:00

but i want

Code:

query1.parambyname('newdate') := newdate /this formate i want 2013.09.05 17:00:00

[DeddyH]

What type is newdate of?

[question]

sorry, i forgot to mention it is DateTime formate

Code:

query1.parambyname('newdate').AsDateTime := newdate /this formate i want 2013.09.05 17:00:00

[mkinzler]

A Windows/Delphi DateTime isn't formated ( it's a Double!). The machnism behind the Komponents should convert the Value into the coresponding fomat. ( Unfortunately mysql unlike other dbms stores string values in the format YYYY-MM-DD HH:MM:SS instead of the use of an independant format).

But as written before, your problem seems be different of this!

[question]

hi, finally i have found the problem, i DB there is one field as a Blob filed, i have read it as a string
forexample: 'lateId' is Blob field

Code:

Query1.ParamByName('lateID').(what to write here) := Query2.FieldByName('lateID').As(?)

[mkinzler]

Something like this

Delphi-Quellcode:

ms: TMemoryStream;
...

try
  ms := TMemoryStream.Create;
  (Query2.FieldByName('lateID') as TBlobField).SaveToStream( ms);
  (Query1.ParamByName('lateID') as TBlobField).LoadFromStream( ms);  
finally
  ms.Free;
end;

[question]

can i also do the following

Code:

QueryA.ParamByName('lateId').AsString := QueryB.FieldByName('lateID').AsString
//Actually i need to do the work with fieldbyname and parambyname
how to that for blobfield with parambyname and fieldbyname

[DeddyH]

As far as can I see Markus uses both FieldByName and ParamByName.

[question]

Hi,
i have still this problem concerning Blob field, i have treid many ways, actually before (when the SQL manager 2010 installed in German language) i have worked with blob field and it was working fine and then i have reinstalled SQL Manager 2010 in English language after that it blob data types are not working, do you think -it can be becaause of the language of SQL Manager?

[question]

Hi,

Code:

//here 'name' is string field and i can write in the following way
 QueryA.ParamByName('Name').AsString :=  QueryB.FieldByName('Name').AsString;

is it also possible to do the same for blob field similar to string/integer field?

Code:

//here NewID is Blob field
QueryA.ParamByName('NewID ').??? :=QueryB.FieldByName('NewID').???; //here how to define the datatype for Blob , i have tried with As TblobField but it does not work

need your suggestion

[DeddyH]

Did you try Markus' suggestion using a temporary MemoryStream? This should IMO do the trick.

[mkinzler]

A Blobfield is not a normal field, because the content isn't automatically fetched.