Problem with SQL error after migration

Discussion forum about all things SqlBase.
sdk69

Problem with SQL error after migration

Post by sdk69 » 30 Jul 2015, 10:31

Hi,

I'm working on a migration from TD 3.1 to 6.2. I used the wizard to migrate the sources, had to fix a few imports and encoding issues and now most of the app works fine, except for SQL errors.

On the old version of the app, for example if the user wanted to change the password, a popup would appear saying "SQL Error n° 32101 : you have to use numbers in the new password." but on the new version it just says "SQL error n°32101", the description is skipped.

I'm new to TD so I have a lot of trouble finding what's the issue but I think that the function in cause is "PrintSqlError" maybe this is deprecated but I can't find anything about it.
The app communicate with a UNIX server which hosts the sql files in which the procedures and "raise error xxxx" calls are, nothing has changed on the server.

I hope I posted in the right place and that my english is understandable.

Thank you very much for your help.

PS : this is sybase that we use for the db

User avatar
Charlie
Canada
Posts: 595
Joined: 07 Mar 2017, 18:52
Location: Fredericton, New Brunswick, Canada

Re: Problem with SQL error after migration

Post by Charlie » 30 Jul 2015, 11:45

G'day,

"PrintSqlError" sounds like a custom function (i.e. not one of the native Team Developer functions that start with either "Sal" or "Sql".)

Where is the function located in your app's SAL outline ?

sdk69

Re: Problem with SQL error after migration

Post by sdk69 » 30 Jul 2015, 13:19

Thank you for your answer.

You are right this was not a native function, I missed that. The function does the following :

Call SalModalDialog( dlgSqlError, hWndMDI, hSql, bDern )

Can you be more specific about what is the "SAL outline" ? Thank you.

My function PrintSqlError is called from all the places that can possibly generate errors at one point. It seems to take the sybase cursor (hSqlgSYBASE) as a parameter with a boolean. Hope this helps.

User avatar
Charlie
Canada
Posts: 595
Joined: 07 Mar 2017, 18:52
Location: Fredericton, New Brunswick, Canada

Re: Problem with SQL error after migration

Post by Charlie » 31 Jul 2015, 12:31

"SAL Outline": Maybe I should have just said "Outline", but I tend to say "SAL Outline" to make sure we both understand I mean the application code (all the code for your application).

From the SQLWindows documentation:
SQLWindows represents the entire application in the outline. Each line in the outline
is called an item. To see the entire outline, click the highest-level item in the explorer
tree and then click the Outline tab on the right. For other items, the Outline tab
presents that part of the outline which corresponds to the selected item.
Now, all that aside.

If the only thing the function does is open a modal dialog, then you need to look at what the dialog is doing (it sounds like all the processing is happening there.)

