1. Richard Bianco
  2. PowerServer Web
  3. Monday, 06 August 2018

DB Connection Issues w/ Mobile deploy w/ Native Microsoft SQL Server 

Summary

Have not been able to deploy as mobile application and go beyond the database connection. Have tried using native and ODBC and have failed numerous times. PB2017 Help contains no indication that connectivity might be different on Mobile/Web deployments so assuming it's done the same way.

Have set up an ODBC Connection in AEM that tests out fine but the Mobile app complains about the dsn keyword not being supported. Have set up native Connection in AEM like my connection on PB2017 but it fails, making AEM unresponsive.

Scoured the PB2017 Help for hints found nothing. Should have known the info would be in the server component help. Would have been nice if there were some hint about what needed be done for mobile deploy but understand why it is that way. 

 

Primary Problem:

  1. Mobile Deploy & Run, at CONNECT statement
    1. Error: "-1 Keyword not supported: 'dsn.'
  2. ODBC & Native Connection same result

Secondary Problem:

  1. Unable to make SQL Native connection in AEM, and "Test Connection" without freezing AEM making it non-responsive except for UI client logic not dependent on back-end.
  2. Able to make ODBC 64 bit connection- and test but on deploy get error shown in primary problem.

Specifics:

  • AEM PowerServer Mobile (PB Edition) 2017 R2 Build 1756.00
  • Microsoft Edge 42.17134.1.0
  • Windows 10 Home 64-bit on Intel i5 8th Gen
  • IIS Manager Version 1803 (OS Build 17134.165)
  • IIS Version 10.0.17134.1
  • PB 2017 Version 2017 R2 Build 1756

Debugging Info & Lessons Learned

Lesson #1:

Server > Resources > Data Source > [ your connection name ] : Change timeout settings while getting things beyond proof of concept or you'll go crazy. If you don't you will not get immediate feedback- and by the time you click trying to unfreeze AEM, your one message will be long gone- before you see it.

The Settings That Work Well

  • Connection Timeout (seconds): 5
  • Command Timeout (seconds): 5

Error #1: 

After making above connection timeout changes I was able to see (for first time) the following error message. NOTE: The database is NOT remote, on the same machine as AEM

Testing Data Source ai_trader_new failed: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - The remote computer refused the network connection.)

 

I've not been able to make Native SQL Server connection via AEM (ever), here are settings which match what I use in Pb2017 so I know they're correct.

I AM able to connect via AEM with ODBC only which I'd be happy with but still cannot make the connection in PB work without the dreaded keyword not supported 'dsn' error.

 

My Mapping

Odd that ODBC Maps to Native SQL maybe it has to be this way no worries. Now my PB connection code I believe ODB-MSS is what I want per online help.

sqlca.DBMS = "ODB-MSS"
sqlca.AutoCommit = False
sqlca.DBParm = "CacheName='ai_trader_native'"
connect using sqlca;

* using this in typical client server mode - pops dialog and I can choose the ODBC dsn but will not run via Mobile app
would you believe in a year I've not had a single success with mobile deploy- frustrating? It sounds like I'm supposed
to use the CasheName in DBParm which I did.

I give up- any ideas or suggestions on what you'd like to see?

Log File Analysis- Saw this and not sure what code 100 is - but verified the license is good so don't think this is what's causing the failure to TEST connection in AEM, nor inability to connect in deployed app. I have MANY instances of this set of messages- is it a failure or is error code 100 expected?

