• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • Last Modified:

String Append truncation - why can't I add more characters to this string?

OK, in the code below I have 77 values in an array.  They are all valid and when accessed directly are fine.  The code was designed to add each element of the array to the string and add parenthesis and other appropriate SQL formatting.  Unfortunately at a point in the string (currently the 27th value in the array, but if the values are changed the problem will occurr in a different value, though at the same point in the string) the string will not allow any more info to be appended to it.  I inherited this code and I am simply switching from Sybase to Oracle.  I have no idea why this problem is occurring, and I cannot fathom that it has anything to do with the DB change.  

At the very least the final append should work (ie adding the ")" to the SQL.  The message boxes I added simply to verify that the data was in the array, which it was.

Any help would be appreciated.  If I haven't followed any posting protocols, I apologize, I've never used this before...

Public Function CreateInsertSQL(strTableName As String, ByRef arrPassedArray As Variant) As String
'****************************************************************************
' Creates "Outgoing" INSERT SQL.                                            *
'                                                                           *
' Arguments:  strTableName - SQL Server table be appended too.              *
'             arrPassedArray - Array containing the values and formats of   *
'                              fields being inserted.                       *
'                                                                           *
' Returns: CreateInsertSQL - Returned SQL string.                           *
'                                                                           *
'****************************************************************************
  Dim intI As Integer
   
  CreateInsertSQL = "INSERT INTO " & strTableName & " VALUES ("
 
  '-----------------------------------
  ' We start on 2nd XL column because
  ' the 1st is the include indicator,
  ' which is not stored in warehouse.
  '-----------------------------------
  For intI = 2 To UBound(arrPassedArray, 1)
    '-------------------------------------------
    ' Check type and assign quotes if necessary
    '-------------------------------------------
    If arrPassedArray(intI, const_XLCellFormat) = "@" Or _
        arrPassedArray(intI, const_XLCellFormat) = "General" Or _
        arrPassedArray(intI, const_XLCellFormat) = """ """ Or _
        arrPassedArray(intI, const_XLCellFormat) = "mm/dd/yy" Then
      CreateInsertSQL = CreateInsertSQL & _
          IIf(arrPassedArray(intI, const_XLCellValue) <> 0 And arrPassedArray(intI, const_XLCellValue) <> "" And _
              arrPassedArray(intI, const_XLCellValue) <> " " And Not IsNull(arrPassedArray(intI, const_XLCellValue)), """" & _
              strReplace(arrPassedArray(intI, const_XLCellValue), """", "'", const_XLCellValue) & """", "Null")
    Else
      CreateInsertSQL = CreateInsertSQL & arrPassedArray(intI, const_XLCellValue)
    End If
   
    If intI <> UBound(arrPassedArray, const_XLCellFormat) Then
      CreateInsertSQL = CreateInsertSQL & ","
    End If
   
  Next
 
  ' test area
  'MsgBox arrPassedArray(25, const_XLCellValue)
  'MsgBox arrPassedArray(26, const_XLCellValue)
  'MsgBox arrPassedArray(27, const_XLCellValue)

  CreateInsertSQL = CreateInsertSQL & ")"
 
End Function

 
0
pairoffives
Asked:
pairoffives
1 Solution
 
SweatCommented:
pairoffives,

I'm basically gonna listen to see what anyone else says.  But, I'm also going to make one suggestion which may or may not work.

Do not use the Function name as your working string variable.  Instead, declare a string variable in this function, build your SQL statement with it and then on exiting the function, send the string variable back with the function.

   Dim stxt$

   Substitute stxt for CreateInsertSQL

   and finally:
   CreateInsertSQL = stxt

Maybe?

Sweat

0
 
Arthur_WoodCommented:
just out of curiosity, have you set a BREAKPOINT in this Function, and then checked the actual string each time you append to it?  If so, wha is the LENGTH of the string when you reach the problem?

How long are the individual values in the array, that you are attempting to append to the String?

AW
0
 
tgraffhamCommented:
Just how big is the string getting?  Could you be running up against an upper limit for the String data type in the platform you're running?

Not sure of the limitations of any of the systems you mentioned in regard to the size a single string can be but it's one place I'd check.  Perhaps you need to store your ever-growing value in some sort of large object until it's fully constructed.

Hope this helps,
Tim
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
JohnMcCannCommented:
I have ran the code with a subsituted strReplace function.

I experienced no problems no matter if I assigned directly to the function or to a variable as Sweat suggested.

I think the problem has something to do with strReplace.

My last test had the SQL string with a length of 916901
as returned by len(str).  Surly your SQL statement cannot be that big.

Are youy getting an error message?

Can you post the code for strReplace and the code used to generate test data for the function.



0
 
pairoffivesAuthor Commented:
Thanks for the replies.  I did try that first suggestion from Sweat, but to no avail.  Here's the data I'm throwing into the SQL INSERT statement.  This data is pulled from an Excel Spreadsheet, which has not changed since the App was run successfully against Sybase (developed by another programmer. offsite, who is not with us anymore).  My best guess is that there's something weird going on with my version of VB or some kinda bug that I need to fix.

Anyway here's the test data and an explanation of what I saw...It's coming from an Excel SS so it's tab delimited below...
-1     48     2028     200211     51     COMB     Production Strategy     GLC     North East Ins Co     Auto Liability     X     TR     N/A     0     0     0     ACTIVE     0     0     No     Yes     WWeighted     0     wweighted paid     Benchmark     Benchmark     0     0     0.80     0.80     0     0     1.25     1.25     All yr avg benchmark     All yr avg benchmark

Here's what We're seeing from the function...(I added a watch to the variable CreateInsertSQL and put a breakpoint at the Next and at the end of the function...

"INSERT INTO RES_XLS_RESERVING_KEY_TEMP VALUES (48,2028,200211,"51","COMB","Production Strategy","GLC","North East Ins Co","Auto Liability","X","TR","N/A",0,0,0,"ACTIVE",0,0,"No","Yes","WWeighted",0,"wweighted paid","Benchmark","Bencm"

I went in and altered the data in the test spreadsheet, changing the value of the cells, but it always cuts off at the same point...


0
 
pairoffivesAuthor Commented:
one further note...the actual test data had more data, but it didn't seem worth posting all 77 values, I've checked the array with the message boxes and the array holds each cell's values correctly...
0
 
JohnMcCannCommented:
I've mdofied the code slightly.  Replace your code with this (Save your code first, obviously).

Public Function CreateInsertSQL(strTableName As String, ByRef arrPassedArray As Variant) As String
Dim intI As Integer, strSQL As String, strFormat As String, strValue As String
Dim tmpSQL As String

   Debug.Print "Ubound Format", UBound(arrPassedArray, const_XLCellFormat)
   Debug.Print "Ubound Value", UBound(arrPassedArray, const_XLCellValue)
   
   strSQL = "INSERT INTO " & strTableName & " VALUES ("

   For intI = 2 To UBound(arrPassedArray, 1)
      strFormat = Trim(arrPassedArray(intI, const_XLCellFormat))
      strValue = Trim(arrPassedArray(intI, const_XLCellValue))
      If strFormat = "@" Or strFormat = "General" Or strFormat = """ """ Or strFormat = "mm/dd/yy" Then
         If strValue <> 0 And strValue <> "" And strValue <> " " And Not IsNull(strValue) Then
            tmpSQL = """" & strReplace(strValue, """", "'", const_XLCellValue) & """"
         Else
            tmpSQL = "Null"
         End If
      Else
         tmpSQL = strValue
      End If
   
      If intI <> UBound(arrPassedArray, const_XLCellFormat) Then
         tmpSQL = tmpSQL & ","
      End If
      Debug.Print intI, Len(tmpSQL), Len(strSQL)
      strSQL = strSQL & tmpSQL
   Next intI
 
   strSQL = strSQL & ")"
End Function


Press Ctrl + G (Brings up debug window)
Press F5 (Start)

Watch the debug window and monitor the three values.

The first is the index the second the length of the value and the third the length of the string.

What happens?
0
 
pairoffivesAuthor Commented:
John,

Thanks for you and the other's efforts.  I was able to track down the problem after looking at the string length as your code suggested.  It turns out that the string was not being concatenated at all.  the watch window was simply limited in size that it could display.  This was corresponding with a DB error b/c one of the 77 values in the spreadsheet contained a semi colon.  Since Sybase had no problem with the semicolon, it wasn't accounted for, but when passed into oracle without an escape character, it would blow up.  I've added ";" to the function as a character that needs to be watched out for.  Again, thanks for your help, and hopefully I don't look too foolish...I'm used to C++ and Sybase, VB and Oracle are both new to me.

Thanks again, and I'm going to be visiting this site much more often!

~K
0
 
pairoffivesAuthor Commented:
Thanks agin John
0
 
JohnMcCannCommented:
No problem glad I could be of help.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now