Solved

SQLPutData example in  ODBC3.0

Posted on 1998-06-17
10
1,493 Views
Last Modified: 2012-08-13
Does anyone have success in using the example provided
under SQLPutData  command of ODBC3.0 ? I've tried the
example, the return code is OK but I see nothing
updated in my SQL database. I also checked the ODBC
tracing and it indicated the operation is successful but
where does the data go ???. I  also tried various
data type such as HEX, BIN and ASCII but no success.
0
Comment
Question by:trinh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 

Author Comment

by:trinh
ID: 1091525
Edited text of question
0
 
LVL 1

Expert Comment

by:lekshmikr
ID: 1091526
SQLPutData allows an application to send data for a parameter or column to the driver at statement execution time.
 This function can be used to send character or binary data values in parts to a column with a character, binary, or data source-specific data type (for example, parameters of the SQL_LONGVARBINARY or SQL_LONGVARCHAR types).
0
 

Author Comment

by:trinh
ID: 1091527
I am not asking for the definition of SQLPutData. I am asking
if anyone has used the example in the ODBC reference successfully.
By the way, you answer text is exact copy (word for word)
of the SQLPutData explanation summary in the reference.
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 2

Expert Comment

by:connex
ID: 1091528
I'm actually using some slightly modified Sample code
to put image/text fields into a database. It works really
without problems. can you post the part of your source that uses the Putdata?

0
 

Author Comment

by:trinh
ID: 1091529
This is the sample source that uses the PutData statement
void main()
{
      int sID;
      char* Err;
      int Errlen;
      FILE* fp1;
    if(ODBCopenconnection()){
      retcode = SQLPrepare(hstmt,"INSERT INTO SPEECH (ID,AUDIO) VALUES (?,?)", SQL_NTS);
      if (retcode== SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
      {
     SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_ULONG, SQL_INTEGER, 0,0,&sID,0, &cbsID);
       SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_LONGVARBINARY,0,0,(SQLPOINTER) 2,0, &cbbin);
       cbbin = SQL_LEN_DATA_AT_EXEC(0);
       sID = 10;// hardcoded for testing
       retcode = SQLExecute(hstmt);
       while(retcode == SQL_NEED_DATA){
            retcode = SQLParamData(hstmt, &ptoken);
            if (retcode == SQL_NEED_DATA) {
                  fp1 = InitUserData((SQLSMALLINT)ptoken);
                  while(GetUserData(fp1, (SQLSMALLINT)ptoken,Data,&cbData)){
                        retcode = SQLPutData(hstmt,Data,cbData);
                        if (retcode== SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
                              printf("successfull put data into DB\n");
                        else {
                              SQLGetDiagRec(SQL_HANDLE_STMT,hstmt,1,Err,0,0,1024,&Errlen);
                              printf("error code:%s\n",Err);
                        }
                  }

            }
       }
 
      }
   
    ODBCcloseconnection();
      }
      else {
            printf("Something unknown happened\n");
            exit(-1);
      }
}


FILE* InitUserData(SQLSMALLINT Ptoken) {
      FILE* fp;
      if (Ptoken == 2){ // parmameter 2
      printf("open the file\n");
      fp = fopen("audio.bin","rb"); // this is the binary data
      return (fp);
      }
}



int GetUserData(FILE* fp1,SQLSMALLINT Ptoken,char* Data,int* cbD){
    if (Ptoken == 2) {
            memset(Data,0,sizeof(Data));
            *cbD = fread(Data,sizeof(char),MAX_DATA_LEN,fp1);
            if(cbD == 0){
                  fclose(fp1);
                  return (TRUE);
            }
      }
      return (FALSE);
}

0
 
LVL 2

Expert Comment

by:jbiswas
ID: 1091530
This might be totally redundant but just curious..because I have seen this happening when I try to execute DML statements from Access to  SQL Server(using a ODBC DSN). It would not allow me to do any DML against any backend table till you have a primary key on the backend table or atleast an index. So try this..just in case it is not already there. Create a Primary key or index on the backend table and execute SQLPutData.
0
 

Author Comment

by:trinh
ID: 1091531
The index on the backend table was in fact created but
it still doesn't work.
0
 
LVL 2

Expert Comment

by:connex
ID: 1091532
Is the Table replicated? If yes is the size of the data you
try to put > 'max text repl size' (sp_configure)? If yes set it to a bigger value :)
exec sp_configure 'max text repl size',xxxx
reconfigure

oh and btw .. You cannot see TEXT/IMAGE field in Access, unless you fill in an OLE-Object .)

0
 

Author Comment

by:trinh
ID: 1091533
I am not sure if you answer my question since I don't
use Access. I am using SQLServer Database. Beside
the IMAGE field in the table is capable of storing upto
2Giga bytes of information while I am trying to
put in only 1 byte and it doesn't work. Thanks for
the comments but it's totally useless.

0
 

Accepted Solution

by:
zimmy earned 100 total points
ID: 1091534
You should be using UPDATETEXT or WRITETEXT commands, not INSERT,
in order to add data to a text or image field.

First you INSERT the record, then you update the field with UPDATETEXT or WRITETEXT.

See the section headed 'Text and Image Manipulation' in the TSQL
Reference.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Query Task 11 43
how to make geography query faster?  SQL 7 45
SSRS Page Header from Group Data 2 24
Related to SQL Query 5 21
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question