Enfocus Community

Hi,



i need some help with a mysql database. It is posibble to pick up the data and put it in the filename?



We have an odbc connector and it finds the tables in the database. I thought it would be possible if i did this:



SELECT * FROM table

INSERT INTO

[Job.Name];



I'm not good at sql. Can someone please explain what i could do?



thanks

congomonster wrote: It is posibble to pick up the data and put it in the filename?




SELECT * FROM table

INTO OUTFILE

[Job.Name];

Hi and thanks for your reply. But i must do something wrong. Switch says that there is an error in my syntax. The error has something to do with the filename. Any idea what is wrong?

congomonster wrote: Hi and thanks for your reply. But i must do something wrong. Switch says that there is an error in my syntax. The error has something to do with the filename. Any idea what is wrong?
It would help if you posted the error. In SwitchScripter you can right click on the message > Copy text.

That's the error:



v1.21,2A3QV,Leseansicht_Standard_721691_21_08_14__20_35_#_A5.pdf,SQL error: [MySQL][ODBC 5.2(w) Driver][mysqld-5.6.14]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Leseansicht_Standard_721691_21_08_14__20_35_#_A5.pdf' at line 3 QODBC3: Unable to execute statement

congomonster wrote: That's the error:



v1.21,2A3QV,Leseansicht_Standard_721691_21_08_14__20_35_#_A5.pdf,SQL error: [MySQL][ODBC 5.2(w) Driver][mysqld-5.6.14]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Leseansicht_Standard_721691_21_08_14__20_35_#_A5.pdf' at line 3 QODBC3: Unable to execute statement





Do you have the job name in quotes? Could you post what you're using for your SQL statement?

The jobname have no quotes. My SQL statement is what i posted before:



SELECT drucker FROM kim_oki_leseansicht

INTO OUTFILE

[Job.Name];


I searched outfile. In most examples there is a path behind the outfile. Like OUTFILE /order/my.xml Did i need that too?

[Job.Name] is the filename for the SQL output. Obviously it's not what you wanted to do since the error messages say it's a PDF.

The PDF-file is inserted in folder one. Then ist should go through the odbc database and in folder two it should have the database entries in the name.



The output PDF should look like this:



712346_A5_printer_28.08.2014.pdf



Where printer and the Date and Time are the entries from the database. I hope this explains better what i'm trying to do.

[Job.Name] is a read-only variable containing the current filename (or folder) of the job. It can not be used to rename files.

OK. But how can i get the entries? It is possible to write a xml file? or something else?

Run the SQL using the DB connect element, use XML Pickup to read the log data generated by the SQL query then use the Rename element using a Switch variable to access the database value.

Do not write to a file in the SQL statement. That will not work. Well, it will work, but Switch will not pick up that file.



Instead, do as Dwight suggests, but with one optimization: when you set the outgoing connection from "Database connect" to "Data with log" the information returned by the SQL statement will automatically be attached as a dataset that you can access through the variables pane. That keeps you from having to add an "XML pickup".



This being said, I still wonder about your SQL statement. With the above method you can leave out the "INTO OUTFILE" part of your SQL query. What is left, is:



SELECT drucker FROM kim_oki_leseansicht



However, this SQL query will return ALL values for 'drucker'. I do not think this is what you want. I guess you will need an additional restriction, e.g.



SELECT drucker FROM kim_oki_leseansicht WHERE jobnr='some_switch_variable'



I have no clue of course about the column name to use in the WHERE clause, nor of the Switch variable that you can use for the value.



Freddy