Database entries

congomonster · Post by **congomonster** » Wed Aug 27, 2014 8:30 pm

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

dkelly · Post by **dkelly** » Wed Aug 27, 2014 9:19 pm

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

SELECT * FROM table

INTO OUTFILE

[Job.Name];

congomonster · Post by **congomonster** » Thu Aug 28, 2014 9:46 am

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?

gabrielp · Post by **gabrielp** » Thu Aug 28, 2014 5:27 pm

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.

congomonster · Post by **congomonster** » Thu Aug 28, 2014 5:32 pm

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

gabrielp · Post by **gabrielp** » Thu Aug 28, 2014 6:39 pm

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?

congomonster · Post by **congomonster** » Thu Aug 28, 2014 6:55 pm

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?

dkelly · Post by **dkelly** » Thu Aug 28, 2014 7:32 pm

[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.

congomonster · Post by **congomonster** » Thu Aug 28, 2014 7:44 pm

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.

dkelly · Post by **dkelly** » Thu Aug 28, 2014 8:43 pm

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

congomonster · Post by **congomonster** » Thu Aug 28, 2014 9:05 pm

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

dkelly · Post by **dkelly** » Fri Aug 29, 2014 2:08 am

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.

freddyp · Post by **freddyp** » Mon Sep 01, 2014 1:50 pm

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