Complex sql acces (Ax 3.0)

[BZN]

As for now I have created a class that search "up" and "down" in
requirements, then deciding if all items are in place for ie. a production
under certain conditions. if so, the class return TRUE.

It takes a long time, as I have to use ReqPO, RegTrans and ReqTransCov
And I have to do it 6-7.000 times - it takes about 20 minutes!

My question is. Can I implement a similar "class" in sql, so sql is doing
the "work"? Then maybe minimizing timeconsumption!
If so, where can I do it?

Thx in advance

[William K]

You may execute SQL statements with using the CCADO* and DSO* classes. The
command can be both a Transact-SQL statement, or you are allowed to call
stored procedures with parameters. Here is an easy example, which works both
in AX 3.0 and 4.0:

static void ExecSQLStatement(Args _args)
{
#CCADO

CCADOConnection connection;
CCADOCommand command;
CCADORecordSet recordSet;
CCADOFields fields;

str provider = "yourdatabaseservername";
str database = "youraxaptadatabasename";
str connectionString = strfmt("Provider=sqloledb;Data
Source=%1;Initial Catalog=%2;Trusted_Connection=yes;", provider, database);
str statement = "SELECT * FROM SYSUSERINFO";
;

connection = new CCADOConnection();
connection.open(connectionString, #adConnectUnspecified);

command = new CCADOCommand();
command.activeConnection(connection);
command.commandText(statement);
recordSet = command.execute();

while (!recordSet.EOF())
{
fields = recordSet.fields();
info(strfmt("%1", fields.itemName("id").value()));
recordSet.moveNext();
}

connection.close();
connection = null;
}

Please note that with trusted connection, AX tries to use your user account
to log in to the SQL server, and you must have read access to the
database/table. You may construct your own connection strings and add
userid/password for example, check the webpage
http://www.connectionstrings.com/

Also do not forget that if you are using tables saved per company, you have
to provide the "WHERE TABLE.DATAAREAID = 'dat'" clause, providing that which
company accounts you would like to use.

[Robert B]

William,
This is the most information I have found in one place on the use of
CCADO. There is just one more issue that I need to address and that is how
to call a stored procedure with an input parameter and an output parameter.
Do you know where I can find a command reference on the CCADO class? One
that is more than TBD?

Thanks

Robert

Post by William K
You may execute SQL statements with using the CCADO* and DSO* classes. The
command can be both a Transact-SQL statement, or you are allowed to call
stored procedures with parameters. Here is an easy example, which works both
static void ExecSQLStatement(Args _args)
{
#CCADO
CCADOConnection connection;
CCADOCommand command;
CCADORecordSet recordSet;
CCADOFields fields;
str provider = "yourdatabaseservername";
str database = "youraxaptadatabasename";
str connectionString = strfmt("Provider=sqloledb;Data
Source=%1;Initial Catalog=%2;Trusted_Connection=yes;", provider, database);
str statement = "SELECT * FROM SYSUSERINFO";
;
connection = new CCADOConnection();
connection.open(connectionString, #adConnectUnspecified);
command = new CCADOCommand();
command.activeConnection(connection);
command.commandText(statement);
recordSet = command.execute();
while (!recordSet.EOF())
{
fields = recordSet.fields();
info(strfmt("%1", fields.itemName("id").value()));
recordSet.moveNext();
}
connection.close();
connection = null;
}
Please note that with trusted connection, AX tries to use your user account
to log in to the SQL server, and you must have read access to the
database/table. You may construct your own connection strings and add
userid/password for example, check the webpage
http://www.connectionstrings.com/
Also do not forget that if you are using tables saved per company, you have
to provide the "WHERE TABLE.DATAAREAID = 'dat'" clause, providing that which
company accounts you would like to use.

[unknown]

Sadly there are no references out there regarding this topic, I have used
MSDN on how the ADO works, and I extended the CCADO classes with the methods
they have described.

I have created a simple stored procedure on my DYNAX09_DEV database to
return a specific sales order name by adding a salesid parameter:

USE [DYNAX09_DEV]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetSalesName]
@SalesId nvarchar(20)
AS
BEGIN
SELECT SalesName
FROM dbo.SalesTable
WHERE SalesId = @SalesId;
END
GO

Then you just call the stored procedure as a simple Transact-SQL command,
replacing the statement to look up sales order number 00017_036:
static void ExecSQLStatement(Args _args)
{
#CCADO

CCADOConnection connection;
CCADOCommand command;
CCADORecordSet recordSet;
CCADOFields fields;

str provider = "mycomputer";
str database = "DYNAX09_DEV";
str connectionString = strfmt("Provider=sqloledb;Data
Source=%1;Initial Catalog=%2;Trusted_Connection=yes;", provider, database);
str statement = "EXEC GetSalesName '00017_036'";
;

connection = new CCADOConnection();
connection.open(connectionString, #adConnectUnspecified);

command = new CCADOCommand();
command.activeConnection(connection);
command.commandText(statement);
recordSet = command.execute();

while (!recordSet.EOF())
{
fields = recordSet.fields();
info(strfmt("%1", fields.itemIdx(0).value()));
recordSet.moveNext();
}

connection.close();
connection = null;
}

And sorry if my answer answers too late, I was on holiday ;-]

Regards,
William

Post by Robert B
William,
This is the most information I have found in one place on the use of
CCADO. There is just one more issue that I need to address and that is how
to call a stored procedure with an input parameter and an output parameter.
Do you know where I can find a command reference on the CCADO class? One
that is more than TBD?
Thanks
Robert

[obedulla ismail]

HI William,

I am having few issues on the below.It will be great help if you could throw some light on this.

I am using AX CCADO class to call a SP of SQL.

The SP in SQL uses cursors and process 1 lakh records.

The roblem i am facing here is when i make a call to this SP from AX, the SP is processing only few records some times 10,000 records sometime 4,000 and some time all the records as processed.All this is happening randomly.

Is there a way where i can make sure that when i make call to the SP from AX using CCADO all the records are processed and AX waits till the SP processes all records.

Please suggest.

Reagards,
Ismail.

Post by BZN
As for now I have created a class that search "up" and "down" in
requirements, then deciding if all items are in place for ie. a production
under certain conditions. if so, the class return TRUE.
It takes a long time, as I have to use ReqPO, RegTrans and ReqTransCov
And I have to do it 6-7.000 times - it takes about 20 minutes!
My question is. Can I implement a similar "class" in sql, so sql is doing
the "work"? Then maybe minimizing timeconsumption!
If so, where can I do it?
Thx in advance

Post by William K
You may execute SQL statements with using the CCADO* and DSO* classes. The
command can be both a Transact-SQL statement, or you are allowed to call
stored procedures with parameters. Here is an easy example, which works both
static void ExecSQLStatement(Args _args)
{
#CCADO
CCADOConnection connection;
CCADOCommand command;
CCADORecordSet recordSet;
CCADOFields fields;
str provider = "yourdatabaseservername";
str database = "youraxaptadatabasename";
str connectionString = strfmt("Provider=sqloledb;Data
Source=%1;Initial Catalog=%2;Trusted_Connection=yes;", provider, database);
str statement = "SELECT * FROM SYSUSERINFO";
;
connection = new CCADOConnection();
connection.open(connectionString, #adConnectUnspecified);
command = new CCADOCommand();
command.activeConnection(connection);
command.commandText(statement);
recordSet = command.execute();
while (!recordSet.EOF())
{
fields = recordSet.fields();
info(strfmt("%1", fields.itemName("id").value()));
recordSet.moveNext();
}
connection.close();
connection = null;
}
Please note that with trusted connection, AX tries to use your user account
to log in to the SQL server, and you must have read access to the
database/table. You may construct your own connection strings and add
userid/password for example, check the webpage
http://www.connectionstrings.com/
Also do not forget that if you are using tables saved per company, you have
to provide the "WHERE TABLE.DATAAREAID = 'dat'" clause, providing that which
company accounts you would like to use.

Post by Robert B
William,
This is the most information I have found in one place on the use of
CCADO. There is just one more issue that I need to address and that is how
to call a stored procedure with an input parameter and an output parameter.
Do you know where I can find a command reference on the CCADO class? One
that is more than TBD?
Thanks
Robert

Post by unknown
Sadly there are no references out there regarding this topic, I have used
MSDN on how the ADO works, and I extended the CCADO classes with the methods
they have described.
I have created a simple stored procedure on my DYNAX09_DEV database to
USE [DYNAX09_DEV]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetSalesName]
@SalesId nvarchar(20)
AS
BEGIN
SELECT SalesName
FROM dbo.SalesTable
END
GO
Then you just call the stored procedure as a simple Transact-SQL command,
static void ExecSQLStatement(Args _args)
{
#CCADO
CCADOConnection connection;
CCADOCommand command;
CCADORecordSet recordSet;
CCADOFields fields;
str provider = "mycomputer";
str database = "DYNAX09_DEV";
str connectionString = strfmt("Provider=sqloledb;Data
Source=%1;Initial Catalog=%2;Trusted_Connection=yes;", provider, database);
str statement = "EXEC GetSalesName '00017_036'";
;
connection = new CCADOConnection();
connection.open(connectionString, #adConnectUnspecified);
command = new CCADOCommand();
command.activeConnection(connection);
command.commandText(statement);
recordSet = command.execute();
while (!recordSet.EOF())
{
fields = recordSet.fields();
info(strfmt("%1", fields.itemIdx(0).value()));
recordSet.moveNext();
}
connection.close();
connection = null;
}
And sorry if my answer answers too late, I was on holiday ;-]
Regards,
William
Submitted via EggHeadCafe
Microsoft Silverlight For Beginners
http://www.eggheadcafe.com/training-topic-area/Microsoft-Silverlight/10/Silverlight.aspx