7 replies to this topic

[Balaram]

Hello everybody!!!

I need to extract incremental data from native PRO IV .For example , i have 100 records , i extract them and send it across as a CSV file .Now after some time i have 10 records being added to that file and 10 records being updated , Now i would have 20 new records(added and updated).How do i go about accessing only those updated and added record .Is there any way out to access only those incremental data and send it across as a CSV file in native PRO IV??

[Rob Donovan]

Hi,

No, there is no automatic way of detecting those records.

You would have to add a flag to the file to say if it needs exporting or not (or a date/ time field), and update that if you change the record or add a record.

Then the extract could detemind which records to export....

Rob.

[Joseph Bove]

Balaram,

If Deletes are a possibility, then you'll have to code specifically for that as well.

Depending on the scope of the project, you could look at moving the table to SQL and using replication as a solution. However, this would be a significant learning curve...

Regards,

Joseph

[Balaram]

Balaram,

If Deletes are a possibility, then you'll have to code specifically for that as well.

Depending on the scope of the project, you could look at moving the table to SQL and using replication as a solution. However, this would be a significant learning curve...

Regards,

Joseph

Thanks Rob and Joseph,

Joseph i am ready to try my hand out at moving the table to SQL ,But does it really work ??if it does then how should i go about writing the code .

P.S :i am working on Native PRO IV and not SL or VIP.

[Joseph Bove]

Balaram,

Let me start backwards...

Does it work?

Yes.

Moving the table to SQL. I'll briefly touch on the overview of this. You should be able to find more details by searching through the archive.

We've only setup replication with SQL server, so I'll give you SQL Server specific info on replication. Others may have had success with PostgreSQL or Oracle.

To move the table to SQL, you'll want to copy the definition and then set the external file type to SQLSERVE. In the table definition itself, set the alternate column names (Expand mode in @F) to whatever you would like them to be called in SQL Server. Avoid using .'s in your alternate column names as these are restricted.

Create a SQL database. Create your table within the SQL database.

Set up your application to connect to your SQL database. (Basically, add the values below that are appropriate for what you are looking to do.)

pro4.ini

SQLSERVE_SELECT_FOR_UPDATE=TRUE
SQL_NOSIG=Y
LOCKED_ROWS_RETURNED=Y
[Database - SQLDEFAULT]
PRODB_CHARSET=7
FILETYPE=SQLSERVE
CONNECTION=username/password
SQL_DBNAME=databasename

In your ODBC Data Source Administrator, under System DSN, create an entry for your databasename.

Now, write an update to read from your old (ProISAM) file def and write to your new (SQLSERVE) file def. I'd recommend setting an iteration count of 1,000.

If all has gone well, your data is now in a SQL table. (And no, I don't expect that everything has gone without problem as simply as stated above!)

In SQL Server Manager, you can now create a merge replication publication of just that table. Suggestion: allow anonymous subscriptions.

Then, whenever you want to update your table on the remote environment, connect to the server and execute your subscription.

If you've never dealt with SQL tables from ProIV, I would budget about 10 - 15 hours to overcome that hurdle.
If you've never dealt with merge replication, I would budget 15 - 20 hours to overcome that hurdle.

Good luck!

Joseph

[Ajaym]

How about writing a trigger, once converted to SQL Server

[Paul Blew]

Hi Ajaym,

Here's a template for a SQL Server trigger. The SET NOCOUNT lines are essential - without them the PRO-IV session will lock up the second time the trigger fires due to a bug in ODBC.

[codebox]
IF EXISTS (SELECT name FROM sysobjects WHERE name = '<TRIGGER_NAME>' AND type = 'TR')
DROP TRIGGER [<TRIGGER_NAME>]
GO

CREATE TRIGGER [<TRIGGER_NAME>] ON [dbo].[<TABLE_NAME>]
FOR INSERT, UPDATE, DELETE AS
DECLARE
@NEW_VAR1 VARCHAR(10),
@OLD_VAR2 VARCHAR(10)
BEGIN
SET NOCOUNT ON

SELECT @NEW_VAR1 = [<COLUMN_NAME>]
FROM [inserted]

SELECT @OLD_VAR1 = [<COLUMN_NAME>]
FROM [deleted]

-- your trigger processing goes here

SET NOCOUNT OFF
END
GO
[/codebox]


Hope this helps

Paul

[Joseph Bove]

Balaram,

The trigger solution looks much simpler than replication.

If your remote database will always be on line and accessible, I would go with the trigger. If your remote database is not consistently on the network, then I don't know if the triggers are a solution or not.

Perhaps Paul or Ajaym could comment.

Regards,

Joseph