Devart Forums

mika

Thanks for answer!
Command timeout helps a lot.

mika

Hi,

We have search functionality in our app, customer use it usually very nicely but sometimes they enter searchterms which cause query to run very longtime and they wan't to cancel it.
Since queries are blocking, it can't be terminated.

This idea sounds ugly, but in separete test app it works:
Create a separete Uniconnection and Uniquery
Pass them to iTask, which runs actual query.
If users wan't to cancel task, call uniconnection.disconnect, catch exceptions. Free components.

I feel this sounds dangerous / can cause un expected behaviour, but is is dangerous?
I'm using Actian ZEN with ODBC.

mika

Thank you.
I'm waiting for fix. We have somehow managed to use string presentation but working with 3rd part libraries which relay on UniDac makes upgrading hard. Have to patch that library and check that everyone in team makes that :)

mika

Hi,
I can't assign guid parameter .asguid, I have to pass guid as string and remove {} from string. Following example demostrates this

Code: Select all

var
  c:tuniconnection;
  uq: TUniQuery;
  g: TGUID;
begin
  c := TUniConnection.Create(nil);
  c.ProviderName := 'ODBC';
  c.server := 'Driver=Pervasive ODBC Unicode Interface;dbq=kirkar;ServerName=mylaptop';
  c.LoginPrompt := FALSE;
  c.Connected := true;
  uq := tuniquery.Create(nil);
  uq.connection := c;
  uq.sql.text := 'drop table if exists guidtest ';
  uq.execsql;
  uq.SQL.Text := 'create table guidtest (g uniqueidentifier not null default newid(), text varchar(100), primary key(g))';
  uq.ExecSQL;
  uq.SQL.Text := 'insert into guidtest (text) values (''hello there'') ';
  uq.ExecSQL;
  uq.SQL.Text := 'select * from guidtest';
  uq.open;
  g := uq.FieldByName('g').AsGuid;
  uq.Close;
  uq.SQL.Text := 'update guidtest set text = :p1 where g= :p2';
  uq.ParamByName('p1').AsString := 'will this work?';
  uq.ParamByName('p2').AsGuid:= g;
  uq.ExecSQL;
  codesite.send('uq.rowsaffected', uq.rowsaffected);
  uq.ParamByName('p1').AsString := 'how about this, will this work?';
  uq.ParamByName('p2').asstring:= copy(g.ToString,2,36); // remove { }
  uq.ExecSQL;
  codesite.send('uq.rowsaffected', uq.rowsaffected);

Ouput of codesite is

Code: Select all

uq.rowsaffected = 0
uq.rowsaffected = 1

Actians own string presentation of guid is without {}
I there anything I can do or do I just have to tell my team to use asstring?

mika

Hi, Awesome that you find the issue.
Sorry for delay, I tought I had email notification turned on but looks like I didn't have.

Actually my example wasn't really using unicode characters.
I also tried with datasample containing pure ascii characters and got same results and handling all strings as ansistrings.
But with this information I can use batchs if customer has unicode interface.

Thanks!

mika

Hi,
I have unidac 9.1.1, I'm trying to batch insert using ODBC provider to Actian Zen.
My problems is that data is stored with wrong length, typically length of first record value

