SQL Functions FieldType, FileldDimension

[PMan]

Hi together,

I am looking for functions to handel SQL insert and update like

Function FieldType(RecordsId as String, Field as String) as Strng
and
Function FieldDim(RecordsId as String, Field as String) as Integer

(s. MySQL Referancemanual 24.2.1 C-API: Datentypen - MYSQL_FIELD http://dev.mysql.com/doc/refman/5.1/de/c-api-datatypes.html)

With this functions it is posible to automate wirting data in an SQL Database and evaluate i.e. if the datafield is String or a date or datetime the value must given in a string compatible form and the length of the value must less or equal then the length of the datafield. Also numaric value will transfer in the right form with this functions.

Is it possible to program those functions do it yourself or must be necessary this as built in functions in kbasic?

/PMan

[berndnoetscher]

Hi together,

I am looking for functions to handel SQL insert and update like

Function FieldType(RecordsId as String, Field as String) as Strng
and
Function FieldDim(RecordsId as String, Field as String) as Integer

(s. MySQL Referancemanual 24.2.1 C-API: Datentypen - MYSQL_FIELD http://dev.mysql.com/doc/refman/5.1/de/c-api-datatypes.html)

With this functions it is posible to automate wirting data in an SQL Database and evaluate i.e. if the datafield is String or a date or datetime the value must given in a string compatible form and the length of the value must less or equal then the length of the datafield. Also numaric value will transfer in the right form with this functions.

Is it possible to program those functions do it yourself or must be necessary this as built in functions in kbasic?

/PMan

Qt seems to provide such functionality, but as I did test it, it seems not to work, so there is no built-in function there.
Access with a C-API is not possible. Sorry.

[Henning]

Hi PMan,

With this functions it is posible to automate wirting data in an SQL Database and evaluate i.e. if the datafield is String or a date or datetime the value must given in a string compatible form and the length of the value must less or equal then the length of the datafield. Also numaric value will transfer in the right form with this functions.

Are you saying that you intend to handle a (MySQL) DB without knowing what fields are what? How do you then intend to use the data returned from SELECT? How do you know what fields to update if you don't know the type?

I don't really understand the grounds for the Q.

For me, knowing the DB structure is the key to write optimized queries.

/Henning

[PMan]

Hi Henning,

I don't really understand the grounds for the Q.

I know my database but the program don't know it. Some examples.

If you initialize a new database with a scheme of table and relations in a script file, the database is empty. Some categories are defined in the database with different table design. The values of the categories are saved in some *.txt or *.csv files. Now you need a function, to read the values of the categiries into the database table. But the database fields have different types i.e. numeric, string or boolean. I wish to program only one function for all categories.

You have the same issue with programing a form with a spreadsheet to show and modify the values of tables in columns and rows like MS-Access. I want only one form for all tables. After modifying the values and before write back into the table, the form must know, how type is the modified fieled to calculate the update string.

Do you now understand my problem?

/PMan

[Henning]

I know I've read somewhere that B is going to add that Run can return the result, but can't find it now. If I remember right it would be as some Variant Array. Havn't tested if it already is in the latest update. I think that would solve your issue.

/Henning

[berndnoetscher]

I know I've read somewhere that B is going to add that Run can return the result, but can't find it now. If I remember right it would be as some Variant Array. Havn't tested if it already is in the latest update. I think that would solve your issue.

/Henning

Generic query to run database commands, e.g. having a sqlite database:

Code: Select all

   Dim a As Dictionary = Query.Command("PRAGMA database_list;")
   
   Dim ss As Strings = a.Keys()
   dim i as integer
   For i=0 to ss.length()
     Print( ss.String(i))
     Print( a.String(ss.String(i)))
   Next   

[Henning]

If you install MySql server complete, libmySQL.dll (with all apis) is located in MySQL/server version/bin.

I don't know if the following works.
Class MySQL Alias Lib "D:/Program/MySQL/MySQL Server 5.0/bin/libmySQL.dll"

Or if a Public Declare function in libmySQL.dll works.

/Henning

[PMan]

Hi together,

while looking for a solution I found the database INFORMATION_SCHEMA. This database accumulate all information about databases, tables and fields in views not in basic tables. I think that this database is a part of all ANSI SQL databases, so the routine should run in MySQL an other SQL-databases.

Because the database INFORMATION_SCHEMA administrate the information in views you can't use recordsets for reading the information but queries will run.

Bevor you can run the routine FieldType you must connect to the database INFORMATION_SCHEMA and to your working database with DataBase.Open. Then you can switch between the connected databases with Database.SetCurrentDatabase.

The variable sColName is only for debugging.
Thank you Bernd for information about QUERY.

Code: Select all

Public Function FieldType(sDB as String, sTable as String, sColumn as String) _
                          as String
'ermittelt den Feldtyp in einer SQL-Tabelle

Dim a as Dictionary
Dim sSQL as String, sColName as String, sColType as String
Dim ss as Strings

  If Database.SetCurrentDatabase("INFORMATION_SCHEMA") Then
    sSQL = "SELECT column_name, data_type FROM INFORMATION_SCHEMA.COLUMNS " & _
           "WHERE table_name = '" & sTable & "' " & _
           "AND table_schema = '" & sDB & "' " & _
           "AND column_name = '" & sColumn & "';"
    a = Query.Command(sSQL)
    ss = a.Keys()
    sColName = a.String(ss.String(1))
    sColType = a.String(ss.String(2))
    Database.SetCurrentDatabase(sDB)
  EndIf
 
  Return sColType
 
End Function

/PMan