simple question needs an answer

[tanghz]

RAD 2010+UniDAC 4.0

Please read the following code, when I stepped into
tUniQury.Prepare;
Got and error message "42000Query was empty" . I used designtime compoent , if left the SQL property empty and populate the SQL string at runtime, same error will happen. Where I did wrong?

Procedure Test;
var tUniQury:TUniQuery;
begin
tUniQury:=TUniQuery.Create(self);
tUniQury.Connection:=HomeMySQL;
tUniQury.SQLinsert.Clear;
tUniQury.SQLInsert.Text:='insert into orders_from_scl select * from tmp where PO_number="'+LabeledEdit1.Text+'"';
tUniQury.Prepare;
tUniQury.execute;
tUniQury.UnPrepare;
tUniQury.Free;
end;

thanks,

[AlexP]

Hello,

This error is connected with the fact that you don't specify a SQL query. You should specify your Insert query in the SQL property instead of SQLInsert:

Code: Select all

Procedure Test; 
var tUniQury:TUniQuery; 
begin 
tUniQury:=TUniQuery.Create(self); 
tUniQury.Connection:=HomeMySQL; 
tUniQury.SQL.Clear; 
tUniQury.SQL.Text:='insert into orders_from_scl select * from tmp where PO_number="'+LabeledEdit1.Text+'"'; 
tUniQury.Prepare; 
tUniQury.execute; 
tUniQury.UnPrepare; 
tUniQury.Free; 
end;

[tanghz]

Thanks for your reply, AlexP. Problem resolved. Could you tell me the differencen? or where I can find the info. There are quite a bunch of sub-SQL properties such as SQLInsert, SQLupdate, etc there. What are the advantages of them compared to SQL?

[AlexP]

Hello,

The SQLinsert, SQLUpdate, SQLDelete, and SQLRefresh properties are used to create SQL operators that are called after calling of the Post method of the corresponding DataSet and if they are not specified, they will be set by default. I. e., if the Select operator is specified in SQL.Text and you need to have specific Insert, Update, etc. operations, you can use these properties.
You can find more detailed information in the help file.

[tanghz]

Thanks , I will read the help file again.

Now I have another problem as shown below:
I am trying to insert a new record with a BLOB field by UniQuery. If I dont open/activate the tUniQury , it wont allow me to Insert on a closed databset. If I use tUniQury.open; It will complain "Query was empty".
What is the correct way of implementing this function here?

Many thanks

Code: Select all

procedure TForm1.AdvSmoothButton2Click(Sender: TObject);
var tUniQury:TUniQuery;
begin
tUniQury:=TUniQuery.Create(self);
tUniQury.Connection:=form2.HomeMySQL;
if OpenDialog1.Execute() then
  begin

  tUniQury.open;
    tUniQury.Insert;
    tUniQury.FieldByName('Batch_number').AsInteger:= strtoint(LabeledEdit2.Text);
    tUniQury.FieldByName('PO_number').AsInteger:= strtoint(LabeledEdit1.Text);
    tUniQury.FieldByName('FileName').Asstring:= OpenDialog1.FileName;
    TBlobField(tUniQury.FieldByName('Bol')).LoadFromFile(OpenDialog1.FileName);
    tUniQury.Post;
  end;
tUniQury.Free;
end;

[AlexP]

Hello,

Before calling the Open method of DataSet, you should specify the query text in the SQL.Text property in the following way:

Code: Select all

if OpenDialog1.Execute() then 
begin 
  tUniQury.SQL.Text := 'select Batch_number, PO_number, FileName, Bol from your_table'
  tUniQury.open;
.............

[tanghz]

Will that SQL drag all the records to local client dataset? Please bear in mind 'bol' is a BLOB field. it seems not so efficient when any row of the table holds a large single BLOB in.

[AlexP]

Hello,

If you need only to insert data into a table, you can use a parametrized Insert, for example:

Code: Select all

tUniQury.SQl.Text := 'insert into orders_from_scl (Batch_number, PO_number, FileName,  Bol) values (:Batch_number, :PO_number, :FileName,  :Bol)';
tUniQury.ParamByName('Batch_number').AsInteger:= strtoint(LabeledEdit2.Text); 
tUniQury.ParamByName('PO_number').AsInteger:= strtoint(LabeledEdit1.Text); 
tUniQury.ParamByName('FileName').Asstring:= OpenDialog1.FileName; 
tUniQury.ParamByName('Bol').AsBlobRef.LoadFromFile(OpenDialog1.FileName);
tUniQury.Execute;

If you need to display data from a table to which large amounts of BLOB data were written, you can increase productivity, decrease memory usage and traffic by excluding BLOB field into the main Select. You can receive data from this field in case of necessity.

[tanghz]

Spot on. I believe it is the correct answer. I will try it later.
Currently, I am using a pseudo select e.g.

tUQery1.SQL.text:='SELECT * from tablename WHERE FLASE_condition limit 1; ' to get the table struction and avoid the QueryEmpty complain, then carry on with my previous code.

this is a dummy way, worked well tho.

[AlexP]

Hello,

To get only table structure (for further inserting), you can use "limit 0" instead of the "limit 1" construction, because if you use "limit 1", one row will be selected, and if you use "limit 0" – only structure will be selected.

[tanghz]

Thanks Alex, It looks more pro. and I still can use UniQuery. I will take this answer.

I have another 2 questions; (If I need to open another thread, pls let me know)

1) MySQL BLOB: I have set the enviroment variable MaxCachesize to 15M to make sure the normal size of PDF file can be populated. However, there might be some change of larger file (>1 5MB) , is there any way UniDAC component can do to overcome the size restriction, e.g split the bigger file into parts of 15MB and merge on the server side? or what is the proper solution for this situation?

2) I want to dev a program, it should have a local database (can be embedded e.g. mysql, or Sqlite) or local file. when the program can not connect to the remote server, it will do the local retrieve and store, etc. Once it is online, it will synch with the remote. What is the best route for this task?


Many thanks.

Haizhou Tang

[AlexP]

Hello,


1) UniDAC does not support possibility to receive/send data from/to the servers by parts.
2) UniDAC does not provide database synchronization/replication mechanism, that's why you need to use utilities of MySQL server itself to synchronize MySQL servers (http://dev.mysql.com/doc/refman/5.0/en/replication.html ).