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:

[ 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.