[SOLVED] get the data type for a specific variable from the loaded database.

[Seenkao]

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

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]

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]

Я делаю программу, в которой ни разработчик, ни сама программа могут не знать о данных в программе. Загрузить БД, посмотреть таблицу и вывести её в 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]

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]

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]

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]

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]

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

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

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. )))

[Zvoni]

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.  

[Seenkao]

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

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]

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]

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


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

[BrunoK]

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]

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!