Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Row Expander not working with AS/400 files

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    Row Expander not working with AS/400 files

    I have a new project with 7 read-only grids that uses AS/400 files thru ODBC. Each grid works fine by itself, but 2 of the grids cannot be linked using Row Expander. The problem seems to be that some of the link field names in the grids that will not link have special characters (example:VAVND#) which seem to be causing SQL Select errors. Any idea how I can overcome this? I have tried "Select VAVND# as VAVNDN..." when defining the grid but that doesn't help because the dropdown list of field names when linking only shows the original field names. And the files are not mine so I cannot change the field names within the file.

    #2
    Re: Row Expander not working with AS/400 files

    Not much help but I know other products have problems accessing AS400 fields with special characters like # or $ in the name via ODBC. MS Excel doesn't like them either. I am still relatively new to working in an AS400 environment but evidently using special characters in field names must have been pretty common practice in the past.

    Comment


      #3
      Re: Row Expander not working with AS/400 files

      Yes, it was very common years ago when field names could not be more than 6 characters in length. However, one of my jobs is supporting our old systems on our AS/400. I was hoping that I could use Alpha Five to modernize some of our applications by making them web-based on our intranet. I have some control over what SQL is used to open a grid, but I can't control what Alpha Five is generating within the Row Expander when I link my grids. If I can't figure out a way to handle "invalid" field names, I'm stuck.

      Comment


        #4
        Re: Row Expander not working with AS/400 files

        I found the IBM document below that talks about this problem. It suggests creating an SQL ALIAS over the table. I don't know how to do this but pehaps you do. Our major AS400 software vendor uses # and $ characters extensively in the field names in their files and it is a pain. Crystal Reports seems to handle them without too much problem but Excel has issues. I was hoping to use Alpha Five in a similar manner as you to access data on the AS400 but if it has problems with these special characters it may not work for me either. Good Luck.


        IBM Software Technical Document
        _______________________________________________________________

        Document Information
        Document Information

        Document Number: 19085113
        Functional Area: Client Access
        Subfunctional Area: ODBC
        Sub-Subfunctional Area: Applications
        OS/400 Release: 6.1; 7.1; V4R5M0; V5R1M0; V5R2M0; V5R3M0; V5R4M0; V6R1M0
        Product: CA/400 WIN 95 EXPRESS CLIENT (5769XE100)
        IBM I ACCESS FOR WINDOWS (5770XE100)
        IBM ISERIES CLIENT ACCESS EXP (5722XE100)
        SYSTEM I ACCESS FOR WINDOWS (5761XE100)
        Product Release: N/A



        _______________________________________________________________

        Document Title
        Some ODBC Applications May Not Be Able to Handle Special Characters in Table/Column Names

        Document Description
        When using ODBC, it is important to be aware that operating system identifiers (table names, column names, and so on) allow special characters that ODBC and common implementations of the SQL specification do not. Using identifiers with these characters may lead to unpredictable results in many PC applications. To circumvent the problem either use the recommended SQL naming convention for table and field names or consider creating an SQL ALIAS over the target table and use a standard name for the alias.

        Background

        The ODBC specification (and the SQL specification) states that names must be in the format of " letter[digit | letter | _]...". The only special character allowed is an underscore.

        To improve backwards support for files using older naming conventions (such as System/36 and System/38), the System i� system extended the SQL syntax to include other characters such as percent (%), ampersand (&), period (.), at ('@'), pound ('#'), forward slash ('/'), backslash ('\'), EBCDIC "not" (which has no matching ASCII character), and so on. Note that the DB2� Universal Database for iSeries SQL Reference recommends that these characters not be used:

        "$, @, #, and all other variant characters should not be used in identifiers because the code points used to represent them vary depending on the CCSID of the string in which they are contained. If they are used, unpredictable results may occur. "

        Because many OS/400 files use this older naming convention, Client Access ODBC extended the ODBC specification (through APAR SA50965) to allow three extra characters: $, #, and @. To link to SA50965 immediately, click here .

        Special Characters Used with Microsoft Office

        The special naming allowed on the System i system may cause problems with SQL-based applications such as ODBC. This can be confusing because, even within the Microsoft Office suite of products, different products produce SQL statements in different ways. Some products will handle illegal characters in table/column names while others will not. In particular, if a period is used in a name, then the application must use "delimited identifiers".

        Microsoft Query and Microsoft Access can be used as an example. For this example we will try to access an operating system database file called N.FILE which contains two fields, FIRSTNAME and LASTNAME.

        Microsoft Query will generate the following SQL Statement:

        SELECT N.FIRSTNAME, N.LASTNAME FROM LIBNAME.N.FILE

        MS Access will generate the following SQL Statement:

        SELECT "FIRSTNAME","LASTNAME" FROM "LIBNAME"."N.FILE"

        The statement generated by Microsoft Query will fail with an SQL5001, Column qualifier or table N undefined. (-5001). Microsoft Access handles the illegal character in the file name because Microsoft Access double quotes library names, table names, and column names. Since the names are double quoted (a delimited identifier), the actual characters in the names are not 'looked at'. Microsoft Query does not double quote the names (an ordinary identifier), so the query fails.

        In both cases, native SQL and ODBC are both working as designed. The failure is expected. Microsoft Access succeeds where an application would normally fail because it was 'cautious' enough to double quote all names.

        Tip for Microsoft Excel and Query Users

        To query a table name that contains a period, use the SQL button in the Microsoft Query toolbar to manually type the SQL statement. This allows you to qualify the table name with double quotes. Other options include using other query tools such as Client Access data transfer or creating an SQL alias over the table.

        While this document is written for Client Access Express, the same rules apply to all Client Access products.

        Comment


          #5
          Re: Row Expander not working with AS/400 files

          I have it working now. I created a logical file (like a SQL view) to change the field names without touching the physical file. It has been years since I last created a logical file. I have to maintain and work with our old systems, but everything new I have done in the last 12-15 years has been with Visual Basic and now Alpha Five. Thanks for the help.

          Comment

          Working...
          X