MySQL50 - newbie problem (Windows)
lazarus / 16-Databases

----------------------------------------------------------------------
arwen · 2007-05-02 09:23

I try to use MySQL50 but I have some problem :
1- I have put in work MySQL50 (and it work fine)
2- I have copy the mySQL.dll from the MySQL installation directory to
the directory of my project.
3- I can connect to the database using the MySQL50Connection
components
4 - I have place too on the main form the SQLTransaction and SQLQuery
components

The problem is that when I try to do a query like below :

Code: [Select]
SQLQuery1.SQL.Text := "show databases";
SQLQuery1.Open ;


I receive a SQL syntax error :
Quote
................ for the right syntax to use near '' at line 1


Why  :!:

Thanks to all .

----------------------------------------------------------------------
DougNettleton · 2007-05-02 16:25

Arven:

I don't know why but, I've only been able to perform: select, insert,
delete and update statements within an SQLQuery statement.

Doug

----------------------------------------------------------------------
DougNettleton · 2007-05-02 16:26

Sorry, I got your name / handle wrong - Arwen.

Doug

----------------------------------------------------------------------
tlacuache · 2007-05-02 18:11

I seem to remember I had this problem. I think setting the ParseSQL
property to "FALSE" fixed it for me. Try that.

Seth

----------------------------------------------------------------------
Vincent Snijders · 2007-05-02 18:25

Instead of  Code: [Select]SQLQuery1.Open;
try  Code: [Select]SQLQuery1.ExecSQL;

----------------------------------------------------------------------
arwen · 2007-05-02 21:35

For understand how work the MySQL components I have download the
mysql_demo_20050408.tar at this URL :
http://prdownloads.sourceforge.net/lazarus-
ccr/mysql_demo_20050408.tar.gz?download
but don't work .
I find it in the lazarus wiki .
http://wiki.lazarus.freepascal.org/MySQLDatabases

Where I can find a complete example for MySQL5?

Vincent if I use
Code: [Select]
SQLQuery1.ExecSQL;


in the example this don't works
Code: [Select]
while not SQLQuery1.EOF do begin
      DatabaseComboBox.Items.Add(SQLQuery1.Fields[0].AsString);
      SQLQuery1.Next;
    end;


Thanks to all  :wink:

----------------------------------------------------------------------
rturney · 2007-05-03 05:42

I am having the same problem as arwen.

QuoteI seem to remember I had this problem. I think setting the
ParseSQL property to "FALSE" fixed it for me. Try that.

This did not fix the problem, but the following did:

QuoteInstead of
Code:
SQLQuery1.Open;

try
Code:
SQLQuery1.ExecSQL;

That fixed the original problem.  Thanks Vincent.

But now the DatabaseComboBox is not getting loaded with the database
names.  It appears that SQLQuery1.EOF is already "True" when the while
loop starts so then goes right to
Code: [Select]ShowString('List of databases received!');
The ComboBox never gets loaded. 

It seems like there needs to be a "MoveFirst" type statement before
the while loop executes.

----------------------------------------------------------------------
rturney · 2007-05-03 13:10

Maybe I'm wrong, but it seems like there needs to be a "MoveFirst"
before the "while not SQLQuery1.EOF do begin" is executed.  Here's an
example of one of my VB6 programs that does something similar with a
MySQL database:
Code: [Select]    '---get record count and move to last
record-----------------------
    Set adoRS = New ADODB.Recordset
    strSQL = "SELECT bug_id FROM " & gstrDBTable
    adoRS.Open strSQL, ADOCn

    adoRS.MoveFirst
    i = 0
   
    Do While Not adoRS.EOF
        cboAllBugIDs.AddItem adoRS.Fields(0)
        cboRecIndex.AddItem i
        adoRS.MoveNext
        i = i + 1
    Loop

----------------------------------------------------------------------
arwen · 2007-05-03 14:23

Like about the TSDFDataSet
http://www.lazarus.freepascal.org/index.php?name=PNphpBB2&file=viewtop
ic&t=3289&highlight=
I can make an example if someone say me where I can find information
on how work the Lazarus MySQL5 components . :cry:
Or is better use the ZeosDBO-6.6.1 components ??
http://zeos.firmos.at/portal.php

Thanks to all  :wink:

----------------------------------------------------------------------
RudieD · 2007-05-03 17:12

Did you try
Code: [Select]SQLQuery1.ExecSQL;
if SQLQuery1.Active then
  SQLQuery1.First;

----------------------------------------------------------------------
MT · 2007-05-03 19:02

Quote from: "arwen"
Or is better use the ZeosDBO-6.6.1 components ??


Yes I'm using Zeos and it is very nice except one bug. I coldn't use
AfterApplyUpdates of Zquery.
it gives this error:
"ZDataset.pas(54,6) Error: identifier not found: TNotifyEvent"

----------------------------------------------------------------------
rturney · 2007-05-03 19:54

QuoteDid you try

SQLQuery1.ExecSQL;
if SQLQuery1.Active then
  SQLQuery1.First;

I don't know about arwen, but I took your suggestion and used the
following code:

