gtDBInsertMultiple.m

function gtDBInsertMultiple(table, varargin)
% gtDBInsertMultiple(table, varargin)
%
% to insert many rows at once do:
% gtDBInsertMultiple(...
%  tablename,'colA',[valA1 valA2 valA3],'colB',[valB1 valB2 valB3])

% modified 10/12/2007 to use mantissa/exponent (%g) for double precision
% modified 13/12/2007 to use mantissa/exponent (%0.20g) for double
%   precision /Peter/
%
% Modified by Nicola Vigano', 2012, ID11 @ ESRF vigano@esrf.eu
%   Added more error detection, made it 20x faster and much simpler
%
% Modified by Andy King - modified error check line 29 to allow single data
% values to be inserted without error.


    % Check the input and reorganize it, to be more efficient!
    if (length(varargin) < 2)
        error('DB:gtDBInsertMultiple:wrong_argument', 'No data passed!');
    end
    if (mod(length(varargin), 2) == 1)
        error('DB:gtDBInsertMultiple:wrong_argument', 'A column has no data!');
    end

    columns = varargin(2:2:end);
    numColumns = length(columns);

    for ii = 1:numColumns
        if (length(find(size(columns{ii}) > 1)) > 1)
            error('DB:gtDBInsertMultiple:wrong_argument', ...
                  'Columns need to be one dimentional!');
        end
        if (~isempty(find(size(columns{ii}) == 0, 1)))
            error('DB:gtDBInsertMultiple:wrong_argument', ...
                  'Empty columns found!');
        end
        % Ensure data in the single columns is internally represented in a
        % single row
        columns{ii} = reshape(columns{ii}, [1 numel(columns{ii})]);
    end

    numItems = length(columns{1});
    for ii = 2:numColumns
        if (length(columns{ii}) ~= numItems)
            error('DB:gtDBInsertMultiple:wrong_argument', ...
                  'Columns need to have the same length!');
        end
    end

    % Assemble table to print (colums -> rows, rows -> colums)
    rawColumns = cat(1, columns{:});

    % do a maximum of 512 rows at a time
    maxItems = 512;

    % Titles of the columns
    titles = varargin(1:2:end);
    titles(2, :) = {','};

    % Beginning of a query
    beginning = ['INSERT INTO ' table ' (' titles{1:end-1} ') VALUES '];

    % sprintf pattern per tuple
    tuplePattern(1, 1:numColumns) = {'%0.20g'};
    tuplePattern(2, 1:numColumns) = {','};
    tuplePattern = ['(' tuplePattern{1:end-1} '),'];

    for itemndx = 1:maxItems:numItems
        % Prepare the indexes of the table to print
        first = itemndx;
        if ((first + maxItems) > numItems)
            last = numItems;
        else
            last = first + maxItems - 1;
        end

        % sprintf does the cicle by itself -> 10x in speed, instead of explicit
        % loop and copies of the temporary strings
        cmd = [beginning sprintf(tuplePattern, rawColumns(:, first:last))];
        cmd(end) = ' ';

        mym(cmd)
    end
end