Hi is there a example somewhere who show how to use text input file and database has input output ?

What I trying to do is simple read a text file print them then
connect to a database and search if the document exist if yes update the record if no add the record

then continue with the original textfile

Alain,

I don't have any example of this... But one this is sure is that you'll need to make a branch. By making a branch, you can perform actions, and once they are done, it will go back to the main branch, with the original data before it entered the branch.

So you'll want to branch out. Then, you'll need to perform your database lookup. There is unfortunately no native functions to do this, so you may have to resort to either the custom "Dynamic SQL" part of the PlanetPress Database Connector, or use a script in a Run Script action to do the database query and update it.

Once this is finished, then it will return to the main branch, and continue with the original data.

Regards,
Raphaël Lalonde Lefebvre

I did it once, but dont remember how I did it

I remember it was something complicated like change emulation use mdb to create the process we stoped using this and called a perl script.

Now I would like to use native Planetpress database plugin to see if its more performant.



In the course I followed in 2008 there was an exercices where the input was text files and we used a SQL database to get client email but again i dont remember anything.

Alain,

If you're just looking to extract a client's email, it's fairly simple. You can add a branch, where you'll have a PlanetPress Database connector. You'll have to use the "Dynamic SQL" option. Use ODBC to connect to your database, and then, you can type your SQL. The Dynamic SQL allows you to insert variable data selections in the SQL, so you could use something like this:

SELECT * FROM Table1 WHERE CUSTNO = @(1,1,1,1,20,KeepCase,NoTrim)

This is a simple example, but basically, this would extract a customer ID from the data, and find the matching record in a database.

It will put it into a "PlanetPress Database" format, so you'll need to switch the emulation to Database. Then you could store the client's email into a variable, using the Set Job Info and Variable, and set it to this: field(1,0,0,'EMAIL',KeepCase,NoTrim)

And then you'd have the email stored. It will then finish the branch, and go back to the main branch with the original data file.


Doing what you want to do in your original post will be similar, although you won't need to store any data in any variables. You will, however, need a more complex SQL code in order to perform conditional INSERTS and UPDATES. I also recommend that you uncheck the "Expect record set", so that it doesn't expect a record set, since you don't need to actually extract anything.

This may be a lot of information. If you're not confortable, feel free to open a technical support issue for further assistance. (do be aware, however, that we cannot provide you with a SQL syntax for this, as this would be considered custom code, which is out of scope. We will be able to point you in the right direction, however...)

Regards,
Raphaël Lalonde Lefebvre

Thanks Raphael
I finaly find it...
I forgoten the way to get variable from the database so this is why I only saw data from the input file

So its simple
1 connect to the database and select the record (via dynamic sql)

2 use the setjobinfo and set %{custfound} to field(1,0,0,'clientnumber',KeepCase,NoTrim)

3 use a text compare : if %{custfound} equal '' then connect to the database and do an insert otherwise connect to the database and do an update