Get some data from remote DB to my local DB

Hi.

I am posting the code i use to get some data from my remote DB (SQL).
~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
procedure TForm1.BitBtn2Click(Sender: TObject);
var
  queryStr ,coded : string;
begin
ListBox1.Clear;
queryStr:='SELECT view.name_view FROM view INNER JOIN mytable ON view.id_views=mytable.name_views';
sqlquery.SQL.Clear;
sqlquery.SQL.Add(queryStr);
sqlquery.ExecSQL();
sqlquery.Open;

while not sqlquery.Eof do
      begin
            coded:=sqlquery.Fields[0].Value;
            ListBox1.Items.Add(coded);
            sqlquery.Next;
      end;
sqlquery.Close;
end;
~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

The problem with this code is that if i execute it 2 or 3 times i get Access Violation Read/Write errors in memory.
What's wrong ?

I use SQL Connection & SQL Query from DBExpress.
LVL 16
CodedKAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ziolkoCommented:
first of all:

sqlquery.ExecSQL();
sqlquery.Open;

use only
sqlquery.Open;

second thing: put loop in try/finally block:

ListBox1.Clear;
queryStr:='SELECT view.name_view FROM view INNER JOIN mytable ON view.id_views=mytable.name_views';
sqlquery.SQL.Clear;
sqlquery.SQL.Add(queryStr);
try
sqlquery.Open;
while not sqlquery.Eof do
      begin
            coded:=sqlquery.Fields[0].Value;
            ListBox1.Items.Add(coded);
            sqlquery.Next;
      end;
finally
sqlquery.Close;
end;

is it only code in OnClick event?
ziolko.
0
CodedKAuthor Commented:
ok my friend ziolko this seem to be the solution now i don't get the above errors but i get 2 other errors

1st)
about project... i get this -->dbexrpess error:parameter not set
SQL Server error:Can't connect to MySQL server on the hostname

or sometimes this:

2nd)Lost connection with server when execute

is something wrong with the server and it is not a problem from my program or what else?
0
ziolkoCommented:
hmmm don't have exp. with MySQL but
1. try setting ParamCheck := False;
2. make sure your connection has KeppConnection set to true

both errors seem to be related to connection configuration

ziolko.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

CodedKAuthor Commented:
i have already this options as you said...

Do you know if there is any DBXpress component update available ?

Now i'm getting "Invalid pointer operation" again and "Unable to execute query".
0
ziolkoCommented:
i use mostly MSSQL with ADO so no great exp with dbx but you can try here:
http://www.crlab.com/dbx/

ziolko.
0
CodedKAuthor Commented:
Ziolko thanks very much for your help.

I was going to close the question from your first post. I've tried the finally close and
it worked for 15 times in a row. After that... I got every kind of error you can imagine.
Here 's a short list :

1) Access violations in memory
2) Cursor didnt return from query
3) SQL statement didnt complete.
4) Could not establish connection
5) Invalid pointer operations
6) Unable to execute queries
7) Delphi IDE termination
8) SQL was partly executed

I don't know what to do. I've reinstalled Delphi (7) and all the updates.
You said u use ADO. Can i use ADO with a remote DB ?
0
ziolkoCommented:
sure you can use ADO with remote DB,
but once again i dont have exp. with MySQL so i'm not sure there are ADO drivers for MySQL
ot at least ODBC for MySQL

ziolko.
0
CodedKAuthor Commented:
I found out why i get so many errors...
Its here ---> coded:=sqlquery.Fields[0].Value;

The Access violations and the Invalid Pointer errors all start from the point i'm trying to get the value.
This is a text field. And i cant get the value this way.
I tried AsString and many other ways. The only valid seems to be "GetData".

