UNICODE - SQL/API - sqlssb

Discussion forum about all things SqlBase or SqlTalk
markjack99

UNICODE - SQL/API - sqlssb

Post by markjack99 » 26 Oct 2010, 19:39

I am converting Team Developer code from 2005.1 to 5.2.

I have a stored procedure which does a simple select statement from my admin database to retreive a date (stored as char). "select LAST_LOGON_DATE FROM MISC_SECURITY ". The code comment says this we done in a stored procedure to eliminate a memory leak.

Here is the C++ (version 6.0) code which works.

LPSTR CStoredProcsApp::SpRetStrNoBinds(LPSTR lpstrConnectString, LPSTR lpstrStoredProcedure)
{
SQLTCUR hSqlHandle;
unsigned char nLength;
unsigned char nFetRet;
static char strData[256];

if (sqlcnc(&hSqlHandle, (unsigned char *) lpstrConnectString, 0) == 0)
{
if (sqlret(hSqlHandle, (unsigned char *) lpstrStoredProcedure, 0) == 0)
{
sqlssb(hSqlHandle, 1, 1, (unsigned char *) strData, 255, 0, &nLength,
&nFetRet);
sqlexe(hSqlHandle);
sqlfet(hSqlHandle);
strData[nLength] = '\0';
}
sqldis(hSqlHandle);
}
return strData;
};



I tried converting all the api functions to the new wide character unicode functions - here is my new code compiled in Microsoft Visual Studio 2008:

LPWSTR CStoredProcsApp::SpRetStrNoBinds(LPWSTR lpstrConnectString, LPWSTR lpstrStoredProcedure)
{
SQLTCUR hSqlHandle;
unsigned short nLength;
unsigned char nFetRet;
static wchar_t strData[256];

if (sqlcncW(&hSqlHandle, (wchar_t *) lpstrConnectString, 0) == 0)
{
if (sqlretW(hSqlHandle, (wchar_t *) lpstrStoredProcedure, 0) == 0)
{
sqlssb2(hSqlHandle, 1, 1, (wchar_t *) strData, 250, 0, &nLength, &nFetRet);
sqlexe(hSqlHandle);
sqlfet(hSqlHandle);
strData[nLength+1] = '\0';
}
sqldis(hSqlHandle);
}
return strData;
};


I am getting garbage in my return. I know it is accessing the database (so my connect string and stored procedure variables are correct) - if i change the value in the database to a 10 character value, I get 5 garbage characters returned. If I change the values in the database to 20 characters, I get 10 garbaage characters returned. Getting 1/2 the correct number of characters makes me think it is an ascii/unicode issue - can anyone see anything wrong with my new Wide character functions that needs changed?

I also changed the parameter and return string in team developer to LPWSTR.

Also, I cannot find any documentation on the sqlssb2 function; I am looking at the sqlbase.h file to define the parameters.

Thanks
Mark

Mike Vandine

Re: UNICODE - SQL/API - sqlssb

Post by Mike Vandine » 27 Oct 2010, 03:08

Hi Mark,

What version of SQLBase are you using? Also the exact build of TD5.2 that you are using.

Have a look at the api.pdf for documentation on the SQLSSB function.

Can you give us a schema of the table that you are accessing? I need to know how the column is defined, etc. I'm positive that this is a Unicode issue.

markjack99

Re: UNICODE - SQL/API - sqlssb

Post by markjack99 » 27 Oct 2010, 20:05

Using Team Developer 5.2-SP2 Build 21679
Have tried both SQLBase 9.0.1 and SQLBase 11.5
I have the SQLBase API document (20-2111-1006) that came with SQLBase 11.5 but it does not have the wide character functions (sqlcncW, sqlssb2, etc.) in it.
I am using the sqlbase.h and sqlwntm.lib files that came with Team Developer 5.2 to build my C code.


Below is the script file which creates the database table - we tried deleting the misc_security table and building it using nvarchar data types – same results.

Code: Select all

CREATE TABLE MISC_SECURITY (
  DEFAULT_PRINT_DIR  CHAR(128)  NOT NULL,
  LAST_LOGON_DATE    CHAR(25)   NOT NULL, 
  ADMIN_IN_USE       CHAR(20));
Here is the TD stored procedure code:

Code: Select all

Call SqlStore(hSqlProcedure, 'SYSADM.GetLog',
      'Procedure: GetLog STATIC
      Parameters
         Receive String: strLog
      Local Variables
      Actions
         On Procedure Execute
            Call SqlImmediate(\'select LAST_LOGON_DATE FROM MISC_SECURITY into :strLog\')
            Call SqlClearImmediate()')
Here is the current C code we are using – it now works to a certain degree – see below.

Code: Select all

LPWSTR CStoredProcsApp::SpRetStrNoBinds(LPWSTR lpstrConnectString, LPWSTR lpstrStoredProcedure)
{
   SQLTCUR hSqlHandle;
   unsigned char nLength;
   unsigned char nFetRet;
   char holder[256];
   char *lp = holder;
   static wchar_t wstrData[256];
   char mbsConnectString[256];
   char mbsStoredProcedure[256];

   wcstombs(mbsConnectString, lpstrConnectString, 256);
   wcstombs(mbsStoredProcedure, lpstrStoredProcedure, 256);

   if (sqlcncA(&hSqlHandle, (unsigned char *) mbsConnectString, 0) == 0)
   {
      if (sqlretA(hSqlHandle, (unsigned char *) mbsStoredProcedure, 0) == 0)
      {
         sqlssb(hSqlHandle, 1, 1, (unsigned char *) lp, 255, 0, &nLength, &nFetRet);
         sqlexe(hSqlHandle);
         sqlfet(hSqlHandle);
         lp[nLength] = '\0';
      }
      sqldis(hSqlHandle);
   }
   mbstowcs(wstrData, lp, 256);
   return wstrData;
};

The value we trying to pull out of the database is encrypted (we use Windows CryptoAPI) – ÞÏpè>œ<“ - the code above pulls this: xxpx>x< (x's are boxes)

If we decrypt the date in the database (i.e. 01222007) or put any other non-special character string in there, the function above pulls it out fine.

We have encrypted the date, written it to the database, pulled it out of the database, and decrypted it properly using Team Developer code.

So it appears the api functions are having difficulty pulling the encrypted values from the database.

Return to “SqlBase General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests