DOWNLOAD db to newer version

[Former Member]

Hi all,

when i execute dbunload -c "uid=dba;pwd=sql;dbf=D:\quartz_sidco\DB\qz98.db"

i get below error.

Adaptive Server Anywhere Unload Utility Version 9.0.1.1873

***** SQL error: Unable to start specified database: Server must be upgraded to start database

i am trying to migrate a db created using sqlanwhere 5 or lesser version, to sqlanwhere 9 . it is not working . ialso tries with sqlanywhere 12. no luck

could anyone help me in migrating my database to sqlanywhere 12 version..

[chris_keating · Accepted solution]

You are likely running an engine that is version 10 or later and the dbunload is attempting to start the database on that engine     .

Try adding a database server name as part of the connection parameter as in:

dbunload -c "uid=dba;pwd=sql;eng=UnloadDbSrv;dbf=D:\quartz_sidco\DB\qz98.db"

[Former Member · Accepted solution]

Thank you Chris Keating, it worked.

i was able to unload and create a new db using sqlanywhere 12.

i have a doubt what is the use of DEMO.db found in sqlanywhere12 CE folder.

i could see that my application running on sqlanywhere 9.0 and 5 use similar db.which are configured in ODBC.

i could see that demo.db is launched by dbeng50.exe and dbeng9.exe respectively in sybase 5 and 9.0. so i used dbeng12.exe to launch demodb in sqlanywhere 12.

similar way i configured this demo db(sqlanywhere12) in ODBC. but i am unable to launch my application now . i am getting ODBC state s1003.

what could be the issue?? could you please give your expertise.

[former_member207653 · Accepted solution]

demo.db is just the sample database. It is mentioned in the product manual below:

DocCommentXchange

You can enable ODBC tracing in the ODBC Data Source Administrator and look into the fine generated while reproducing the issue

[chris_keating · Accepted solution]

SQLSTATE S1003 is Program type out of range. It indicates that the application is built using an older ODBC framework (likely ODBC 2.x). The application will need to use ODBC3,x 

[Former Member · Accepted solution]

i was able to launch the application .When i migrated the db there was no data in the db that was causing the issue.

the application launches but at start i get SQLSTATE S1003. Once i close those errors the application launches. But the client db that i migrated has not migrated correctly some data is missing and in between i when i trigger some search option in application i get SQL state 37000(syntax error near desc on line 1).

what could be the problem and how to resolve this..  

Thanks a million for all responses

[former_member207653 · Accepted solution]

Do you know the sql code of your queries? Can you execute the same queries in InteractiveSQL without any issue?
That error message, SQLState 37000, is returned by ODBC 2.x

SQLSTATE Mappings

So, I would follow the suggestion from Chris.

[chris_keating · Accepted solution]

SQLSTATE 37000 appears to confirm that you are running with an older ODBC API version. This syntax error may still occur in ODBC 3.x based applications so it would be helpful:

1) To confirm that the syntax of the statement is in fact valid in v12. You took a huge leap moving from v5 to v12 and there have been significant changes in SQL Anywhere between those versions.

2) if the statement works from DBISQL (for example), it may be a config issue. "desc" is a reserved word and may need to be qualified. In PB for example, you can use DelimitIdentifier='Yes' to delimit the statement and that might help.

NOTE: IIRC PB 6.5.1 is needed for ODBC v3 support. I think you posted previously that you were planning to update from PB6 to a newer PB version.

[Former Member · Accepted solution]

Hi Chris,

for your second point , how to  make desc as qualified. and i dont know how to get the sql code of queries being used.

we are still in process of thinking about migrating to pb12.6.

it would be helpful if you can provide some inputs . thank you

[chris_keating · Accepted solution]

Is this issue occurring in PB6 or PB12.x?

You can look at the Datawindow SQLPreview event or a PB database trace. I have not looked at PB6 in over 10 years so I cannot recall the specifics of the file name and location that is generated but you simply need to add TRACE to the DBMS transaction object parameter.

    sqlca.DBMS = "ODBC"