I've tried to put the Data into a buffer :
~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
    with MySqlQuery.Fields[15] do
    begin
     { Allocate space }
     GetMem(MyBuffer, DataSize);
         try
            if not GetData(MyBuffer) then
               MyGrid1.Cells[6,MyGrid1.LastAddedRow]:=DisplayName + 'is Null :('
            else
            begin
               GetData(MyBuffer,true);
               MyGrid1.Cells[6,MyGrid1.LastAddedRow]:=DisplayName + ' Data snatched';
               coded:=BufToStr(MyBuffer,DataSize);
               ShowMessage(coded);
               //Data appear to be null ???!!!
            end;
            finally { Free the space }
            FreeMem(MyBuffer, DataSize);
         end;
    end;
~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

Can you help me with this ? :)
0
ziolkoCommented:
well, this works for me:

procedure TForm1.Button1Click(Sender: TObject);
begin
  SQLQuery1.SQL.Clear;
  SQLQuery1.SQL.Add('select * from dat0001');
  SQLQuery1.Open;
  while not SQLQuery1.Eof do begin
    Memo1.Lines.Add(SQLQuery1.FieldByName('Col2').AsString);
    SQLQuery1.Next;
  end;
end;

ziolko.
0
CodedKAuthor Commented:
What type is Col2 is it text ?
If it is text put some data manually (over 255 chars) and try to get it.
Put some ...more than 1 at least.
And then try to get it with this code... Press the button 2-3 times...
0
ziolkoCommented:
ok, i'll give it a hit and get back to you

ziolko.
0
CodedKAuthor Commented:
Thanks ziolko :)
0
ziolkoCommented:
ok i did this:

procedure TForm1.Button1Click(Sender: TObject);
var cnt: Integer;
begin
  Memo1.Clear;
  SQLQuery1.SQL.Clear;
  SQLQuery1.SQL.Add('select top 10 * from dat0001');
  SQLQuery1.Open;
  while not SQLQuery1.Eof do begin
    for cnt := 0 to SQLQuery1.Fields.Count - 1 do
      Memo1.Lines.Add(SQLQuery1.Fields[cnt].AsString);
    SQLQuery1.Next;
  end;
end;

dat0001 has 790 records in 8 fields (int, text, varchar, nvarchar)
and there are fields that contain more than 300 chars,
i pressed button few dozen times, no errors

ziolko.
0
ziolkoCommented:
...removed top10 from select still no errors

ziolko.
0
ziolkoCommented:
oh whats important i dont have MySQL i use MSSQL

ziolko.
0
CodedKAuthor Commented:
Ziolko i dont know what to say.

I've tried this in 3 different servers.
Compiled and run my application in 2 computers.
Reinstalled Delphi in my PC.
Remember that i also did this for 5-6 times successfully when i implemented finally from your first post.
If the error appear even for one time. WHATEVER i do it wont go away.

I also cleared and created the remote DB many many times.
0
CodedKAuthor Commented:
>>> I dont have MySQL i use MSSQL

Maybe this is why. I really cant explain it.
If i turn text to varchar in the remote DB then everything works fine !
But i need text....
0
ziolkoCommented:
my bet is that MySQL drivers for dbx are corrupt.

ziolko.
0
CodedKAuthor Commented:
I will try to get some new drivers, i found a site.
I'll get back to post the results.
Thanks for your help :D
0
ziolkoCommented:
i found this on crlag website:

dbExpress driver for MySQL (DbxMda) provides access to MySQL database. It can work using native MySQL network protocol or MySQL client. DbxMda supports MySQL server 5.0, 4.1, 4.0 and 3.23. If you do not use Direct mode then MySQL client library is required.

so maybe so maybe its version problem

also you can try to search thru forum or contact support

ziolko.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CodedKAuthor Commented:
Ziolko i've tried several components.
Only the one you said "DbxMda" works without no problems. :D
But unfortunately its not free.

The only problem i thing is that DBExpress stores the result in a short string.
I dont know how to tamper with it, to make my own component... An AnsiString version..

Anyway. Thank you very much for your help and support :)
0
ziolkoCommented:
:) glad i could help

ziolko.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.