[SOLVED] Always return a string

[madref]

I have this function

Code: Pascal  [Select][+][-]

1. function MLookup(Veld, Tabel, Voorwaarde: string): variant;
2. var mySql: TSQLQuery;
3. begin
4.   mySQl := TSQLQuery.Create(nil);
5.   mySQl.DataBase := DB_Info.Connect_DB;
6.   try
7.     mySql.SQL.Text := Format('SELECT %s FROM %s WHERE %s', [veld, tabel, voorwaarde]);
8.     mySql.Open;
9.     Result := mySQL.Fields[0].AsVariant;
10.   finally
11.     MySql.Free;
12.   end;
13. end;     // MLookup

and it serve me well.But now i want to change it so it ALWAYS returns a string.
Even when the result is a digit. Then it has to return the sting of that digit (101 -=> '101')
Or when the result is a date it has to return the string of that date (01-03-2016 -=> '1 March 2016')


The code would probably look something like

Code: Pascal  [Select][+][-]

1. function MLookup(Veld, Tabel, Voorwaarde: string): variant;
2. var mySql: TSQLQuery;
3.      vr: variant;
4. begin
5.   mySQl := TSQLQuery.Create(nil);
6.   mySQl.DataBase := DB_Info.Connect_DB;
7.   try
8.     mySql.SQL.Text := Format('SELECT %s FROM %s WHERE %s', [veld, tabel, voorwaarde]);
9.     mySql.Open;
10.     vr := mySQL.Fields[0].AsVariant;
11.  
12.  
13.     if vr = "Digit" then Result := IntToStr(vr);
14.     if vr = "Some Date" then Result := FormatDateTime('d mmmm yyyy', vr);
15.     if vr = "String" then Result := vr;
16.     .....
17.     .....
18.   finally
19.     MySql.Free;
20.   end;
21. end;     // MLookup

But how to do these if-statement?

[molly]

try one of the following functions:
function VarToStr(const V: Variant): string;
function VarToStrDef(const V: Variant; const ADefault: string): string;
function VarToWideStr(const V: Variant): WideString;
function VarToWideStrDef(const V: Variant; const ADefault: WideString): WideString;
function VarToUnicodeStr(const V: Variant): UnicodeString;
function VarToUnicodeStrDef(const V: Variant; const ADefault: UnicodeString): UnicodeString;

These are in unit variants but afaik unfortunately there is no documentation for that unit yet

Don't forget to change the type of your function result

edit:
With regards to your original question try the example on this page.

[rvk]

Am I missing the point here??

Why not just this?

Code: Pascal  [Select][+][-]

1. function MLookup(Veld, Tabel, Voorwaarde: string): string;
2. //...
3. Result := mySQL.Fields[0].AsString;

And in case of the date you can use

Code: Pascal  [Select][+][-]

1. if mySQL.Fields[0].DataType = ftDate then Result := FormatDateTime('d mmmm yyyy', mySQL.Fields[0].AsDateTime);

[molly]

Am I missing the point here??

Not with regards to my answers. My bad. sorry for that.

[Thaddy]

But why not?

Code: Pascal  [Select][+][-]

1. ]
2. writestr(Result, whateverquery)
3.  

You can even use basic formatting on that!

[madref]

But why not?

Code: Pascal  [Select][+][-]

1. ]
2. writestr(Result, whateverquery)
3.  

You can even use basic formatting on that!

Does that also work with this?
Even when result = NULL?

Code: Pascal  [Select][+][-]

1.   with TStringList.Create do
2.     try
3.       Add ('tell application "Microsoft Word"');
4.       .....
5.       .....
6.       Add ('set content of text object of bookmark "bkmEmail99" of active document to "' + DB_Email + '"');
7.       Add ('set content of text object of bookmark "bkmGebDatum" of active document to "' + SLookup('Lid_Geb_datum','tbl_Personen_Leden', 'Lid_ID = ' + IntToStr(lid)) + '"');
8.       .....
9.       .....
10.       .....
11.       Add ('activate');
12.       Add ('end tell');
13.       SaveToFile(Script);
14.     finally
15.         Free;
16.     end;

[madref]

Am I missing the point here??

Why not just this?

Code: Pascal  [Select][+][-]

1. function MLookup(Veld, Tabel, Voorwaarde: string): string;
2. //...
3. Result := mySQL.Fields[0].AsString;

And in case of the date you can use

Code: Pascal  [Select][+][-]

1. if mySQL.Fields[0].DataType = ftDate then Result := FormatDateTime('d mmmm yyyy', mySQL.Fields[0].AsDateTime);

But what happens when the Result = NULL?

[rvk]

Code: Pascal  [Select][+][-]

1. if mySQL.Fields[0].DataType = ftDate then Result := FormatDateTime('d mmmm yyyy', mySQL.Fields[0].AsDateTime);

But what happens when the Result = NULL?

You'll get "30 december 1899".
Otherwise you can just put in an extra check for NULL and set the string to empty.

[ASerge]

Or you can also convert to char in the SQL statement.
By the way, a little mistake in the "try" location:

Code: Pascal  [Select][+][-]

1.   mySQl := TSQLQuery.Create(nil);
2.   mySQl.DataBase := DB_Info.Connect_DB;
3.   try
4.   ...
5.   finally
6.     MySql.Free;
7.   end;

If the error happens in line 2, mySql will not be released

[madref]

The error CAN'T Happen because there already is a connection to a database that has been checked. But i get your point

[madref]

My final code which might be a bit 'big'. But now i can do what i want.

Code: Pascal  [Select][+][-]

1. function SLookup(Veld, Tabel, Voorwaarde: string): String;
2. var mySql: TSQLQuery;
3.     vr: variant;
4. begin
5.   mySQl := TSQLQuery.Create(nil);
6.   mySQl.DataBase := DB_Info.Connect_DB;
7.   try
8.     mySql.SQL.Text := Format('SELECT %s FROM %s WHERE %s', [veld, tabel, voorwaarde]);
9.     mySql.Open;
10.     vr := mySQL.Fields[0].AsVariant;
11.     Case varType(vr) of
12.         varEmpty   : Result := '';
13.         varNull    : Result := '';
14.         varSingle  : Result := FloatToStr (vr);
15.         varDouble  : Result := FloatToStr (vr);
16.         varDate    : Result := FormatDateTime('d mmmm yyyy', vr);
17.         varString  : Result := vr;
18.         varBoolean : if vr = True then Result := 'True'
19.                                  else Result := 'False';
20.         varShortInt: Result := IntToStr(vr);
21.         varSmallint: Result := IntToStr(vr);
22.         varInteger : Result := IntToStr(vr);
23.         varInt64   : Result := IntToStr(vr);
24.         varByte    : Result := IntToStr(vr);
25.         varWord    : Result := IntToStr(vr);
26.         varLongWord: Result := IntToStr(vr);
27.      end;
28.   finally
29.     MySql.Free;
30.   end;
31. end;     // SLookup

[wp]

Believe me: it WILL happen

[rvk]

The error CAN'T Happen because there already is a connection to a database that has been checked. But i get your point

Believe me: it WILL happen

Actually, assigning a Database to a dataset only goes wrong if the dataset itself is active (I think). I don't think it can generate an error otherwise. And because the dataset is just created it can't be active so I do think the assignment is safe here in this particular instance.

But it's always good practice to move this sort of assignment inside the try.

[wp]

It will also happen when, some day in the future, the OP will refactor the code, after having forgotten all these mini details and assumptions, without having created DB_Info in this particular context.