SQL Find a record example

[Joncombat]

I am creating a simple database.  I am first trying to check to see if a record exists, if exists I will then update it.  if not, I will create a new record.  do simple examples exist to perform these tasks using the SQL toolkit? 

[RobertoBozzolo · Accepted solution]

This problem can be solved by creating a query that looks for the record you want to locate and update / create a new record depending on whether the query actually returned a record. The SQL toolkit can run the queries like "SELECT ... FROM... WHERE ...", after that you can check whether a record is returned with DBNumberOfRecords, getting its value with DBFetchNext.

The sample program <CVISampleFolder>\sql\simple\readtab3 can be a good starting point to elaborate your own strategy for that.

[Joncombat]

actually this only solved half my problem.  I am able to determine if a record exists using the example.  it uses the bind command.  The example that uses the insert/update command uses the map command.  can I create a new record using the bind commands?  are there examples?

[RobertoBozzolo]

I must admit that I normally do not update records in a database, but I see in the documentation for DBPutRecord that it can be used for this task (highlight is mine):

Places the current record in the database. You can use DBPutRecord with new records you create with DBCreateRecord or with existing records you fetched from a SELECT statement.

So as far as I can understand the procedure should be:

1. DBActivateSQL
2. Bind columns to appropriate variables
3. Fetch a record
4. Update the variables bound to record fields
5. DBPutRecord
6. Restart from 3. until end
7. DBDeactivateSQL

[Joncombat]

Here is a code snippet that searches the database for a record.  the record (2) does not exist so I want to add a new record using the provided serial number (2).  I receive error messages.  see below:

//R_SERIAL is set to 2, no record with serial number 2 in database

Fmt(SQL,"SELECT SERIAL_NUM FROM AIO WHERE SERIAL_NUM = '%s'",R_SERIAL);

hstmt = DBActivateSQL (hdbc, SQL);

resCode = DBBindColChar (hstmt, "SERIAL_NUM", 11, Sn, &Sn_stat, "");

resCode = DBBindColInt (hstmt, "ICT", &ict, &ict_stat);

resCode = DBBindColInt (hstmt, "PROGRAMMING", &pg, &pg_stat);

ct = DBNumberOfRecords(hstmt);

if(ct==0) // no record found

{

resCode = DBCreateRecord (hstmt);

strcpy(Sn,R_SERIAL);

ict=0;

pg=1;

resCode = DBPutRecord (hstmt); // returns a -10

}

[Joncombat]

I added an INSERT sql statement after update the variables and it appears to work.  using the put record command gave me an error.  Thank you for your help!

[RobertoBozzolo]

I'm happy you found a way to solve your problem. Just out of curiosity, have you tried using DBCreateRecord before DBPutRecord as stated in the documentation?