DB use

[DannyHust]

Help!

I'm moving some code to C++ Builder and a small part of the code is database stuff and I can't get it done. The code is really simple but since it is database stuff it is virtually impossible to find good example code (at least for me. I spent hours looking thru examples, online, etc.) Everything is forms and controls. I want to do it without that stuff. Like I said, this is just a small part of the application and I really can't have any form
or user interface stuff.

Here is the code I want in C# for a Microsoft environment. I'd like it to be in a Borland C++ Builder environment. Can you help?

Note it does the usual (for me!) stuff. Opens/closes a db, does a standard SQL query (Select) with parameters and sorting, inserts, and updates. All under program control, no user interface. I left out a lot of error checking for clarity.

//Class vars
OleDbConnection m_conn;
int m_IDate[200];
string m_UniName;
float m_avalue[200];
m_uninames[200];
int m_cnt;
int m_dbcnt;

public void OpenIt()
{
string source = @"Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;";
source += @"Data Source=C:\xdata\";
source += "alltables.mdb;";
source += "Mode=ReadWrite;";
m_conn = new OleDbConnection(source);
m_conn.Open();
}

public void CloseIt()
{
m_conn.Close();
}

public void Updateit(int j)
{
int astatus = 2;
string sSQLCommand = "UPDATE ATable SET Status = ? WHERE ";
sSQLCommand += "IDate = ? AND UniName = ? And AValue = ?";

OleDbCommand cmd;
cmd = new OleDbCommand();
cmd.CommandText = sSQLCommand;
cmd.Connection = m_conn;
cmd.Parameters.Clear();
cmd.Parameters.Add ( "Status", astatus) ;
cmd.Parameters.Add ( "IDate", m_IDate[j]) ; // int
cmd.Parameters.Add ( "UniName", m_UniName) ; // string
cmd.Parameters.Add ( "AValue", m_avalue[j]) ; // float

cmd.ExecuteNonQuery() ;
}