816:1 [18-08-06 14:15:15] [PBLicenseFacade (ClearProxy)] 
816:1 [18-08-06 14:15:15] [PBLicenseFacade (StartCheckLicense)] input para: productType:3 pVersion:9.0
421:1 [18-08-06 14:15:16] [ProcessManagerForPB (StartCheckLicense pBufferOfResult)] {
421:1 [18-08-06 14:15:16] "AllowNoInternetDays" : 5,
421:1 [18-08-06 14:15:16] "CheckLicenseInterval" : 5,
421:1 [18-08-06 14:15:16] "CheckLicenseStartTime" : 1534018517,
421:1 [18-08-06 14:15:16] "CurrentCheckTime" : 1533586517,
421:1 [18-08-06 14:15:16] "EditType" : "Developer",
421:1 [18-08-06 14:15:16] "ErrorCode" : 100,
421:1 [18-08-06 14:15:16] "ErrorMessage" : "null",
421:1 [18-08-06 14:15:16] "Language" : "en",
421:1 [18-08-06 14:15:16] "LicenseExpireRemindFlags" : 0,
421:1 [18-08-06 14:15:16] "LicenseExpireTime" : 1561953599,
421:1 [18-08-06 14:15:16] "LicenseRemainDays" : 329,
421:1 [18-08-06 14:15:16] "LicenseType" : "CloudLicense",
421:1 [18-08-06 14:15:16] "LicenseVer" : 2,
421:1 [18-08-06 14:15:16] "LicenseVerType" : 2,
421:1 [18-08-06 14:15:16] "LoginState" : 1,
421:1 [18-08-06 14:15:16] "PackCommand" : 6,
421:1 [18-08-06 14:15:16] "ProductEditType" : "Developer",
421:1 [18-08-06 14:15:16] "ProductType" : 3,
421:1 [18-08-06 14:15:16] "ProductVersion" : "9.0",
421:1 [18-08-06 14:15:16] "ReqId" : "74FA08CC-12D0-4284-BA0A-5D8643D8A15B",
421:1 [18-08-06 14:15:16] "SwitchFlag" : 0,
421:1 [18-08-06 14:15:16] "UserAccount" : "rich@displacedguy.com"
421:1 [18-08-06 14:15:16] }

 

NEXT STEPS occurring now...

1. Now that I'm seeing a message- wondering if it has to do w/ remote connections. Checking it now; and may be able to answer my own question.

* Found the database allows remote connections so this wasn't the problem, darn!

 

