SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Evangelist
    Join Date
    Dec 2004
    Posts
    419
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    question about selecting from a longtext field

    Hi,

    I am running the following simple

    SELECT fldID, fldName, fldDesc FROM tbl_parent WHERE fldID = 1

    The "fldDesc" field is in longtext format.

    I'm using ASP and MySQL Connector/ODBC 3.51 on Windows 2000 to connect to the DB via a web page.

    Is there any way I can modify my SQL statement to automatically return a value of "NULL" if the fldDesc field is empty.

    This sounds silly, but it would be useful to me in the coding of my ASP pages if I could get a set value back for longtext (and it's only a prob. with longtext fields, rather than varchar for example) when that field is empty.

    If I try to do something like this:

    SQL0 = "SELECT fldID, fldName, fldDesc FROM tbl_parent WHERE fldID = "&id
    set RS1 = oConn.Execute(SQL0)
    var_desc = rs1("fldDesc")

    Then I get this error when the field is empty:

    Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
    Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

    However - if the value just said "NULL" I could build a check into the page and not get the error.

    Thanks

    Jim

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you want the field to show NULL when it is empty then why don't you set those values to NULL?

    You could also do:

    Code:
    select
     foo,
     bar,
     qux,
     (case when length fldDSC=0 then NULL else fldDSC end)
     from yourtable
    but technically that is incorrect since the field isn't null (which indicates an unknown value), but is empty or '' which is a value.

  3. #3
    SitePoint Evangelist
    Join Date
    Dec 2004
    Posts
    419
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks daveman. Can I set the default for the field to be NULL unless otherwise populated via an INSERT statement? Apologies for asking a stupid question.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yep, use DEFAULT NULL when declaring the column
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •