Verfasst am: 01.11.2011, 18:13
Titel: Java exception verursacht durch fastinsert.m
Hallo,
ich benutze das file fastinsert.m aus der database tollbox.
Hierbei tritt dieser Fehler auf:
??? Error using ==> ParamDAO.ParamDAO>ParamDAO.setParam at 154
Java exception occurred:
sun.jdbc.odbc.JdbcOdbcBatchUpdateException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in
INSERT INTO statement.
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.emulateExecuteBatch(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeBatchUpdate(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.executeBatch(Unknown Source)
Error in ==> WOverview>pb_details_ov_Callback at 110
ParamDAO.setParam(selectedParameter.Parameter_ID);
Error in ==> gui_mainfcn at 96
feval(varargin{:});
Error in ==> WOverview at 16
gui_mainfcn(gui_State, varargin{:});
Error in ==> @(hObject,eventdata)WOverview('pb_details_ov_Callback',hObject,eventdata,guidata(hObject))
??? Error using ==> waitfor
Error while evaluating uicontrol Callback
Code:
function fastinsert(connect,tableName,fieldNames,data)
%FASTINSERT Export MATLAB cell array data into database table.
% FASTINSERT(CONNECT,TABLENAME,FIELDNAMES,DATA). % CONNECT is a database connection handle structure, FIELDNAMES % is a string array of database column names, TABLENAME is the % database table, DATA is a MATLAB cell array.
%
%
% Example:
%
%
% The following FASTINSERT command inserts the contents of % the cell array in to the database table yearlySales % for the columns defined in the cell array colNames.
%
% % fastinsert(conn,'yearlySales',colNames,monthlyTotals);
%
% where
%
% The cell array colNames contains the value:
%
% colNames = {'salesTotal'};
%
% monthlyTotals is a cell array containing the data to be % inserted into the database table yearlySales % % fastinsert(conn,'yearlySales',colNames,monthlyTotals);
%
%
% See also INSERT, UPDATE, MSSQLSERVERBULKINSERT, MYSQLBULKINSERT, ORACLEBULKINSERT.
% Copyright 1984-2005 The MathWorks, Inc. % $Revision: 1.1.6.18 $ $Date: 2009/12/22 18:51:21 $%
case 'struct'
sflds = fieldnames(data);
fchk = setxor(sflds,fieldNames);
if ~isempty(fchk) error('database:fastinsert:fieldMismatch','Structure fields and insert fields do not match.') end
numberOfRows = size(data.(sflds{1}),1);
cols = length(sflds);
%Getclasstype of each field to be used later
numberOfFields = length(sflds);
fieldTypes = cell(numberOfFields,1);
for i = 1:numberOfFields
fieldTypes{i} = class(data.(sflds{i}));
end
case 'dataset'
dSummary = summary(data);
sflds = {dSummary.Variables.Name};
fchk = setxor(sflds,fieldNames);
if ~isempty(fchk) error('database:database:writeMismatch','Dataset variable names and insert fields do not match.') end
numberOfRows = size(data.(sflds{1}),1);
cols = length(sflds);
%Getclasstype of each field to be used later
numberOfFields = cols;
fieldTypes = cell(numberOfFields,1);
for i = 1:numberOfFields
fieldTypes{i} = class(data.(sflds{i}));
end
otherwise error('database:fastinsert:inputDataError','Input data must be a cell array, matrix, or structure')
% Case 1 all fields are being written to in the target database table.
insertField = '';
tmpq = '';
% Create the field name string for the INSERT statement .. this defines the % fields in the database table that will be receive data. % Also build variable ?'s string for later set* commands
for i=1:cols,
if( i == cols),
insertField = [ insertField fieldNames{i}]; %#ok
tmpq = [tmpq '?)']; %#ok
else
insertField = [ insertField fieldNames{i} ',' ]; %#ok
tmpq = [tmpq '?,']; %#ok
end end
% Create the head of the SQL statement
startOfString = [ 'INSERT INTO ' tableName '(' insertField ')' 'VALUES (' ];
% Get NULL string and number preferences
prefs = setdbprefs({'NullStringWrite','NullNumberWrite'});
nsw = prefs.NullStringWrite;
nnw = str2num(prefs.NullNumberWrite); %#ok
%Get Database name
dMetaData = dmd(connect);
sDbName = get(dMetaData,'DatabaseProductName');
%Determine type values by fetching one row of data from table switch sDbName %Query only a single row where possible for speed
case{'MySQL'}
e = exec(connect,['select ' insertField ' from ' tableName ' LIMIT 0']);
case{'Microsoft SQL Server','ACCESS'}
e = exec(connect,['select TOP 1' insertField ' from ' tableName]);
case{'Oracle'}
e = exec(connect,['select ' insertField ' from ' tableName ' where ROWNUM <= 1']);
otherwise
e = exec(connect,['select ' insertField ' from ' tableName]);
end if ~isempty(e.Message) error('database:database:insertError',e.Message) end
e = fetch(e,1);
if ~isempty(e.Message) error('database:database:insertError',e.Message) end
a = attr(e);
close(e)
Meiner Meinung nach ist das sql-Statment korrekt, die DB-Tabelle existiert mit diesen Attributen.
An anderen Stellen benutze ich fastinsert.m ebenfalls (diverse Kombinationen von DBs, Tabellen, Attributen, read/write) ohne Probleme.
Im Anhang befindet sich ein screenshot meines workspace.
Ich weiss nicht wo ich anfangen soll den Fahler zu suchen. Ich denke meine Übergabeparameter sind korrekt.
??? Error using ==> ParamDAO.ParamDAO>ParamDAO.setParam at 155
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
Error in ==> WOverview>pb_details_ov_Callback at 110
ParamDAO.setParam(selectedParameter.Parameter_ID);
Error in ==> gui_mainfcn at 96
feval(varargin{:});
Error in ==> WOverview at 16
gui_mainfcn(gui_State, varargin{:});
Error in ==> @(hObject,eventdata)WOverview('pb_details_ov_Callback',hObject,eventdata,guidata(hObject))
??? Error using ==> waitfor
Error while evaluating uicontrol Callback
insert ruft die Funktion cursor (toolbox) auf:
Code:
function curs = cursor(connect,sqlQuery,qTimeOut)
%CURSOR Cursor constructor.
% CURS = CURSOR(CONNECT,SQLQUERY,QTIMEOUT) returns a cursor object. % CONNECT is a database connection object, QUERY is a valid SQL query, % and QTIMEOUT is the query timeout value.
%
% This function is called by EXEC and never invoked directly from % the MATLAB command line.
%
% See also FETCH.
% Copyright 1984-2008 The MathWorks, Inc. % $Revision: 1.27.4.13 $ $Date: 2009/11/05 16:58:12 $%
%Create parent object for generic methods
dbobj = dbtbx;
% These fields will be invisible to the user
curs.Type = 'Database Cursor Object';
curs.ResultSet = 0 ;
curs.Cursor = 0 ;
curs.Statement = 0 ;
curs.Fetch = 0 ;
ifnargin == 0
curs.Message = 'Function requires connection handle and SQL statement.';
curs=class(curs,'cursor',dbobj);
return end
message = statementErrorMessage(curs.Cursor,curs.Statement);
try %Close statement object if it was created to free resources
close(curs.Statement);
catch exception %#ok
end
curs.Cursor = 0;
curs.Statement = 0;
curs.Message = message;
return;
try %Close statement object if it was created to free resources
close(curs.Statement);
catch exception %#ok
end
curs.Message = statementErrorMessage(curs.Cursor,curs.Statement);
end
% Delete, insert and update SQL operations. % Set the resultSet object element to be zero as these operations % do not return a result set.
curs.ResultSet = 0;
mein Vorschlag wäre, an der Stelle, wo es "knallt", den Debugger anhalten lassen, curs.Statement anzeigen lassen, das Statement kopieren und in Microsoft Access direkt als Abfrage eingeben. Vielleicht spuckt Access einen Fehler im Statement aus, der weiterhilft?
danke, das hat geholfen. In der Tabelle gibt es ein Attribut Namens Value. Dummerweise handelt es sich hierbei um ein reserviertes Wort der sql-Syntax. Nun habe ich das Attribut umbenannt und das insert Statement funktioniert einwandfrei.
Oh man, das hat verdammt viel Zeit gekostet. Die Aussagekraft der Fehlermeldungen, aller hierbei involvierten Pakete (Matlab, Java, Acces), ist wirklich unter aller Sau!!!
Was mich wundert: Im select Statement welches diese Tabelle ebenfalls ausliest, tauchte Value ebenfalls auf. Hier gab es keine Probleme.
Vielen Dank & Grüße,
Lloyd
Einstellungen und Berechtigungen
Du kannst Beiträge in dieses Forum schreiben. Du kannst auf Beiträge in diesem Forum antworten. Du kannst deine Beiträge in diesem Forum nicht bearbeiten. Du kannst deine Beiträge in diesem Forum nicht löschen. Du kannst an Umfragen in diesem Forum nicht mitmachen. Du kannst Dateien in diesem Forum posten Du kannst Dateien in diesem Forum herunterladen
MATLAB, Simulink, Stateflow, Handle Graphics, Real-Time Workshop, SimBiology, SimHydraulics, SimEvents, and xPC TargetBox are registered trademarks and The MathWorks, the L-shaped membrane logo, and Embedded MATLAB are trademarks of The MathWorks, Inc.
RSS