Recent

Author Topic: [SOLVED] get the data type for a specific variable from the loaded database.  (Read 1402 times)

Seenkao

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

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?!
« Last Edit: August 05, 2024, 05:08:07 pm by Seenkao »
Rus: Стремлюсь к созданию минимальных и достаточно быстрых приложений.

Eng: I strive to create applications that are minimal and reasonably fast.
Working on ZenGL

cdbc

  • Hero Member
  • *****
  • Posts: 1588
    • http://www.cdbc.dk
Re: get the data type for a specific variable from the loaded database.
« Reply #1 on: August 04, 2024, 11:48:13 am »
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
If it ain't broke, don't fix it ;)
PCLinuxOS(rolling release) 64bit -> KDE5 -> FPC 3.2.2 -> Lazarus 2.2.6 up until Jan 2024 from then on it's: KDE5/QT5 -> FPC 3.3.1 -> Lazarus 3.0

Seenkao

  • Hero Member
  • *****
  • Posts: 609
    • New ZenGL.
Re: get the data type for a specific variable from the loaded database.
« Reply #2 on: August 04, 2024, 11:57:15 am »
Я делаю программу, в которой ни разработчик, ни сама программа могут не знать о данных в программе. Загрузить БД, посмотреть таблицу и вывести её в 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?
Rus: Стремлюсь к созданию минимальных и достаточно быстрых приложений.

Eng: I strive to create applications that are minimal and reasonably fast.
Working on ZenGL

af0815

  • Hero Member
  • *****
  • Posts: 1373
Re: get the data type for a specific variable from the loaded database.
« Reply #3 on: August 04, 2024, 12:25:51 pm »
In the Sources delivered with Lazarus is a DB Explorer in Source and this program can read metadata s. Maybe a source of knowledge.
regards
Andreas

paweld

  • Hero Member
  • *****
  • Posts: 1250
Re: get the data type for a specific variable from the loaded database.
« Reply #4 on: August 04, 2024, 01:14:11 pm »
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
Best regards / Pozdrawiam
paweld

eldonfsr

  • Hero Member
  • *****
  • Posts: 518
Re: get the data type for a specific variable from the loaded database.
« Reply #5 on: August 04, 2024, 08:35:06 pm »
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

  • Hero Member
  • *****
  • Posts: 2719
Re: get the data type for a specific variable from the loaded database.
« Reply #6 on: August 05, 2024, 08:30:14 am »
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?
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Seenkao

  • Hero Member
  • *****
  • Posts: 609
    • New ZenGL.
Благодарю всех за информацию!

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

Google translate:
Thanks to everyone for the information!

I used the capabilities of the DBMS itself.


SQLite:
Code: Pascal  [Select][+][-]
  1. SQLQuery.Close;
  2. SQLQuery.SQL.Text := 'PRAGMA table_info(myTable)';
  3. SQLQuery.Open;
  4. while not SQLQuery.EOF do
  5. begin
  6.   writeln('Table name - ', SQLQuery.FieldByName('name').AsAnsiString, ' table type ', SQLQuery.FieldByName('type').AsAnsiString);
  7.   SQLQuery.Next;
  8. end;
  9.  
Other:
Code: Pascal  [Select][+][-]
  1. SQLQuery.SQL.Text := 'SHOW COLUMNS FROM myFirstDB.firstTable';
  2. SQLQuery.DataBase := myDB;
  3.  
  4. SQLQuery.Open;
  5. ...


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

Zvoni, I asked how this can be done. )))
Rus: Стремлюсь к созданию минимальных и достаточно быстрых приложений.

Eng: I strive to create applications that are minimal and reasonably fast.
Working on ZenGL

Zvoni

  • Hero Member
  • *****
  • Posts: 2719
Thanks to everyone for the information!

I used the capabilities of the DBMS itself.


SQLite:
Code: Pascal  [Select][+][-]
  1. SQLQuery.Close;
  2. SQLQuery.SQL.Text := 'PRAGMA table_info(myTable)';
  3. SQLQuery.Open;
  4. while not SQLQuery.EOF do
  5. begin
  6.   writeln('Table name - ', SQLQuery.FieldByName('name').AsAnsiString, ' table type ', SQLQuery.FieldByName('type').AsAnsiString);
  7.   SQLQuery.Next;
  8. end;
  9.  
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][+][-]
  1. SQLQuery.Close;
  2. SQLQuery.SQL.Text := 'PRAGMA table_info(myTable)';
  3. SQLQuery.Open;
  4. while not SQLQuery.EOF do
  5. begin
  6.   writeln('Column name - ', SQLQuery.FieldByName('name').AsAnsiString, ' Column type ', SQLQuery.FieldByName('type').AsAnsiString);
  7.   SQLQuery.Next;
  8. end;
  9.  
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Seenkao

  • Hero Member
  • *****
  • Posts: 609
    • New ZenGL.