Code: Select all

    aq.sql.Text := 'insert into ashakuterm (hakuasiakasnro, hakusana) values (:n, :s)';
    aq.params[0].DataType := ftInteger;
    aq.params[1].DataType := ftString;
    aq.Params.ValueCount := apuLista.Count; //apuLista=tstringlist
    for i := 0 to apuLista.count -1 do
    begin
      aq.Params[0][i].AsInteger := aasiakasnro;
      aq.params[1][i].asstring  := apuLista.strings[i];
      codesite.send('%d / %s',[aasiakasnro, apulista.Strings[i]);
    end;
    aq.execute(apuLista.count);

hakuasiakasnro is an integer , hakusana is varchar(40)

This is the codesite output of parameters

Code: Select all

8 / 8
8 / SKJ SYSTEMS LTD OY
8 / SKJ
8 / SYSTEMS
8 / LTD
8 / OY
8 / KUOPIO
8 / MÖTTÖNEN MAKE
8 / MÖTTÖNEN
8 / MAKE
8 / PENTTI HIRVONEN
8 / PENTTI
8 / HIRVONEN

And this is output of query select hakuasiakasnro, hakusana from ashakuterm where hakuasiakasnro=8

Code: Select all

8	8
8	S
8	L
8	O
8	K
8	M
8	P
8	H

If I do it without batch, it works ok.

mika

Hi,
We are currently using UniDAC 8.0.1 in our production enviroment.
I have in my development 8.3.2 - compiled from sources. delphi 10.3.1
In my development environment for example query select id, name, '' as addtionalname from table ...
produces additionalname as TMemofield while with application compiled with unidac 8.0.1 it's string field.
Is there an option to change this behaviour?

I'm using ODBC provider with Actian ZEN db.

mika

Uuh, sorry.
I'm using Actian Zen( or Pervasive PSQL as it was known previously)
version 13.2.

mika

Thanks for reply.
I have two problems with this
1) I have to put 0 to Id field before posting it (with this I can live ). Otherwise I get parameter 'id' not found exception.
2) But after post I get refresh failed, found 0 records.
I have tried with options.InsertAllSetFields := true.

Code: Select all

TUpdateexecutehelper = class
  procedure UniQueryBeforeUpdateExecute(Sender: TDataSet; StatementTypes: TStatementTypes; Params: TDAParams);
end;
procedure test;
var
  lCon: TUniConnection;
  lquery: TUniQuery;
  I: Integer;
  id: integer;
  Helper: TUpdateexecutehelper;
begin
  lCon := TUniConnection.Create( nil );
  lquery := TUniQuery.Create( nil );
  try
    Helper := TUpdateexecutehelper.Create;
    lCon.server := 'demodata';
    lCon.ProviderName := 'ODBC';
    lCon.LoginPrompt := false;
    lCon.Connect;
    lquery.Connection := lCon;
    lquery.SQL.Text := 'select * from  tuition';
    // ID   Degree   Residency   Cost_Per_Credit   Comments
    Lquery.Options.ReturnParams := True;
    lquery.RefreshOptions := [roAfterInsert];
    lquery.Options.InsertAllSetFields := true;
    lquery.BeforeUpdateExecute := Helper.UniQueryBeforeUpdateExecute;
    lquery.Open;
    lquery.KeyFields := 'id';
    lquery.Insert;
    lquery.FieldByName('id').AsVariant := 0;
    lquery.FieldByName( 'comments' ).AsString := TGuid.NewGuid.ToString;
    lquery.FieldByName( 'Cost_Per_Credit' ).asfloat := random( 1000 ) / 10;
    lquery.FieldByName( 'Degree' ).AsString := 'x';
    lquery.FieldByName('Residency').AsBoolean := false;
    lquery.Post;
    // field id should have value
    for I := 0 to lquery.fields.Count - 1 do
      writeln( lquery.fields[ I ].fieldname + ': ' + lquery.fields[ I ].AsString );

  finally
    lquery.Free;
    lCon.Free;
  end;
end;


procedure TUpdateexecutehelper.UniQueryBeforeUpdateExecute(Sender: TDataSet; StatementTypes: TStatementTypes; Params: TDAParams);
begin
  if StatementTypes = [stInsert] then
    Params.ParamByName('ID').ParamType := ptInputOutput;
end;

mika

Sorry about the delay. We are small company and i'm forced to other project and didn't have time for this. But Sample will be sent soon. Actually the Access violation has gone, but I still can't get autoincremnt field value after post.
I upgraded UniDac 7.5.13 which helped to AV.

mika

ok! Thanks for reply. I'll try to do it in next few days!

mika

We are still using MyDac also. I tried to download earlier version of them and installation of 10.0.1 told that it requires delphi 10.3.2. Would it be same case with newest unidac?

mika

Actually I have same problem.
I don't think that any of my components are depending on unidac, because if I remove them my delphi starts just fine.
Of course can't compile/open my application but delphi starts.

I have Delphi 10.3.1 installed.

mika

Hi,
I was wondering back in 2016 how to get autoinc value after sql insert
viewtopic.php?f=28&t=33967&p=117649#p117649

