SQL, strcat(), and more segmentation fault
Tags: c, sql, beginners, odbc
Published: 2020-04-02 00:40:43

Also known as: how to screw up a working program with `strcat()`.

I've been working on learning to use Microsoft SQL. The trending/archiving system at my job uses SQL to record machine data (HVAC) so that we can prepare reports for management and our accrediting surveyors.  Learning SQl has been helpful in helping me learn other programming languages because working toward a goal (making my SQL work without opening SQL) seems to motivate me to practice coding beyond just working on a tutorial.

Most of what I've done lately is to find something to do in SQL and then work through how to do that in `Python`, `perl`, `nim`, and `C`.  Yes, I saved `C` for last.

Connecting with SQL databases and engines in the other languages was fairly straight forward, and not too difficult. However, when I got to `C`, things changed drastically.  The only complete description of interfacing UnixODBC (what is needed to connect to SQL) with `C` is from a website called easySoft.com. I'll put a link below if you are interested.

This site, while complete with all one needs to use `C` in order to connect to SQL, left a lot to be desired in clarity.  However, after a couple of days and an evening of frustration, I finally got their basic connection program to work.  

While I was happy to get that far, there was a lot more to do.  The first thing I needed to expand upon was a way to get connection information from the command line, format it correctly, and shove all that into the SQL connection function. Shouldn't be too difficult, right?  I know `C`, right?

Wrong.

I knew the tool I needed was something to concatenate argument strings into the complete connection string. In `Python`, we just use `'+'` as in `str1 + str2`.  No problem.  In `perl`, we have dot notation as in `str1 . str2`.  No biggy.  `Nim`?  Simple, use `str1 & str2`.  `C`? Well....

`C` doesn't have a concatenation operator.  But, it does have a handy-dandy group of string utilities available when one puts this at the top of their program: `#include <string.h>`.  

This allows the programmer to use `strcat()`, a function that appends a source string (it is supposed to be a constant, but that seems fluid) to a destination string pointer then returns the new string to a result string pointer. And, there goes the neighborhood.

Each of the items in the `strcat()` statement have to abide by typing rules (which I am not complaining about, just pointing out it is difficult to remember them all).  C is considered strongly typed and gcc enjoys reminding the programmer at every opportunity.

So the correct format to make `strcat()` work is:

`*result=strcat( destination, source);`

Where `result` and `destination` are definitely pointers; `source` should be a `constant char` array, but I have used a `char` variable.

As part of my frustration, I'm also trying to do this in the middle of a finicky SQL routine. I made progress after I got out of the SQL routine and just ran several mini-programs with just `strcat()` working in order to remove extraneous errors from the compiler.

So, to set up the final script, I had to create an exact copy of the following string that would go into my SQL connect function:

`"DSN=mydsn_name;UID=sa;PWD=password"`

Unless your really interested in going down the rabbit hole of SQL, just note that each of these has meaning to the connection function. A `DSN` is a Data Source Name; a specification of how to connect to an SQL instance (engine I call them).  `UID` is a user name; `sa` being the system administrator (usually this isn't used, but I'm just testing).  `PWD` is the correct password to go with the username.

What I want to do is run it like this:

`$>sqlconnect mydsn_name sa password`

And, hopefully, ODBC will respond with a nice little connect as my reward.  However, to get there, this is how it looks:

   `char *dsn=malloc( sizeof(char) * 100 );`
   `char  dsnstring[100]="DSN=";`
   `char  uidstring[100]="UID=";`
   `char  pwdstring[100]="PWD=";`
         `dsn=strcat(dsnstring,argv[1]);`
         `dsn=strcat(dsn,";");`
         `dsn=strcat(dsn,uidstring);`
         `dsn=strcat(dsn,argv[2]);`
         `dsn=strcat(dsn,";");`
         `dsn=strcat(dsn,pwdstring);`
         `dsn=strcat(dsn,argv[3]);`
  
The `argv[x]` are the command line argument strings.  You get them when you open your `main()` function as follows (and, yes, I screw it up often):

`int main(int argc, char *argv[])`

`argc` is an `int`(eger) variable that will hold the number of command line arguments.  `*argv[]` is a pointer to a number of arrays of `char`(acter) type.  `argv[0]` is the filename that you used to start the command with (in our case `argv[0]="sqlconnect"`.  Therefore, `argv[1]` gets the `DSN` we entered, and you go on from there.

I used `malloc()` to initialize my main variable (`dsn`) to a 100 character array, but empty. Technically, I reserved a memory space assigned to the pointer `dsn`. `#include <stdlib.h>` is needed to make it work. 

`dsnstring`, `uidstring`, and `pwdstring` CANNOT be pointers to strings in the usual sense. I know--a character array is theoretically the same, but `gcc` does know the difference. So, yes, `*dsnstring` is out. I believe it has to do with the destination and result need to have room for appending characters and be the same length, but I haven't tested it; yet. 

`Strcat()` will quietly not tell you anything (except `segmentation fault`) if your destination pointer has no room.

`dsn=strcat(dsnstring,argv[1])` works like this:

Take a copy of what the `dsnstring` is pointing to and append the contents of `argv[1]` to that, then return the new string to the what `dsn` is pointing at (the 100 char memory allocation).  At least, that is what I've deduced it does.  Wash, rinse, and repeat with the next string needing to be appended.

`dsn=strcat(dsn,";")` looks like it should fail, correct?  It doesn't.  Because `strcat()` takes a copy of `dsn`, adds the semicolon and then RETURNS that to the original location, this construct is legal (and tested, but not thoroughly).  Doing it like this, definitely provides cleaner code and a simpler method. Well, simpler for `C` anyway.

I used this method and ran the SQL connection program several times today.  Worked every time.  

This method of using strcat() has many uses outside of this one instance.  This is a good way to get command line arguments processed, building result strings from collections of words, or any time that there are multitudes of things to stick together.  However, I do recommend doing this in isolation without the rest of your program along to muddy the waters. Additionally, be ready to trial and error your findings and build from one working strcat() to the next.  Code with patience, Padawan (and that means me).

[Link to the easySoft site with ODBC and C instructions](https://www.easysoft.com/developer/languages/c/odbc_tutorial.html)