[SOLVED] get the data type for a specific variable from the loaded
database.
lazarus / 16-Databases

----------------------------------------------------------------------
Seenkao · 2024-08-04 09:30

По сути сама проблема написана в топике. Есть ли возможность получит
именно тип определённой переменной из загруженной БД? Я могу это
сделать вручную, но я думаю уже есть готовые методы для решения
подобных проблем?!

Google translate:
In fact, the problem itself is written in the topic. Is it possible to
get the type of a specific variable from the loaded DB? I can do it
manually, but I think there are already ready-made methods for solving
such problems?!

----------------------------------------------------------------------
cdbc · 2024-08-04 09:48

Hi
Hmmm, most DB keep a lot of metadata about the actual innards of the
data they keep. Wouldn't it be possible to query these metadata
yourself?!?
E.g.: In 'Sqlite3' the program, you can view the metadata with
'.schemas' & '.tables' etc.
Regards Benny

----------------------------------------------------------------------
Seenkao · 2024-08-04 09:57

Я делаю программу, в которой ни разработчик, ни сама программа могут
не знать о данных в программе. Загрузить БД, посмотреть таблицу и
вывести её в DBGrid я могу. Но мне нужно взять любой элемент БД и
получить нужную информацию о нём. И на данном этапе я не понимаю как
это я могу сделать.

Как я и писал изначально, я могу просто прочитать БД вручную и
получить нужные мне данные. Но неужели нет ни чего уже готового для
таких решений?


Google translate:
I am making a program in which neither the developer nor the program
itself may know about the data in the program. I can load the DB, view
the table and display it in DBGrid. But I need to take any element of
the DB and get the necessary information about it. And at this stage I
do not understand how I can do this.

As I wrote initially, I can simply read the DB manually and get the
data I need. But is there really nothing ready-made for such
solutions?

----------------------------------------------------------------------
af0815 · 2024-08-04 10:25

In the Sources delivered with Lazarus is a DB Explorer in Source and
this program can read metadata s. Maybe a source of knowledge.

----------------------------------------------------------------------
paweld · 2024-08-04 11:14

SqlDBConnection* components have several procedures to retrieve
database information, such as GetTableNames or GetFieldNames. To
retrieve field types, execute select * from table.
A simple example for SQLite is attached

----------------------------------------------------------------------
eldonfsr · 2024-08-04 18:35

I don't know id looking for something like this on dbgrid
fiedl.datatype porperity  you can apply this direclty to datasource


procedure TFormMas90.DBGTabKeyPress(Sender: TObject; var Key: char);
Var ColName:String;
    ldate:Tdate;
begin
  Colname:= DbGTab.Columns.Items[DbGTab.SelectedIndex].FieldName;
  with DbgTab  do begin
   if( (Key >=#40) and( Key<=#125)) then begin
     search+=key;
     if(DbGTab.Columns.Items[DbGTab.SelectedIndex].Field.DataType<>
ftDate) then begin;
         DBgTab.DataSource.DataSet.Locate(ColName,
search,[loCaseInsensitive, loPartialKey]);
         StatusBar1.Panels[0].Text:='Buscando: '+search;
     end else begin
         lDate:=SearchDate(search, Key );
         DBgTab.DataSource.DataSet.Locate(ColName, ldate,[]);
        StatusBar1.Panels[0].Text:='Buscando: '+DateToStr(ldate);
     end;
     key:=#0;
   end;
   if( Key=#27) then begin
     search:='';
     nYY:=Yearof(Now);nMM:=Monthof(Now);nDD:=Dayof(Now);
   end;

  end;
end;

----------------------------------------------------------------------
Zvoni · 2024-08-05 06:30

Could you explain what you mean with "variable"????
There are DBMS that support "variables", but i'm not sure that's what
you mean.

Do you mean "Datatype of a specific Field"?
If yes, cdbc gave you the answer.

Pretty much every modern DBMS has metadata you can query.
So the next question: Which DBMS? MySQL, SQlite or what?

----------------------------------------------------------------------
Seenkao · 2024-08-05 10:44

Благодарю всех за информацию!

Воспользовался возможностями самих СУБД.

Google translate:
Thanks to everyone for the information!

I used the capabilities of the DBMS itself.


SQLite:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLQuery.Close;SQLQuery.SQL.Text := 'PRAGMA
table_info(myTable)';SQLQuery.Open;while not SQLQuery.EOF dobegin 
writeln('Table name - ', SQLQuery.FieldByName('name').AsAnsiString, '
table type ', SQLQuery.FieldByName('type').AsAnsiString); 
SQLQuery.Next;end; Other:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLQuery.SQL.Text := 'SHOW COLUMNS FROM
myFirstDB.firstTable';SQLQuery.DataBase := myDB; SQLQuery.Open;...

Zvoni, я спрашивал как это можно сделать. )))

Zvoni, I asked how this can be done. )))

----------------------------------------------------------------------
Zvoni · 2024-08-05 10:51

Quote from: Seenkao on August 05, 2024, 12:44:01 pmThanks to everyone
for the information!

I used the capabilities of the DBMS itself.