GetIt(int adate)
{
string selectname = "SELECT * FROM ATable Where IDate=?";
selectname += " ORDER BY UniName";

OleDbDataReader aReader;
OleDbCommand cmd;
cmd = new OleDbCommand();

cmd.CommandText = selectname;
cmd.Connection = m_conn;
cmd.Parameters.Clear();
cmd.Parameters.Add ( "IDate", adate) ;
aReader = cmd.ExecuteReader();

int j, k;
while(aReader.Read())
{
j = aReader.GetOrdinal("IDate"
k = aReader.GetInt32(j);
if (k != adate)
{
// this should not happen
continue;
}
j = aReader.GetOrdinal("UniName"
if (!aReader.IsDBNull(j))
m_uninames[m_cnt] = "";
else
m_uninames[m_cnt] = aReader.GetString(j);
j = aReader.GetOrdinal("AValue"
m_avalue[m_cnt] = aReader.GetFloat(j);
m_cnt++;
}

aReader.Close();
}

public void InsertIt(int adate, string uniname, int status, float avalue)
{
string strInsert = "INSERT INTO ATable ";
strInsert += "(Status, IDate, UniName, AValue) ";
strInsert += "VALUES (?, ?, ?, ?)";
OleDbCommand cmd;
cmd = new OleDbCommand(strInsert , m_conn);
cmd.Parameters.Add ("IDate", adate) ;
cmd.Parameters.Add ("UniName", uniname) ;
cmd.Parameters.Add ("Status", status) ;
cmd.Parameters.Add ("AValue", avalue) ;
cmd.ExecuteNonQuery() ;
m_dbcnt++;
}

 

[cmsamcfe]

Danny,

I too had problems when I started out with Database programming in BCB, but it gets easier once you've seen a few examples.

Try the following link - you should find all the info you need there

http://www.thebits.org/tutorials/dbase.asp

Regards,

Andy

 

[DannyHust]

Nope, I'd seen that site and looked at it before I posted here.

The first tutorial was about creating tables and aliases. My tables will already be created and aliases I hope to be simple, set it up once and it never changes. And the user never sees it.

The second tutorial had lots of forms and components. I have no form so I can't very well drag and drop something on to it.

I liked the idea of using SQL but I would have liked it more if he had used it more exclusively and stayed away from the Borland dependent stuff.

I hope I'm not asking for too much. Just a small app to open, close, select, update, insert with no forms, just the code.

 

[butthead]

I use this from a program I use to load the qif file that I download from my bank. just ignore the form stuff and look at how the table is being accesed. the table will be accessible even without the visuall stuff. when you add a DBEdit to the form you must link it from the properties to the table. the table dosent need the edit, Its the other way around. I would suggest that you start a project that has as its sole visuall component a DBStringGrid. practice the code using the string grid only to see the actions that you are trying to code. visuall feedback.

I would also suggest that you study the methods of the TTable object. open, close, createtable and such are quite straightforward. It will take you a few weeks to get the hang of TTable and the sql will take some more getting used to.

good luck

tomcruz.net

//---------------------------------------------------------------------------
#include <vcl.h>
#include <dir.h>
#include <stdio.h>

#pragma hdrstop

#include &quot;qif.h&quot;
#include &quot;report.h&quot; 
#include &quot;qifveiwform.h&quot;
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma resource &quot;*.dfm&quot;
TForm1 *Form1;

char Qif_FileName [] =&quot;qif.dbf&quot;;
char Main_FileName [] =&quot;bankone.dbf&quot;;


//---------------------------------------------------------------------------
__fastcall TForm1::TForm1(TComponent* Owner)
    : TForm(Owner)
{
    // If the database file is not present, create it.
    if (!FileExists (&quot;bankone.dbf&quot;))
        Table2->CreateTable ();

    Table2->Active = true;
}
//---------------------------------------------------------------------------
void __fastcall TForm1::OpenClick(TObject *Sender)
{
    // Set the start directory
    char *buff1 = new char [256];
    getcwd(buff1, 256);
    OpenDialog->InitialDir = buff1;

    if (OpenDialog->Execute ())
    {
        strcpy (filename, OpenDialog->FileName.c_str ());

        Form1->Caption = filename;

        FILE *file;
        int x = 0;

        if ((file = fopen(filename, &quot;r&quot;)) != NULL)
        {
            if (!FileExists (Qif_FileName))
                Table1->CreateTable ();

            Table1->Active = false;
            Table1->EmptyTable ();
            Table1->Active = true;

            while (!feof(file))
            {
                fgets(buff1, 256, file);
            
                Table1->Insert();

                for (; x < 5; x++)
                {
                    fgets(buff1, 256, file);
                    int z = strlen (buff1);
                    int y = 0;
                    for (; y < z; y++)
                    {
                        buff1 [y] = buff1 [y + 1];
                    }
                    buff1 [y - 2] = NULL;

                    Table1->Fields[x]->AsString = buff1;
                }

                Table1->Post();

                x = 0;

                Table1->Next();
            }

            Form2->Show();
        }
        else
            Application->MessageBox(&quot;File access error&quot;, &quot;FILE ERROR&quot;, MB_OK);
    }

    delete (buff1);
}
//---------------------------------------------------------------------------


void __fastcall TForm1::FormResize(TObject *Sender)
{
    DBGrid1->Height = Form1->Height - 80;
    Form1->Width = 544;
}
//---------------------------------------------------------------------------

void __fastcall TForm1::ExitClick(TObject *Sender)
{
    Application->Terminate ();
}
//---------------------------------------------------------------------------

void __fastcall TForm1::PreviewClick(TObject *Sender)
{
    //ReportForm->Table1->Active = true;
    ReportForm->QuickRep1->Preview ();
    //ReportForm->Table1->Active = false;
}
//---------------------------------------------------------------------------


void __fastcall TForm1::New1Click(TObject *Sender)
{
    Table2->Close ();
    Table2->CreateTable ();
    Table2->Open ();
}
//---------------------------------------------------------------------------

 

[DannyHust]

Thanks to all for the replies.

Let me clarify why using a form is not really feasible unless I want to do major code modifications. I have a User Interface Thread. Based on user input, that thread starts a worker thread. Based on other outside events, the worker thread needs database access. Other outside events determine what database to write to, read from, what kind of queries, etc (the other outside events come from the serial comm port or an IP socket). After doing it's thing, the worker thread exits.

What that means is that the DB access takes place on a thread other than the User Interface thread. It also means that I have no idea how many worker threads will be active and if I do one DB connection per worker thread then I have no idea how many DB connections will be active at once. Also, db connections will go away as the thread finishes and new db connections will start as new worker threads start.

The simple answer is to have one DB connection per worker thread and just start a new DB connection every time there is a new worker thread started. That is what the old code does that I'm trying to upgrade (the old code just writes stuff to flat files, part of the upgrade is to have it go to a database).

So what is the answer? Put 50 DB connection and DB Query objects on the form and assign them to worker threads and keep track of which connection is in use and which is available? Put an array of DB connections and DB Query objects on the form? And that doesn't even take into consideration thread safety.

Maybe just have one DB connection on the User Interface thread shared by all the worker threads. Sounds like a mess to me.

So I'm not a happy camper. Is formless db access in Borland C++ Builder really so difficult?

 

[butthead]

the database access in itself is not difficult.
the difficulties arise in your implementation of the
database. your solution is in TTable you just have
ignore the visual stuff like the TDBEdits and such.
TTable works fine by itself but you will not get anywhere
unless you jump in there with both feet. look at the
examples that you have and try to replace the visual gui inputs and access with straight code.

this is my suggestion for loading the BDE onto another
computer. the BDE needs to be installed on the users
computer to use the program you create. otherwise you will
have to load builder on the client computer or the somewhat
buggy instal that comes with builder. I have had good luck
with it but the file size of the install program is 6.9 meg.
this is my largest hangup with the bde at the moment.

http://www.ibinstall.defined.net/dl_bdeinfo.htm

***************************

in the code above

Table1->Fields[x]->AsString = buff1;

this merely sets the text of this feild to a char string.

then you have to post the change to the table.

Table1->Post ();

in those two lines there was not any gui involved.

TLocateOptions Opts;

        Opts.Clear();
        Opts << loPartialKey;
        Variant locvalues;
        locvalues = Variant(DBGrid1->SelectedField->AsString);

        Table1->Locate(&quot;Group&quot;, locvalues, Opts);

no gui involved

char sql[100] = {"SELECT Groups.\"Group\" "
"FROM \"groups.db\" Groups "
"WHERE KeyMap LIKE '"};

strcat (sql, MapForm->Image1->Hint.c_str ());
strcat (sql, "'"

MapForm->Query1->Close ();
MapForm->Query1->SQL->Clear ();
MapForm->Query1->SQL->Add (sql);
MapForm->Query1->Open ();

no gui involved


ignore the mask edit and replace it with a text char array.

// This loads the date to the Qreport label
        Money->MaskEdit1->GetTextBuf (buf, 256);
        QReport2->QRLabel7->Caption = buf;

        for (int y = 0; y < 7; y++)
        {
            switch (y)
            {
                case 0:
                    strcat (buffer, &quot;select *&quot;);
                    break;
                case 1:
                    strcat (buffer, &quot; from '00022.dbf'&quot;);
                    break;
                case 2:
                    strcat (buffer, &quot; where \(date_ between '&quot;);
                    break;
                case 3:
                    strcat (buffer, buf);
                    break;
                case 4:
                    strcat (buffer, &quot;' and '&quot;);
                    break;
                case 5:
                    strcat (buffer, buf);
                    break;
                case 6:
                    strcat (buffer, &quot;' + 6\)&quot;);
                    break;
            }
        }
        QReport2->Query1->Close();
        QReport2->Query1->SQL->Clear();
        QReport2->Query1->SQL->Add(buffer);
        QReport2->Query1->Open ();