You should find all sorts of calls to SQL error handling functions, like SqlError(), SqlErrorText(), SqlExtractArgs(), SqlGetError(), etc. It could be something as simple as a data type that needs to change. You will need to figure out if the Sql error functions changed between TD3.1 and TD6.2 (I can't help you there as we use TD5.2)

Good luck !

Jeff Luther
Site Admin
Site Admin
United States of America
Posts: 2364
Joined: 04 Mar 2017, 18:34
Location: Palm Springs, California

Re: Problem with SQL error after migration

Post by Jeff Luther » 03 Aug 2015, 16:38

Let me jump in and add to what Charlie replied to you, sdk69. (One thing to add to his "SAL" reference is that this acronym, pronounced like the name Sal, came to be with the original version of the TD product back in the latter 1980's and means: "SQLWindows Applicatin Language". SQLWindows was the original name for Team Dev.)

One thing that caught my eye when I read your original posting was this:
saying "SQL Error n° 32101 : you have to use numbers in the new password."
and the fact that numbers are needed for a password. This sounds to me much more like an application requirement, not a Sybase requirement. If this is so, then it seems that the code for special-casing a SQL error return of #32101 has been caught somewhere and the TD code itself has defined that error as "you have to use numbers in the new password."

How is this done? Charlie mentioned the TD function SqlExtractArgs(), which is one way to catch a specific error # when SQL error trapping is done. And given that, the "you have..." text is then assigned as the error text. If this no longer works, then perhaps that SQL error-handling wasn't ported to the new TD version?

Here's how and where I'd look to try to track this down in your TD code:
>> Do a search/find in the code for 32101 to see if it is defined. And if it's not in your new TD code, go back to v3.1 and look for it there.

>> Where might 32101 trapping/processing be found? Maybe
* at the Global Declaration/Application Actions section in the outline, in the SAM_SqlError msg.
or
* at some local -- more likely - place in the code where, perhaps, a dlg. box is defined to allow the user to change his/her password. SQL errors are caught at a local-code site using the When SqlError syntax.

A big clue is that the error text is explicit about a password requirement. A 'Change Password' or similarly-named dialog box would be a likely place this local error-handling to be. Searching for the string text you have to use numbers in the new password is another good way to find this code.

For more info. on SQL and errors, check TD's F1 Help and do a search for: SQL error handling
Also, the TD book DEV.PDF has a section called SQL Programming I see, and in there is a topic called "SQL error handling" for a more in-depth discussion.

An interesting question to me is why this code didn't port (if indeed that's the issue) when you upgraded your TD application. And if that code did port, why is 32101 still being trapped and reported but the special-case error text isn't now being defined. Let us know what you find.
Jeff Luther @ PC Design
Palm Springs, California

sdk69

Re: Problem with SQL error after migration

Post by sdk69 » 04 Aug 2015, 13:53

Thank you for your answer.

The apps connects to a UNIX server which hosts different sql file which contains the custom error codes and a description, for example :

Code: Select all

use <basenom>
go

if exists (select 1 from sysobjects where name = 'hab_pss_controlemdpasse' 
and type = 'P')
 drop proc hab_pss_controlemdpasse
go

create proc hab_pss_controlemdpasse
      @cod_util varchar (30),
      @mdpasse  varchar(40)
      
as
begin
declare @indice                 int,
        @mdpasse_1   varchar(40),
        @mdpasse_2   varchar(40),
        @mdpasse_3   varchar(40),
        @mdpasse_4   varchar(40),
        @mdpasse_5   varchar(40),
        @mdpasse_hash varchar(40)
    
    select @indice = 0
    /* verification de la structure du mot de passe*/   
    /* 1- le mot de passe doit comporter au moins un caractère spécial */
    If patindex ('%[^A-Z^0-9]%', @mdpasse) = 0
    begin
       raiserror 32101
      "Attention: le mot de passe doit comporter un caractère spécial"
    end
It is important to note that NOTHING has changed on server side since the migration, it worked perfectly like this with the old app.

On client side you are right about SqlExtractArgs :
when there is an error (for example with the password structure) the function PrintSqlError si called, then a function GethSqlwParamParam, then SqlExtractArgs. I admit that I have trouble understanding how the whole thing work. Don't hesitate to tell me if you need more information of code parts to help me solve this.

Thank you again for your help.

Jeff Luther
Site Admin
Site Admin
United States of America
Posts: 2364
Joined: 04 Mar 2017, 18:34
Location: Palm Springs, California

Re: Problem with SQL error after migration

Post by Jeff Luther » 04 Aug 2015, 17:34

I am not able to debug your code, nor any connectivity issues, with the limits of forum messaging, sdk69. I can only give you some hints and directions as to where to look for yourself.

You wrote:
The apps connects to a UNIX server which hosts different sql file[s] which contains the custom error codes and a description
This sounds like your new TD is using the wrong (or a different) server SQL file than the older TD version. Part of porting to a new TD is to work out the network and connectivity environment. Or perhaps a new SQL file is being used for the new TD but does not have the code routines such as you show.

I have no experience with a UNIX server now how SQL files are managed there. It seems you should be contacting your SYSADM or DBA person to work out such connectivity issues so your new TD is able to use the correct SQL file.
Jeff Luther @ PC Design
Palm Springs, California

sdk69

Re: Problem with SQL error after migration

Post by sdk69 » 05 Aug 2015, 07:50

Thank you for your answer,

I think that TD use the write sql files because all the requests are working on the same pattern but only the requests that can generate custom errors have issues.

During the migration process I had an issue with encoding, I use to have iso for french language and after the migration I had to make some change (replacing deprecated function by new ones) to handle utf8. Do you think that encoding could also be the cause of the problem ?

Is there a way to explore objects like a debugger inside TD ? I only found tutorials for debuging with .net but I can't manage to make it work with my code. I'd like to see what's returned in the SQL object to identify at least if the UNIX server return the right thing (error + description) or if the problem is definitively inside my TD code.

I'm waiting for my dba support and someone from gupta support has just suggested me to download the new update of my TD version. I'll let you know how it goes.

Again, thank you for your time.

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests