Devart Forums

tinof

That's the solution, thank you very much.

Now it works!

Tino

tinof

Hello,

i want to update an program written with SDAC 3.xxx to actual SDAC version.
In this program i use CRBatchMove for copying table data.
Some of the tables have an identity - column.
So i execute an "set identity_insert [tablename] on" bevore executing crbatchmove.
In the old version it works.
In the new version (6.2.

it fails because the crbatchmove automaticly does not include the identity column in the insert - code.

How can i tell crbatchmove, that i want ALL columns, even the identity column too?

Within the mappings - property i do assign the identity column to a source column.
But if i look the final code with SQL profiler i see, the identity column is missed.
So the server can't insert the data.

SQL Server 2008 R2 / Delphi XE2

Thanks for Help
Tino

tinof

* SOLVED *

we have to set

MSQuery.Options.ReturnParams:=true!

complete:

1. place an TMSQuery, generate the Scripts
2. set Options -> ReturnParams = true
3. edit TMSQuery.BeforeUpdateExecute:

Code: Select all

procedure Tdm_Projekt.queryBeforeUpdateExecute(
  Sender: TCustomMSDataSet; StatementTypes: TStatementTypes; Params: TMSParams);
begin
  If stInsert IN statementTypes then  // otherwise there is no param with this name
    Params.ParamByName('query_ID').ParamType := ptInputOutput;
end;

And you get an ID.

Regards
Tino

tinof

Hi,

i have the same problem with SDAC 6.1.6 / Delphi 2009 / MSSQL 2005.
The sample connects with the northwind - database.

If i set the query.sql property to

Code: Select all

 "SELECT * FROM categories"

and leave all properties at default i get the categoryID after an insert as expected.

If i now use the SQL - Query Builder from SDAC i get the i.e. the update statement i get

Code: Select all

INSERT INTO categories
  (CategoryName, Description, Picture)
VALUES
  (:CategoryName, :Description, :Picture)
SET :CategoryID = SCOPE_IDENTITY()

But i never get an CategoryID - value after insert / append now.

- Tfield.autogeneratevalue = arDefault / arNone /arAutoInc --> no affect
- TMSQuery.Options.QueryIdentity = true/false --> no affect
- TMSQuery.BeforeUpdateExecute :
Params.ParamByName('CategoryID').ParamType:=ptInputOutput;
--> no affect

If i delete the Update SQL Statement and leave the "SELECT * FROM .." - it will work.

If i set debug=on i see, that Params.ParamByName('Category_ID').ParamType:=ptInputOutput; influences the final code. But i dont get an ID back.

What's wrong with my code?

Thanks für any Help!
Tino

tinof

Perhaps it is tooo simple -

But do you have checked, if sql server authentication is enabled at this server (Management Studio -> select Server -> Settings -> Safety)?

Can you connect with any other tool (i.e. Management Studio) using server auth?

Just one idea (i have had the same problem just a couple of days before

)

tino

tinof

Thank you very much, that was the reason!

Please, tell in the history.html if you change default values for some properties.

Anyway, thank you for the quick help!

Regards
Tino

tinof

Hi,

sorry, but i'm here again.

I have updated from SDAC 4.70.0.48 to
SDAC 4.80.0.56

in Version 4.70.xxx TField.Origin returns the origin field name,
in 4.80.xxx it does not.

I have'nt made any other changes within my application, only the SDAC - update.

Is there a difference between theese 2 versions handling Tfield.Origin?
How can i fix it (the dataset is NOT read only)?

thank you, regards

Tino

tinof

Oh, sorry.

I'ts my own post and i did'nt remember. I have had the problem 2 year ago

Sorry, and thanks for response.

tinof

Hi,
i have a problem with Tfield.Origin - Property in a MSQuery-Komponent:

e.g.

Code: Select all

CREATE TABLE Table1( 
ID1 Integer NOT NULL,
Data nVarchar(50)
)

SELECT Table1.ID1, Table1.Data FROM Table1

MSQuery1.FieldByName('ID1').origin returns "Table1.ID1" - as i expected.

If i set MSQuery1.readonly = true

MSQuery1.FieldByName('ID1').origin returns only "."

Why does SDAC so ?

Thank's for help
Tino

edit: SDAC 4.70.0.48, Delphi 2009

tinof

Hi,

i have this problem, maybe i did'nt understand the FetchAll - Idea as well.

Situation:
A table i.g. Customers with up to 100.000 Records, additional up to 20 related tables in master-detail relation to this maintable.

Form[1]:
Based on the query SELECT * FROM Customers WHERE ID = [one id] it shows one record with all relations. First i open the query on base table, after that i open the details tables.
It works fine and quick.
All tables are FetchAll = True, editing data works well.

Form[2]:
Only a crDBGrid + Datasource + MSQuery with SELECT [some fields] FROM Customers.
MSQuery.Fetchall=false, MSQuery.Readonly=true,
MSQuery.Cursortype=ctDefaultResultSet (because of enabling local sorting)

My Idea is: first search with form[2] one record, than show detail data by calling form[1]. If i single user, it works fine.

But in Multi -User enviorment my Problem is:
The Form[2] (with the fetchall=false - query) locks records. Depending on sorting in the crdbgrid there are sometimes many, many locks at the DB - Tables. These locks prevent other users from changing data.

But i dont want any locking while searching with my Form[2].

How can i configure SDAC, that there are no locks when fetchall=false ?

Thanks
Tino

Edit:
Latest SDAC,
MSSQL Server 2000
Delphi 2006

tinof

Well, now we are just two!
A non-trial version would be fine.

Thanks
Tino

tinof

Hi again,

does the 4.50.0.39 Release just support D2009 ?

I've got the D2009 Partner CD yesterday, there is a DevArt SQL - Server - Trial Version. What are the limitations of this release ?

Sorry, but i have to switch to 2009 as early as possible.

Thanks
Tino

tinof

Hi,

i am not sure, but please try to specify the parameter types:

Code: Select all

...
Query.Prepared := True; 
Query.Params.ParamByName('FirstParam').datatype := ftInteger; 
Query.Params.ParamByName('FirstParam').AsInteger := 1; 
Query.Params.ParamByName('SecondParam').datatype := ftBoolean;
Query.Params.ParamByName('SecondParam').AsBoolean := False; 
Query.Open; // !!! HERE IS AN EXCEPTION (EMSError) 
...

tinof

Many thank's for testing.

My Demo - App did work now, my 'really' app unfortunally not. But I've found the solution for that :

I call locateEx on a table like this:

Code: Select all

EXEC sp_addtype N'ID', N'numeric(18,0)', N'null'
go

CREATE TABLE MyTable
(
Variant_ID ID,
Date SmallDateTime,
...
)

SDAC does not recognize the user column - type "ID".
If i change the column data type to numeric(18,0) instead of 'ID' (what means the same) it works !
Well, i'ts not neccesary for SDAC to 'parse' user defined types, i have to change my database.

But, sorry, now i've a second problem:

See my Example

Code: Select all

empID, Orderdate 
... 
3 , 30.04.1998 
4 , 08.07.1996 
.... 
4 , 20.12.1996 
4 , 03.01.1997 
...

If i locate [4,31.12.1996] i get [4, 03.01.1997] - perfect.

If i locate [3 , 01.05.1998] i expect [4 , 08.07.1996] because this is the next record 'greater than the specified values'. But the record pointer does'nt moves, there is no effect if i 'search' such keys.
Is this a bug or a feature

?

Thanks again for help

Tino

tinof

Hi,

i try to use TMSQuery.LocateEx in this way:

(SQL Server DAC 4.50.0.35, BDS (Delphi) 2007, connect to northwind - DB SQL-Server 2000)

Code: Select all

Var empID:Integer;
      date:TDateTime;

MSQuery1.SQL.Text:="SELECT * FROM Orders ORDER BY EmloyeeID,OrderDate";
MSQuery1.IndexFieldNames:="EmployeeID;OrderDate";
MSQuery1.Open;

empID:=4;
date:=StrToDate('01.01.1997');
MSQuery1.locateEx('EmployeeID;Orderdate',VarArrayOf([empID,date]),[lxNearest])

Code: Select all

Sample - Data in Orders:

empID, Orderdate
...
3 , 30.04.1998
4 , 08.07.1996
....
4 , 20.12.1996
4 , 03.01.1997
...

In documentation i read:

lxNearest
LocateEx moves the cursor to a specific record in a dataset or to the
first record in the dataset that is greater than the values specified in
the KeyValues parameter. For this option to work correctly the dataset should be sorted by the fields the search is performed in. If the dataset is not sorted, the function may return a line that is not connected with the search condition

I expect, that my locateEX() - statement positions at [4, 03.01.1997], but it sets the pointer to [4,08.07.1996] - the first record with employeeID = 4

It seems, the second field is unused for finding the record.

Is it a problem especially with datetime - columns or do i sth. wrong ?

Thanks
Tino

P.S. Demo - App is available if needed.

Devart Forums

Search found 39 matches

Re: CRBatchMove and Identity Columns

CRBatchMove and Identity Columns

TField.Origin

FetchAll=False and Locks - Problem

LocateEx and segmented keys