ADO Resync() and autoincrement with PostgreSQL

I was forced to do a project in classic ASP. Of course, I chose Javascript. And PostgreSQL. I use the unicode ODBC driver for PostgreSQL. I connect with a DSN (you need to create a data source in the windows control panel (yuck) ).

And I actually made a small Object Relational Mapper that kinda works very well for now. It’s about 400 lines of code for now. However, that’s another topic.

When I was implementing the save() method for my models I needed to insert some records
and after that to access their fields.

The solution is to use the AddNew() method of a recordset. So:

                var rs = Server.CreateObject("ADODB.Recordset");
                rs.CursorLocation = 3; // adUseClient, without this, resync() does not work!

                rs.Open(quoted_table_name, db._conn, 3, 3, 512);

                rs.AddNew(cols, vals);
                rs.Resync();
                /* Do stuff with the rs fields, like get a default value that the database put for us */

                rs.Close();

I know it sucks seing integers instead of constant names, but I haven’t gotten around to reference the DLL that defines these constants (or whatever, I read something in an ASP book).

The important line is the second one. 3 is adUseClient, which means “Uses a client-side cursor supplied by a local cursor library” whatever that actually means. That was the key after about an hour spent trying to solve this shit! If you do not set the CursorLocation, you’ll get an error when you call Resync() saying that the current provider does not support refreshing underlying variables.

Unfortunately, this does not work with primary key values like autoincrement IDs, which was my first intent.

However, after many more minutes surfing the internet I have finally found the solution!

Well, this works only when the primary key is an autoincrement value. So we use resync() when the
field is not AI, and the absolutePosition method, otherwise.

My code looks like this now:

// Part of the save() method
                var rs = Server.CreateObject("ADODB.Recordset");
                rs.CursorLocation = 3; // adUseClient. This is required
                rs.Open(quoted_table_name, db._conn, 3, 3, 512);

                rs.AddNew(cols, vals);
                rs.Update();
                this._refresh(rs);

                rs.Close();

        /* Refresh values in the object from a recordset
         * Changes argument rs
         * For internal use only
         *
         * - If the model has a serial (autoincrement, AI) field,
         *   that was specified in the creation of the model,
         *   to refresh the row, we use absolutePosition and Requery().
         *   - If there is column named 'id', we assume it to be AI
         *     and to the same as above.
         *
         * - If klass.serial is null, we do not make the assumption about
         *   the 'ID' column
         * 
         */
        _refresh: function(rs) { 

            if(klass.serial || (this.id === null && klass.serial === undefined)) {
                var pos = rs.absolutePosition;
                rs.Requery();
                rs.absolutePosition = pos;
            }
            else {
                rs.Resync();
            }

            var cols = klass.columns;
            for(var i in cols) {
                 this[cols[i].name] = rs.Fields(cols[i].name).value;
            }

        },