Suggestions For Appeon Management along w/ critique, compliment & ramblings. Big $$$ value in my humble opinion. Skip everyone else- long and wordy

  1. Add to PB2017 help, at minimum a notation that something is different about transaction object when deploying Mobile/Web, and better yet put detailed info or link to the server side help file. It will save experience PB developers (but new PB Server developers) much, much time and headache, especially if they are like myself- confident, cocky, thinking I can figure anything out that is intuitive. Wrong!
  2. Add to PB2017 help - differences and/or quick start for expert PB devs w/ Mobile/Web. Just the important stuff, it would take a tech-writer a day or two max and the benefit- guarantee it would be huge. We have learned over many years all answers are in that help file- but now- all of the sudden they are not. SHOCK, it cannot be- that loyal help file we trusted is no longer what we thought it was. (Note: it's gotten WAY better since SAP/Sybase though, noticed, and thanks) . Appeon has a huge resource in this older talent pool & best leverage them before too late- some of us are growing weary, not wanting much more green paper- sick of the slave culture in corporate world today- the culture of writing spaghetti code & band-aid repairs, and expectations of 80 hours billed as 40 hours. Many of us are considering or have gone "Galt", I'm now on my second "Galt" scenario and with each the risk I'll never return increases. Just not worth it- nor fun anymore. Money doesn't motive, challenges, and writing software to be proud of does. Point is I could be strong asset to Appeon, or could just give up- frustrated w/ corporate world & the way things are. Almost a year ago I've been through these same connectivity issues- then gave up, then few months later rinse/repeat, never having more than .5 day to follow through before slave work needed. Moral: Had help been what I described above, I may have sold a dozen copies of PB2017 to my last client- I may still be working there instead of going 'Galt' because work not rewarding & abusive environment. And had those sales been made, look at all the other things that could have happened as a result. If I had ownership in Appeon, would put tech writers on this ASAP, at minimum link from help file to the web URL for the server info. My $0.02. And I appreciate Appeon much, like PowerSoft, you listen to & appreciate developers- we are loyal animals- treat us w/ respect- pretend we are important- and you have the most loyal evangelists you could have. Heck I'd have written articles of my success on PB blog also by now- if just had that first success a year ago.
  3. Appeon Kudos, love giving this were due, not just rants like this, this site is awesome, and the PB help file is much better than it used to be under SAP/Sybase (losers). You're doing all the right things, if a publicly traded company I would invest in Appeon because I believe in what I see. I would contribute work (for free) to improve the product- guess that's what I'm doing right now- but I mean serous effort like working on the actual PB C++ code if parts of it were open source. Should Appeon want to hire me- I'd work for half my normal PB bill rate for a while out of interest & pride of ownership especially if remote work was possible. Heck I'd work for free for some sort of ownership and/or pay per performance because I believe in the software. I can afford to work a few years without pay & am such a nerd just like doing it. You can drop that bill rate down to one fourth my normal PB bill rate if remote- with complete flexibility of schedule I digress again. Armeen is valuable to Appeon- the few times we've spoken- very impressed this man has vision, brilliance. I see no mistakes (who am I to judge, true) yet- and that makes me happy.
  4. Minor Critique / Observation hurting Appeon - lack of market penetration means lack of help resources. Stack Overflow has zilch on Appeon (generally speaking) and much of the info on YouTube or elsewhere comes from Spanish native speakers- therefore - they are hard to understand and/or speak in their native language. Open source apps are in Spanish language, not as helpful and web based apps are hard to find- ones that use more typical databases like SQL Server or Oracle over ASA, let's face it nobody has it- and it's not that easy to find a free version to install and make work. I would LOVE to be one of the sources for assistance/training for English (first) speakers but cannot do it until I become expert myself. Catch 22. Also, notice huge surge in users in Asia- same problem- cannot read Chinese and don't even bother looking at Chinese projects on Github even though some look very interesting. 

 

 

Attachments (2)
Armeen Mazda Accepted Answer Pending Moderation
0
Votes
Undo

Hi Rich,

All of us at Appeon really appreciate the kudos! 

I have escalated your concerns about the product manuals to our doc team.  They will get back to you.

We are working on expanding PB's market penetration, but we first need more of the new product roadmap delivered before we could target the 800-lb gorilla (Microsoft's C# dev base).

Thank you for taking the time to give us constructive feedback!

Best regards,
Armeen Mazda
CEO, Appeon

Comment
My pleasure Armeen- knew it wouldn't be a waste of time to make the suggestions.



If I could get a Hello World that Connected to SQL Server (while deployed on IIS) I'd be thrilled. Spent almost all of today and made zero progress- demoralizing but reminds me of early days w/ PowerBuilder and that was with the dreaded GPF twenty times a day.



It cannot be that difficult but don't know what I don't know. A simple article that shows how things are set up in PB & PB Server on AEM would be invaluable and when I figure it out- will do something like that for others. And how the CacheName is mapped to transaction as the online help isn't clear enough, or there's a bug I'm experiencing. May try another DB out of desperation hate giving up as have done this at least half a dozen times over last year.
  1. Richard Bianco
  2. Tuesday, 7 August 2018
There are no comments made yet.
  1. Monday, 6 August 2018
  2. PowerServer Web
  3. # 1
Appeon Support Team Accepted Answer Pending Moderation
1
Votes
Undo

Hi Rich,

For MS SQL Server, PowerSerer does not suppot ODBC driver, please use natie interace and set DBMS to 'MSS'.

And please refer to article below to solve the connect to SQL Server issue.
https://support.appeon.com/index.php?/Knowledgebase/Article/View/48/11/cannot-connect-to-the-sql-server

Regards,
ZhaoKai

Comment
There are no comments made yet.
  1. Tuesday, 7 August 2018
  2. PowerServer Web
  3. # 2
Armeen Mazda Accepted Answer Pending Moderation
1
Votes
Undo

A Hello World that connects to SQL Server is not ambitious at all... I suspect there is some configuration issue.

The primary problem you listed in your post is covered in the troubleshooting guide: https://www.appeon.com/support/documents/appeon_online_help/2017/appeon_troubleshooting_guide/ch04s03s01.html

I would recommend to carefully read the server configuration doc doc: https://www.appeon.com/support/documents/appeon_online_help/2017/server_configuration_guide_for_net/index.html

Then following the migration tutorial: https://www.appeon.com/support/documents/appeon_online_help/2017/migration_guidelines/ch06.html

 

 

Comment
I am known to try and figure things out myself. I got that from my Father I think, or so my Mother says. lol.

Overconfidence. Thank you.

I accomplished my Hello World that connects to SQL Express, now I can make a real program! An excuse to stay up late tonight.
  1. Richard Bianco
  2. Tuesday, 7 August 2018
There are no comments made yet.
  1. Tuesday, 7 August 2018
  2. PowerServer Web
  3. # 3
Richard Bianco Accepted Answer Pending Moderation
0
Votes
Undo

SUCCESS !!

Thanks all for your responses. 

There were several problems and your help provided may have lead me to what I found- haven't had a chance. 

 

Challenges w/SQL Server Express & AEM with Native Connection

  • AEM connects using TCP/IP which is disabled by default on SQLEXPRESS for SQL Server 2014. Since this tool is used primarily in business you probably haven't encountered it. The people that will be encountering are developers like myself experimenting with their own software.
  • AEM assumes TCP/IP Port of 1433 but SQLEXPRESS uses dynamic ports in later versions. This is once you figure out how to enable TCP/IP.

Solution

TCP/IP must be enabled, and dynamic port must be turned off to use 1433. I remember doing this years ago but it's not as simple as it used to be because Microsoft decided to remove the SQL Server Configuration Manager from the start menu. 

  • This Stack Overflow link helped me with this sub-challenge of finding the SQL Server Configuration Manager. Simply locate your "msc" file and open it.  https://stackoverflow.com/questions/9844771/sql-server-configuration-manager-not-found
  • Once you open the manager, select protocols for SQLEXPRESS, then right click TCP/IP in the right pane and enable it. 
  • Once you enable TCP/IP then you can go into properties for TCP/IP and under IP Addresses tab you must remove the zero from dynamic port boxes and enter 1433 in each of the port fields for all IP addresses you wish to use to connect to SQLEXPRESS. I enabled 127.0.0.1 and set port to 1433 on that one.

This is what my connection looks like in AEM (now), the important part was using the IP that I enabled with port 1433 I could have enabled TCP/IP v6, or other variants of the localhost ip address, loopback address, etc.

 

Finally, this is what I put in the PowerBuilder code

Minus error handling and all that good stuff. The key is matching data source name from AEM and using that for the CacheName in DBParm. Also note, MSS is different than SQL Server Native in the PB 2017 client server mod.

sqlca = create transaction

sqlca.DBMS = "MSS"
sqlca.LogId = "rbianco3"
sqlca.LogPass = "president_trump_destroys_cnn"
sqlca.ServerName = "127.0.0.1/SQLEXPRESS"
sqlca.DBParm = "CacheName='sqlexpress_aitrader'"

connect using sqlca;

 

No matter if it took me all day- I'm happy as can be- if there's a will, there is a way! Well when I say all day I mean between all my other real work. But these challenges are good learning exercises I should have known about the TCP/IP had that issue long ago.

Thanks all who helped.

Comment
in your aem database setup, you could have used . (period) as the server/host name to indicate to the database driver to use the shared memory connection rather than 127.0.0.1 ( tcp/ip). shared memory is turned on by default for sql server, including express. it is also faster since it doesn't go through the network stack for communications.
  1. mike S
  2. Wednesday, 8 August 2018
There are no comments made yet.
  1. Tuesday, 7 August 2018
  2. PowerServer Web
  3. # 4
Tracy Huang Accepted Answer Pending Moderation
0
Votes
Undo
 

Hi Rich,

Thank you for spending time to help us improve the product help.

Per your suggestion, we are considering to make the following changes to the product help:

In "Task 2: Configure database connection" section of PowerServer Mobile Tutorials: https://www.appeon.com/support/documents/appeon_online_help/2017/appeon_mobile_tutorials/ch02s02.html, add a note to briefly introduce the concept of PowerServer database connection and a link to the detailed description and the configuration for all of the supported drivers (the "Database Connection Setup" chapter of PowerServer Configuration Guide: https://www.appeon.com/support/documents/appeon_online_help/2017/server_configuration_guide_for_net/database_connection_setup.html).

Reason for this change: PowerServer Mobile Tutorials is the quick start guide for PowerServer; it provides step-by-step guidance in installing/configuring/deploying etc. (in a word, trying to cover all of the tasks that can get a PowerServer mobile app up and running), but it didn't explain why users need to do these, so providing links to the other manuals may help users conveniently find answers if they have further questions or problems when they follow this guide, for example, when configuring database connection which is totally different between PB and PowerServer.

In "Data source settings for SQL Server native driver" section in the "Database Connection Setup" chapter of PowerServer Configuration Guide: https://www.appeon.com/support/documents/appeon_online_help/2017/server_configuration_guide_for_net/connection_cache_settings_for_sql_server_native_driver.html, add a link to the following KB article that addresses the SQL Server connection issue:
https://support.appeon.com/index.php?/Knowledgebase/Article/View/48/11/cannot-connect-to-the-sql-server

This will get users directly to the solution when they have difficulties in setting up connection.

We will gradually add links to build connections between the related topics in different user manuals. Hope this would help users easily figure out the complete story when they are searching for a specific topic.

Thank you again for your suggestion!

Best Regards,

Tracy Huang

Tech Writer

Comment
Y'all are pretty awesome. Thank you.
  1. Richard Bianco
  2. Monday, 20 August 2018
There are no comments made yet.
  1. Thursday, 9 August 2018
  2. PowerServer Web
  3. # 5
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.