SQLite:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLQuery.Close;SQLQuery.SQL.Text := 'PRAGMA
table_info(myTable)';SQLQuery.Open;while not SQLQuery.EOF dobegin 
writeln('Table name - ', SQLQuery.FieldByName('name').AsAnsiString, '
table type ', SQLQuery.FieldByName('type').AsAnsiString); 
SQLQuery.Next;end; Zvoni, I asked how this can be done. )))

err.....to avoid confusion for anyone else finding this:
the usage of "table" in the writeln above (Line 6) is misleading.
It's "Column"
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLQuery.Close;SQLQuery.SQL.Text := 'PRAGMA
table_info(myTable)';SQLQuery.Open;while not SQLQuery.EOF dobegin 
writeln('Column name - ', SQLQuery.FieldByName('name').AsAnsiString, '
Column type ', SQLQuery.FieldByName('type').AsAnsiString); 
SQLQuery.Next;end;

----------------------------------------------------------------------
Seenkao · 2024-08-05 11:09

Да, вы правы. В данном случае я делал для себя в первую очередь. И
учту ваши правки!

Google translate:
Yes, you are right. In this case, I did it for myself first and
foremost. And I will take your edits into account!

----------------------------------------------------------------------
BrunoK · 2024-08-05 13:09

Dont know if it replies to your question. Any way ...
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i program
pgmDataType; {$mode objfpc}{$H+} uses  Classes,  SysUtils, 
BufDataset,  DB; var  vDataSet: TBufDataSet = nil;  { <-- Change to a
suitable TDataSet class for  ex :  vDataSet: TSqlQuery = nil;  }    
procedure CreateDataset;  var    lFieldDef: TFieldDef;  begin   
vDataSet := TBufDataset.Create(nil);    with vDataSet, FIeldDefs do
begin      with AddFieldDef do begin        Name := 'IntegerField';  
    DataType := ftInteger;      end;      with AddFieldDef do begin  
    Name := 'IAmAtringFIeld';        DataType := ftString;        Size
:= 20;      end;      CreateDataset;      Append;      Fields[0].Value
:= 0;      Fields[1].Value := 'Hello world !';      Post;     
Append;      Fields[0].Value := 263;      Fields[1].Value := 'I am  a
TStringField !';      Post;      Close;    end;  end;   procedure
DumpDataSetFieldInfo;  var    i: integer;  begin    with vDataSet do
begin      WriteLn('I''m active=', Active);{  Add following line for
data sets other than TBufDataset -->   for example for an TSQLQuery
dataset  ->      Open;            { One or the other or both, I dont
remember  }      UpdateFieldDefs; { May be not even required }}     
for i := 0 to FieldDefs.Count - 1 do        with FieldDefs[i] do     
   { Here you have each database field definition as selected by
FPC.DB,            to know the type that's DataType }         
WriteLn(Name, ' ', DataType, ' ', Size);    end;  end;begin 
CreateDataset;  DumpDataSetFieldInfo;  vDataSet.Free;  ReadLn;end.

----------------------------------------------------------------------
Seenkao · 2024-08-05 15:09

BrunoK, я не уверен. По моему слишком громоздкое решение...


Google translate:
BrunoK, I'm not sure. Too cumbersome a solution in my opinion...

----------------------------------------------------------------------
BrunoK · 2024-08-05 16:23

Quote from: Seenkao on August 05, 2024, 05:09:29 pmBrunoK, I'm not
sure. Too cumbersome a solution in my opinion...
Cumbersome ? This is a full demo program.
You add the lines in the following code that will retireve in an easy
way to the Datatype attributed by the DB code. This does not depend on
any specific Database like you do where querying table_select * from
sqlite_master where TableName = myTable.
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i   function
FieldTypeForName(aDataSet: TDataSet; aFieldName: string): TFieldType; 
var    lFieldDefIx: integer;  begin    lFieldDefIx :=
aDataSet.FieldDefs.IndexOf(aFieldName);    if lFieldDefIx < 0 then   
 Result := ftUnknown    else      Result :=
aDataSet.FieldDefs[lFieldDefIx].DataType;  end; begin  CreateDataset; 
DumpDataSetFieldInfo;  WriteLn('FieldType for ', 'IAmAtringFIeld', ' =
',    FieldTypeForName(vDataSet, 'IAmAtringFIeld'));  vDataSet.Free; 
ReadLn;end.

----------------------------------------------------------------------
Seenkao · 2024-08-05 17:51

Quote from: BrunoK on August 05, 2024, 06:23:21 pmCumbersome ? This is
a full demo program.
Извиняюсь, но да. И я не говорю что ваш пример - это плохой пример!
Вполне возможно для других он больше подойдёт.
Приведённый мной пример, это так же можно сказать полноценная
программа. Я могу данные просто записать либо в массив, либо какой-то
компонент и работать с ними.
Надеюсь вы меня поймёте!


Google translate:
Sorry, but yes. And I'm not saying that your example is a bad example!
It's quite possible that it would be more suitable for others.
The example I gave is also a full-fledged program. I can simply write
the data either to an array or to some component and work with it.
I hope you understand me!