Code: [Select]    SQLQuery1.SQL.Text := 'show databases;';
    {SQLQuery1.Open;}
    SQLQuery1.ExecSQL;
    if SQLQuery1.Active then
       SQLQuery1.First;
    while not SQLQuery1.EOF do begin
      ShowString('Inside loop');
      DatabaseComboBox.Items.Add(SQLQuery1.Fields[0].AsString);
      SQLQuery1.Next ;
    end;
    SQLQuery1.Close;

The "DatabaseComboBox" did not load the database names.  It still is
empty.  I also inserted the "ShowString" function to get a quick way
of seeing if the execution had entered the "while" loop.  But it did
not.  I think that "SQLQuery1.EOF" is "True" and execution never
enters the loop.  I thought that your suggestion would fix the
problem, but it didn't.

----------------------------------------------------------------------
rturney · 2007-05-03 19:56

I think I forgot to mention that I am running Linux and it appears
that arwen is running MS Windows.

----------------------------------------------------------------------
arwen · 2007-05-04 06:58

I'm using windows but I hope that the MySQL components works too on
Linux . :wink:
The reason for which I'm using Lazarus is that an application can be
compiled on both the OS .

The ZeosDBO-6.6.1 components works too on Linux ??  :shock: I hope

Thanks to all  :wink:

----------------------------------------------------------------------
MT · 2007-05-04 15:44

Quote from: "arwen"
The ZeosDBO-6.6.1 components works too on Linux ??  :shock: I hope

Thanks to all  :wink:

Yes, it does.

----------------------------------------------------------------------
rturney · 2007-05-04 23:05

For some reason that I can't explain, I have gotten past the previous
problems.  I was thinking I'd try the "SQLQuery1.Open;" again instead
of "SQLQuery1.ExecSQL;" and maybe the "SQLQuery1.First;" statement
would then work.  It did!!!!  Here's what I used:
Code: [Select]  // First lets get a list of available databases.
  if MySQLConnection1.Connected then begin
    ShowString('Connected to server: ' + HostEdit.Text);
    ShowString('Retrieving list of available databases.');
    SQLQuery1.SQL.Text := 'show databases';

    SQLQuery1.Open;
    {SQLQuery1.ExecSQL;}

    if SQLQuery1.Active then begin
       SQLQuery1.First;
       ShowString('SQLQuery1 is Active');
       end
       else
       ShowString('SQLQuery1 is not Active');
      
    while not SQLQuery1.EOF do begin
      ShowString('Inside loop');
      DatabaseComboBox.Items.Add(SQLQuery1.Fields[0].AsString);
      SQLQuery1.Next ;
    end;
    SQLQuery1.Close;
    ShowString('List of databases received!');
  end;
Out of curiosity, I commented out the "if SQLQuery1.Active then begin"
statements and re-ran the program.  And to my surprise it also ran
without any errors!  In both variations the DatabaseComboBox was
filled with the database names in my MySQL. 

I'm not sure how to explain this because now I am back to the original
code that was used in the tutorial!!!!  And this is what caused arwen
to originate this thread.  Arwen (using Windows) and I (using Linux)
would get an error stating:
QuoteMySQLConnection1: Error executing query: You have an error in
your SQL syntax; Check the manual that corresponds to your MySQL
server version for the right syntax to use near " at line 1.
Maybe one of the Lazarus developers can shed some light on this.

Now that I got past that problem, with the program still running, in
the DatabaseComboBox, I clicked on a simple database I had created in
MySQL called "addressbook" with a table called "tbl_friends".  When I
clicked the SelectDBButton I got the following error:
QuoteOperation cannot be performed on an active dataset.
I am still looking at this latest problem and will post any progress I
make to this thread.  I suppose I should post to this thread.  Maybe I
should start a new thread?

----------------------------------------------------------------------
arwen · 2007-05-05 07:04

I try your code but I receive only the old error message [On Windows].
:cry:

This Weekend I'll try the Zeos components  :evil:
I need to use a MySQL database . I must use it . :twisted:

Thanks to all and have a Good WeekEnd  :wink:

----------------------------------------------------------------------
bram · 2007-05-07 11:21

Note: SQLQuery.ExecSQL does not create a result set, whereas
SQLQuery.Open does.

For a query like SHOW DATABASES, it is best to set
Code: [Select]SQLQuery.ParseSQL:=false;
SQLQuery.ReadOnly:=true;

----------------------------------------------------------------------
rturney · 2007-05-07 15:58

Bram,

Thanks for the clarification on SQLQuery.Open and .ExecSQL.  Just out
of curiosity are you able to run the Lazarus MySQL tutorial?  I have
run into many problems with it and am wondering if it is broken or I'm
just going crazy?

----------------------------------------------------------------------
arwen · 2007-05-07 16:05

QuoteNote: SQLQuery.ExecSQL does not create a result set, whereas
SQLQuery.Open does.

For a query like SHOW DATABASES, it is best to set

Code: [Select]SQLQuery.ParseSQL:=false;
SQLQuery.ReadOnly:=true;


  THANKS bram now WORKS