Is the feature metioned by AlexP implemented? I haven't seen that in version history.
Currently If i so
q.sql.text := 'select id, name from table'; (id is autoinc field)
q.open;
q.append;
q.fieldbynaname('name').asstring := 'xx';
q.post;
I have q.keyfield := 'id';
I get access violation in TDaDatasetUpdater.GetIdentityfieldValue
(my version of unidac is 7.4.12)

Code: Select all

|7FFFFFFE|03     |00000000|0090F58F|uqedit.exe    |0050F58F|DBAccess.pas                          |DBAccess      |TDADataSetUpdater|GetIdentityFieldValue                    |                                         |
|00000060|03     |0019F160|0090F484|uqedit.exe    |0050F484|DBAccess.pas                          |DBAccess      |TDADataSetUpdater|SetIdentityFieldValue                    |                                         |
|00000060|03     |0019F194|009136C4|uqedit.exe    |005136C4|DBAccess.pas                          |DBAccess      |TDADataSetUpdater|PerformAppend                            |                                         |
|00000020|04     |0019F1B0|00830776|uqedit.exe    |00430776|Vcl.DbGrids.pas                       |Vcl.DBGrids   |TCustomDBGrid    |RecordChanged                            |3937[18]                                 |
|00000060|03     |0019F1C4|0086A81E|uqedit.exe    |0046A81E|MemDS.pas                             |MemDS         |TDataSetUpdater  |DoPerformAppend                          |                                         |
|00000020|03     |0019F1E0|00865ABF|uqedit.exe    |00465ABF|MemDS.pas                             |MemDS         |TMemDataSet      |DoPerformAppend                          |                                         |
|00000020|03     |0019F1E4|00850039|uqedit.exe    |00450039|MemData.pas                           |MemData       |TData            |InternalAppend                           |                                         |
|00000020|03     |0019F1EC|00856232|uqedit.exe    |00456232|MemData.pas                           |MemData       |TMemData         |InsertRecord                             |                                         |
|00000020|03     |0019F1F8|0086517F|uqedit.exe    |0046517F|MemDS.pas                             |MemDS         |TMemDataSet      |InternalPost                             |                                         |
|00000020|03     |0019F210|009080FD|uqedit.exe    |005080FD|DBAccess.pas                          |DBAccess      |TCustomDADataSet |InternalPost                             |                                         |
|00000030|04     |0019F330|00803195|uqedit.exe    |00403195|Data.DB.pas                           |Data.DB       |TDataSet         |CheckOperation                           |14599[5]                                 |
|00000020|04     |0019F338|008031A4|uqedit.exe    |004031A4|Data.DB.pas                           |Data.DB       |TDataSet         |CheckOperation                           |14600[6]                                 |
|00000030|04     |0019F358|00802C88|uqedit.exe    |00402C88|Data.DB.pas                           |Data.DB       |TDataSet         |Post                                     |14450[7]                                 |
|00000020|03     |0019F374|00865242|uqedit.exe    |00465242|MemDS.pas                             |MemDS         |TMemDataSet      |Post                                     |                                         |

mika

Hi,
I have been using with ODBC without problems

Code: Select all

function CreateConnection(const  ADataBaseName: string);
begin
   Result := TUniConnection.Create( nil );
    Result.LoginPrompt := false;
    Result.ProviderName := 'ODBC';
    Result.Server := ADataBaseName;
    Result.Pooling := true;
    Result.Connect;
end;

I didn't see connect in your code..

Devart Forums

Search found 27 matches

Re: stopping a long running query

stopping a long running query

Re: Guid issue with Actian ZEN

Guid issue with Actian ZEN

Re: Wrong length of inserted data in batch operations

Wrong length of inserted data in batch operations

Data type of empty string has changed

Re: Getting Autoinc value after insert with ODBC

Re: Getting Autoinc value after insert with ODBC

Re: Getting Autoinc value after insert with ODBC

Re: Getting Autoinc value after insert with ODBC

Re: The Classic Install/Upgrade Errors (entry point, etc)

Re: The Classic Install/Upgrade Errors (entry point, etc)

Getting Autoinc value after insert with ODBC

Re: Connection pooling setup