Re: get the data type for a specific variable from the loaded database.
« Reply #9 on: August 05, 2024, 01:09:39 pm »
Да, вы правы. В данном случае я делал для себя в первую очередь. И учту ваши правки!

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!  ;)
Rus: Стремлюсь к созданию минимальных и достаточно быстрых приложений.

Eng: I strive to create applications that are minimal and reasonably fast.
Working on ZenGL

BrunoK

  • Hero Member
  • *****
  • Posts: 620
  • Retired programmer
Re: get the data type for a specific variable from the loaded database.
« Reply #10 on: August 05, 2024, 03:09:49 pm »
Dont know if it replies to your question. Any way ...
Code: Pascal  [Select][+][-]
  1. program pgmDataType;
  2.  
  3. {$mode objfpc}{$H+}
  4.  
  5. uses
  6.   Classes,
  7.   SysUtils,
  8.   BufDataset,
  9.   DB;
  10.  
  11. var
  12.   vDataSet: TBufDataSet = nil;  { <-- Change to a suitable TDataSet class for
  13.   ex :
  14.   vDataSet: TSqlQuery = nil;
  15.   }
  16.  
  17.  
  18.  
  19.   procedure CreateDataset;
  20.   var
  21.     lFieldDef: TFieldDef;
  22.   begin
  23.     vDataSet := TBufDataset.Create(nil);
  24.     with vDataSet, FIeldDefs do begin
  25.       with AddFieldDef do begin
  26.         Name := 'IntegerField';
  27.         DataType := ftInteger;
  28.       end;
  29.       with AddFieldDef do begin
  30.         Name := 'IAmAtringFIeld';
  31.         DataType := ftString;
  32.         Size := 20;
  33.       end;
  34.       CreateDataset;
  35.       Append;
  36.       Fields[0].Value := 0;
  37.       Fields[1].Value := 'Hello world !';
  38.       Post;
  39.       Append;
  40.       Fields[0].Value := 263;
  41.       Fields[1].Value := 'I am  a TStringField !';
  42.       Post;
  43.       Close;
  44.     end;
  45.   end;
  46.  
  47.   procedure DumpDataSetFieldInfo;
  48.   var
  49.     i: integer;
  50.   begin
  51.     with vDataSet do begin
  52.       WriteLn('I''m active=', Active);
  53. {  Add following line for data sets other than TBufDataset -->
  54.    for example for an TSQLQuery dataset  ->
  55.       Open;            { One or the other or both, I dont remember  }
  56.       UpdateFieldDefs; { May be not even required }
  57. }
  58.       for i := 0 to FieldDefs.Count - 1 do
  59.         with FieldDefs[i] do
  60.           { Here you have each database field definition as selected by FPC.DB,
  61.             to know the type that's DataType }
  62.           WriteLn(Name, ' ', DataType, ' ', Size);
  63.     end;
  64.   end;
  65. begin
  66.   CreateDataset;
  67.   DumpDataSetFieldInfo;
  68.   vDataSet.Free;
  69.   ReadLn;
  70. end.

Seenkao

  • Hero Member
  • *****
  • Posts: 609
    • New ZenGL.
BrunoK, я не уверен. По моему слишком громоздкое решение...


Google translate:
BrunoK, I'm not sure. Too cumbersome a solution in my opinion...
Rus: Стремлюсь к созданию минимальных и достаточно быстрых приложений.

Eng: I strive to create applications that are minimal and reasonably fast.
Working on ZenGL

BrunoK

  • Hero Member
  • *****
  • Posts: 620
  • Retired programmer
BrunoK, 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][+][-]
  1.   function FieldTypeForName(aDataSet: TDataSet; aFieldName: string): TFieldType;
  2.   var
  3.     lFieldDefIx: integer;
  4.   begin
  5.     lFieldDefIx := aDataSet.FieldDefs.IndexOf(aFieldName);
  6.     if lFieldDefIx < 0 then
  7.       Result := ftUnknown
  8.     else
  9.       Result := aDataSet.FieldDefs[lFieldDefIx].DataType;
  10.   end;
  11.  
  12. begin
  13.   CreateDataset;
  14.   DumpDataSetFieldInfo;
  15.   WriteLn('FieldType for ', 'IAmAtringFIeld', ' = ',
  16.     FieldTypeForName(vDataSet, 'IAmAtringFIeld'));
  17.   vDataSet.Free;
  18.   ReadLn;
  19. end.      

Seenkao

  • Hero Member
  • *****
  • Posts: 609
    • New ZenGL.
Cumbersome ? 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!
Rus: Стремлюсь к созданию минимальных и достаточно быстрых приложений.

Eng: I strive to create applications that are minimal and reasonably fast.
Working on ZenGL

 

TinyPortal © 2005-2018