Solved

Get some data from remote DB to my local DB

Posted on 2007-03-21
22
234 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
ok, i'll give it a hit and get back to you

ziolko.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 16

Author Comment

by:CodedK
Comment Utility
Thanks ziolko :)
0
 
LVL 21

Expert Comment

by:ziolko
Comment Utility
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
Comment Utility
...removed top10 from select still no errors

ziolko.
0
 
LVL 21

Expert Comment

by:ziolko
Comment Utility
oh whats important i dont have MySQL i use MSSQL

ziolko.
0
 
LVL 16

Author Comment

by:CodedK
Comment Utility
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
Comment Utility
>>> 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
Comment Utility
my bet is that MySQL drivers for dbx are corrupt.

ziolko.
0
 
LVL 16

Author Comment

by:CodedK
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
:) glad i could help

ziolko.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Creating and Managing Databases with phpMyAdmin in cPanel.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

763 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

10 Experts available now in Live!

Get 1:1 Help Now