Accessing Firebird from Delphi Prism 2010.
By admin. Filed in ASP.NET, Delphi, Uncategorized |Tags: ASP.NET, delphi Prism, Firebird, pascal
Here is a quick routine to read values from a Firebird Database into Delphi Prism.
uses FirebirdSql.Data.FirebirdClient;
method RunSqlCmd(sqlCmd: string; resultName: string; var FieldVar: array of string; FieldName: array of string): string;
var
conStr: string;
count: integer;
i,j: Integer;
colName: string;
val: string;
dataType: System.Type;
dataTypeStr: String;
fbConn: FbConnection;
fbTran: FbTransaction;
fbReader: FBDataReader;
fbCmd: FbCommand;
begin
count:=WebConfigurationManager.ConnectionStrings.Count;
if(count>0)then
begin
conStr := WebConfigurationManager.ConnectionStrings['RMU'].ConnectionString;
end;
fbConn := new FbConnection(conStr);
fbConn.Open;
fbTran := fbConn.BeginTransaction();
fbCmd := new FbCommand(sqlCmd, FbConn, fbTran);
fbReader := fbCmd.ExecuteReader();
if (fbReader.Read()) then
try
for i := 0 to fbReader.VisibleFieldCount – 1 do
begin
colName := fbReader.GetName(i).ToUpper; //PRISM GetDataTypeName ValueType[i].Name.ToUpper();
dataTypeStr := fbReader.GetDataTypeName(i);
dataType := fbReader.GetFieldType(i);
try
if (dataType = typeof(String) ) then
val := fbReader.GetString(i).ToString
else
if (dataType = typeof(Int16)) then
val := fbReader.GetInt16(i).ToString
else
if (dataType = typeof(Int32)) then
val := fbReader.GetInt32(i).ToString
else
if (dataType = typeof(Double)) then
val := fbReader.GetDouble(i).ToString
else
if (dataType = typeof(DateTime)) then
val := fbReader.GetDateTime(i).ToString
else
val := ”;
except;
val := ”;
end;
if ((colName = resultName.ToUpper()) and (resultName <> ”)) then
result := val
else
begin
for j := Low(FieldName) to High(FieldName) do
begin
if ((colName = FieldName[j].ToUpper()) and (FieldName[j] <> ”)) then
begin
FieldVar[j] := val;
break;
end;
end;
end;
end;
except
end;
fbReader.Close;
FbConn.Close;
end;
To call it, you have a function like:
method RunSqlSelect(someParam: string);
var
custId: string;
usrFullName: string;
usrAccessLevel: string;
userActive: string;
sqlStr: string;
results: stringArray;
recMask: integer;
bondMask: integer;
testMask: integer;
allMask: integer;
begin
results := new stringArray(19);
sqlStr := ‘SELECT USR_CUST_ID, USR_ID,C_TEMP_DISPLAY,USR_FIRST_NAME,USR_LAST_NAME’ +
‘ from GET_USER_INFO_R02 (”’ + userName + ”’) ‘;
RunSqlCmd(sqlStr, ”, results,
['USR_CUST_ID', 'USR_ID', 'C_TEMP_DISPLAY', 'USR_FIRST_NAME', 'USR_LAST_NAME',
'C_NAME', 'USR_RECTIFIER_PG_MASK', 'USR_BOND_PG_MASK', 'USR_TEST_PG_MASK',
'USR_ALL_PG_MASK', 'USR_ACTIVE', 'USR_PASSWORD_OK', 'USR_SHARED_MASK', 'EMAIL',
'TZ_OLSON_NAME', 'USR_REPORTS_IN_UTC', 'TZ_SHORT_DESC', 'RPT_TZ_OLSON_NAME',
'RPT_TZ_NAME_FULL']);
usrFullName := results[3] + ‘ ‘ + results[4];
custId := results[0];
//blaaa, blaa, blaaa…
end;
