Solved

Get some data from remote DB to my local DB

Posted on 2007-03-21
22
235 Views
Last Modified: 2010-04-05
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.
0
Comment
Question by:CodedK
  • 12
  • 10
22 Comments
 
LVL 21

Expert Comment

by:ziolko
ID: 18762923
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
 
LVL 16

Author Comment

by:CodedK
ID: 18763056
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
 
LVL 21

Expert Comment

by:ziolko
ID: 18763110
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
 
LVL 16

Author Comment

by:CodedK
ID: 18763367
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
 
LVL 21

Expert Comment

by:ziolko
ID: 18763400
i use mostly MSSQL with ADO so no great exp with dbx but you can try here:
http://www.crlab.com/dbx/

ziolko.
0
 
LVL 16

Author Comment

by:CodedK
ID: 18769261
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
 
LVL 21

Expert Comment

by:ziolko
ID: 18769778
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
 
LVL 16

Author Comment

by:CodedK
ID: 18769800
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
 
LVL 21

Expert Comment

by:ziolko
ID: 18769896
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
 
LVL 16

Author Comment

by:CodedK
ID: 18769906
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
 
LVL 21

Expert Comment

by:ziolko
ID: 18769929
ok, i'll give it a hit and get back to you

ziolko.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 16

Author Comment

by:CodedK
ID: 18769931
Thanks ziolko :)
0
 
LVL 21

Expert Comment

by:ziolko
ID: 18769965
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
 
LVL 21

Expert Comment

by:ziolko
ID: 18769969
...removed top10 from select still no errors

ziolko.
0
 
LVL 21

Expert Comment

by:ziolko
ID: 18769978
oh whats important i dont have MySQL i use MSSQL

ziolko.
0
 
LVL 16

Author Comment

by:CodedK
ID: 18769987
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
 
LVL 16

Author Comment

by:CodedK
ID: 18769993
>>> 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
 
LVL 21

Expert Comment

by:ziolko
ID: 18770001
my bet is that MySQL drivers for dbx are corrupt.

ziolko.
0
 
LVL 16

Author Comment

by:CodedK
ID: 18770032
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
 
LVL 21

Accepted Solution

by:
ziolko earned 500 total points
ID: 18770047
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
 
LVL 16

Author Comment

by:CodedK
ID: 18772531
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
 
LVL 21

Expert Comment

by:ziolko
ID: 18772679
:) glad i could help

ziolko.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
A short film showing how OnPage and Connectwise integration works.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now