How can I create database tables using structures

Question

I am trying to create and store databases using c interface. I have a structure table that contains some variables and datatypes. How would I convert them into database tables. The details are mentioned below. In, database.c file, I initialized createTable function and Folder_table structure that contains the constraints and data types and I have a function to connect to the database in firebird. Once I read this structure, I would like to know how can I convert this structure into table and store it in the database. (Is there something I can do with sprintf?)

**database.c:**
#include "/Library/Frameworks/Firebird.framework/Versions/A/Headers/ibase.h"
#include <stdio.h>
#include <string.h>

void* CreateTable(char *tableName, uint rows)
{
    int error   = 0;
    void *table;

    //// Allocate table
    table = Allocate(GetTableSize(tableName)*rows);

    //// List table pointers
    for(uint rowNumber=0; rowNumber < rows; rowNumber++)
    {
        //// Add code to Connect linked list row pointers
    }

    return error;
}

typedef struct Folder_Table       // Table type name gets "_Table" added to the SQLite name
{
    //// Fields                   // Comments are added to seperate the fields, Relationships, and metadata
    int     folderID;             // Fields are added as elements of the type and length listed in SQLite
    float   field1;
    int     field2;
    float   field3;
    char    field4[40];           // string length '40' is queried from SQLite settings for field

} Folder_Table;
int SQLOpen(void)
{
    char                logInData [256];
    short               bufferLength;
    sprintf(logInData, "%c%c%c%c%c%c%s%c%c%s",      isc_dpb_version1,
            isc_dpb_num_buffers,
            1,
            90,
            isc_dpb_user_name,
            strlen("SYSDBA"),
            "SYSDBA",
            isc_dpb_password,
            strlen("masterkey"),
            "masterkey");

    bufferLength = strlen(logInData);
    if (isc_attach_database(status_vector, strlen(DATABASE_PATH), DATABASE_PATH, &DatabaseHandle,bufferLength, logInData))
    {

        isc_print_status(status_vector);

    }


    return 0;

}
**main.c:**
#include <stdio.h>
#include<string.h>
#include "/Library/Frameworks/Firebird.framework/Versions/A/Headers/ibase.h"

char  Query[] = "SELECT * FROM Folder_Table WHERE ID = 3";
int main(int argc, char *argv[])
{
    SQLOpen();
    Folder_Table *myTable;
    myTable = CreateTable("Folder_Table", ONE_ROW);

}

Answer 1

You have alread made a good start defining your folder_table layout. If that is what your table needs to look like for your data, you have that done. There is no magic here. You simply need to write the structure members out to the table using the SQL INSERT command in the field order you have defined for your table.

Currently, your folder_table function and use of sprintf seems to simply be collecting all your login information into the logInData buffer and then attaching to the database. If you successfully attach, then you need a loop to iterate through all your structure data and use the SQL INSERT command to insert that data into your table. You have no code for that yet.

You can probaly do something simlar for inserting the structure values themselves. You will need to create your SQL insert statements in a similar way. Something that will build a buffer holding something similar to "INSERT INTO tablename (FIELD1, FIELD2, FIELD3, FIELD4) VALUES ('VALUE1', 'VALUE2', 'VALUE3', VALUE4');" The likewise attach to your database and execute that sql command for each structure element.

You can either continue with firebird, or use the C-API provided by sqlite itself. I haven't used firebird, so I don't know how much trouble it will be compared to the supplied SQLite API, but the SQLite C-API is relatively simple and straight forward.

As fo the syntax, simply refer to An Introduction To The SQLite C/C++ Interface to use the SQLite connector, or take a look at Firebird Connectivity and API for firebird connectivity.

Above all, make sure you have defined your database and table layout before picking up a keyboard and trying to code it. Much of the confusion seems to come from the fact that you don't know exactly what you want your table containing your structure data to look like. If your struct Folder_Table is the definition you want, then with your table created, loop through your structure data and write the members out with the insert command on each iteration.

There are numerous examples to help you implement your table after you define what it will look like. SQLite C/C++ Tutorial.

That is probably the best road-map I can give based on where it looks like you are with your code. Work on mapping your structure member into your INSERT statements, then if you get stuck, you can ask a more specific question (e.g. Why do I get an error doing...)