This discussion is archived
8 Replies Latest reply: Mar 16, 2013 5:44 PM by rp0428 RSS

varchar with variable length

mrsAngelo Newbie
Currently Being Moderated
Hi

I would store strings with variable length on a table of a database - using Apache Derby.

After that I looked on Internet, I suppose to know that the _(new)_ SQL type "VARCHAR2", allows to do that (to store strings with with not fix size).

In my search anywhere I was not able to know if VARCHAR2 can be used with derby.

My ask is: derby implements the use of VARCHAR2 and, if not, I would like have some advice about another way to solve my problem.

Regards

Angelo
  • 1. Re: varchar with variable length
    gimbal2 Guru
    Currently Being Moderated
    no, you just use varchar for it - guess what the 'var' in the name stands for. varchar2 as far as I know is something Oracle that has something to do with character encoding.
    My ask is: derby implements the use of VARCHAR2 and
    Then why don't you check its manual/website :/ Google: java derby. First hit, 2 seconds of effort. How long did you take to write this post? How long did you have to wait for my less than forthcoming response?

    http://db.apache.org/derby/manuals/index.html
  • 2. Re: varchar with variable length
    mrsAngelo Newbie
    Currently Being Moderated
    Hi gimbal2,

    thank you for answering me...

    I think VARCHAR2 is more than to use unicode.. :

    (http://www.geekinterview.com/question_details/16756)
    "The other difference is that, varchar occupy total length which is define
    to create database while the varchar2 occupy the total length of the
    given string."


    ---------------

    That is not a simple difference.. in my case:
    I need to contemplate the possibility that registrations on a table can contain
    also thousands of characters but it is no predictable how much the
    user (that have to write annotations) shall utilize of them.

    I know the manuals of derby (I also printed them) and I also not believe
    derby supports VARCHAR2, but I can hope it ... :).

    Making treys with databases if a frustrating experience and that is
    the reason I that I posted my ask.

    Regards

    Angelo
  • 3. Re: varchar with variable length
    rp0428 Guru
    Currently Being Moderated
    >
    I would store strings with variable length on a table of a database - using Apache Derby.

    After that I looked on Internet, I suppose to know that the (new) SQL type "VARCHAR2", allows to do that (to store strings with with not fix size).

    In my search anywhere I was not able to know if VARCHAR2 can be used with derby.

    My ask is: derby implements the use of VARCHAR2 and, if not, I would like have some advice about another way to solve my problem.
    >
    What problem are you talking about? The imaginary one in your head?

    You could have answered your own question is you have just looked at the Derby documentation.

    Enter 'java apache derby' into your search engine and the very first link is this one:
    http://db.apache.org/derby/

    Select the 'Documentation' tab and then the Reference Manual
    https://builds.apache.org/job/Derby-docs/lastSuccessfulBuild/artifact/trunk/out/ref/refderby.pdf
    >
    VARCHAR
    Derby does not pad a VARCHAR value whose length is less than specified
    >
    No - Derby does NOT support VARCHAR2

    Yes - Derby supports variable length strings. It does so using VARCHAR.

    Please explain what problem you are talking about.
  • 4. Re: varchar with variable length
    rp0428 Guru
    Currently Being Moderated
    >
    varchar2 as far as I know is something Oracle that has something to do with character encoding.
    >
    No - it has nothing to do with character encoding. The max for SQL is 4000 bytes. The actual maximum number of characters that can be stored DOES depend on the character encoding and the actual characters. Multi-byte characters can take 4 bytes per character so that would limit the column to 1000 characters.

    For Oracle both VARCHAR and VARCHAR2 are currently synonymous.

    http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements001.htm#i54330
    >
    VARCHAR2 Datatype
    The VARCHAR2 datatype specifies a variable-length character string. When you create a VARCHAR2 column, you supply the maximum number of bytes or characters of data that it can hold. Oracle subsequently stores each value in the column exactly as you specify it, provided the value does not exceed the column's maximum length of the column. If you try to insert a value that exceeds the specified length, then Oracle returns an error.
    . . .
    VARCHAR Datatype
    Do not use the VARCHAR datatype. Use the VARCHAR2 datatype instead. Although the VARCHAR datatype is currently synonymous with VARCHAR2, the VARCHAR datatype is scheduled to be redefined as a separate datatype used for variable-length character strings compared with different comparison semantics.
  • 5. Re: varchar with variable length
    rp0428 Guru
    Currently Being Moderated
    >
    I think VARCHAR2 is more than to use unicode.. :

    (http://www.geekinterview.com/question_details/16756)
    "The other difference is that, varchar occupy total length which is define
    to create database while the varchar2 occupy the total length of the
    given string."
    >
    You need to find a new source of information since both of those statements are dead wrong.

    I suggest you use the OFFICIAL documentation for the DB you are interested in (Derby or Oracle).
  • 6. Re: varchar with variable length
    EJP Guru
    Currently Being Moderated
    And note that that's a general rule. Don't rely on secondary or tertiary sources. As you have just discovered, many of them can contain complete junk. Entire sites are complete drivel from start to finish. Use the standard reference materials provided by the vendor.
  • 7. Re: varchar with variable length
    gimbal2 Guru
    Currently Being Moderated
    rp0428 wrote:
    >
    varchar2 as far as I know is something Oracle that has something to do with character encoding.
    >
    No - it has nothing to do with character encoding. The max for SQL is 4000 bytes. The actual maximum number of characters that can be stored DOES depend on the character encoding and the actual characters. Multi-byte characters can take 4 bytes per character so that would limit the column to 1000 characters.

    For Oracle both VARCHAR and VARCHAR2 are currently synonymous.
    Hm, I never really took the time to learn but I had both the please and displeasure of working with "Oracle developers" (IE people who write piles and piles of stored procedures and write whole satellite control programs inside the DBMS) for a couple of years and they specifically used varchar2 types to work around some sort of problem I never cared to ask what it was. Perhaps some day I'll be curious enough to go look it up :)
  • 8. Re: varchar with variable length
    rp0428 Guru
    Currently Being Moderated
    >
    they specifically used varchar2 types to work around some sort of problem I never cared to ask what it was
    >
    Not enough info to know what you are referring to.

    VARCHAR is not used, or recommended, simply because Oracle itself recommends using VARCHAR2 instead.

    There are both SQL and PL/SQL versions of VARCHAR2. The PL/SQL (code that those folk would have written) version supports lengths up to 32k but the SQL version only supports 4000 bytes. Both are variable length contrasted with CHAR which is fixed length and will be padded with spaces to the full length if a value is shorter than the defined length.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points