Database problem with Left() taking too much [Resolved] Thx!

imtcb · Post by **imtcb** » Tue Apr 28, 2009 2:14 am

Could someone please tell me why the commented line in the following code would cause the last few database entries to get cleared? The thing I am trying to do is that if a column has date in the name I want to remove the last 13 characters before I add it to the list. The dates look like: "2009-04-27 00:00:00.000" and I only want: "2009-04-27". I know there is something wrong with that line because commented out all of the entries show correctly (well, plus the added text I want removed), whereas if I uncomment it the last 3-4 are either totally or mostly blanked out.

The Update() procedure takes the lists created in this procedure and adds it to a ListIconGadgetPlus table. I did it this way for code portability between several utilities.

Thanks in advance,
tcb

Code: Select all

Procedure Refresh(SelectColumns.s, FromTable.s, WhereCriteria.s="")
  ClearList(Columns()) : ClearList(Rows())
  Query.s = "select "+SelectColumns+" from "+FromTable
  If WhereCriteria<>"" : Query.s = "select "+SelectColumns+" from "+FromTable+" where "+WhereCriteria : EndIf
  If DatabaseQuery(#Database, Query)
    For i=0 To DatabaseColumns(#Database)-1
      Column$ = DatabaseColumnName(#Database, i)
      AddElement(Columns()) : Columns() = Column$
    Next
    While NextDatabaseRow(#Database)
      ForEach Columns()
        If ListIndex(Columns()) = 0 : Row$="" : EndIf
        Select DatabaseColumnType(#Database, ListIndex(Columns()))
          Case #PB_Database_Long : Row$ = Row$+Str(GetDatabaseLong(#Database, ListIndex(Columns())))
          Case #PB_Database_String : Row$ = Row$+GetDatabaseString(#Database, ListIndex(Columns()))
          Case #PB_Database_Float : Row$ = Row$+Str(GetDatabaseFloat(#Database, ListIndex(Columns())))
          Case #PB_Database_Double : Row$ = Row$+Str(GetDatabaseDouble(#Database, ListIndex(Columns())))
          Case #PB_Database_Quad : Row$ = Row$+Str(GetDatabaseQuad(#Database, ListIndex(Columns())))
        EndSelect
;         If FindString(Columns(), "Date", 1) : Row$ = Left(Row$, Len(Row$)-13) : EndIf
        If ListIndex(Columns()) <> ListSize(Columns())-1 : Row$=Row$+Chr(10) : EndIf
      Next
      AddElement(Rows()) : Rows() = Row$
    Wend
    SortList(Rows(), #PB_Sort_Ascending)
    While GetGadgetItemText(#List1, -1, 1) <> ""
      RemoveGadgetColumn(#List1, 1)
    Wend
    ForEach Columns()
      If ListIndex(Columns())=0 : SetGadgetItemText(#List1, -1, Columns(), 0) 
      Else : AddGadgetColumn(#List1, ListIndex(Columns()), Columns(), 5)
      EndIf
      SetListIconHeader(#List1, ListIndex(Columns()), #ListIconHeader_FixedSize | #ListIconHeader_Sort) ; #ListIconHeader_AutoSize | 
    Next
    Update()
    WindowWidth1=0
    ForEach Columns()
      SetGadgetItemAttribute(#List1, -1, #PB_ListIcon_ColumnWidth, #PB_Ignore, ListIndex(Columns()))
      WindowWidth1 = WindowWidth1+GetGadgetItemAttribute(#List1, -1, #PB_ListIcon_ColumnWidth, ListIndex(Columns()))
    Next
    ResizeWindow(#Window1, #PB_Ignore, #PB_Ignore, WindowWidth1+30, #PB_Ignore)
    ResizeGadget(#String1, #PB_Ignore, #PB_Ignore, WindowWidth1+20, #PB_Ignore)
    ResizeGadget(#List1, #PB_Ignore, #PB_Ignore, WindowWidth1+20, #PB_Ignore)
    ResizeGadget(#Button1, WindowWidth1+30-#GadgetSpacing-#GadgetWidth1, #PB_Ignore, #PB_Ignore, #PB_Ignore)
    SetActiveGadget(#String1)
    HideWindow(#Window1, 0)
  EndIf
EndProcedure

AND51 · Post by **AND51** » Tue Apr 28, 2009 2:24 am

It has to be

Code: Select all

Row$=Left(Row$, 13)

The 'length' parameter wants to know the length of the snippet you want to get, not the length of the remaining snippet that you want to throw away.

By the way, if you want a 100%-safe and more flexible solution, you might have a look at Regular Expressions. Using them enables you to find also different kind of date formats.

imtcb · Post by **imtcb** » Tue Apr 28, 2009 2:37 am

AND51 wrote:It has to be
Code: Select all
Row$=Left(Row$, 13)
The 'length' parameter wants to know the length of the snippet you want to get, not the length of the remaining snippet that you want to throw away.

By the way, if you want a 100%-safe and more flexible solution, you might have a look at Regular Expressions. Using them enables you to find also different kind of date formats.

What I want Left() to do is leave me with everything except the last 13 characters. I only want the date portion, not the time portion, even though the whole thing has been concatenated onto the end of the previous columns of the entry.

The problem with trying to use Regular Expressions is that I want this to work on the fly no matter where I have the date columns in the query, so I wouldn't know ahead of time if any given column is a date field or not.

AND51 · Post by **AND51** » Tue Apr 28, 2009 2:57 am

Oh sorry, I did a mistake. The date portion has only a length of 10 characters, so this code works:

Code: Select all

Row$="2009-04-27 00:00:00.000"
Row$=Left(Row$, 10)
Debug Row$

I hope this is what you are searching for? It simply returns the first ten characters and throws the rest away. If this is not your wish, I do misunderstand you.

pdwyer · Post by **pdwyer** » Tue Apr 28, 2009 3:28 am

Put a debug on the value of Len(Row$) in that commented line, I have a feeling it might return a number less than you think and then subtracting 13 leaves you with an empty string when you left() it.

or

Code: Select all

if len(Row$) < 14
    debug Row$
endif

should show you what is in the rows that get cleared

imtcb · Post by **imtcb** » Tue Apr 28, 2009 5:13 am

Thank you both for making me look more closely at the information I was parsing. Yes, in fact, some of the entries had no date in those columns, thus causing the left() to take off of the information from previous columns.
I moved the conditional above the selection and made the selection the failure condition when "date" was not found. This let me manipulate only the date string to get exactly what I needed. Here is the code that I ended with:

Code: Select all

Procedure Refresh(SelectColumns.s, FromTable.s, WhereCriteria.s="")
  ClearList(Columns()) : ClearList(Rows())
  Query.s = "select "+SelectColumns+" from "+FromTable
  If WhereCriteria<>"" : Query.s = "select "+SelectColumns+" from "+FromTable+" where "+WhereCriteria : EndIf
  If DatabaseQuery(#Database, Query)
    For i=0 To DatabaseColumns(#Database)-1
      Column$ = DatabaseColumnName(#Database, i)
      AddElement(Columns()) : Columns() = Column$
    Next
    While NextDatabaseRow(#Database)
      ForEach Columns()
        If ListIndex(Columns()) = 0 : Row$="" : EndIf
        If FindString(Columns(), "Date", 1) : Row$ = Row$+Left(GetDatabaseString(#Database, ListIndex(Columns())), 10)
        Else
          Select DatabaseColumnType(#Database, ListIndex(Columns()))
            Case #PB_Database_Long : Row$ = Row$+Str(GetDatabaseLong(#Database, ListIndex(Columns())))
            Case #PB_Database_String : Row$ = Row$+GetDatabaseString(#Database, ListIndex(Columns()))
            Case #PB_Database_Float : Row$ = Row$+Str(GetDatabaseFloat(#Database, ListIndex(Columns())))
            Case #PB_Database_Double : Row$ = Row$+Str(GetDatabaseDouble(#Database, ListIndex(Columns())))
            Case #PB_Database_Quad : Row$ = Row$+Str(GetDatabaseQuad(#Database, ListIndex(Columns())))
          EndSelect
        EndIf
        If ListIndex(Columns()) <> ListSize(Columns())-1 : Row$=Row$+Chr(10) : EndIf
      Next
      AddElement(Rows()) : Rows() = Row$
    Wend
    SortList(Rows(), #PB_Sort_Ascending)
    While GetGadgetItemText(#List1, -1, 1) <> ""
      RemoveGadgetColumn(#List1, 1)
    Wend
    ForEach Columns()
      If ListIndex(Columns())=0 : SetGadgetItemText(#List1, -1, Columns(), 0) 
      Else : AddGadgetColumn(#List1, ListIndex(Columns()), Columns(), 5)
      EndIf
      SetListIconHeader(#List1, ListIndex(Columns()), #ListIconHeader_FixedSize | #ListIconHeader_Sort) ; #ListIconHeader_AutoSize | 
    Next
    Update()
    WindowWidth1=0
    ForEach Columns()
      SetGadgetItemAttribute(#List1, -1, #PB_ListIcon_ColumnWidth, #PB_Ignore, ListIndex(Columns()))
      WindowWidth1 = WindowWidth1+GetGadgetItemAttribute(#List1, -1, #PB_ListIcon_ColumnWidth, ListIndex(Columns()))
    Next
    ResizeWindow(#Window1, #PB_Ignore, #PB_Ignore, WindowWidth1+30, #PB_Ignore)
    ResizeGadget(#String1, #PB_Ignore, #PB_Ignore, WindowWidth1+20, #PB_Ignore)
    ResizeGadget(#List1, #PB_Ignore, #PB_Ignore, WindowWidth1+20, #PB_Ignore)
    ResizeGadget(#Button1, WindowWidth1+30-#GadgetSpacing-#GadgetWidth1, #PB_Ignore, #PB_Ignore, #PB_Ignore)
    SetActiveGadget(#String1)
    HideWindow(#Window1, 0)
  EndIf
EndProcedure

Thank you both again!
tcb