Recently there has been a posting in the Advantage newsgroups (http://devzone.advantagedatabase.com/forum/questions/2339/any-similar-command-with-mysql-load-command-in-ads) that inspired me to write this blog post. A user asked on how he could load from a text file.
Advantage Extended Procedures
ADS doesn’t have a concept on loading text files (except Advanage Data Architect, the database management utility, which does it client side). But ADS allows to write Extended Procedures, that reside – above other options – in DLLs. An AEP (that’s the term used) is easy to write in Delphi: A project exists in the object gallery (file | new | other | Delphi projects) that contains the stub. I’ll not explain the concept of AEPs here – that’s documented in detail in the Advantage help files – but start with an example.
Create a new project from the gallery, save it to any folder on your computer and add your function. I usually copy the example function (myProcedure) and modify function name and content.
{* Sample exported Advantage Extended Procedure. If you change the name of this
* procedure remember to also change the name in the exports list at the bottom
* of this file. *}
function MyProcedure( ulConnectionID: UNSIGNED32;
hConnection: ADSHANDLE;
pulNumRowsAffected: PUNSIGNED32 ): UNSIGNED32;
{$IFDEF WIN32}stdcall;{$ENDIF}{$IFDEF LINUX}cdecl;{$ENDIF} // Do not change prototype
var
DM1 : TDM1;
begin
Result := AE_SUCCESS;
{* Get this connection's data module from the session manager. *}
DM1 := TDM1( AEPSessionMgr.GetDM( ulConnectionID ) );
// IMPORTANT NOTE: IF you create any table or query components at runtime,
// always use the TAdsTable.AdsConnection or TAdsQuery.AdsConnection
// property when pointing tables and queries at your connection. Using the
// TAdsTable.Databasename or TAdsQuery.Databasename property is not thread safe,
// and will cause problems in triggers and AEPs.
try
with DM1 do
begin
{* The code commented out below shows how input parameters may be
* read. *}
// tblInput.open;
// strManufacturer := tblInput.FieldByName( 'mfr' ).Value;
// strProduct := tblInput.FieldByName( 'product' ).Value;
// tblInput.close;
{* Place to business logic of your procedure here. *}
{* Finally return any output parameters. *}
// tblOutput.open;
// tblOutput.append;
// tblOutput.FieldByName( 'total_price' ).Value := 68.99;
// tblOutput.post;
// tblOutput.close;
end; {* with DM1 *}
except
on E : EADSDatabaseError do
{* ADS-specific error, use ACE error code *}
DM1.DataConn.Execute( 'INSERT INTO __error VALUES ( ' + IntToStr( E.ACEErrorCode ) + ', ' + QuotedStr( E.Message ) + ' )' );
on E : Exception do
{* other error *}
DM1.DataConn.Execute( 'INSERT INTO __error VALUES ( 1, ' + QuotedStr( E.Message ) + ' )' );
end;
end;
As you can see, the data exchange to and from the AEP is handled via tables. The first function to add will get a file name as input parameter and return the file content as a memo output parameter:
function LoadFile( ulConnectionID: UNSIGNED32;
hConnection: ADSHANDLE;
pulNumRowsAffected: PUNSIGNED32 ): UNSIGNED32;
{$IFDEF WIN32}stdcall;{$ENDIF}{$IFDEF LINUX}cdecl;{$ENDIF} // Do not change prototype
var
DM1 : TDM1;
filename: string;
begin
Result := AE_SUCCESS;
DM1 := TDM1( AEPSessionMgr.GetDM( ulConnectionID ) );
try
DM1.tblInput.Open;
filename:=DM1.tblInput.Fields[0].AsString;
DM1.tblInput.Close;
DM1.tblOutput.Open;
DM1.tblOutput.Append;
TBlobField(DM1.tblOutput.Fields[0]).LoadFromFile(filename);
DM1.tblOutput.Post;
DM1.tblOutput.Close;
except
on E : EADSDatabaseError do
{* ADS-specific error, use ACE error code *}
DM1.DataConn.Execute( 'INSERT INTO __error VALUES ( ' + IntToStr( E.ACEErrorCode ) + ', '
+ QuotedStr( E.Message ) + ' )' );
on E : Exception do
{* other error *}
DM1.DataConn.Execute( 'INSERT INTO __error VALUES ( 1, ' + QuotedStr( E.Message ) + ' )' );
end;
end;
After compiling the project (mine was renamed to FileAeps) and copying it to a database folder, you can register the function:
CREATE PROCEDURE LoadFile(filename Memo,filecontent Memo OUTPUT) FUNCTION "LoadFile" IN LIBRARY ".FileAeps.aep";
A test will show if it works:
EXECUTE PROCEDURE LoadFile('c:tmptest.csv')
The result should have one record with one column that contains the file content. This could fail for one of the following reasons:
- error in the source code or file not found: check for the error message
- wrong Architecture: a 64Bit ADS Server requires a 64Bit DLL, 32Bit server requires a 32Bit DLL (see my other post here)
- function not exported, add it to the exports section of the DLL:
exports GetInterfaceVersion, LoadFile, Startup, Shutdown;
Parse CSV content into a table
The second step is a bit more complicated. Now we need to take the file content and create a table with enough (and large enough) columns to store the content. This can also be done in an AEP. The first input parameter contains the name of the table to be created, the second one the CSV content. An output parameter can be used to return status messages (I’ve used it to debug, but you can put any text into it).
function csv2table( ulConnectionID: UNSIGNED32;
hConnection: ADSHANDLE;
pulNumRowsAffected: PUNSIGNED32 ): UNSIGNED32;
{$IFDEF WIN32}stdcall;{$ENDIF}{$IFDEF LINUX}cdecl;{$ENDIF} // Do not change prototype
var
DM1 : TDM1;
tablename: string;
csvcontent: string;
sl, slLine: TStringList;
iFieldCount: integer;
iRecordCount: integer;
aMaxFieldSize: array of integer;
CreateSQL: string;
I,K: Integer;
begin
Result := AE_SUCCESS;
DM1 := TDM1( AEPSessionMgr.GetDM( ulConnectionID ) );
try
DM1.tblInput.Open;
tablename:=DM1.tblInput.Fields[0].AsString;
csvcontent:=DM1.tblInput.Fields[1].AsString;
DM1.tblInput.Close;
DM1.tblOutput.Open;
sl:=TStringList.Create;
slLine:=TStringList.Create;
try
sl.Text:=csvcontent;
//get the record count
iRecordCount:=sl.Count;
//hide empty lines at the end
while (iRecordCount>0) do
if (sl[iRecordCount-1]='')
then dec(iRecordCount)
else break;
//get the maximum number of fields in the records
//retrieve the maximum field sizes
iFieldCount:=0;
for k := 0 to iRecordCount-1 do
begin
slLine.Delimiter:=';';
slLine.DelimitedText:=sl[k];
if (slLine.Count>iFieldCount)
then begin
iFieldCount:=slLine.Count;
SetLength(aMaxFieldSize, iFieldCount);
end;
for I := 0 to iFieldCount-1 do
try
//minimum char(1)!
aMaxFieldSize[i]:=Max(Max(aMaxFieldSize[i], Length(slLine[i])), 1);
except
end;
end;
//built sql statement for table creation
CreateSQL:='create table ['+tablename+'] (f1 char('+
IntToStr(aMaxFieldSize[0])+') ';
for I := 1 to High(aMaxFieldSize) do
CreateSQL:=CreateSQL+', f'+IntToStr(i+1)+' char('+
IntToStr(aMaxFieldSize[i])+') ';
CreateSQL:=CreateSQL+');';
//create the table
DM1.DataConn.Execute(CreateSQL);
// insert records
DM1.AdsTable1.Close;
DM1.AdsTable1.TableName:=tablename;
DM1.AdsTable1.Open;
for k := 0 to iRecordCount-1 do
begin
DM1.AdsTable1.Append;
slLine.Delimiter:=';';
slLine.DelimitedText:=sl[k];
for I := 0 to iFieldCount-1 do
try
Dm1.AdsTable1.Fields[i].AsString:=slLine[i];
except
end;
DM1.AdsTable1.Post;
end;
DM1.AdsTable1.Close;
finally
FreeAndNil(sl);
FreeAndNil(slLine);
end;
DM1.tblOutput.Close;
except
on E : EADSDatabaseError do
{* ADS-specific error, use ACE error code *}
DM1.DataConn.Execute( 'INSERT INTO __error VALUES ( ' + IntToStr( E.ACEErrorCode ) + ', ' + QuotedStr( E.Message ) + ' )' );
on E : Exception do
{* other error *}
DM1.DataConn.Execute( 'INSERT INTO __error VALUES ( 1, ' + QuotedStr( E.Message ) + ' )' );
end;
end;
Add this function also to the exports section, compile and replace the AEP file in the database folder. Then register it:
CREATE PROCEDURE csv2table(tablename Memo, csvcontent Memo, status Memo OUTPUT) FUNCTION "csv2table" IN LIBRARY ".FileAeps.aep";
Import text files
Both AEPs can now be combined in a script to import from a CSV formatted text file on the server:
declare @s string;
@s=(select filecontent from (EXECUTE PROCEDURE LoadFile('c:\tmptest.csv')) a);
EXECUTE PROCEDURE csv2table('#test',@s);
select * from #test;
The result of the script has the conent of the CSV file as a table. This table can now be used to verfiy the data and insert the content into permanent tables.
Development considerations
As you can see it’s not that complicated to add your own functionality to an Advantage database. But there are few topics you need to be aware of.
Temp table usage
A temp table (#tablename) is not permanently created in the database but exists for the current connection only. If you close the connection, the temp table gets deleted automatically. This helps to ensure not to have to many unnecessary tables in the database and to prevent naming issues between different users/connections. But you still need to delete the table before running the script above in order not to conflict with a second execution of the script.
Security
By default the ADS service runs under the local SYSTEM account. This could lead to a security leak: Any file on the server can be loaded into the memo column, even if the user itself has no permissions to it. To bypass this, I usually add a new limited user (local user or domain user) named ‚Advantage‘ and modify the startup parameters of the Advantage service to run under this account. Linux does it by default;)
unhandled Exceptions
The post shows just a very simplified example how to load and parse CSV files. In order not to crash the server, a more detailled exception handling should be used.
Enhancements
Using this technique it is also possible to return all files and subfolders of a given folder. So you can even add a server side file browse to your applications.