changes to

    sqlca.DBMS = "TRACE ODBC"

[Former Member · Accepted solution]

Thank you chris,

i could figure out on making reserved word as qualified, i just enabled "Enclose table and Column name in quotes in  POwer builder Database Profile setup. This happened in PB12.XX

[Former Member · Accepted solution]

i was able to migrate the db to Sybase 9.0 version and used it launch my executable and it works fine.

But when i migrated the db to sybase 12.X and used it to launch the application , i get SQLSTATE s1003 ODBC error . (the application is created in PB6.5).

how it is working with Sybase 9.0 but not in sybase 12.X? wont the executables creted with PB6.5 work with SYBASE greater than 9.0.

I took odbc trace and i could see <SQL_ODBC_VER> =03.80.0000. and below is the trace

2c68-1d70

EXIT  SQLBindCol  with return code -1 (SQL_ERROR)

HSTMT               0x02175A38

UWORD                        1

SWORD                        0 <unknown>

PTR                0x058EDCC0

SQLLEN                   101

SQLLEN *            0x058EDB18

DIAG [S1003] [Microsoft][ODBC Driver Manager] Program type out of range (0) .

we are trying to provide a mitigation before migration entire code to PB12.X.

are there any work around to solve this ODBC issue??

[chris_keating · Accepted solution]

This is likely the result of not having the appropriate information in the PBODB65x.ini file

This block is missing from the ini file. There is a similar block but for an older version of SQL Anywhere.

[SQL Anywhere]

PBSyntax='WATCOM50_SYNTAX'

PBDateTime='STANDARD_DATETIME'

PBFunctions='ASA_FUNCTIONS'

PBDefaultValues='autoincrement,current date,current publisher,current time,current timestamp,current user,last user,sqlcode,sqlstate,timestamp,null,user'

PBDefaultCreate='YES'

PBDefaultAlter='YES'

PBDefaultExpressions='YES'

DelimitIdentifier='YES'

PBDateTimeInvalidInSearch='NO'

PBTimeInvalidInSearch='YES'

PBQualifierIsOwner='NO'

PBSpecialDataTypes='WATCOM_SPECIALDATATYPES'

IdentifierQuoteChar='"'

PBSystemOwner='sys,dbo,sa_debug,rs_systabgroup'

PBUseProcOwner='YES'

SQLSrvrTSName='YES'

SQLSrvrTSQuote='YES'

SQLSrvrTSDelimit='YES'

ForeignKeyDeleteRule='Disallow if Dependent Rows Exist (RESTRICT),Delete any Dependent Rows (CASCADE),Set Dependent Columns to NULL (SET NULL)'

TableListType='GLOBAL TEMPORARY'

(These lines may be word wrapped. Ensure that each value=<....> is a single line )

This block may or may not exist but will be missing the uniqueidentifier portion and is likely the reason for the error you are seeing:

[WATCOM_SPECIALDATATYPES]

SpecialDataTypes='tinyint=DBI_TINYINT=-6,uniqueidentifier=DBI_TYPEBLOB=-11'

If that does not help, you may want to take this to a PB specific forum. 

[Former Member · Accepted solution]

Hi Chris,

the entire block was there already along with WATCOM_SPECIALDATATYPES

, i dont know what else to do

[chris_keating · Accepted solution]

The next steps are to more this question to the PowerBuilder SCN. Please note that PB 6.5x is EOL and SA12 shipped after that EOL. You may have some folks state that connection PB 6.5x to SA12 is unsupported - which is technically true - but in theory should work.

I do not have access to PB6.5 software to experiment but my guess is

1) the sections are not correctly added into the PB 6.5 ODBC ini file

-or-

2) that file is not being read by the PB application at the time that the connection is being made

[former_member182948]

I think that you can use the Unload Database wizard.

Please refer to the following manual for the upgrading of the database.

What's New in SQL Anywhere Studio

  Upgrading Software and Databases

Upgrading Adaptive Server Anywhere