0

Working in the query-box of phpMyAdmin I want to write an outfile 'protokoll' for each table in a MySQL 5.5 database. As I have many databases that contain the same tables and are different by name only, I want the filename of the outfile to look like /tmp/dbname_protokoll_tablename_.csv

This works:

SELECT DATABASE() into @client;
SET @dir = '/tmp/';
SET @table = 'Adressen';
SET @stmt = CONCAT( 'SELECT * from ', @table, ' WHERE MarkDel=1 into outfile ''', @dir, @client , '_Protokoll_', @table , '.csv'' CHARACTER SET utf8 FIELDS TERMINATED BY '','' OPTIONALLY ENCLOSED BY ''"'' LINES TERMINATED BY ''\r\n'' ');
PREPARE DoExport from @stmt;
EXECUTE DoExport;
DEALLOCATE PREPARE DoExport;

From what I read about prepare, I should be able to use a '?' inside the statement, like

SET @stmt = CONCAT( 'SELECT * from ? WHERE MarkDel=1 into outfile ...

and then execute this with a list of arguments like

EXECUTE DoExport USING 'Adressen', 'Familien', 'Kinder';

but I can't get this to work, all I receive is an unspecific syntax error. How do I have to rewrite this?

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Nicolaus Busch
  • 1
  • 1
  • 2
    You can´t use prepared statements placeholders for table or column names, only for values – nacho Feb 15 '21 at 17:43
  • @nacho ... for values and as LIMIT parameters. – Akina Feb 15 '21 at 17:49
  • Damn, that's the answer I feared. Is there another way to acchieve this? – Nicolaus Busch Feb 15 '21 at 17:50
  • 2
    @Akina limit parameters are also values. – Mark Rotteveel Feb 15 '21 at 18:55
  • achieve *what*? – Your Common Sense Feb 15 '21 at 18:56
  • Does this answer your question? [Can I parameterize the table name in a prepared statement?](https://stackoverflow.com/questions/11312737/can-i-parameterize-the-table-name-in-a-prepared-statement) – Mark Rotteveel Feb 15 '21 at 18:58
  • @MarkRotteveel https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1f684f00775da068161f4aabd313101b – Akina Feb 15 '21 at 19:00
  • 2
    @Akina Yes, what is your point? Nacho said parameters can only be used for values, you then replied "[..] and as LIMIT parameters". Given limit parameters **are** values, that distinction doesn't make much sense, hence my reply. – Mark Rotteveel Feb 15 '21 at 19:03
  • @MarkRotteveel In the query, not in prepared statement, LIMIT parameters are LITERALS (like database/table/column names), not values. Like database/table/column names, LIMIT parameters may be parametrized via prepared statement only. – Akina Feb 15 '21 at 19:05
  • @Akina Prepared statements are the only place where you can have parameters – Mark Rotteveel Feb 15 '21 at 19:07
  • @MarkRotteveel Values allows expressions. Literal values are treated as-is. – Akina Feb 15 '21 at 19:08
  • 2
    @Akina database/table/column names are called identifiers not literals. and limit parameters are definitely not identifiers, but as you rightfully called them, [literals](https://dev.mysql.com/doc/refman/5.7/en/literals.html), just like other strings and numbers – Your Common Sense Feb 15 '21 at 19:09
  • @Akina Database column names, etc are not literals, they are identifiers. Literals are values, just like parameters are (placeholders for) values. – Mark Rotteveel Feb 15 '21 at 19:11

0 Answers0